This week, we’re looking at the latest data from Transparency International, via the data:
The questions I asked were:
- According to Transparency International, what five countries were least corrupt in 2022?
- According to the same data, what five countries were most corrupt in 2022?
- Which region of the world was least corrupt?
- Finally, which five countries have made the greatest improvement in 10 years, between the 2012 report and the 2022 report?
Discussion
First and foremost, I had to turn the data set into a Pandas data frame. One way to do that is to download the file onto your local computer, and then use the “read_excel” on the file.
Another way is to give the full URL to the file, and pass it to read_excel as a first argument. Which sounds great… except that in this particular case, I found that I got a 403 (“forbidden”) HTTP response code. It would seem that certain software has been forbidden from downloading the file; you can use a browser, and even wget, but not Pandas.
So I downloaded the file, and then read it into memory:
filename = '/Users/reuven/Downloads/CPI2022_GlobalResultsTrends.xlsx'
df = pd.read_excel(filename)But there’s a problem, as we can see if we look at the first few rows of the data frame. The columns have weird names. Row index 0 is all NaN values. Row index 1 contains text. and then, starting with row index 2, we get information about the countries.
The problem? The headers are in row index 2. Everything before that is just decoration and informative. If we want to read the data, and if we want the column names in the Excel file to be used as column names in our data frame, we’ll need to tell Pandas to use row index 2 as our headers:
df = pd.read_excel(filename, header=2)Now, when I run df.head(), things look much better.
And now, we can start to answer some questions, starting with: What five countries were least corrupt in 2022?
The column containing that information is “CPI score 2022”. I can retrieve that with:
df['CPI score 2022']That’ll give me the column with those scores. If I want the highest five scores, then I’ll want to sort the values in that column:
df['CPI score 2022'].sort_values()But sort_values normally works in ascending order, and I want the five top values. So I’ll just ask for the sort to happen in reverse (descending) order:
df['CPI score 2022'].sort_values(ascending=False)But we only want the first five, so we’ll run “head” to cut it off:
df['CPI score 2022'].sort_values(ascending=False).head()But wait a second: If we do this, then we get the top five scores, but we don’t know what countries they belong to! That’s because the countries were in a separate column, called “Country / Territory”.
The easiest solution here, I think, is to set the index of df to be that country column, then sort based on it:
df.set_index('Country / Territory')[
'CPI score 2022'].sort_values(ascending=False).head()In this way, I found that the five least corrupt countries were found to be Denmark, Finland, New Zealand, Norway, and Sweden.
The second question was: What five countries were most corrupt in 2022?
This is pretty easy to answer, given what we’ve just done: We just sort in ascending order, and the lowest-scoring countries will be at the top:
df.set_index('Country / Territory')[
'CPI score 2022'].sort_values().head()The winners, if you can call them that, are: Somalia, Syria, South Sudan, Venezuela, and Yemen. I guess I’ll now have to rethink attending Python conferences in each of these countries. Oh, well.
The next question was: Which region of the world was least corrupt?
Here, we’ll notice that there is a “Region” column. I’ll use “groupby” to calculate the mean corruption index for each region:
df.groupby('Region')['CPI score 2022'].mean()Grouping is a simple idea, but the syntax can make it complex. Here, we’re saying:
Find the unique values of the “Region” column
For each of the unique values of “Region”:
Find all of the rows for that value
Get all of the values for “CPI score 2022”
Calculate the mean on those values
The result is a Pandas series, one in which the index contains the unique values of “Region”, and the values are the mean of “CPI score 2022” for that region.
But of course, I asked to find the name of the region that had the highest score. One way to do this would be to sort the group-result series, and take the row with the highest score:
df.groupby('Region')['CPI score 2022'].mean().sort_values().tail(1)
In the above code, I take the series we got back, and apply sort_values. Because sort_values works, by default, in ascending order the element with the highest score will be the final one. We can get that by invoking tail(1).
If you’re only interested in the least-corrupt region’s name, and not its score, then we can instead use the “idxmax” method on the result of our group + mean:
df.groupby('Region')['CPI score 2022'].mean().idxmax()The "idxmax” method returns the index associated with the max value in the series. (So the “max” method returns the highest value, but “idxmax” returns its corresponding index.) And because the index of our groupby contains region names, we can get the highest-scoring region name.
My final question was: Which five countries have made the greatest improvement in 10 years, between the 2012 report and the 2022 report?
Now, this is admittedly a bit trickier. First, because we want to read from the third sheet in the Excel file, rather than from the (default) first. That requires we re-read our Excel file, specifying the sheet name. We could use the name, but I’ll just pass 2, indicating the third sheet:
df = pd.read_excel(filename, sheet_name=2, header=2)Note that while this sheet has different formatting (more on that in a moment), it still has column names in row index 2, so specifying “header=2” is still a good idea.
But the sheet is a bit weird, in that it contains comparisons across several different years and 2022: Columns A-E are for the time period we want, namely 2012-2022. Columns G-K are for 2013-2022, columns M-Q are for 2014-2022, and so forth. How can we indicate that we’re only interested in specific columns?
The answer is with the “usecols” parameter, which takes a list argument — either the names of the columns we want, or their numeric indexes, starting with 0. I decided to specify the numbers, since the column names are a bit clunky:
df = pd.read_excel(filename, sheet_name=2,
header=2, usecols=[0,1,2,3,4])With that in hand, we can now calculate how many places a country rose (or fell) from 2012 to 2022:
df['change'] = df['CPI 2022'] - df['CPI 2012']The countries that improved the most will have the highest values. Now, we could work with the series that we got back. But it’ll probably be easier to assign that series to a new column on the data frame:
df['change'] = df['CPI 2022'] - df['CPI 2012']We can now sort the “change” column by value, in descending order, and grab the five most-improved countries:
df['change'].sort_values(ascending=False).head()The problem? Once again, we’re missing the country names. We can solve this by (temporarily) setting the data frame’s index to be the “Country” column:
df.set_index('Country')['change'].sort_values(ascending=False).head()And in this way, we can see that the five most-improved countries from 2012 to 2022 are: Seychelles, Greece, Italy, Uzbekistan, and Guyana.
And there you have it! We have learned a lot about corrupt (and non-corrupt) countries, as well as how to analyze data about them.
Did you have a different solution? A better one? Are there other questions that we could (or should) ask about this data, or your code? I’d love to get your feedback.
Get the Jupyter notebook with my solutions here: https://drive.google.com/file/d/11s36lcnrcz4EPfx6V3mUUhtzNG6xicIe/view?usp=drive_link
Until next week,
Reuven