Marathon Data

Getting the Data Ready

In reality, datasets rarely arrive with clean column names. In this series, we work with marathon race data: a results file with finish times and a separate registration file with runner details. The results file has columns like full_name and dnf that need renaming before anything else. rename() and drop() get us started.

In the real world, datasets rarely arrive clean and ready to use. In this series, we work with data from a city marathon. There are two CSV files: one with race results (finish times, categories) and one with runner registrations (age, country, experience). Let's explore both before changing anything.

Start with the results file. How big is it?

import pandas as pd
 
results = pd.read_csv(
    '/data/marathon_results.csv')
print(results.shape)

Python

Output

55 rows and 6 columns. Let's look at the first few rows to see what's actually in there:

import pandas as pd
 
results = pd.read_csv(
    '/data/marathon_results.csv')
print(results.head(3))

Python

Output

Already some things stand out. The dnf column has 'N' and 'No' and 'n' for what should be the same value. One name is in ALL CAPS. We'll deal with those later. For now, let's check the other file.

Load the registrations file and check its size:

import pandas as pd
 
reg = pd.read_csv(
    '/data/marathon_registrations.csv')
print(reg.shape)

Python

Output

52 rows, also 6 columns. Different size from the results file. Let's peek at the data:

import pandas as pd
 
reg = pd.read_csv(
    '/data/marathon_registrations.csv')
print(reg.head(3))

Python

Output

Both files share a runner_id column. That's how we'll connect them later. But notice: the results file calls the name column full_name, while the registrations file uses name.

Compare the column names side by side:

import pandas as pd
 
results = pd.read_csv(
    '/data/marathon_results.csv')
reg = pd.read_csv(
    '/data/marathon_registrations.csv')
print(list(results.columns))
print(list(reg.columns))

Python

Output

The results file has full_name, the registrations file has name. Both refer to the same thing. Before we can combine these files, the column names need to match. That's what rename() does.

rename() takes a dict mapping old names to new ones:

df = df.rename(columns={
    'old_name': 'new_name'
})

Python

Rename full_name to name in the results file so it matches the registrations:

import pandas as pd
 
results = pd.read_csv(
    '/data/marathon_results.csv')
results = results.rename(
    columns={'full_name': 'name'})
print(list(results.columns))

Python

Output

What will be the output?

import pandas as pd
 
df = pd.DataFrame({
    'a': [1], 'b': [2], 'c': [3]
})
df = df.rename(columns={'b': 'x'})
print(list(df.columns))

Python

You can rename multiple columns in a single call:

import pandas as pd
 
results = pd.read_csv(
    '/data/marathon_results.csv')
results = results.rename(columns={
    'finish_time': 'time',
    'dnf': 'did_not_finish'
})
print(list(results.columns))

Python

Output

What will be the output?

import pandas as pd
 
df = pd.DataFrame({
    'finish_time': ['3:15'],
    'dnf': ['N'],
    'city': ['Oslo']
})
df = df.rename(columns={
    'finish_time': 'time',
    'dnf': 'did_not_finish'
})
print(list(df.columns))

Python

The results file also has a city column, but the registrations file already has the runner's location under country. We can remove columns we don't need with drop().

Syntax for drop():

df = df.drop(columns=['col_name'])

Python

Drop the city column from the results:

import pandas as pd
 
results = pd.read_csv(
    '/data/marathon_results.csv')
results = results.drop(columns=['city'])
print(list(results.columns))

Python

Output

What will be the output?

import pandas as pd
 
df = pd.DataFrame({
    'a': [1], 'b': [2], 'c': [3]
})
df = df.drop(columns=['b'])
print(list(df.columns))

Python

What will be the output?

import pandas as pd
 
df = pd.DataFrame({
    'id': [1], 'name': ['Alice'],
    'city': ['Paris']
})
df = df.rename(columns={'name': 'runner'})
df = df.drop(columns=['city'])
print(list(df.columns))

Python

What will be the output?

import pandas as pd
 
df = pd.DataFrame({
    'a': [1], 'b': [2]
})
df.rename(columns={'a': 'x'})
print(list(df.columns))

Python