Tasks & Activities

A panda, waiting for the votes from the national jury. MidJourney 5

The Eurovision Song Contest has been held almost every year since 1956. Fifty-two countries have entered at least once, the scoring system has been rebuilt four times, the number of entrants has nearly doubled, and the whole of it is in one file of 1,603 rows.

It is not environmental data, and that is deliberate. Everything you learned today is about the shape of tables rather than what is in them, and a dataset you have opinions about is a good place to find out whether you have actually learned it. You will recognize half the country names and none of the songs.

Tonight you will do all four of the day’s moves on it, and along the way the file will hand you three separate reasons to distrust an answer you have just computed. Finding those is the real exercise.

Reference

Eurovision Song Contest contestant data, compiled from the contest’s public results archive.

Today’s sentences

Everything below is built from these three, plus what you already had.

pd.merge(left, right, on='key')                        # the join sentence
pd.merge(left, right, left_on='a', right_on='b')       # when the key names differ
pd.merge(left, right, on='key', how='left')            # keep every row on the left

pd.concat([a, b, c], ignore_index=True)                # the stacking sentence

df.pivot_table(index=, columns=, values=)              # the pivot sentence

counts = df['col'].value_counts().reset_index()        # Series to table

And these, from earlier in the week:

df[df['column'] != value].copy()                       # the filter sentence
df.sort_values('column', ascending=False).head(n)      # the top-N sentence
df.groupby('key')['col'].agg(['count', 'mean'])        # split, apply, combine
for name, group in df.groupby('key'):                  # the loop, when a sentence is not enough

Setup

Create a new notebook named EOD_Day6_Eurovision.ipynb.
Add a title cell:

# Day 6 EOD: Sixty-Four Contests, Two Tables

Date: 09/08/2026

Read the file:

Code

import pandas as pd

url = 'https://eds-217-essential-python.github.io/data/eurovision_contestants.csv'
eurovision = pd.read_csv(url)

Part 1: What is in this file, and what is missing from it

Answer each question with code, then write the answer in a markdown cell underneath, in a complete sentence with the numbers in it.

How many rows and columns? What are the column names, and what is the range of year?
Run .isnull().sum(). Several columns are null on well over a thousand of the 1,603 rows. Pick two of them and, in a markdown cell, propose an explanation for each that has nothing to do with anybody making a mistake.
points_final is the column the rest of this exercise depends on, and it is null on 295 rows. Group by year and count the non-null points_final values in each. Look at the last few years.
One year has a count of zero. Which one, and what happened? (You may look this up. It is the only fact in this exercise that is not in the file.)
Build a table called contests that excludes that year, using the filter sentence and ending the line with .copy(). How many rows does it have?

Why this matters more than it looks

A very common first move on a file like this is df.fillna(0), to make the nulls go away.

Do it here and every one of that year’s 41 entries acquires a real score of zero points. Those zeros then flow into every average you compute for the rest of the evening, and one decade in your final table becomes an artifact of a contest that never took place.

A null means no value. A zero means the value is zero. Filling one with the other is not tidying, it is fabrication, and pandas will help you do it without a word of complaint.

Part 2: Decades

Add a decade column. Divide year by ten, convert the result to an integer, and multiply by ten. .astype(int) truncates toward zero, which is exactly what you want here. Then count the entries in each decade.

Note

🐍 Notice what this part does not do. You spent an hour this morning parsing dates, and there is a column here called year. It is tempting to reach for pd.to_datetime().

Leave it alone. year is a number that happens to name a year, and this file holds nothing below the year: no month, no day, no hour. Parsing it would produce a column whose only useful accessor, .dt.year, hands back the integer you started with. The parse earns its place in files that carry a real date, like the Toolik record from this morning, where .dt.month and .dt.dayofyear tell you something the raw column does not.

Recognizing which tool a file does not need is as much a part of the job as knowing which one it does.

Count the entries in each year from 2000 to 2007. Something changes abruptly. What, and in which year? In a markdown cell, say what a sudden jump in entries per contest would do to a comparison of average scores between the 1990s and the 2010s.

Part 3: The best country of each decade

This is the question the file invites, and answering it well takes the loop from yesterday afternoon and the stacking sentence from this morning.

Build a table with one row per decade, holding the country with the highest average points_final in that decade, how many entries that average came from, and the average itself.

Do it with a loop over decade groups. For each decade: group by to_country, .agg() for the count and mean of points_final, .reset_index(), take the top row with the top-N sentence, add a decade column, and append the one-row table to a list. Then stack the list with pd.concat().
Read the count column before you read the mean column. Three of the seven winners won on fewer than four entries. Name them, and say in one sentence why you would not put any of them in a headline.
The mean column rises from about 20 in the 1950s to about 360 in the 2010s. In a markdown cell, two or three sentences: is Europe getting better at writing songs? What else changed between 1956 and 2019 that would produce exactly this pattern, and what would you have to divide by to remove it?

Part 4: The second table

Read the population file at https://eds-217-essential-python.github.io/data/eurovision_country_populations.csv. How many rows, and what are its columns?
Build a table called modern holding only contests from 1990 onwards, then count the entries per country and turn that count into a two-column table using the Series-to-table move. Rename the columns to country and entries.
Merge counts with populations. The key columns have different names, so you will need left_on= and right_on=. Do it twice, once with the default how= and once with how='right', and report both shapes.
One country is in the population file and not in your counts. Find it, name it, and then find every row for it in the full eurovision table. In a markdown cell, say what you learned and why the inner merge was right to drop it.
Add an entries_per_million column to entries: the entry count divided by the population in millions. (1_000_000 is a perfectly good way to write a million in Python; the underscores are ignored.) Rank it and show the top eight and the bottom five.
The top of that ranking is San Marino, Andorra, Iceland, Monaco and Malta, and the bottom is Russia, Italy and Ukraine. In a markdown cell, three or four sentences: is entries per million people a meaningful quantity? What is it actually measuring, and what would you have to know about how the file’s population numbers were collected before you would publish this table?

Look at the population column before you answer

Iceland is listed at 255,866 and the United Kingdom at 57,247,586. Neither of those is a current figure, and the file does not say what year any of them are from. That is a real limitation of a real dataset, and naming it is a better answer than working around it.

Part 5: The whole contest in one grid

Build a wide table with to_country down the rows, decade across the columns, and the mean points_final in the cells. What shape is it?
Build the same table again with aggfunc='count', and look at both for four countries that have competed throughout: Ireland, Sweden, the United Kingdom and Norway.
In a markdown cell: the empty cells in the means grid are not all the same kind of empty. Name two different reasons a country might have no number in a given decade, and say how you would tell them apart using the counts grid.

Part 6: Write it up

In a single markdown cell of 200 to 300 words, answer this:

A magazine editor has read that Ireland is the most successful country in Eurovision history and wants you to confirm it with the data. Can you?

Your answer must cite at least three specific numbers you computed tonight, must name at least two distinct reasons the question is harder than it sounds, and must end with the single table or number you would actually send them. Complete sentences, no bullet fragments.

The grammar minute

Look back through your notebook. Three sentences did all of the structural work tonight, and none of them calculated anything:

pd.merge(a, b, left_on=, right_on=)    # two tables become one, side by side
pd.concat([...], ignore_index=True)    # many tables become one, top to bottom
df.pivot_table(index=, columns=, ...)  # one long table becomes one wide one

Every other line in your notebook was something you already knew, operating on a table that one of those three sentences had put into the right shape.

That is the whole of today, and it is worth saying plainly. Most of the difficulty in real data analysis is not the analysis. It is that the numbers you need to compare are in two files, or in one file at right angles to each other. The three sentences above are how you fix that, and after tonight none of them should be mysterious.

Wrap-up

Before you close your notebook, check that:

you never called .fillna(0) on a whole DataFrame
every average you reported has a count beside it
you compared the row count before and after both of your merges
your Part 6 answer names something the data cannot tell the editor
your notebook reads top to bottom as a document, not as a pile of cells

Resources

Looking ahead to Day 7

Tomorrow every table you have built this week becomes a picture. You have spent six days getting data into the right shape; tomorrow is what that shape was for.