Remember: I'm offering three Claude Code workshops this month! The workshops focus on solving real problems in an interactive environment. Learn more, and sign up for Monday's session where I'll explain it all, at https://lernerpython.com/code-with-claude/ .
Earlier this week, we got initial results from the 2025 PISA (Programme for International Student Assessment) exams. PISA is administered by the Organisation for Economic Co-operation and Development (OECD), what the Economist likes to call, "a club of mostly rich countries," and aims to compare schools and student achievement across dozens of countries. A country that gets high PISA scores is seen as having a good education system, and one that is rising is also seen in a good light.
The OECD itself said, in releasing its results, that these were the lowest-ever reading and math scores, with reading down sharply since 2015. Between that, and the great book, "The Smartest Kids in the World" by Amanda Ripley (https://www.amandaripley.com/the-smartest-kids-in-the-world) that I read several years ago, I thought it would be interesting to see how various countries were doing.
Data and five questions
You can download all of the PISA data from the OECD's StatLink, https://www.oecd.org/en/publications/pisa-2025-results-volume-i_73451bc5-en.html . From there, click on "full report," which brings you to a download page. We'll be looking at just two of the files they provided, I.B1.1 and I.B1.2.
Paid subscribers, both to Bamboo Weekly and to my LernerPython+data membership program (https://LernerPython.com) get all of the questions and answers, as well as downloadable data files, downloadable versions of my notebooks, one-click access to my notebooks, and invitations to monthly office hours.
Learning goals for this week include working with Excel files, cleaning data, correlations, and plotting with Plotly.
Here are my solutions and explanations for this week:
Read the first sheet (I.B1.1.1, "Learners' competencies") from the table I.B1.1 Excel file into a Pandas data frame. You'll want the country names to be in the index, the values to all be floats (treating the letter m as NaN), and only keep the columns with "%" in them. What 5 countries have the highest percentage in column A, the percentage of students at level 2 or above in science, reading, and mathematics?
I started off, as usual, by loading Pandas and Plotly:
import pandas as pd
from plotly import express as pxI then started the process of reading the file into a Pandas data frame. I used read_excel to read the file – but because the file contains multiple sheets, and I was only interested in one of them, I used the sheet_name keyword argument to indicate which sheet I would want. (Without that, I would get a dict whose keys are the sheet names and whose values are data frames.)
The Excel spreadsheet had a few lines of text before the data actually began. I thus used the header keyword argument to indicate the line that should be used for the column names. However, it turns out that the column names were spread across two lines – which meant that it would be easiest, at least at this point, to treat them as a multi-index. I passed the list [7,8] to header in order to do that.
In the question, I also mentioned that I wanted to treat the m value ("missing") as NaN. Without doing so, each column would be treated as strings. Fortunately, read_excel supports the na_values keyword argument, letting us indicate which value or values should be treated as NaN, beyond the defaults.
The query thus started as this:
table_1_filename = 'data/bw-187-table_ibi11.xlsx'
table_1_df = (
pd
.read_excel(table_1_filename, sheet_name=1, header=[7,8], na_values='m')
)
There were still a few things to do, though. For starters, I wanted the countries to be the index of the data frame. The first column contained the country names, but because of the multi-index, the name was annoying to type.
I thus decided to be a bit sneaky, creating a new country column with precisely the same values as the existing column, but with an easier-to-type name, using assign. Why assign? Because it takes a lambda expression that gets the data frame as an argument. From this data frame, I can retrieve the first column's name without knowing its name. Then I can use set_index to turn the new country column into an index:
table_1_df = (
pd
.read_excel(table_1_filename, sheet_name=1, header=[7,8], na_values='m')
.assign(country = lambda df_: df_[df_.columns[0]])
.set_index('country')
)
The good news? I now have the countries in the index, and the PISA data in the data frame's columns. However, I only want the columns with the "%". Meaning, I want half of the remaining columns. I can most easily extract them with xs, the cross-sectional extractor for multi-indexed data. We indicate the value we want, the level of the multi-index where that value will be, and then whether we want rows or columns:
table_1_df = (
pd
.read_excel(table_1_filename, sheet_name=1, header=[7,8], na_values='m')
.assign(country = lambda df_: df_[df_.columns[0]])
.set_index('country')
.xs('%', axis='columns', level=1)
)
This left us with a regular, one-level set of column names. But they were still long and hard to type, so I used set_axis to rename them to a through f, plus engaged at the end:
table_1_df = (
pd
.read_excel(table_1_filename, sheet_name=1, header=[7,8], na_values='m')
.assign(country = lambda df_: df_[df_.columns[0]])
.set_index('country')
.xs('%', axis='columns', level=1)
.set_axis(list('abcdef') + ['engaged'], axis='columns')
)
The result? A data frame with 97 rows and 7 columns. Each row represents a country or territory, along with a few aggregations (e.g., "OECD Average"). But we can now start to analyze the data.
And indeed, the first thing I wanted to do was find out which countries had the highest scores for column A:
table_1_df['a'].nlargest(10)In other words, I grabbed the a column with [], then invoked nlargest, which returned the 10 largest values, along with the index (i.e., the country names):
country a
B-S-J-Z (China) 91.94103000000001
Singapore 82.60828000000001
Macao (China) 81.83963
Chinese Taipei 77.81561
Japan 76.92318
Estonia 76.32718000000001
Korea 74.15611000000001
Ireland 71.05835
United Kingdom 68.31307000000001
Hong Kong (China) 68.12897000000001
It's important to note that these aren't scores; it's not that students in China and Singapore got sky-high scores in science, reading, and math. But it does show the percentage of students who achieved a certain level in these areas – a measure of academic literacy, if you will. And it shows extremely high levels in a handful of countries.
What about the lowest scores? I repeated my query, but used nsmallest instead:
table_1_df['a'].nsmallest(10)The result:
country a
Kurdistan Region (Iraq) 1.7934
Rwanda 3.0311800000000004
Cambodia 6.169560000000001
Guatemala 6.380280000000001
Kenya 6.64719
Dushanbe (Tajikistan) 6.972230000000001
Paraguay 8.03772
Dominican Republic 8.236500000000001
Morocco 8.455250000000001
Kosovo 9.254660000000001
In other words, only 3 percent of 15-year-old children in Rwanda passed the standard set in column A.
The table includes the OECD average. Which countries are above the OECD average for column A, and how did they score?
First, I had to find a way to check which rows had a column A value greater than the OECD row's value. I decided to again use assign with lambda. This time, I used loc within the lambda expression, passing it two arguments: the row for OECD average , and the a column. This gave me a single numeric value in response. Assigning a scalar value to a column in assign creates a new column whose values are all that scalar. In other words, after the assign, every row had an oecd_average column with the OECD average value for column a:
(
table_1_df
.assign(oecd_average = lambda df_: df_.loc['OECD average', 'a'])
)The next step was to keep only those rows whose a value was greater than the OECD average. I did this using pd.col within loc, and then keeping the a column if it was indeed greater than the OECD average. The result of this line was a series, with country names in the index and the A column's value as the data:
(
table_1_df
.assign(oecd_average = lambda df_: df_.loc['OECD average', 'a'])
.loc[pd.col('a') > pd.col('oecd_average'), 'a']
)I then ran sort_values to get the values in order, and used pipe along with px.bar to get a bar plot in Plotly:
(
table_1_df
.assign(oecd_average = lambda df_: df_.loc['OECD average', 'a'])
.loc[pd.col('a') > pd.col('oecd_average'), 'a']
.sort_values()
.pipe(px.bar)
)The plot:
