𧱠Building a Table Row by Row
Question 8 of yesterdayβs practice asked you to build a table one row at a time, using a loop over groups. If you got stuck on it, the fault is ours: we took a lighter day on Friday and skipped π When a Pattern Is Not Enough, which would have introduced looping over groupsβ¦ and thenβ¦ π« we forgot about that yesterday afternoon and we went ahead and asked you for it anyway!
Soβ¦ letβs spend about fifteen minutes before we start on figures, to cover some of the most essential parts of Fridayβs session this morning. We will work through the three things question 8 depended on: building a DataFrame yourself, taking one group at a time out of a groupby, and (gasp!) grouping a group.
Nearly every table we have used this course came out of a file, by way of pd.read_csv(). However, yesterday we built two of our own: in π The Join Pattern we typed the seven-row regions lookup table out by hand and merged it into the parks data, and in π A Date Is Not a Number we built the twelve-row Toolik comparison table out of two groupby results rather than out of values.
That comparison example is closer than the regions example to what question 8 wanted, because it had no file behind it and its numbers were ones we had just computed a moment earlier. Question 8 asked for the same kind of table, put together one decade at a time inside a loop, which is something you hadnβt seen before.
Open a new notebook called Session_70_Building_Tables.ipynb and start it with a title cell, the way you have started every notebook this week:
The setup below is the same one you built across questions 3, 5 and 6 of yesterdayβs practice, gathered here into a single cell:
Yesterday we built regions out of a dictionary. If you hand a dictionary to pd.DataFrame(), you get a table back: each key becomes a column name, and the list stored under that key becomes the column.
| country | entries | |
|---|---|---|
| 0 | Norway | 58 |
| 1 | Sweden | 59 |
| 2 | Malta | 32 |
The one rule of making DataFrames using dictionaries is that every list has to be the same length, because a table is a rectangle.
The table that comes back is a real DataFrame even though no file was involved: .sort_values(), pd.merge() and everything else from this week work on a table you typed out yourself exactly as they work on the Eurovision one.
pd.DataFrame() will also take a list of dictionaries. Each dictionary in the list is one row, and its keys are the column names:
| country | entries | |
|---|---|---|
| 0 | Norway | 58 |
| 1 | Sweden | 59 |
| 2 | Malta | 32 |
The result is the same table, down to the column types. A dictionary of lists describes a table column by column, and a list of dictionaries describes the same table row by row. So reach for whichever form matches the pieces you already have!
If you already have whole columns, use the dictionary of lists. If you are producing one row at a time, use the list of dictionaries.
A loop usually gives you one row at a time, so a list of dictionaries is the form to reach for whenever a loop is producing the rows. Typing the rows out by hand, as we just did, works only when you already know every row in advance. More often you do not, because each row is the result of a calculation the loop is working through. So we collect the rows as we go!
The code pattern has three parts: (1) Start with an empty list, (2) append one dictionary (i.e. βrowβ) per group inside the loop, and (3) turn the finished list into a DataFrame in one call at the end:
records = [] # Step 1: Start with an empty list.
for decade, decade_data in contests.groupby('decade'):
records.append({ # Step 2: append one dictionary per group inside the loop
'decade': decade,
'entries': len(decade_data),
'countries': decade_data['to_country'].nunique()
})
pd.DataFrame(records) # Step 3: Turn the finished list into a DataFrame| decade | entries | countries | |
|---|---|---|---|
| 0 | 1950 | 43 | 12 |
| 1 | 1960 | 163 | 18 |
| 2 | 1970 | 176 | 22 |
| 3 | 1980 | 200 | 23 |
| 4 | 1990 | 236 | 34 |
| 5 | 2000 | 336 | 47 |
| 6 | 2010 | 408 | 46 |
Notice that a groupby loop gives you two variables rather than one when you iterate over it. decade is the name of the group, which is the value we grouped on. decade_data is a DataFrame with every row for that decade in it. Yay!! Because you get a DataFrame with every pass through the loop, everything we have used this week works on it!
len(decade_data) gives you the number of rows in that group, because len() on a DataFrame counts its rows, which is something we have seen before.
Each dictionary in records is one row, and its keys are the column names. contests has seven decades in it, so seven dictionaries go in and a seven-row table comes out.
Question 8 asked for the country with the highest average points_final in each decade, which is not a single number, so .agg() on its own cannot produce it. The answer for each decade is a whole row, which we create by sorting inside each decade group.
The loop is the same one we wrote in section 2, and so is the ending: an empty list, one dictionary appended per group, and pd.DataFrame() once at the end. The hard part is working out what goes in each dictionary, because the numbers we want are buried inside each set of data in the groupby object.
winners = []
for decade, decade_data in contests.groupby("decade"):
# Find the average points_final by country in each decade:
means = decade_data.groupby("to_country")["points_final"].mean()
# Count the number of times a country was in the finals in each decade:
counts = decade_data.groupby("to_country")["points_final"].count()
# What is the index (country) associated with the max avg. points_final in each decade?
best = means.idxmax()
print(f"{decade} best country was {best}")
# Append this decade to our winners list:
winners.append(
{
"decade": decade, # Current decade
"country": best, # Country that had highest avg. points_final for this decade
"finals": counts[best], # The number of times this country was in the finals
"mean_points": means[best], # The average points_final for this country
}
)
decade_winners = pd.DataFrame(winners) # Turn our list of dictionaries into a dataframe
decade_winners1950 best country was France
1960 best country was United Kingdom
1970 best country was United Kingdom
1980 best country was Ireland
1990 best country was Ireland
2000 best country was Serbia
2010 best country was Bulgaria| decade | country | finals | mean_points | |
|---|---|---|---|---|
| 0 | 1950 | France | 3 | 19.666667 |
| 1 | 1960 | United Kingdom | 10 | 23.100000 |
| 2 | 1970 | United Kingdom | 10 | 93.200000 |
| 3 | 1980 | Ireland | 9 | 99.222222 |
| 4 | 1990 | Ireland | 10 | 119.200000 |
| 5 | 2000 | Serbia | 2 | 214.000000 |
| 6 | 2010 | Bulgaria | 3 | 362.666667 |
Two lines in the loop are worth pointing at.
The middle of the loop groups decade_data, which is itself a group. Grouping a group is allowed, and it is the part of question 8 that was genuinely new: the outer groupby gives us one decade at a time, and the inner one gives us the average for each country inside that decade.
The line with best in it is .idxmax(), which is not new either. It gives back the label of the largest value in a Series, and we used it on the survey data to name the best site once grouping had made the labels site names. Here the grouping has made the labels country names, so means.idxmax() is the country we are after, and means[best] and counts[best] then look up that one countryβs two numbers, which is the same label lookup we did on the survey means.
A dictionary of lists describes a table by column, and a list of dictionaries describes it by row. A loop gives you rows, so start with an empty list, append one dictionary per group, and call pd.DataFrame() once on the finished list. Every table on this page was built that way, including question 8.