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 more than once, the number of entrants has grown from twelve in the first contest to more than forty in recent ones, and the whole of it is in one file of 1,603 rows.

Most of what we learned today is about the shape of tables rather than what is in them, so it’s okay that the Eurovision results are not environmental data.

Today we will try to work out the most successful Eurovision country, and find a number of reasons to be wary of easy answers.

Reference

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

Today’s patterns

Every question below is built from the patterns we learned this morning:

pd.merge(left, right, on='key')                        # the join pattern
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 pattern

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

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

And from these, which we have been using since earlier in the week:

df[df['column'] != value].copy()                       # the filter pattern
df.sort_values('column', ascending=False).head(n)      # the top-N pattern
df.groupby('key')['col'].agg(['count', 'mean'])        # split, apply, combine
for name, group in df.groupby('key'):                  # the loop, when a pattern 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 your answer in a markdown cell underneath it, 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 of the years you just printed has a count of zero. Which one, and what happened? (You may look this up. It is the only thing you are asked to look up in this exercise.)
Build a table called contests that excludes that year, using the filter pattern and ending the line with .copy(). How many rows does it have?

Why this matters

A very common first move on a file full of nulls 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. The fabricated zeros would then be included in every average you compute for the rest of the practice, and one decade in your final table would become an artifact of a contest that never took place.

A null means no value. A zero means the value is zero. Replacing one with the other invents 41 results that nobody ever voted on.

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 of the practice does not do. We spent an hour this afternoon parsing dates, and there is a column here called year, so it is tempting to reach for pd.to_datetime().

Leave it alone. year is a number that happens to name a year, and the Eurovision file has nothing finer than the year in it: no month, no day, no hour. Parsing it would produce a column whose only useful accessor, .dt.year, returns the integer you started with. Parsing earns its place in a file that holds a real date, like the Toolik weather record from this afternoon, where .dt.month and .dt.dayofyear tell you something the raw column does not.

Count the entries in each year from 2000 to 2007. What changes abruptly? 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

You can answer the questions below with the loop over groups from Friday afternoon and the stacking pattern 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 scored finals that average came from, and the average itself.

Do it with a loop over decade groups, building the finished table one row at a time. Start with an empty list, and then for each decade:
1. Group the decade’s rows by to_country and take the mean of points_final. What comes back is a Series with one number per country, labelled by country name.
2. Do the same again with count in place of mean, so you have both numbers to hand.
3. Run .idxmax() on the means. It gives back the label of the largest value, which is the country you are looking for.
4. Look that country up in each of the two Series, and append a dictionary holding the decade, the country, the count and the mean.
After the loop, turn the finished list of dictionaries into a table with pd.DataFrame().
Read the count column before you read the mean column. Remember what .count() counts: the non-null points_final values, which is finals a country was scored in, not contests it entered. Three of the seven winners were scored in fewer than four finals. 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 this pattern? 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 Yugoslavia, Russia and Serbia & Montenegro. 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. A population column with no stated year is a real limitation of a real dataset, and you should say so in your answer rather than quietly work 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 here, 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. Use complete sentences.

The grammar minute

Look back through your notebook. Two patterns did all of today’s structural work without calculating a single number:

pd.merge(a, b, left_on=, right_on=)    # two tables become one, side by side
df.pivot_table(index=, columns=, ...)  # one long table becomes one wide one

The third pattern from this morning, pd.concat([...], ignore_index=True), stacks many tables into one from top to bottom, and today’s questions did not happen to need it. It is the one to reach for when the rows you want arrive split across several files, which is how the three air quality monitors arrived on Friday.

Every other line in your notebook was something you already knew how to do, applied to a table that one of the two patterns above had already put into the right shape.

Much of the difficulty in real data analysis has nothing to do with the analysis itself. The numbers you want to compare often sit in two different files, or sit in one file at right angles to each other, and merging, stacking and pivoting are how you get them side by side before the analysis starts.

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

If one of these is not true and you are not sure how to fix it, grab Cella or Kelly before you leave today!

Resources

Looking ahead to Day 7

Tomorrow every table you have built this week can become a figure. We have spent six days getting data into the right shape, and tomorrow we start making pictures that other people can read.