This week, in consideration of the many people (like me!) traveling to the US for PyCon, decided to examine the data produced by the International Trade Administration describing how many people have entered the US each month since the year 2000.
The data is in a single Excel spreadsheet, which you can get from here:
/content/files/sites/default/files/2022-02/monthly-arrivals-2000-present.xlsx
I asked you to start by creating two data frames from this Excel file:
- In the smaller data frame (countries_df), there are only two columns. The index contains country names, and the one non-index data column (region) comes from Excel's column B. You'll have to ignore a bunch of blank/empty rows in the Excel spreadsheet.
- In the larger data frame (travel_df), there are many columns, once for each monthly report. Make the index the same as in countries_df, with country names from Excel's column B. The other columns will all be datetime values, with a year and month. (The day doesn’t matter, and the time will always be midnight.)
Then there will be some other cleaning:
- The final columns in the file have the word “Preliminary”; that word should be stripped.
- All column names/headers should be turned into datetime objects. Remove any columns whose names that cannot be turned into datetimes.
- In the data, replace any instances of ' - ' (i.e., a string containing one space, a minus sign, and three spaces with 0.
- In the data, replace NaN with 0.
- Set the dtype of all columns to int.
I then asked you to answer the following questions:
- In the most recent report, which 10 countries had the greatest number of tourists enter the US?
- In the first report, which 10 countries had the greatest number of tourists enter the US?
- Total the number of tourists from each region in the earliest report vs. the latest report. (Yes, you could get this directly from the original Excel spreadsheet, but I want you to calculate this yourself!) Do we see any changes in the last two decades or so?
- Have any countries had more month-to-month declines in tourism to the US than increases?
- Calculate the mean of tourists from each country for each decade. (And yes, the current decade will be listed as December 31st, 2030.)
Let’s get to it!
Create countries_df: The index contains country names, and the one non-index data column (region) comes from Excel's column B. Ignore blank/empty rows.
Before starting anything else, I need to load up Pandas:
import pandas as pdI then wanted to create the smaller data frame based on the Excel spreadsheet, containing just the countries (as the index) and the regions (as the sole column). In order to do that, I can use read_excel, which can either take a filename (as a string) or a URL (also a string):
url = '/content/files/sites/default/files/2022-02/monthly-arrivals-2000-present.xlsx'
countries_df = pd.read_excel(url)At a basic level, the above code will work, creating a data frame. But this data frame contains more information than we need. The problem is that the Excel file contains many different layers and types of information, above and beyond the per-country statistics. To a person, it’s easy to read and navigate this spreadsheet. But if we want to read it into Pandas and analyze the data, we’ll need to specify exactly what we want, and from where.
For starters, since we are only interested in the countries, we can ignore the first 20 lines of the file. We can pass the “skiprows” keyword argument to read_excel, which will do exactly that.
But read_excel, like its cousin read_csv, will always try to name the columns based on the first row it does read. We don’t need those column headers, because in countries_df, we’ll only have the index (with countries) and the region. We can tell read_excel to ignore the header (with header=None), to use the columns at indexes 1 and 2 (with usercols=[1,2]), and to name the two columns (with names=['country', 'region']):
countries_df = pd.read_excel(url,
skiprows=20,
header=None,
usecols=[1,2],
names=['country', 'region'])This works fine, except that we want the “country” column to act as an index. I could run “set_index” on an existing data frame, but I can do that right away within read_excel by passing index_col:
countries_df = pd.read_excel(url,
skiprows=20,
header=None,
usecols=[1,2],
names=['country', 'region'],
index_col=0)Notice that I can pass index_col the numeric index of the column I wish to use. Also note that this number is counted from the columns actually added to the data frame, not in the original spreadsheet. Thus, while “country” is at column index 1 in Excel, it’s at column index 0 in the data frame.
Finally, I want to get rid of any row that has NaN values. We’re dealing with a very small data frame here, and it’s just to be able to associate countries with regions. Any NaN is just going to give us trouble:
countries_df = pd.read_excel(url,
skiprows=20,
header=None,
usecols=[1,2],
names=['country', 'region'],
index_col=0).dropna() This is what the first rows of this data frame look like in my Jupyter notebook:
With this out of the way, we can go onto the bigger cleaning headache.
Create travel_df, with many columns, one for each monthly report. Make the index the same as in countries_df, with country names from Excel's column B. The other columns will all be datetime values, with a year and month.
There’s a lot packed into this. And really, whenever you’re dealing with data from the real world, there will be a lot of cleaning work to do. Sometimes an awful lot of it.
So let’s start by reading the Excel spreadsheet into a new data frame, and then chipping away at the problems with it:
travel_df = pd.read_excel(url)Once again, we have a bunch of rows that we don’t want, with blank space and regional information. Couldn’t I use skip_rows here, as I did with countries_df?
Not really, because skip_rows tells the parser to ignore those rows entirely when reading the Excel file into memory. The thing is, I want the first row, so that I can use it for headers. And skip_rows doesn’t really allow for that.
Instead, I’ll read the data frame into memory and drop the first 19 rows. Ironically, because the first row was used for headers, I don’t need to skip over it, and can just remove those first 19 rows:
travel_df = pd.read_excel(url).drop(range(0,19))Notice that “drop” doesn’t actually modify the data frame, but returns a new one. In this case, we invoke drop on the data frame we got back from read_excel. The result is then assigned to travel_df.
And indeed, I next get rid of four columns whose names aren’t going to be datetime values, and which don’t contain any travel information. I do this by using “df.drop”.
As we see, drop can be used to remove either rows or columns. By default, it removes rows, but if you pass “axis='columns' ” it’ll drop the named column(s) instead:
travel_df = travel_df.drop([1, ' ',
'World \nRegion', 'Notes:'], axis='columns')As you can see, the spaces and newlines in Excel have to be matched exactly in order for this to work. What a pain!
Also notice that df.drop, like so many other Pandas methods, can take either a single string (for one column) or a list of strings (for multiple columns).
Next, I want to rename the first column to be “country”. By default, it has the comically long name of
"International Visitors--
1) Country of Residence
2) 1+ nights in the USA
3) Among qualified visa types"I mean… really?
How can I rename this column? The best method is to use “rename”. We can pass it a dict in which the keys are the old column names and the values are the new column names, as in:
a_df.renane(columns={'a':'b'})Note that you have to pass “columns” as a keyword argument, since “rename” can be used in a few other ways and contexts, also.
The thing is, the old name is pretty ugly. Do I want to write it all out as a dict key? Of course not! We can retrieve its name, and then rename based on it:
travel_df = travel_df.rename(columns={travel_df.columns[0]: 'country'})I created a dict with a single key and value. The key? The first element of df.columns, which returns the columns in the data frame. The value will then be the string “country”, which is exactly what I want.
Like so many other methods in Pandas, “rename” returns a new data frame, which we then assign back to the original variable.
I then want to make this “country” column into the index. I can do that by invoking “set_index”, specifying that we want to use “country”:
travel_df = travel_df.set_index('country')This is all great, except for one little thing: Because the “country” column contained some NaN values, we now have several rows whose index is NaN, and whose values are completely worthless. These are the final three rows, and you could argue that I should just use “drop” to get rid of them.
But I decided to use a slightly more complex system, just to show you how versatile indexes are — they’re very similar to series, and can handle a lot of sophisticated operations.
In particular, I can run “isna” on the index, finding out which values are NaNs:
travel_df.index.isna()Then I can use “loc” to retrieve those rows that don’t have a NaN index.
travel_df = travel_df.loc[~travel_df.index.isna()]Notice my use of ~, the tilde, which flips the logic of every element I got back from travel_df.index.isna(). Meaning that we’ll drop the rows with NaN indexes, and keep the normal ones.
Next, I want to remove the columns whose names start with “Unnamed”. Why would we have such columns? Who knows? But they exist, and we have to get rid of them.
Remember that we can drop a list of columns by passing a list of strings to “drop”. But how can I get a list of all column names that start with “Unnamed”?
I could iterate over each of the columns, and keep track of those that start with “Unnamed”. And then I could pass that list to “drop”.
That’ll work, but I think it’s more idiomatic to use one of my favorite Python constructs, a list comprehension:
[one_name
for one_name in travel_df.columns
if one_name.startswith("Unnamed") ]If you’ve never seen a list comprehension before, then the above probably looks really weird and daunting. The basic idea is:
- We go through each element of travel_df.columns
- Does it start with “Unnamed”?
- If so, then return the name.
The result is a list of strings, column names we can use.
At least, that’s the theory. In reality, many of our column names are actually datetime values. Which means that they don’t support the “startswith” method. So we’ll get an error.
We’ll need to change our condition to turn the value into a string and then check what it starts with:
[one_name
for one_name in travel_df.columns
if str(one_name).startswith("Unnamed") ]This works just great! As always, a list comprehension creates a list. And we can pass that list to “drop” as an argument. Together with “axis='columns' ”, and assigning the result back to travel_df, we get rid of the bad columns:
travel_df = travel_df.drop([one_name
for one_name in travel_df.columns
if str(one_name).startswith("Unnamed") ],
axis='columns')The final columns in the file have the word “Preliminary”; that word should be stripped.
There remain a few more things that we have to clean up.
One of them is to remove the word “Preliminary” from some of the more recent columns. I realize that they’re trying to stress the fact that the data isn’t perfectly accurate, but I’m willing to sacrifice some accuracy in order to get more recent data.
Since we’re interested in renaming some columns, I think that we’ll once again want to use “rename.” That’ll mean defining a dictionary based on the column names — with the original names as the keys, and the “Preliminary” - removed versions as the values.
This is a perfect opportunity to use a dictionary comprehension, which creates a dict based on an iterable. It’s similar to a list comprehension, but the expression on the first line has two parts, the key and the value. Here’s what my dict comprehension would look like:
{one_name : one_name.removesuffix('Preliminary').strip()
for one_name in travel_df.columns
if str(one_name).endswith('Preliminary')}Here’s a quick tour through this dict comprehension:
- We iterate over travel_df.columns
- We turn each column name into a string, and check if it ends with “Preliminary”
- If it does, then we produce one key-value pair. The key is the current name, and the value is the name without “Preliminary” or any whitespace at the end.
We can then take this dict, and pass it to the “rename” method:
travel_df = travel_df.rename(columns={one_name :
one_name.removesuffix('Preliminary').strip()
for one_name in travel_df.columns
if str(one_name).endswith('Preliminary')})The result? These columns are strings, but they’re in a format that can be turned into datetime objects.
All column names/headers should be turned into datetime objects. Remove any columns whose names that cannot be turned into datetimes.
Here, we see some other weirdness that can occur when we read data from Excel. In a CSV file, everything is text, and Pandas tries to figure out what data types to use. We can give it hints, or even be explicit, but it’s all a matter of parsing. By contrast, Excel data actually comes tagged with a certain type. And so some of the values come as datetime, but others don’t. Why? I’m guessing it has to do with the pesky humans who construct this Excel document.
However, we’re in luck: All of the column names are now either datetime values or strings that can be turned into datetime values. We can use the Pandas “to_datetime” function, passing it the current column names. It’ll return a list of datetime objects of the same size as its input, leaving the existing datetime objects in place, and converting the strings into datetime:
travel_df.columns = pd.to_datetime(travel_df.columns)In the data, replace any instances of ' - ' (i.e., a string containing one space, a minus sign, and three spaces with 0.
I’m not quite sure why (again, except for those humans making the spreadsheet), but we also have some missing values marked as " - ". That is, it’s a string containing a space, a minus sign, and then three more spaces. I want to replace that value, wherever it exists, with 0.
We’ve so far used “replace” to change the names of our columns. But “replace” is more versatile than that, allowing us to replace every occurrence of a value in the data frame with a different value. Here, we’ll replace our annoying string with 0:
travel_df = travel_df.replace(' - ', 0)We get a new data frame back from invoking “replace”, and assign it back to travel_df.
In the data, replace NaN with 0.
Our data describes the number of people who traveled to the US. In theory, counting people can be done with integers. Why, then, do we have some float values?
Because we have some NaN values. NaN is a float, and the moment that there is one NaN in a column, the entire column needs to be of dtype “float”. In this data set, I’m willing to say that NaN is the same as 0; sure, the semantics are different, but for the purposes of counting, I’m willing to do it:
travel_df = travel_df.fillna(0)The “fillna” method returns a new series, identical to the series on which we called fillna, but with all NaN values replaced by whatever was passed as an argument.
Set the dtype of all columns to int.
At this point, all of our data should contain digits. Which means that we can set the dtypes for all columns to “int”, and get back an all-int data frame:
travel_df = travel_df.astype(int)Notice that I didn’t specify the size of the integer; if you do that, you’ll get back int64 data.
🎉 Finally! We’re now ready to ask some questions of our data. 🎉
In the most recent report, which 10 countries had the greatest number of tourists enter the US?
This seems like a relatively simple question: We know that the right-most column is the most recent data. So we can retrieve it, sort by values, and get the top 10, something like:
travel_df['2023-02-01'].sort_values(ascending=False).head(10)In the above code:
- I first retrieved values for the column ‘2023-02-01’. Notice that Pandas is smart enough to allow us to use a string to describe the column, even though the columns are really datetime objects.
- That returns a Pandas series, on which we run sort_values. The result is a new series, one with the same index and values as before, but in a different order.
- I want to have the sorting happen from highest value to lowest, so I pass “ascending=False”.
- Finally, I run “head(10)” in order to get just the top 10 values
And indeed, I get them:
country
United Kingdom 251750
South Korea 116425
France 114659
Brazil 101447
Germany 90144
Japan 82225
India 80806
Argentina 48524
Australia 46891
Italy 45419
Name: 2023-02-01 00:00:00, dtype: int64But what happens if I run this query again in another month? Or six months? Then the date that I put into the query will no longer be accurate. I know that I’ll still want the right-most column, but the name on that column will have changed.
Remember that in Python, I can always retrieve from the end of a sequence using a negative index. So index -1 is the rightmost (final) item in a sequence. The columns object is subscriptable, meaning that we can retrieve elements with square brackets. So I can get the same result, but in a more generalizable form, with:
travel_df[travel_df.columns[-1]].sort_values(ascending=False).head(10)Remember that these reports are for flights, not for land travel. So it’s not a surprise that Canada and Mexico aren’t listed. But I never knew just how many people from the UK visited the US. And South Korea is second? And Brazil is fourth? And Argentina? Really, this report surprised me.
In the first report, which 10 countries had the greatest number of tourists enter the US?
Now that we’ve retrieved data from the most recent report, let’s get data from the first report in the spreadsheet. Even though that’s unlikely to budge, I still like the idea of retrieving “the first” programmatically, rather than using a string. So I can say:
travel_df[travel_df.columns[0]].sort_values(ascending=False).head(10)As you can see, the query is precisely the same as before, except that I’ve specified index 0 in the columns rather than index -1. And the result?
country
Canada 937669
Japan 354266
United Kingdom 242422
Mexico 234000
Germany 100632
Brazil 69634
France 63624
Argentina 54136
South Korea 53797
Italy 41661
Name: 2000-01-02 00:00:00, dtype: int64A lot of similarities, for sure: Many of the same countries are on the list, including (again, to my surprise) Brazil, Argentina, and South Korea. (I have nothing against these countries! I just didn’t expect them to be on the top-10 list of travelers to the US!)
Notice that the number of Canadians and Mexicans coming to the US at the time was massive, even though neither of those countries appears on the modern list. I’m guessing (and this just a guess) that they appeared on the early list but not on the late one because the methodology was changed. For example, perhaps they stopped counting crossing by land borders at some point — and while there are Mexicans and Canadians who enter the United States by air, most do it on land.
Total the number of tourists from each region in the earliest report vs. the latest report. Do we see any changes in the last two decades or so?
Now I want to calculate the number of tourists per region. Normally, “per region” would be straightforward, because we would be able to run a “groupby” operation on our data frame, grouping by region, and summing all of the tourists from countries in that region.
But we’ve split the data into two different data frames. In one, we have the countries and regions, and in the other, we have the per-country tourism numbers.
We’ll have to join the two together, using the “join” method. This works when we have two data frames with the same index. Each index on the left will be matched with the same one on the right. We’ll thus end up with a large data frame whose rows contain both the columns from the left and the columns from the right:
travel_df[[travel_df.columns[0]]].join(countries_df)If you look carefully at the above, you’ll see that I’m selecting a list of columns, but that this selection is itself in a list. The reason is that “join” is a method that only runs on data frames, not on series. If we retrieve a list of columns, rather than a single column, then we get a data frame back, rather than a series.
With that new data frame resulting from the join, we can now run the “groupby” we discussed earlier, summing up the number of people from each region:
travel_df[[travel_df.columns[0]]].join(countries_df).groupby(
'region').sum()Finally, we can sort the entire thing in ascending order, such that the region with the fewest tourists will be at the top, and the one with the most will be at the bottom:
travel_df[[travel_df.columns[0]]].join(countries_df).groupby('region').sum().sort_values(travel_df.columns[0])Notice that I’m once again using “travel_df.columns[0]” to get the first month’s column header, rather than putting the name explicitly inline.
I can do roughly the same thing to get the most recent month’s regional breakdown:
travel_df[[travel_df.columns[1]]].join(countries_df).groupby('region').sum().sort_values(travel_df.columns[1])Have any countries had more month-to-month declines in tourism to the US than increases?
Next, I wondered how many of these countries have seen more month-to-month declines in their countries’ tourism than increases. How can we find that out?
Normally, I would want to use “pct_change” to calculate whether the values had gone up or down each month. But pct_change compares rows with each other. We want to compare columns, comparing the first month (column index 0) with the second month (column index 1) with the third month (column index 2), and so forth.
Fortunately, “pct_change” – like so many other aggregate methods — takes an optional “axis” argument, which defaults to “rows”. But if we run it on the columns, we’ll see by how much a country’s migration to the US changed from month to month:
travel_df.pct_change(axis='columns') Now I want to find out how often each row has positive values, and how often they have negative values:
((travel_df.pct_change(axis='columns') < 0)This will result in a boolean data frame, one containing only True and False values. I now want to find out whether True is a majority of the values in each row.
I can do this by summing the result of the above query, taking advantage of the fact that True is 1 and False is 0 in Python. The sum of each row tells us how many True values are on each line — meaning, how many times the percentage change from the previous month was negative:
But of course, “sum” also usually works on rows. We can get it to work on columns by passing “axis='columns'”:
((travel_df.pct_change(axis='columns') < 0).sum(axis='columns') We now have, for each country, the number of times it was negative. But what percentage was that? We can just divide by the number of columns in travel_f:
((travel_df.pct_change(axis='columns') < 0).sum(axis='columns') / len(travel_df.columns))Finally, we can sort these values in increasing order, so that we can find those countries in which at least half of the months marked a decline in tourism.
Calculate the mean of tourists from each country for each decade. (And yes, the current decade will be listed as December 31st, 2030.)
Finally, I wanted to know the number of tourists from each country entering the US, on average, for each decade. That is, I want to know, on average, how many people from Afghanistan entered during each of the three decades of dat awe have, followed by every other country.
Normally, this kind of problem is solveable when the index a bunch of datetime values, aka a “time series.” We can then ask Pandas to start with the earliest datetime value, end with the latest datetime value, and divide that interim into three buckets, one for each decade. Then we could do a form of “groupby” known as “resampling,” in which we assign each value into one of these buckets, and then take the per-bucket mean for each country.
But we can’t do it in this case, because the columns are datetime values, not the index. A shame, right?
Ah, but actually, we can do this! Once again, it seems that Pandas supports many row operations on the columns, too. We just need to specify “axis='columns’ ”, and we’ll be set.
To run “resample”, we need to use a number-letter “time code” to specify what period of time we should use. We can resample into 1-year intervals with “1Y”, so it stands to reason that a decade would be “10Y”.
Our final query thus looks like:
travel_df.resample('10Y', axis='columns').mean()We’ll end up getting a mean value for every row in the data frame. You might not think that a decade-long mean isn’t that meaningful or useful, but I see it as a 30,000-foot view of trends. We can see, at a glance, which countries’ tourism numbers have risen or fallen dramatically.
And with that, we’ve finished this week’s analysis! Please do share your thoughts in the comment section. (And I’ll admit that I’m writing part of this when jet-lagged and after two full initial days of PyCon, so there might well be more mistakes than usual!)
Meanwhile, here’s my Jupyter notebook for the week: https://drive.google.com/file/d/1RuTeBjEy77J7UZzJ1IS4eeS5W3SGUSnQ/view?usp=drive_link
Let me know what you think. I’ll be back next week with a new set of Pandas challenges based on the news.
Reuven