I don't normally think of June as a month full of holidays, but then I realized just how many were taking place this month — many of them foreign (quite literally) to me, but obviously important to the people celebrating them.
Yesterday, I mentioned Dragon Boat, Shavuot, Eid al-Adha, and Juneteenth (the most recent official American holiday). But how could I forget that tomorrow (June 21st) is the (northern hemisphere's) Summer Solstice? And of course, there's Midsummer, celebrated in a number of countries. I'm sure that I'm missing no small number of other religious and national festivals also taking place this month.
Data and six questions
In honor of all of these (and other) holidays, I thought that it would be interesting to analyze a database of holidays. I couldn't find an existing database of holidays, and thus decided that we would build our own data frame using the holidays package on PyPI, which lets you retrieve the holidays for any country in any year range. This week, we'll not only get some practice querying a data frame; we'll also get practice creating one based on other data.
Below are my solutions to the six tasks and questions. As usual, a link to the Jupyter notebook that I used is at the bottom of this message.
Create a data frame with four columns (country name, alpha2, date, and holiday name) for all countries, from the years 2010 through 2024. Use the pycountry module (from PyPI) to go through all of the countries in the world, and the holidays module (also from PyPI) to grab all of the holidays from there. The dates should be in a datetime column.
As usual, the first thing that we need to do is grab Pandas – not just the overall library, but also the Series and DataFrame types, which I often like to have around as aliases:
import pandas as pd
from pandas import Series, DataFrame
I already said that you'll want to use the holidays package in order to retrieve holidays. The package allows us to retrieve holidays from a specific country during a specified range of years. For example, to get US holidays in 2024, we pass 'US' (the two-letter code for the United States) and the keyword argument years=2024:
holidays.country_holidays('US', years=2024)That returns a dictionary in which the keys are datetime objects, and the values are strings naming the holiday:
{datetime.date(2024, 1, 1): "New Year's Day", datetime.date(2024, 5, 27): 'Memorial Day', datetime.date(2024, 6, 19): 'Juneteenth National Independence Day', datetime.date(2024, 7, 4): 'Independence Day', datetime.date(2024, 9, 2): 'Labor Day', datetime.date(2024, 11, 11): 'Veterans Day', datetime.date(2024, 11, 28): 'Thanksgiving', datetime.date(2024, 12, 25): 'Christmas Day', datetime.date(2024, 1, 15): 'Martin Luther King Jr. Day', datetime.date(2024, 2, 19): "Washington's Birthday", datetime.date(2024, 10, 14): 'Columbus Day'}If I want all of the US holidays from 2010 through 2024, then instead of passing the integers 2024 to the years keyword argument, I can pass a range object. (Don't forget that range, like so many things in Python, goes "up to and not including" the second argument.) In other words, I could say
holidays.country_holidays('US', years=range(2010, 2025))That's nice, but it raises at least two questions: First, how can I get the holidays for all countries? And second, how can I turn that information into a Pandas data frame?
For the first part, we'll get some help from the pycountry project on PyPI, which lists all country names, along with their two- and three-character abbreviations. We can use this information to loop through all of the countries, grabbing their holidays.
But then what? Even with a dict for each country, how do we turn that into a data frame? There are a few ways to do this, but I decided to turn each holiday into a list containing four elements: The country name, the two-letter abbreviation, the date of the holiday, and the name of the holiday. I can then use list.append to add that new, one-holiday list to a larger list containing all of the holidays.
Once I've created my large list-of-lists, I can then pass that to DataFrame and create a Pandas data frame, with one row for each holiday:
import pycountry
import holidays
all_holidays = []
for one_country in pycountry.countries:
try:
for (holiday_date,
holiday_name) in holidays.country_holidays(
one_country.alpha_2,
years=range(2010, 2025)
).items():
all_holidays.append([one_country.name,
one_country.alpha_2,
holiday_date,
holiday_name])
except NotImplementedError as e:
pass
# print(f'\t{one_country.name} has no holidays in the database')
In the above code, we iterate over each element of pycountry.countries. From the resulting object, we then retrieve one_country.alpha_2, with the two-letter country abbreviation. We then add a new four-element list to the existing all_holidays list.
Notice that I wrap all of this in a try block, because we might get a NotImplementedError exception, indicating that the holidays package doesn't have any holidays for that country. I originally used a print call to indicate that we didn't have information about the country, but I quickly got annoyed and bored from such warnings, and replaced it with a call to pass, which is how we tell Python that yes, we need something in the indented block, but no, we don't really want to do anything.
Once this loop has finished running, I have a list of lists. I can then pass that to DataFrame to create a new data frame. Pandas cannot know what the column names should be, so I'll pass the columns keyword argument, along with a list of strings:
df = (DataFrame(all_holidays,
columns='country alpha_2 date holiday'.split())
)This is great, except for one thing: All of the columns now have a dtype of object, meaning that they're treated as strings. To treat the dates and times as actual datetime objects, we'll call pd.to_datetime on the date column.
Rather than calling pd.to_datetime on the column and re-assigning it outside of our invocation of DataFrame, we can just use assign to set a column, along with a lambda that is invoked on the date column. Assigning to an existing column replaces it with the new one. We can thus finalize creating the data frame with this code:
df = (DataFrame(all_holidays,
columns='country alpha_2 date holiday'.split())
.assign(date=lambda df_: pd.to_datetime(df_['date']))
)The resulting data frame has four columns (of course), and 30,958 rows.
Which countries have holidays in June 2024? Which of this month's holidays, if any, are celebrated in more than one country? Do we see any issues that might result in a mis-count?
My interest in holidays started by noticing that this month has a large number of them. Which countries have at least one holiday in June 2024?
Let's start by finding all of the rows in our data frame with a holiday in June, 2024. One easy way to do this is by setting the date column to be our data frame's index with set_index. With that in place, we can use loc to retrieve only those rows that match our year and month by leaving out the date. That'll provide us with a wildcard for the date:
(
df
.set_index('date')
.loc['2024-06']
)Now that we've removed rows from other months and years, let's count the number of times each country appears. We can do this by retrieving only the country column and then running drop_duplicates on the result:
(
df
.set_index('date')
.loc['2024-06']
['country']
.drop_duplicates()
)We find that 88 different countries have at least one holiday this month.
Then I asked a different question: What holidays are celebrated in more than one country? I'll again selected only rows with holidays in June 2024, then used groupby along with the count method:
(
df
.set_index('date')
.loc['2024-06']
.groupby('holiday')['country'].count()
)We now have a series whose index contains the names of countries with holidays in June, 2024. I kept only those in which the county was greater than 1, using lambda and loc to perform the filtering:
(
df
.set_index('date')
.loc['2024-06']
.groupby('holiday')['country'].count()
.loc[lambda s_: s_ > 1]
)Finally, I sorted the results from highest to lowest:
(
df
.set_index('date')
.loc['2024-06']
.groupby('holiday')['country'].count()
.loc[lambda s_: s_ > 1]
.sort_values(ascending=False)
)Here are the results that I got:
holiday
Eid al-Adha (estimated) 30
Eid al-Adha Holiday (estimated) 18
Arafat Day (estimated) 7
Juneteenth National Independence Day 7
Eid al-Adha 5
Sunday 5
Midsummer Day 4
Independence Day 4
Midsummer Eve 3
Eid-ul-Adha (estimated) 3
Eid al-Adha (observed, estimated) 3
Dragon Boat Festival 2
Eid al-Adha (estimated) (observed) 2
Father's Day 2
King's Birthday 2
National Day 2
Pentecost 2
Saint Peter and Saint Paul 2
Whit Monday 2
Name: country, dtype: int64I asked you whether we see anything that might have led to a miscount. And to my eyes, one holiday sticks out, namely the Muslim festival of Eid al-Adha, which often has the terms "estimated" and "observed" in parentheses after the name. These are all the same holiday, but Pandas sees them as different, and thus counts them separately.
In the next exercise, we'll make a stab at fixing this problem.
Remove parenthetical comments (e.g., "(estimated)") and the word "holiday" from the ends of holiday names, and rerun the count from the previous question.
To unify the count of Eid al-Adha, we can remove any comments in parentheses, as well as any time the word "holiday" appears at the end of a string, so that our count of June, 2024 holidays is more accurate.
But how do we do that? Longtime readers already know the answer: Regular expressions!
Let's start off by again removing rows that aren't from June, 2024:
(
df
.set_index('date')
.loc['2024-06']
)Now, before we perform our groupby operation, let's modify the holiday column such that it no longer has parenthetical comments. We can do this by using str.replace on our column. (New to regular expressions? Take my free, 14-day Regexp Crash Course!)
The regular expression that I use is a bit complex:
'\s*\([\w\s,]+\)\s*'Let's break it down a bit:
- First, we look for zero or more whitespace characters,
\s* - Then we look for a literal left parenthesis,
\( - Then we look for one or more alphanumeric, whitespace, and/or comma characters. We do this by creating a character class,
[\w\s,]and then putting+after it - Then we look for a literal right parenthesis,
\) - Finally, we look for zero or more whitespace characters,
\s*
In order to ensure that Python doesn't get confused between its backslashed characters and the backslashed characters in a regular expression, we use a raw string, aka a string with r before the initial quote. That doubles any backslashes inside of the string, ensuring that they arrive at the regexp engine untouched.
I call str.replace on this regexp, and then ask to replace it with the empty string, namely ''. I then use assign and lambda to assign the result of invoking str.replace back onto the same holiday column from which we took it. While we're technically assigning one series instead of another, we're realistically modifying the strings such that they don't have those parenthetical comments any more:
import re
(
df
.set_index('date')
.loc['2024-06']
.assign(holiday=lambda df_: df_['holiday']
.str.replace(r'\s*\([\w\s,]+\)\s*',
'',
regex=True))
)The above is great, but it's not enough: I also asked you to remove the word "holiday" from the end of any string. We'll use another regular expression for this:
- Remove any whitespace characters at the start of the string,
\s* - Remove the word
holiday - Remove any whitespace characters at the end of the string,
\s* - Anchor the pattern to the end of the string with
$
Note that in order for our regexp to match holiday and Holiday, we'll need to add a regular expression flag, re.IGNORECASE. We grab that from the re module, which comes with Python as part of the standard library, and we pass it as part of the flags keyword argument to str.replace.
Notice that we're running str.replace twice – once on the contents of df_['holiday'], and a second time on the output of the first str.replace. In other words, we're using method chaining on our string replacement, inside of a method-chained call to assign. Not confusing at all, right?
Once we're done with those replacements, we can continue to use our groupby, loc, and sort_values calls from before:
import re
(
df
.set_index('date')
.loc['2024-06']
.assign(holiday=lambda df_: df_['holiday']
.str.replace(r'\s*\([\w\s,]+\)\s*',
'',
regex=True)
.str.replace(r'\s*holiday\s*$',
'',
regex=True,
flags=re.IGNORECASE)
)
.groupby('holiday')['country'].count()
.loc[lambda s_: s_ > 1]
.sort_values(ascending=False)
)This time, the results are a bit more aligned:
holiday
Eid al-Adha 59
Arafat Day 7
Juneteenth National Independence Day 7
Independence Day 5
Sunday 5
Midsummer Day 4
Eid-ul-Adha 3
Midsummer Eve 3
Eid-el-Kabir 3
Eid al Adha 2
Dragon Boat Festival 2
Armed Forces Day 2
Eid ul-Adha 2
King's Birthday 2
Father's Day 2
National Liberation Day 2
National Day 2
Pentecost 2
Saint Peter and Saint Paul 2
Whit Monday 2
Youth Day 2
Name: country, dtype: int64There are still a few variations on Eid al-Adha, but many fewer than before. This is another great example of why getting data written by humans, who often misspell things or are inconsistent, can be problematic. Wherever you can, it's best to get data chosen from a list. But it's also an example of how we need to clean data before analyzing it, and how regular expressions can help.
What countries did not celebrate New Year's Day (i.e., January 1st, 2024) as an official holiday?
People are always surprised to hear that Israel's workweek is Sunday through Thursday, and that neither Christmas nor New Year's Day are national holidays. I thought that it might be interesting to find which other countries don't celebrate New Year's Day (i.e., January 1st) as an official holiday.
I decided to do this in two parts. First, I found all of the countries that did celebrate January 1st this year. I first used set_index to again use the date column as our index. I then used loc to retrieve 2024-01-01, meaning only those rows from January 1st, 2024. (I guess that this could include non-New Year's Day holidays on that day, but I didn't really check.) Then I retrieved just the country column:
new_years_celebrating = (
df
.set_index('date')
.loc['2024-01-01']
['country']
)With this series in place, I was then able to construct a query asking which countries were not included.
First, I used the isin method, along with the negating operator ~, to find which values in the country column do not celebrate New Year's Day. I find isin to be a readable and efficient way to check whether the elements of a column are equal to a number of different values.
After finding which rows contains a country that wasn't in new_years_celebrating, we can then retrieve only the country column and invoke drop_duplicates on it:
(
df
.loc[lambda df_: ~df_['country'].isin(new_years_celebrating)]
['country']
.drop_duplicates()
)The result:
3083 Bangladesh
9079 Ethiopia
12377 India
12787 Iran, Islamic Republic of
13428 Israel
19820 Malaysia
21135 Pakistan
23631 Saudi Arabia
28322 Ukraine
Name: country, dtype: objectI wasn't hugely surprised to see Bangladesh, Iran, Malaysia, Pakistan, and Saudi Arabia on that list. I was somewhat surprised to see Ethiopia and India. But I was rather surprised to see that Ukraine doesn't celebrate it – until I did a bit of research, and found that because of the war with Russia, national holidays in Ukraine have been suspended.
Some holidays are based on religious and national calendars, and thus move around from year to year relative to the Gregorian calendar. How many holidays will be celebrated in 2024 on a different Gregorian day than in 2023?
Israel operates according to the (solar) Gregorian calendar, but most of our holidays take places on the Jewish calendar, which is (similar to the Chinese) combines both the solar and lunar years. The Islamic calendar, by contrast, is only lunar. This means that Jewish and Islamic holidays, as well as other holidays that are set according to non-solar calendars, will be on different Gregorian dates each year.
I'll add that I'm often amused to find that I know when Easter will take place more easily than many of my Christian friends. (It's on the Sunday of Passover, which starts on the 15th of the month of Nissan. unless it's a Jewish leap year, in which case it's one month earlier, just after the holiday of Purim. Really, what's so hard about remembering that?)
I asked you to find which holidays in 2024 were celebrated on a different date in 2024 than they were in 2023, on the assumption that such holidays were likely on non-Gregorian calendars. Of course, that's not always the case; the United States is particularly good (or bad) about moving holidays to the nearest weekend.
To do this, we're going to do something known as a "self-join," in which we join a data frame with itself, or a close approximation of itself. In this case, we'll join a data frame of holidays in 2024 with a data frame of holidays in 2023. We'll use the combination of country name and holiday name in the index, so that the join will work. And then we'll be able to perform some comparisons with that resulting data frame.
We'll start by using loc and lambda to retrieve only those rows in which the year is 2024, using dt.year. We'll then use set_index to set the index to a combination of the country and holiday columns:
(
df
.loc[lambda df_: df_['date'].dt.year == 2024]
.set_index(['country', 'holiday'])
)Now we're going to create a (temporary) data frame that's almost identical to this one, but grabbing only holidays from 2023. That is, we'll look for where the year is 2023, and then set the index to be a two-part multi-index with country and holiday, too.
We then invoke join on our 2024 data frame, passing the 2023 data frame as argument. This basically asks Pandas to find where the index on the left side (2024 data) matches the index on the right side (2023 data), giving us a wide data frame with info from both 2023 and 2024 in one place, and separate columns.
However, there's a problem with doing this: We'll end up with identically named columns. And while a Pandas index (i.e., rows) can have repeated values, the columns cannot. To avoid the ValueError exception when we join, we can tell join to add one suffix to the left-side columns (lsuffix), and another suffix to the right-side columns (rsuffix). I added _2024 and _2023, respectively:
(
df
.loc[lambda df_: df_['date'].dt.year == 2024]
.set_index(['country', 'holiday'])
.join(df
.loc[lambda df_: df_['date'].dt.year == 2023]
.set_index(['country', 'holiday']),
lsuffix='_2024',
rsuffix='_2023'))
)Next, I created a new column, same_date, with a boolean (True/False) value indicating whether the month and day for the holiday were identical in both years. I did this (again) using assign and lambda:
(
df
.loc[lambda df_: df_['date'].dt.year == 2024]
.set_index(['country', 'holiday'])
.join(df
.loc[lambda df_: df_['date'].dt.year == 2023]
.set_index(['country', 'holiday']),
lsuffix='_2024',
rsuffix='_2023')
.assign(same_date=lambda df_:
((df_['date_2024'].dt.month ==
df_['date_2023'].dt.month) &
(df_['date_2024'].dt.day ==
df_['date_2023'].dt.day)))
)With this in place, I used .loc and ~ to find those rows in which same_date was False – in other words, where the dates were different in 2023 and 2024. I ran dropna to get rid of any NaN values, used reset_index to put the inner part of the mult-index (i.e., the country column) back into the data frame as a "regular" column, and then counted how many holidays shifted:
(
df
.loc[lambda df_: df_['date'].dt.year == 2024]
.set_index(['country', 'holiday'])
.join(df
.loc[lambda df_: df_['date'].dt.year == 2023]
.set_index(['country', 'holiday']),
lsuffix='_2024',
rsuffix='_2023')
.assign(same_date=lambda df_:
((df_['date_2024'].dt.month ==
df_['date_2023'].dt.month) &
(df_['date_2024'].dt.day ==
df_['date_2023'].dt.day)))
.loc[lambda df_: ~df_['same_date']]
.dropna()
.reset_index(level=1)
['holiday']
.value_counts()
)Here are the results:
holiday
Sunday 2400
Good Friday 76
Easter Monday 60
New Year Holidays 42
Eid al-Fitr Holiday (estimated) 39
...
Heroes' Day 1
Unity Day 1
Farmers' Day 1
Zimbabwe Heroes' Day 1
Defense Forces Day 1
Name: count, Length: 270, dtype: int64As you can see, the holiday that changes date most often from year to year is... Sunday! Yes, this database included Sundays as a holiday for some countries. If we look at the top 20 elements, we get a more expansive view:
holiday
Sunday 2400
Good Friday 76
Easter Monday 60
New Year Holidays 42
Eid al-Fitr Holiday (estimated) 39
Eid al-Adha (estimated) 32
Eid al-Adha Holiday (estimated) 32
Eid al-Fitr 30
Ascension Day 27
Eid al-Fitr (estimated) 26
Easter Sunday 25
Spring Festival 20
Additional day off by Presidential decree 20
Whit Monday 20
New Year's Day 20
Eid al-Adha 17
Easter 16
Bridge Public Holiday 15
Labor Day 15
Chinese New Year 15
Name: count, dtype: int64Not a small number of holidays shift dates, including (not surprisingly) New Year's Day, as well as a number of Easter-related holidays.
Juneteenth was first made a national US holiday on June 19th, 2021. What holidays, in what countries, were first celebrated on or after that date?
Finally, Juneteenth is a very new holiday. What holidays were first celebrated since then?
The first thing I did was to set the date column to be the index, and then sort according to the index using sort_index.
I then used assign and lambda, along with str.replace, to again remove any parenthetical notes in the holiday names:
(
df
.set_index('date')
.sort_index()
.assign(holiday=lambda df_: df_['holiday']
.str.replace(r'\s*\([\w\s,]+\)\s*', '', regex=True)
)
)With that done, I then used groupby by two columns – country and holiday. This mean that I wanted to perform a calculation for every unique combination of country and holiday. But what calculation did I perform? I used idxmin, which is similar to min, but returns the index at which that value appears, rather than the value itself. And of course, I had set the data frame's index to be the date column.
In other words, I was able to find, for each unique country-holiday combination, the date on which it was first celebrated.
Because we had used date for the index and country and holiday in the groupby, the only column left was alpha_2. Which meant, somewhat weirdly, that the date indexes were in a column named alpha_2. I used loc to find all values that were >= the first Juneteenth, namely 2021-06-19:
(
df
.set_index('date')
.sort_index()
.assign(holiday=lambda df_: df_['holiday']
.str.replace(r'\s*\([\w\s,]+\)\s*', '', regex=True)
)
.groupby(['country', 'holiday']).idxmin()
.loc[lambda df_: df_['alpha_2'] >= '2021-06-19']
)I'm going to show you the first 40 rows, which indicates just how tricky it can be to make such determinations:
country holiday
Albania Public Holiday 2022-03-21
Angola Day off for All Souls' Day 2021-11-01
Argentina National Census Day 2022 2022-05-18
Aruba Monday before Ash Wednesday 2023-02-20
Australia National Day of Mourning for Queen Elizabeth II 2022-09-22
Azerbaijan Day off (substituted from 03/05/2022) 2022-03-07
Day off (substituted from 06/24/2023) 2023-06-27
Day off (substituted from 06/25/2023) 2023-06-30
Day off (substituted from 07/17/2021) 2021-07-19
Day off (substituted from 11/04/2023) 2023-11-10
Day off (substituted from 11/05/2022) 2022-11-07
Victory Day 2021-11-08
Bahamas State Funeral of Queen Elizabeth II 2022-09-19
Barbados 50th Anniversary of CARICOM Holiday 2023-07-31
Belarus Day off (substituted from 03/12/2022) 2022-03-07
Day off (substituted from 04/29/2023) 2023-04-24
Day off (substituted from 05/13/2023) 2023-05-08
Day off (substituted from 05/14/2022) 2022-05-02
Day off (substituted from 11/11/2023) 2023-11-06
Belize Emancipation Day 2021-08-02
Indigenous Peoples' Resistance Day 2021-10-11
Bosnia and Herzegovina Eid al-Fitr; International Labor Day 2022-05-02
Brazil Dia Nacional de Zumbi e da Consciência Negra 2024-11-20
Burkina Faso All Saints' Day; Martyrs' Day 2021-11-01
Eid al-Fitr; Labour Day 2022-05-02
Burundi Eid ul Fitr; Labour Day 2022-05-02
President Nkurunziza Day 2022-06-08
Cambodia Constitution Day; Pchum Ben Day 2022-09-24
Chad Eid al-Fitr; Labour Day 2022-05-02
Chile National Day of Indigenous Peoples 2021-06-21
China Day off (substituted from 01/28/2023) 2023-01-26
Day off (substituted from 01/29/2022) 2022-01-31
Day off (substituted from 01/29/2023) 2023-01-27
Day off (substituted from 01/30/2022) 2022-02-04
Day off (substituted from 02/04/2024) 2024-02-15
Day off (substituted from 02/18/2024) 2024-02-16
Day off (substituted from 04/02/2022) 2022-04-04
Day off (substituted from 04/07/2024) 2024-04-05
Day off (substituted from 04/23/2023) 2023-05-02
Day off (substituted from 04/24/2022) 2022-05-03I mean, it's true that "Day off" was a new holiday in some countries in 2024, but is that really a holiday? We might want or need to clean our data further here. And it seems that Eid al-Fitr was on May 2nd in 2022, which means that the holiday description included a semicolon. I did a bit of trimming, and brought the number of holidays down to 66 by removing mentions of "Elizabeth" and "Charles" (sorry, royal family), "funeral", and the aforementioned "Day off" and semicolon:
(
df
.set_index('date')
.sort_index()
.assign(holiday=lambda df_: df_['holiday']
.str.replace(r'\s*\([\w\s,]+\)\s*', '', regex=True)
)
.groupby(['country', 'holiday']).idxmin()
.loc[lambda df_: df_['alpha_2'] >= '2021-06-19']
.reset_index(level='holiday')
.loc[lambda df_: ~df_['holiday'].str.contains('Day off')]
.loc[lambda df_: ~df_['holiday'].str.contains(';')]
.loc[lambda df_: ~df_['holiday'].str.contains('Elizabeth')]
.loc[lambda df_: ~df_['holiday'].str.contains('Charles')]
.loc[lambda df_: ~df_['holiday'].str.contains('Funeral')]
)Doing all of the above brought it down to 49 holidays established since Juneteenth, which is still more than I expected.
Here's a link to my Jupyter notebook: https://drive.google.com/file/d/1c9ILdx3isLaTzR-wdngBWw9Z8-gXbLfJ/view?usp=sharing
I'll be back next Wednesday with more Pandas puzzles based on current events.
Reuven