This week, we looked at a variety of data sources having to do with Iceland. As I wrote yesterday, I just got back from a vacation there, during which we drove around the country’s ring road.
It was hard not to notice how few people are in Iceland. It wasn’t unusual for us to go half an hour — on their main national highway! — without seeing any other cars, in either direction. Gas stations are all self-serve, stores have a limited number of employees, and parking lots are all automated, using a combination of apps, cameras, and threatening signs to ensure that you pay.
I thus thought it would be fun and interesting to look into some statistical data about Iceland — and along the way, use a variety of Pandas techniques and tools.
By the way, above is an AI-generated illustration of Godafoss, a huge and beautiful waterfall in northern Iceland.
And here’s a photo I took at Godafoss on March 26th:
The waterfall is huge and impressive — but I gotta say, ChatGPT did a pretty great job with its illustration!
Data and six questions
This week, we looked at a few different data sets, all having to do with Iceland’s population and tourism. As always, a link to the Jupyter notebook I used in my solutions is at the bottom of this message.
Create a data frame from Wikipedia's page listing the size of each country and territory (https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_area). Now create a second data frame from Wikipedia's page listing the population of each country and territory (https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations) ). Where does Iceland rank in terms of size? In terms of population? In terms of people per square kilometer of land?
The first thing I did was to load Pandas:
import pandas as pdWith that in place, I wanted to create two data frames, each of them from a table on a Wikipedia page. Fortunately, Pandas comes with “read_html”, which returns a list of data frames — one for each table that it encountered on the provided URL. I started by defining the URL and then using “read_html”, retrieving index 1 from that list:
area_url = 'https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_area'
area_df = (pd
.read_html(area_url)[1]
)The thing is, I wanted to get the size of each country as a number, so that I could perform calculations on it, The Wikipedia page displayed the area as both square km and square miles, and also with commas every three digits.
I first decided to remove the square miles. I did this using a combination of “assign”, which lets me add a new column to a data frame, along with “lambda”, for an anonymous function. This function is handed the data frame, which I assign to a parameter “df_”, indicating that it’s temporary.
But what did I want to assign? I called the new column “area_with_commas”, so that I’d remember that it might not have square miles, but still has commas in it. I then used “str.replace” with a regular expression to remove anything in parentheses from the string. Specifically, my regular expression said:
- Look for \(, meaning a literal open parentheses
- Look for \S+, meaning one or more non-whitespace characters
- Look for \), meaning a literal closed parentheses
I used a Python raw string (starting with an “r”) to ensure that the backslashed characters were passed along to the regular expression engine untouched.
Given this, the string “10 (20)” would be turned into the string “10”. I passed the keyword argument “regex=True” to tell “str.replace” that my source string was a regular expression.
But “assign” can take any number of keyword arguments, and they’re processed in order. I thus passed it a second keyword argument, this one assigning to “area” — and here, I removed the commas from the number, and returned a floating-point number using “astype”.
The result of this call to “assign” is a data frame with two new columns, “area_with_commas” and “area”:
area_url = 'https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_area'
area_df = (pd
.read_html(area_url)[1]
.assign(area_with_commas = lambda df_: df_['Total in km2 (mi2)'].str.replace(r'\(\S+\)', r'', regex=True),
area = lambda df_: df_['area_with_commas'].str.replace(',', '').astype(float))
)Next, I used a list of strings inside of square brackets to select only the columns that are of interest to me, namely “area” (which I just created) and “Country / dependency”, the name of the country:
area_url = 'https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_area'
area_df = (pd
.read_html(area_url)[1]
.assign(area_with_commas = lambda df_: df_['Total in km2 (mi2)'].str.replace(r'\(\S+\)', r'', regex=True),
area = lambda df_: df_['area_with_commas'].str.replace(',', '').astype(float))
[['area', 'Country / dependency']]
)Finally, I used “set_index” to make the country name into the index of the data frame:
area_url = 'https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_area'
area_df = (pd
.read_html(area_url)[1]
.assign(area_with_commas = lambda df_: df_['Total in km2 (mi2)'].str.replace(r'\(\S+\)', r'', regex=True),
area = lambda df_: df_['area_with_commas'].str.replace(',', '').astype(float))
[['area', 'Country / dependency']]
.set_index('Country / dependency')
)Why set the index in this way? Because the “join” method only works when the index matches up. By ensuring that both of our data frames (this one and the next one) both have countries in their indexes, we’ll be able to join them more easily.
And indeed, I also asked you to create a data frame based on Wikipedia’s country population page. Here, I had to do something similar. I first used “read_html” to download a list of data frames from the Wikipedia page, and selected index 0:
population_url = 'https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)'
population_df = (pd
.read_html(population_url)[0]
)I then used “assign” to replace the existing “Location” column with its current value, minus any letters in square brackets that served as footnotes. My regular expression looked like this:
- \[, search for a literal open square bracket
- \w+, search for one or more alphanumeric characters
- \], search for a literal close square bracket
In this way, I replaced the existing “Location” column with one that didn’t have any footnotes.
I also assigned to “population”, just copying the existing column with the population numbers from July 1st 2023. I just found this a bit easier than renaming the column:
population_url = 'https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)'
population_df = (pd
.read_html(population_url)[0]
.assign(Location=lambda df_: df_['Location'].str.replace(r'\[\w+\]', '', regex=True),
population=lambda df_: df_['Population (1 July 2023)'])
)Finally, I selected the two columns to which I had assigned (“Location” and “population”), and used “set_index” to set the country to be the index:
population_url = 'https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)'
population_df = (pd
.read_html(population_url)[0]
.assign(Location=lambda df_: df_['Location'].str.replace(r'\[\w+\]', '', regex=True),
population=lambda df_: df_['Population (1 July 2023)'])
[['Location', 'population']]
.set_index('Location')
)With these two data frames in place, I can now answer the questions that were posed.
First, where does Iceland rank in terms of size (i.e., land area)? I’ll start by using “assign” to create a new column. The values in this new column will come from “rank”, a Pandas method that generates a numbered ranking based on the column:
(
area_df
.assign(rank=lambda df_: df_['area'].rank(ascending=False))
)We can then use “loc” with the row selector (“Iceland”) and column selector (“rank”) to get Iceland’s size rank:
(
area_df
.assign(rank=lambda df_: df_['area'].rank(ascending=False))
.loc['Iceland', 'rank']
)I get a rank of 114. This means that of the various countries and regions in this table (which includes the entire planet, “Earth,” so it’s not exactly a precise ranking of countries), we’re at 114 from the top.
We can do something similar with population:
(
population_df
.assign(rank=lambda df_: df_['population'].rank(ascending=False))
.loc['Iceland', 'rank']
)Here, I get a rank of 181.
Finally, how can I rank Iceland’s population per square kilometer? I’ll need to use data from both of the data frames we’ve created. I can combine them, as I mentioned earlier, with “join”, thanks to the fact that both data frames use country names in their indexes:
df = (population_df
.join(area_df)
)Note that this is a “left inner join,” the default way that joins are accomplished. That means the index of population_df will determine which rows are kept; if a country exists in area_df but not in population_df, it’ll be ignored. That’s find for our purposes, but you should know that there are both “right” and “outer” joins that take different approaches.
Next, I calculate the number of people per square km, and use “assign” to add this as a new column:
df = (population_df
.join(area_df)
.assign(people_per_km = lambda df_: df_['population'] / df_['area'])
)Finally, I use “sort_values” to sort by the new column, and I show the top 5 results — meaning, the countries with the smallest number of people per square km:
df = (population_df
.join(area_df)
.assign(people_per_km = lambda df_: df_['population'] / df_['area'])
.sort_values('people_per_km')
.head(5)
)The result:
population area people_per_km
Location
Greenland (Denmark) 56643.0 2166086.0 0.026150
Mongolia 3447157.0 1564116.0 2.203901
Namibia 2604172.0 824292.0 3.159283
Australia 26439112.0 7741220.0 3.415368
Iceland 375319.0 103000.0 3.643874Iceland might not have the smallest number of people per square km, but it’s not too far off! It has about the same density as Australia and Namibia — but those countries are far larger than Iceland. And they still have much larger populations, even if they’re spread out over a greater area.
Next, we'll look at tourism to Iceland. Go to the OECD's data portal (https://stats.oecd.org/?lang=en). Even though it claims that this page is no longer relevant, that's not true! Search for "inbound tourism" in the widget on the left side, and download the CSV file with info about inbound tourism for the entire OECD. Create a data frame from that data in which the year is the index, and the value (i.e., number of tourists) is a column. You'll want only those rows where the variable is INB_ARRIVALS_TOTAL.
How many tourists come to Iceland? From walking through Reykjavik, seeing the people and the oodles of shops aimed at tourists, it would seem like there are quite a lot. But why trust my eyes, when I can check the data, instead?
I downloaded the OECD’s tourism data for countries over the last few years. (Yes, I admit that it’s a bit of a pain.) As is often the case with OECD data, they jam a bunch of different things into the same CSV file, expecting you to filter out the rows having to do with the topic you’re researching.
In this case, I used “read_csv” to read the data into Pandas. However, I wasn’t interested in all of the columns, so I passed “usecols” to select the ones I did want:
tourism_filename = 'TOURISM_INBOUND_03042024145736816.csv'
tourism_df = (pd
.read_csv(tourism_filename,
usecols=['Country', 'Year',
'Value', 'VARIABLE'])
)I then used “loc” to select only those rows where “VARIABLE” was equal to “INB_ARRIVALS_TOTAL”, meaning the number of tourists who entered a country in a given year:
tourism_filename = 'TOURISM_INBOUND_03042024145736816.csv'
tourism_df = (pd
.read_csv(tourism_filename,
usecols=['Country', 'Year',
'Value', 'VARIABLE'])
.loc[lambda df_: df_['VARIABLE'] == 'INB_ARRIVALS_TOTAL']
)I used “set_index” to set the year as the index, and then kept only the “Value” and “Country” columns:
tourism_filename = 'TOURISM_INBOUND_03042024145736816.csv'
tourism_df = (pd
.read_csv(tourism_filename,
usecols=['Country', 'Year',
'Value', 'VARIABLE'])
.loc[lambda df_: df_['VARIABLE'] == 'INB_ARRIVALS_TOTAL']
.set_index('Year')
[['Value', 'Country']]
)This gave me a data frame in which the (non-unique) year values formed the index, with each row showing a country name and the number of tourists who entered that year.
The resulting data frame had 562 rows and the two columns we requested.
Create a line plot showing tourism over the years to Iceland, the UK, and Australia. Interpolate any missing values with the mean of the adjacent values.
I asked you to compare the number of tourists entering Iceland over the years (according to our data) with the UK and Australia. (Why? Because some other countries that I chose had such enormous tourism numbers that the other lines ended up looking fairly flat.)
To do this, we’ll need to change our data frame to have years in the rows (as now), but to have a separate column for each country. That’ll make it far easier to do our plotting.
That change is most easily done with “pivot_table”:
- We want the rows to be the unique values from our index, “Year”
- We want the columns to be the unique values from our “Country” column
- We want the values to be taken from the “Values” column
We can thus say:
(
tourism_df
.pivot_table(index='Year', columns='Country', values='Value')
)With that in place, we can now select columns for the countries that interest us:
(
tourism_df
.pivot_table(index='Year', columns='Country', values='Value')
[['Iceland', 'United Kingdom', 'Australia']]
)However, if we plot the data we have here, we’ll find that there is at least one hole, namely for 2018 in Iceland. I don’t know why that year is missing, but it is. Assuming — and this isn’t always a good assumption — that the missing data would make sense as the mean of the values on either side, we can invoke the “interpolate” method on the data frame. That method has many options for how to interpolate, but for our purposes here, it’ll be just fine:
(
tourism_df
.pivot_table(index='Year', columns='Country', values='Value')
[['Iceland', 'United Kingdom', 'Australia']]
.interpolate()
)Finally, we take our fully interpolated, limited data frame, and plot it:
(
tourism_df
.pivot_table(index='Year', columns='Country', values='Value')
[['Iceland', 'United Kingdom', 'Australia']]
.interpolate()
.plot.line()
)The result:
We can see that in 2021, Iceland actually had more tourists than Australia. That’s pretty remarkable, but I’m sure that covid-19, and Australia’s relatively late opening of its gates to tourists, had something to do with this.
Calculate, for 2019, the number of tourists per the (2023) population we calculated earlier. Where does Iceland rank, in terms of tourists per citizen?
What if we calculate the number of tourists per citizen? Where does Iceland rank then? I asked you to look at 2019 tourism information, before the pandemic affected things. And on the assumption that the population didn’t change too much, I said that we should use the 2023 population counts, which we already have in a data frame.
(
tourism_df
.loc[2019]
.reset_index()
.set_index('Country')
)I started by taking tourism_df, selecting only data from 2019, and then invoking “reset_index”, thus returning “Year” to be a regular column. I then used “set_index” to make the “Country” column our index.
Why swap the index in this way? So that I can once again run “join” on two data frames. The “merge” method lets you join even on non-index columns, but I prefer to use “join” and have the indexes in place, if only for organizational purposes.
With this in place, I can then join the two data frames together:
(
tourism_df
.loc[2019]
.reset_index()
.set_index('Country')
.join(population_df)
)Now that we have a single data frame, we can create (via “assign”) a new column, in which we calculate the number of tourists per citizen (or perhaps resident):
(
tourism_df
.loc[2019]
.reset_index()
.set_index('Country')
.join(population_df)
.assign(tourist_per_local = lambda df_: df_['Value'] / df_['population'])
)Finally, I used “sort_values” and “head” to get the five countries with the greatest number of tourists per local resident. I also used “drop” to remove the “Year” column, for nicer output:
(
tourism_df
.loc[2019]
.reset_index()
.set_index('Country')
.join(population_df)
.assign(tourist_per_local = lambda df_: df_['Value'] / df_['population'])
.sort_values('tourist_per_local', ascending=False)
.drop('Year', axis='columns')
.head(5)
)The results:
Value population tourist_per_local
Country
Iceland 2597536 375319.0 6.920875
Malta 3382515 535065.0 6.321690
Hungary 58618707 9604000.0 6.103572
Denmark 30089073 5910913.0 5.090427
Estonia 6102646 1322766.0 4.613549Iceland has, the most tourists per local resident, beating out Malta and Hungary. Indeed, with 2.5 million tourists in 2019, that’s a very large number of people entering a very small country. No wonder Reykjavik’s main street is full of shops selling souvenirs; if only a handful of the tourists buy something, you’ve still got a great business going.
Download Airbnb data for Iceland from Doorbll (https://doorbll.com/report?location=Reykjavik&country=Iceland). (There's a small link at the bottom of the page, allowing you to download the data in CSV format.) Create a data frame from that data. Remove any listings whose basic_night_price is more than 2 standard deviations above the mean. Create a scatter plot showing the number of bedrooms (x axis) vs. the price of the Airbnb rental (y axis). Color the dots based on the number of pictures shown on the Airbnb site. Do we see any correlation between the number of rooms and the basic night price?
Doorbll.com is one of several sites offering analytics about Airbnb and other short-term rentals. Fortunately, you can download some of their data, which is what I did here for Iceland. (They only seem to have data about Reykjavik, which makes sense given how many tourists stay there, but it’s a shame that they don’t have info about the rest of the country, which I found far more interesting, and where we used Airbnb quite a bit.)
I was curious to know if we can see any correlation between the number of rooms offered in an Airbnb and the price that is charged. I started by using “read_csv” to turn the downloaded CSV file into a data frame:
airbnb_filename = 'Doorbll_Reykjavik_Iceland.csv'
(pd
.read_csv(airbnb_filename)
)Next, since I wanted to work with the basic_night_price column as a number, I needed to remove non-digit characters from that column. Once again, I used a regular expression to replace any \D character (i.e., any non-digit) with nothing. I was then able to use “astype” to get a float series back, which I assigned back (with “assign”) to the same column name, “basic_night_price”:
airbnb_filename = 'Doorbll_Reykjavik_Iceland.csv'
(pd
.read_csv(airbnb_filename)
.assign(basic_night_price = lambda df_: df_['basic_night_price'].replace(r'\D', '', regex=True).astype(float))
)Next, I wanted to remove any price that was at least two standard deviations above the mean. This is a pretty standard way to remove outliers, which can mess around with your data. Here, I used “loc” along with “lambda”, using a comparison operation (<). Wherever the comparison returns True, we keep the row:
airbnb_filename = 'Doorbll_Reykjavik_Iceland.csv'
(pd
.read_csv(airbnb_filename)
.assign(basic_night_price = lambda df_: df_['basic_night_price'].replace(r'\D', '', regex=True).astype(float))
.loc[lambda df_: df_['basic_night_price'] < (df_['basic_night_price'].mean() + df_['basic_night_price'].std() * 2)]
)I didn’t ask you to do this, but I kept only those homes that had “Enter” at the start of the description. Otherwise, the numbers were just a bit too weird. Once again, I used “str.contains” and regex=True to perform my selection:
airbnb_filename = 'Doorbll_Reykjavik_Iceland.csv'
(pd
.read_csv(airbnb_filename)
.assign(basic_night_price = lambda df_: df_['basic_night_price'].replace(r'\D', '', regex=True).astype(float))
.loc[lambda df_: df_['basic_night_price'] < (df_['basic_night_price'].mean() + df_['basic_night_price'].std() * 2)]
.loc[lambda df_: df_['room_and_property_type'].str.contains('^Entire', regex=True)]
)I then got rid of all columns except for three: basic_night_price, Bedrooms, and picture_count:
airbnb_filename = 'Doorbll_Reykjavik_Iceland.csv'
(pd
.read_csv(airbnb_filename)
.assign(basic_night_price = lambda df_: df_['basic_night_price'].replace(r'\D', '', regex=True).astype(float))
.loc[lambda df_: df_['basic_night_price'] < (df_['basic_night_price'].mean() + df_['basic_night_price'].std() * 2)]
.loc[lambda df_: df_['room_and_property_type'].str.contains('^Entire', regex=True)]
[['basic_night_price', 'Bedrooms', 'picture_count']]
)Finally, I created a scatter plot with “plot.scatter”, indicating that the x axis should be the number of bedrooms and the y axis should be basic_night_price. By default, that gave me the following plot:
We can see, to some degree, that the more bedrooms there are, the higher the price goes. We’ll calculate that correlation more exactly in the next question.
But I also asked you to use the “picture_count” column to color the dots. This gives our scatter plot a third dimension of sorts, letting us see if there’s any pattern to the number of pictures vs. the price. We do this by passing the “c” keyword argument to “plot.scatter”, and the “colormap” keyword argument along with a legal colormap name. I like to use “Spectral”, which means that my query looked like this:
airbnb_filename = 'Doorbll_Reykjavik_Iceland.csv'
(pd
.read_csv(airbnb_filename)
.assign(basic_night_price = lambda df_: df_['basic_night_price'].replace(r'\D', '', regex=True).astype(float))
.loc[lambda df_: df_['basic_night_price'] < (df_['basic_night_price'].mean() + df_['basic_night_price'].std() * 2)]
.loc[lambda df_: df_['room_and_property_type'].str.contains('^Entire', regex=True)]
[['basic_night_price', 'Bedrooms', 'picture_count']]
.plot.scatter(x='Bedrooms', y='basic_night_price', c='picture_count', colormap='Spectral')
)The resulting plot:
We do see, at least to some degree, that the number of bedrooms appears to have some influence on the price. But we see many homes, of all prices, with red dots, all over the pricing map. Which means that there’s not a very clear correlation between the number of pictures and the price charged.
If we calculate the correlation between basic night price and the number of bedrooms, do we find a correlation? How strong of a correlation is it?
We can find the correlation with the “corr” method. I’ll just use the same query as we did in the last question, but instead of plotting, I’ll call “corr”:
(pd
.read_csv(airbnb_filename)
.assign(basic_night_price = lambda df_: df_['basic_night_price'].replace(r'\D', '', regex=True).astype(float))
.loc[lambda df_: df_['basic_night_price'] < (df_['basic_night_price'].mean() + df_['basic_night_price'].std() * 2)]
.loc[lambda df_: df_['room_and_property_type'].str.contains('^Entire', regex=True)]
[['basic_night_price', 'Bedrooms', 'picture_count']]
.corr()
)Here’s what we get:
basic_night_price Bedrooms picture_count
basic_night_price 1.000000 0.646299 0.173597
Bedrooms 0.646299 1.000000 0.150672
picture_count 0.173597 0.150672 1.000000We thus see that there’s a +0.65 correlation between the price and the number of bedrooms. That’s what we would expect; the more rooms you have a in a home, the more you can charge for it, right?
But I had often heard that places with more pictures (or was it better pictures?) can charge more. We do see that there’s a +0.17 correlation between picture count and the price, so that’s not wrong. But it’s a much weaker correlation than the other one.
That’s it for Iceland, and for this week’s set of questions.
My Jupyter notebook is here: https://drive.google.com/file/d/1hCdExceJ-Kq9cTxDnSSPshGm5DMnJ6jl/view?usp=sharing
I’ll be back next week with more
Reuven