Post

Advanced Techniques for Merging DataFrames with pandas

Posted Updated
By Trenton McKinney 150 min read

Introduction and Setup

Synopsis

The code cells that follow have been updated to pandas v2.2.2, so some code does not match associated instructions, but these updates are minor.

Here is a concise synopsis of each major section within the document:

Preparing Data

  • Discusses various techniques for importing multiple files into DataFrames.
  • Focuses on how to use Indexes to share information between DataFrames.
  • Covers essential commands for reading data files such as pd.read_csv() and highlights their importance for subsequent merging tasks.

Concatenating Data

  • Explores database-style operations to append and concatenate DataFrames using real-world datasets.
  • Describes methods like .append() and .concat() which stack rows or join DataFrames along an axis.
    • Some instructions reference .append(), which is removed in favor of .concat().
  • Emphasizes the handling of indices during the concatenation process and introduces the concept of hierarchical indexing.

Merging Data

  • Provides an in-depth look at merging techniques in pandas, including different types of joins (left, right, inner, outer).
  • Explains the use of merge() function to align rows using one or more columns.
  • Discusses ordered merging, which is particularly useful when dealing with columns that have a natural ordering, like dates.

Case Study - Summer Olympics

  • Applies previously discussed DataFrame skills to analyze Olympic medal data, integrating lessons from both the current and prior pandas courses.
  • Uses the dataset of Summer Olympic medalists from 1896 to 2008 to showcase data manipulation capabilities in pandas.

Each section builds on the knowledge from the previous, culminating in a practical case study that utilizes all the discussed DataFrame manipulation techniques.

Imports

1
2
3
4
5
6
7

import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
from pprint import pprint as pp
import csv
from pathlib import Path
import yfinance as yf

1
2
3
4

print(f'Pandas Version: {pd.__version__}')
print(f'Matplotlib Version: {plt.matplotlib.__version__}')
print(f'Numpy Version: {np.__version__}')
print(f'Yahoo Finance Version: {yf.__version__}')

1
2
3
4

Pandas Version: 2.2.2
Matplotlib Version: 3.8.4
Numpy Version: 1.26.4
Yahoo Finance Version: 0.2.38

Pandas Configuration Options

1
2
3

pd.set_option('display.max_columns', 200)
pd.set_option('display.max_rows', 300)
pd.set_option('display.expand_frame_repr', True)

Data Files Location

Data File Objects

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

data = Path.cwd() / 'data' / 'merging-dataframes-with-pandas'
auto_fuel_file = data / 'auto_fuel_efficiency.csv'
baby_1881_file = data / 'baby_names1881.csv'
baby_1981_file = data / 'baby_names1981.csv'
exch_rates_file = data / 'exchange_rates.csv'
gdp_china_file = data / 'gdp_china.csv'
gdp_usa_file = data / 'gdp_usa.csv'
oil_price_file = data / 'oil_price.csv'
pitts_file = data / 'pittsburgh_weather_2013.csv'
sales_feb_hardware_file = data / 'sales-feb-Hardware.csv'
sales_feb_service_file = data / 'sales-feb-Service.csv'
sales_feb_software_file = data / 'sales-feb-Software.csv'
sales_jan_2015_file = data / 'sales-jan-2015.csv'
sales_feb_2015_file = data / 'sales-feb-2015.csv'
sales_mar_2015_file = data / 'sales-mar-2015.csv'
sp500_file = data / 'sp500.csv'
so_bronze_file = data / 'summer_olympics_Bronze.csv'
so_bronze5_file = data / 'summer_olympics_bronze_top5.csv'
so_gold_file = data / 'summer_olympics_Gold.csv'
so_gold5_file = data / 'summer_olympics_gold_top5.csv'
so_silver_file = data / 'summer_olympics_Silver.csv'
so_silver5_file = data / 'summer_olympics_silver_top5.csv'
so_all_medalists_file = data / 'summer_olympics_medalists 1896 to 2008 - ALL MEDALISTS.tsv'
so_editions_file = data / 'summer_olympics_medalists 1896 to 2008 - EDITIONS.tsv'
so_ioc_codes_file = data / 'summer_olympics_medalists 1896 to 2008 - IOC COUNTRY CODES.csv'

Course Description

As a Data Scientist, you’ll often find that the data you need is not in a single file. It may be spread across a number of text files, spreadsheets, or databases. You want to be able to import the data of interest as a collection of DataFrames and figure out how to combine them to answer your central questions. This course is all about the act of combining, or merging, DataFrames, an essential part of any working Data Scientist’s toolbox. You’ll hone your pandas skills by learning how to organize, reshape, and aggregate multiple data sets to answer your specific questions.

Preparing Data

In this chapter, you’ll learn about different techniques you can use to import multiple files into DataFrames. Having imported your data into individual DataFrames, you’ll then learn how to share information between DataFrames using their Indexes. Understanding how Indexes work is essential information that you’ll need for merging DataFrames later in the course.

Reading multiple data files

Tools for pandas data import

  • pd.read_csv() for CSV files
    • df = pd.read_csv(filepath)
    • dozens of optional input parameters
  • Other data import tools:
    • pd.read_excel()
    • pd.read_html()
    • pd.read_json()

Loading separate files

1
2
3

import pandas as pd
dataframe0 = pd.read_csv('sales-jan-2015.csv')
dataframe1 = pd.read_csv('sales-feb-2015.csv')

Using a loop

1
2
3
4

filenames = ['sales-jan-2015.csv', 'sales-feb-2015.csv']
dataframes = []
for f in filenames:
    dataframes.append(pd.read_csv(f))

Using a comprehension

1
2

filenames = ['sales-jan-2015.csv', 'sales-feb-2015.csv']
dataframes = [pd.read_csv(f) for f in filenames]

Using glob

1
2
3

from glob import glob
filenames = glob('sales*.csv')
dataframes = [pd.read_csv(f) for f in filenames]

Exercises

Reading DataFrames from multiple files

When data is spread among several files, you usually invoke pandas’ read_csv() (or a similar data import function) multiple times to load the data into several DataFrames.

The data files for this example have been derived from a list of Olympic medals awarded between 1896 & 2008 compiled by the Guardian.

The column labels of each DataFrame are NOC, Country, & Total where NOC is a three-letter code for the name of the country and Total is the number of medals of that type won (bronze, silver, or gold).

Instructions

  • Import pandas as pd.
  • Read the file 'Bronze.csv' into a DataFrame called bronze.
  • Read the file 'Silver.csv' into a DataFrame called silver.
  • Read the file 'Gold.csv' into a DataFrame called gold.
  • Print the first 5 rows of the DataFrame gold. This has been done for you, so hit 'Submit Answer' to see the results.
1
2
3
4
5
6
7
8
9
10
11

# Read 'Bronze.csv' into a DataFrame: bronze
bronze = pd.read_csv(so_bronze_file)

# Read 'Silver.csv' into a DataFrame: silver
silver = pd.read_csv(so_silver_file)

# Read 'Gold.csv' into a DataFrame: gold
gold = pd.read_csv(so_gold_file)

# Print the first five rows of gold
gold.head()
NOCCountryTotal
0USAUnited States2088.0
1URSSoviet Union838.0
2GBRUnited Kingdom498.0
3FRAFrance378.0
4GERGermany407.0

Reading DataFrames from multiple files in a loop

As you saw in the video, loading data from multiple files into DataFrames is more efficient in a loop or a list comprehension.

Notice that this approach is not restricted to working with CSV files. That is, even if your data comes in other formats, as long as pandas has a suitable data import function, you can apply a loop or comprehension to generate a list of DataFrames imported from the source files.

Here, you’ll continue working with The Guardian’s Olympic medal dataset.

Instructions

  • Create a list of file names called filenames with three strings 'Gold.csv', 'Silver.csv', & 'Bronze.csv'. This has been done for you.
  • Use a for loop to create another list called dataframes containing the three DataFrames loaded from filenames:
    • Iterate over filenames.
    • Read each CSV file in filenames into a DataFrame and append it to dataframes by using pd.read_csv() inside a call to .append().
  • Print the first 5 rows of the first DataFrame of the list dataframes. This has been done for you, so hit ‘Submit Answer’ to see the results.
1
2
3
4
5
6
7
8
9
10

# Create the list of file names: filenames
filenames = [so_bronze_file, so_silver_file, so_gold_file]

# Create the list of three DataFrames: dataframes
dataframes = []
for filename in filenames:
    dataframes.append(pd.read_csv(filename))

# Print top 5 rows of 1st DataFrame in dataframes
dataframes[0].head()
NOCCountryTotal
0USAUnited States1052.0
1URSSoviet Union584.0
2GBRUnited Kingdom505.0
3FRAFrance475.0
4GERGermany454.0

Combining DataFrames from multiple data files

In this exercise, you’ll combine the three DataFrames from earlier exercises - gold, silver, & bronze - into a single DataFrame called medals. The approach you’ll use here is clumsy. Later on in the course, you’ll see various powerful methods that are frequently used in practice for concatenating or merging DataFrames.

Remember, the column labels of each DataFrame are NOC, Country, and Total, where NOC is a three-letter code for the name of the country and Total is the number of medals of that type won.

Instructions

  • Construct a copy of the DataFrame gold called medals using the .copy() method.
  • Create a list called new_labels with entries 'NOC', 'Country', & 'Gold'. This is the same as the column labels from gold with the column label 'Total' replaced by 'Gold'.
  • Rename the columns of medals by assigning new_labels to medals.columns.
  • Create new columns 'Silver' and 'Bronze' in medals using silver['Total'] & bronze['Total'].
  • Print the top 5 rows of the final DataFrame medals. This has been done for you, so hit ‘Submit Answer’ to see the result!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# Make a copy of gold: medals
medals = gold.copy()

# Create list of new column labels: new_labels
new_labels = ['NOC', 'Country', 'Gold']

# Rename the columns of medals using new_labels
medals.columns = new_labels

# Add columns 'Silver' & 'Bronze' to medals
medals['Silver'] = silver['Total']
medals['Bronze'] = bronze['Total']

# Print the head of medals
medals.head()
NOCCountryGoldSilverBronze
0USAUnited States2088.01195.01052.0
1URSSoviet Union838.0627.0584.0
2GBRUnited Kingdom498.0591.0505.0
3FRAFrance378.0461.0475.0
4GERGermany407.0350.0454.0
1

del bronze, silver, gold, dataframes, medals

Reindexing DataFrames

“Indexes” vs. “Indices”

  • indices: many index labels within Index data structures
  • indexes: many pandas Index data structures

Importing weather data

1
2
3

import pandas as pd
w_mean = pd.read_csv('quarterly_mean_temp.csv', index_col='Month')
w_max = pd.read_csv('quarterly_max_temp.csv', index_col='Month')

Examining the data

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

print(w_mean)
        Mean TemperatureF
Month
Apr     61.956044
Jan     32.133333
Jul     68.934783
Oct     43.434783

print(w_max)
        Max TemperatureF
Month
Jan     68
Apr     89
Jul     91
Oct     84

The DataFrame indexes

1
2
3
4
5
6
7
8

print(w_mean.index)
Index(['Apr', 'Jan', 'Jul', 'Oct'], dtype='object', name='Month')

print(w_max.index)
Index(['Jan', 'Apr', 'Jul', 'Oct'], dtype='object', name='Month')

print(type(w_mean.index))
<class 'pandas.indexes.base.Index'>

Using .reindex()

1
2
3
4
5
6
7
8
9
10

ordered = ['Jan', 'Apr', 'Jul', 'Oct']
w_mean2 = w_mean.reindex(ordered)
print(w_mean2)

        Mean TemperatureF
Month
Jan     32.133333
Apr     61.956044
Jul     68.934783
Oct     43.434783

Using .sort_index()

1
2
3
4
5
6
7

w_mean2.sort_index()
        Mean TemperatureF
Month
Apr     61.956044
Jan     32.133333
Jul     68.934783
Oct     43.434783

Reindex from a DataFrame Index

1
2
3
4
5
6
7

w_mean.reindex(w_max.index)
        Mean TemperatureF
Month
Jan     32.133333
Apr     61.956044
Jul     68.934783
Oct     43.434783

Reindexing with missing labels

1
2
3
4
5
6
7

w_mean3 = w_mean.reindex(['Jan', 'Apr', 'Dec'])
print(w_mean3)
        Mean TemperatureF
Month
Jan     32.133333
Apr     61.956044
Dec     NaN

Reindex from a DataFrame Index

1
2
3
4
5
6
7
8
9
10
11
12

w_max.reindex(w_mean3.index)
        Max TemperatureF
Month
Jan     68.0
Apr     89.0
Dec     NaN

w_max.reindex(w_mean3.index).dropna()
        Max TemperatureF
Month
Jan     68.0
Apr     89.0

Order matters

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

w_max.reindex(w_mean.index)
        Max TemperatureF
Month
Apr     89
Jan     68
Jul     91
Oct     84

w_mean.reindex(w_max.index)
        Mean TemperatureF
Month
Jan     32.133333
Apr     61.956044
Jul     68.934783
Oct     43.434783

Exercises

Sorting DataFrame with the Index & columns

It is often useful to rearrange the sequence of the rows of a DataFrame by sorting. You don’t have to implement these yourself; the principal methods for doing this are .sort_index() and .sort_values().

In this exercise, you’ll use these methods with a DataFrame of temperature values indexed by month names. You’ll sort the rows alphabetically using the Index and numerically using a column. Notice, for this data, the original ordering is probably most useful and intuitive: the purpose here is for you to understand what the sorting methods do.

Instructions

  • Read 'monthly_max_temp.csv' into a DataFrame called weather1 with 'Month' as the index.
  • Sort the index of weather1 in alphabetical order using the .sort_index() method and store the result in weather2.
  • Sort the index of weather1 in reverse alphabetical order by specifying the additional keyword argument ascending=False inside .sort_index().
  • Use the .sort_values() method to sort weather1 in increasing numerical order according to the values of the column 'Max TemperatureF'.
1
2

monthly_max_temp = {'Month': ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'],
                    'Max TemperatureF': [68, 60, 68, 84, 88, 89, 91, 86, 90, 84, 72, 68]}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

# Read 'monthly_max_temp.csv' into a DataFrame: weather1
# weather1 = pd.read_csv('monthly_max_temp.csv', index_col='Month')
weather1 = pd.DataFrame.from_dict(monthly_max_temp)
weather1.set_index('Month', inplace=True)

# Print the head of weather1
print(weather1.head())

# Sort the index of weather1 in alphabetical order: weather2
weather2 = weather1.sort_index()

# Print the head of weather2
print(weather2.head())

# Sort the index of weather1 in reverse alphabetical order: weather3
weather3 = weather1.sort_index(ascending=False)

# Print the head of weather3
print(weather3.head())

# Sort weather1 numerically using the values of 'Max TemperatureF': weather4
weather4 = weather1.sort_values(by='Max TemperatureF')

# Print the head of weather4
print(weather4.head())

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

       Max TemperatureF
Month                  
Jan                  68
Feb                  60
Mar                  68
Apr                  84
May                  88
       Max TemperatureF
Month                  
Apr                  84
Aug                  86
Dec                  68
Feb                  60
Jan                  68
       Max TemperatureF
Month                  
Sep                  90
Oct                  84
Nov                  72
May                  88
Mar                  68
       Max TemperatureF
Month                  
Feb                  60
Jan                  68
Mar                  68
Dec                  68
Nov                  72

Reindexing DataFrame from a list

Sorting methods are not the only way to change DataFrame Indexes. There is also the .reindex() method.

In this exercise, you’ll reindex a DataFrame of quarterly-sampled mean temperature values to contain monthly samples (this is an example of upsampling or increasing the rate of samples, which you may recall from the pandas Foundations course).

The original data has the first month’s abbreviation of the quarter (three-month interval) on the Index, namely Apr, Jan, Jul, and Oct. This data has been loaded into a DataFrame called weather1 and has been printed in its entirety in the IPython Shell. Notice it has only four rows (corresponding to the first month of each quarter) and that the rows are not sorted chronologically.

You’ll initially use a list of all twelve month abbreviations and subsequently apply the .ffill() method to forward-fill the null entries when upsampling. This list of month abbreviations has been pre-loaded as year.

Instructions

  • Reorder the rows of weather1 using the .reindex() method with the list year as the argument, which contains the abbreviations for each month.
  • Reorder the rows of weather1 just as you did above, this time chaining the .ffill() method to replace the null values with the last preceding non-null value.
1
2
3
4
5

monthly_max_temp = {'Month': ['Jan', 'Apr', 'Jul', 'Oct'],
                    'Max TemperatureF': [32.13333, 61.956044, 68.934783, 43.434783]}
weather1 = pd.DataFrame.from_dict(monthly_max_temp)
weather1.set_index('Month', inplace=True)
weather1
Max TemperatureF
Month
Jan32.133330
Apr61.956044
Jul68.934783
Oct43.434783
1
2
3
4
5
6
7

year = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']

# Reindex weather1 using the list year: weather2
weather2 = weather1.reindex(year)

# Print weather2
weather2
Max TemperatureF
Month
Jan32.133330
FebNaN
MarNaN
Apr61.956044
MayNaN
JunNaN
Jul68.934783
AugNaN
SepNaN
Oct43.434783
NovNaN
DecNaN
1
2
3
4
5

# Reindex weather1 using the list year with forward-fill: weather3
weather3 = weather1.reindex(year).ffill()

# Print weather3
weather3
Max TemperatureF
Month
Jan32.133330
Feb32.133330
Mar32.133330
Apr61.956044
May61.956044
Jun61.956044
Jul68.934783
Aug68.934783
Sep68.934783
Oct43.434783
Nov43.434783
Dec43.434783

Reindexing DataFrame using another DataFrame Index

Another common technique is to reindex a DataFrame using the Index of another DataFrame. The DataFrame .reindex() method can accept the Index of a DataFrame or Series as input. You can access the Index of a DataFrame with its .index attribute.

The Baby Names Dataset from data.gov summarizes counts of names (with genders) from births registered in the US since 1881. In this exercise, you will start with two baby-names DataFrames names_1981 and names_1881 loaded for you.

The DataFrames names_1981 and names_1881 both have a MultiIndex with levels name and gender giving unique labels to counts in each row. If you’re interested in seeing how the MultiIndexes were set up, names_1981 and names_1881 were read in using the following commands:

1
2

names_1981 = pd.read_csv('names1981.csv', header=None, names=['name','gender','count'], index_col=(0,1))
names_1881 = pd.read_csv('names1881.csv', header=None, names=['name','gender','count'], index_col=(0,1))

As you can see by looking at their shapes, which have been printed in the IPython Shell, the DataFrame corresponding to 1981 births is much larger, reflecting the greater diversity of names in 1981 as compared to 1881.

Your job here is to use the DataFrame .reindex() and .dropna() methods to make a DataFrame common_names counting names from 1881 that were still popular in 1981.

Instructions

  • Create a new DataFrame common_names by reindexing names_1981 using the Index of the DataFrame names_1881 of older names.
  • Print the shape of the new common_names DataFrame. This has been done for you. It should be the same as that of names_1881.
  • Drop the rows of common_names that have null counts using the .dropna() method. These rows correspond to names that fell out of fashion between 1881 & 1981.
  • Print the shape of the reassigned common_names DataFrame. This has been done for you, so hit ‘Submit Answer’ to see the result!
1
2

names_1981 = pd.read_csv(baby_1981_file, header=None, names=['name', 'gender', 'count'], index_col=(0,1))
names_1981.head()
count
namegender
JenniferF57032
JessicaF42519
AmandaF34370
SarahF28162
MelissaF28003
1
2

names_1881 = pd.read_csv(baby_1881_file, header=None, names=['name','gender','count'], index_col=(0,1))
names_1881.head()
count
namegender
MaryF6919
AnnaF2698
EmmaF2034
ElizabethF1852
MargaretF1658
1
2
3
4
5

# Reindex names_1981 with index of names_1881: common_names
common_names = names_1981.reindex(names_1881.index)

# Print shape of common_names
common_names.shape

1

(1935, 1)

1
2
3
4
5

# Drop rows with null counts: common_names
common_names = common_names.dropna()

# Print shape of new common_names
common_names.shape

1

(1587, 1)

1

common_names.head(10)
count
namegender
MaryF11030.0
AnnaF5182.0
EmmaF532.0
ElizabethF20168.0
MargaretF2791.0
MinnieF56.0
IdaF206.0
AnnieF973.0
BerthaF209.0
AliceF745.0
1

del weather1, weather2, weather3, weather4, common_names, names_1881, names_1981

Arithmetic with Series & DataFrames

Loading weather data

1
2
3
4
5
6
7
8
9
10
11
12
13

import pandas as pd
weather = pd.read_csv('pittsburgh2013.csv', index_col='Date', parse_dates=True)
weather.loc['2013-7-1':'2013-7-7', 'PrecipitationIn']

Date
2013-07-01 0.18
2013-07-02 0.14
2013-07-03 0.00
2013-07-04 0.25
2013-07-05 0.02
2013-07-06 0.06
2013-07-07 0.10
Name: PrecipitationIn, dtype: float64

Scalar multiplication

1
2
3
4
5
6
7
8
9
10
11

weather.loc['2013-07-01':'2013-07-07', 'PrecipitationIn'] * 2.54

Date
2013-07-01 0.4572
2013-07-02 0.3556
2013-07-03 0.0000
2013-07-04 0.6350
2013-07-05 0.0508
2013-07-06 0.1524
2013-07-07 0.2540
Name: PrecipitationIn, dtype: float64

Absolute temperature range

1
2
3
4
5
6
7
8
9
10
11

week1_range = weather.loc['2013-07-01':'2013-07-07', ['Min TemperatureF', 'Max TemperatureF']]
print(week1_range)
Min TemperatureF Max TemperatureF
Date
2013-07-01 66 79
2013-07-02 66 84
2013-07-03 71 86
2013-07-04 70 86
2013-07-05 69 86
2013-07-06 70 89
2013-07-07 70 77

Average temperature

1
2
3
4
5
6
7
8
9
10
11

week1_mean = weather.loc['2013-07-01':'2013-07-07', 'Mean TemperatureF']
print(week1_mean)
Date
2013-07-01 72
2013-07-02 74
2013-07-03 78
2013-07-04 77
2013-07-05 76
2013-07-06 78
2013-07-07 72
Name: Mean TemperatureF, dtype: int64

Relative temperature range

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

week1_range / week1_mean
RuntimeWarning: Cannot compare type 'Timestamp' with type 'str', sort order is
undefined for incomparable objects
return this.join(other, how=how, return_indexers=return_indexers)

2013-07-01 00:00:00 2013-07-02 00:00:00 2013-07-03 00:00:00 \
Date
2013-07-01 NaN NaN NaN
2013-07-02 NaN NaN NaN
2013-07-03 NaN NaN NaN
2013-07-04 NaN NaN NaN
2013-07-05 NaN NaN NaN
2013-07-06 NaN NaN NaN
2013-07-07 NaN NaN NaN
2013-07-04 00:00:00 2013-07-05 00:00:00 2013-07-06 00:00:00 \
Date
2013-07-01 NaN NaN NaN
... ...

Relative temperature range

1
2
3
4
5
6
7
8
9
10
11

week1_range.divide(week1_mean, axis='rows')

Min TemperatureF Max TemperatureF
Date
2013-07-01 0.916667 1.097222
2013-07-02 0.891892 1.135135
2013-07-03 0.910256 1.102564
2013-07-04 0.909091 1.116883
2013-07-05 0.907895 1.131579
2013-07-06 0.897436 1.141026
2013-07-07 0.972222 1.069444

Percentage changes

1
2
3
4
5
6
7
8
9
10
11

week1_mean.pct_change() * 100

Date
2013-07-01 NaN
2013-07-02 2.777778
2013-07-03 5.405405
2013-07-04 -1.282051
2013-07-05 -1.298701
2013-07-06 2.631579
2013-07-07 -7.692308
Name: Mean TemperatureF, dtype: float64

Bronze Olympic medals

1
2
3
4
5
6
7
8
9

bronze = pd.read_csv('bronze_top5.csv', index_col=0)
print(bronze)
Total
Country
United States 1052.0
Soviet Union 584.0
United Kingdom 505.0
France 475.0
Germany 454.0

Silver Olympic medals

1
2
3
4
5
6
7
8
9

silver = pd.read_csv('silver_top5.csv', index_col=0)
print(silver)
Total
Country
United States 1195.0
Soviet Union 627.0
United Kingdom 591.0
France 461.0
Italy 394.0

Gold Olympic medals

1
2
3
4
5
6
7
8
9

gold = pd.read_csv('gold_top5.csv', index_col=0)
print(gold)
Total
Country
United States 2088.0
Soviet Union 838.0
United Kingdom 498.0
Italy 460.0
Germany 407.0

Adding bronze, silver

1
2
3
4
5
6
7
8
9
10

bronze + silver

Country
France 936.0
Germany NaN
Italy NaN
Soviet Union 1211.0
United Kingdom 1096.0
United States 2247.0
Name: Total, dtype: float64

Adding bronze, silver

1
2
3
4
5
6
7
8
9
10
11
12
13
14

bronze + silver

Country
France 936.0
Germany NaN
Italy NaN
Soviet Union 1211.0
United Kingdom 1096.0
United States 2247.0
Name: Total, dtype: float64
In [22]: print(bronze['United States'])
1052.0
In [23]: print(silver['United States'])
1195.0

Using the .add() method

1
2
3
4
5
6
7
8
9
10

bronze.add(silver)

Country
France 936.0
Germany NaN
Italy NaN
Soviet Union 1211.0
United Kingdom 1096.0
United States 2247.0
Name: Total, dtype: float64

Using a fill_value

1
2
3
4
5
6
7
8
9
10

bronze.add(silver, fill_value=0)

Country
France 936.0
Germany 454.0
Italy 394.0
Soviet Union 1211.0
United Kingdom 1096.0
United States 2247.0
Name: Total, dtype: float64

Adding bronze, silver, gold

1
2
3
4
5
6
7
8
9
10

bronze + silver + gold

Country
France NaN
Germany NaN
Italy NaN
Soviet Union 2049.0
United Kingdom 1594.0
United States 4335.0
Name: Total, dtype: float64

Chaining .add()

1
2
3
4
5
6
7
8
9
10

bronze.add(silver, fill_value=0).add(gold, fill_value=0)

Country
France 936.0
Germany 861.0
Italy 854.0
Soviet Union 2049.0
United Kingdom 1594.0
United States 4335.0
Name: Total, dtype: float64

Exercises

Adding unaligned DataFrames

The DataFrames january and february, which have been printed in the IPython Shell, represent the sales a company made in the corresponding months.

The Indexes in both DataFrames are called Company, identifying which company bought that quantity of units. The column Units is the number of units sold.

If you were to add these two DataFrames by executing the command total = january + february, how many rows would the resulting DataFrame have? Try this in the IPython Shell and find out for yourself.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

jan_dict = {'Company': ['Acme Corporation', 'Hooli', 'Initech', 'Mediacore', 'Streeplex'],
            'Units': [19, 17, 20, 10, 13]}
feb_dict = {'Company': ['Acme Corporation', 'Hooli', 'Mediacore', 'Vandelay Inc'],
            'Units': [15, 3, 12, 25]}

january = pd.DataFrame.from_dict(jan_dict)
january.set_index('Company', inplace=True)
print(january)

february = pd.DataFrame.from_dict(feb_dict)
february.set_index('Company', inplace=True)
print('\n', february, '\n')

print(january + february)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

                  Units
Company                
Acme Corporation     19
Hooli                17
Initech              20
Mediacore            10
Streeplex            13

                   Units
Company                
Acme Corporation     15
Hooli                 3
Mediacore            12
Vandelay Inc         25 

                  Units
Company                
Acme Corporation   34.0
Hooli              20.0
Initech             NaN
Mediacore          22.0
Streeplex           NaN
Vandelay Inc        NaN

Broadcasting in Arithmetic formulas

In this exercise, you’ll work with weather data pulled from wunderground.com. The DataFrame weather has been pre-loaded along with pandas as pd. It has 365 rows (observed each day of the year 2013 in Pittsburgh, PA) and 22 columns reflecting different weather measurements each day.

You’ll subset a collection of columns related to temperature measurements in degrees Fahrenheit, convert them to degrees Celsius, and relabel the columns of the new DataFrame to reflect the change of units.

Remember, ordinary arithmetic operators (like +, -, *, and /) broadcast scalar values to conforming DataFrames when combining scalars & DataFrames in arithmetic expressions. Broadcasting also works with pandas Series and NumPy arrays.

Instructions

  • Create a new DataFrame temps_f by extracting the columns 'Min TemperatureF', 'Mean TemperatureF', & 'Max TemperatureF' from weather as a new DataFrame temps_f. To do this, pass the relevant columns as a list to weather[].
  • Create a new DataFrame temps_c from temps_f using the formula (temps_f - 32) * 5/9.
  • Rename the columns of temps_c to replace 'F' with 'C' using the .str.replace('F', 'C') method on temps_c.columns.
  • Print the first 5 rows of DataFrame temps_c. This has been done for you, so hit ‘Submit Answer’ to see the result!
1
2
3

weather = pd.read_csv(pitts_file)
weather.set_index('Date', inplace=True)
weather.head(3)
Max TemperatureFMean TemperatureFMin TemperatureFMax Dew PointFMeanDew PointFMin DewpointFMax HumidityMean HumidityMin HumidityMax Sea Level PressureInMean Sea Level PressureInMin Sea Level PressureInMax VisibilityMilesMean VisibilityMilesMin VisibilityMilesMax Wind SpeedMPHMean Wind SpeedMPHMax Gust SpeedMPHPrecipitationInCloudCoverEventsWindDirDegrees
Date
2013-1-1322821302716100897730.1030.0129.941062108NaN0.08Snow277
2013-1-225211714121077675530.2730.1830.08101010145NaN0.04NaN272
2013-1-33224161915977675630.2530.2130.1610101017826.00.03NaN229
1
2
3
4
5
6
7
8
9
10
11

# Extract selected columns from weather as new DataFrame: temps_f
temps_f = weather[['Min TemperatureF', 'Mean TemperatureF', 'Max TemperatureF']]

# Convert temps_f to celsius: temps_c
temps_c = (temps_f - 32) * 5/9

# Rename 'F' in column names with 'C': temps_c.columns
temps_c.columns = temps_c.columns.str.replace('F', 'C')

# Print first 5 rows of temps_c
temps_c.head()
Min TemperatureCMean TemperatureCMax TemperatureC
Date
2013-1-1-6.111111-2.2222220.000000
2013-1-2-8.333333-6.111111-3.888889
2013-1-3-8.888889-4.4444440.000000
2013-1-4-2.777778-2.222222-1.111111
2013-1-5-3.888889-1.1111111.111111

Computing percentage growth of GDP

Your job in this exercise is to compute the yearly percent-change of US GDP (Gross Domestic Product) since 2008.

The data has been obtained from the Federal Reserve Bank of St. Louis and is available in the file GDP.csv, which contains quarterly data; you will resample it to annual sampling and then compute the annual growth of GDP. For a refresher on resampling, check out the relevant material from pandas Foundations.

Instructions

  • Read the file 'GDP.csv' into a DataFrame called gdp.
  • Use parse_dates=True and index_col='DATE'.
  • Create a DataFrame post2008 by slicing gdp such that it comprises all rows from 2008 onward.
  • Print the last 8 rows of the slice post2008. This has been done for you. This data has quarterly frequency so the indices are separated by three-month intervals.
  • Create the DataFrame yearly by resampling the slice post2008 by year. Remember, you need to chain .resample() (using the alias 'A' for annual frequency) with some kind of aggregation; you will use the aggregation method .last() to select the last element when resampling.
  • Compute the percentage growth of the resampled DataFrame yearly with .pct_change() * 100.
1
2
3

# Read 'GDP.csv' into a DataFrame: gdp
gdp = pd.read_csv(gdp_usa_file, parse_dates=True, index_col='DATE')
gdp.head()
VALUE
DATE
1947-01-01243.1
1947-04-01246.3
1947-07-01250.1
1947-10-01260.3
1948-01-01266.2
1
2
3
4
5

# Slice all the gdp data from 2008 onward: post2008
post2008 = gdp.loc['2008-01-01':]

# Print the last 8 rows of post2008
post2008.tail(8)
VALUE
DATE
2014-07-0117569.4
2014-10-0117692.2
2015-01-0117783.6
2015-04-0117998.3
2015-07-0118141.9
2015-10-0118222.8
2016-01-0118281.6
2016-04-0118436.5
1
2
3
4
5

# Resample post2008 by year, keeping last(): yearly
yearly = post2008.resample('YE').last()

# Print yearly
yearly
VALUE
DATE
2008-12-3114549.9
2009-12-3114566.5
2010-12-3115230.2
2011-12-3115785.3
2012-12-3116297.3
2013-12-3116999.9
2014-12-3117692.2
2015-12-3118222.8
2016-12-3118436.5
1
2
3
4
5

# Compute percentage growth of yearly: yearly['growth']
yearly['growth'] = yearly.pct_change() * 100

# Print yearly again
yearly
VALUEgrowth
DATE
2008-12-3114549.9NaN
2009-12-3114566.50.114090
2010-12-3115230.24.556345
2011-12-3115785.33.644732
2012-12-3116297.33.243524
2013-12-3116999.94.311144
2014-12-3117692.24.072377
2015-12-3118222.82.999062
2016-12-3118436.51.172707

Converting currency of stocks

In this exercise, stock prices in US Dollars for the S&P 500 in 2015 have been obtained from Yahoo Finance. The files sp500.csv for sp500 and exchange.csv for the exchange rates are both provided to you.

Using the daily exchange rate to Pounds Sterling, your task is to convert both the Open and Close column prices.

Instructions

  • Read the DataFrames sp500 & exchange from the files 'sp500.csv' & 'exchange.csv' respectively..
  • Use parse_dates=True and index_col='Date'.
  • Extract the columns 'Open' & 'Close' from the DataFrame sp500 as a new DataFrame dollars and print the first 5 rows.
  • Construct a new DataFrame pounds by converting US dollars to British pounds. You’ll use the .multiply() method of dollars with exchange['GBP/USD'] and axis='rows'
  • Print the first 5 rows of the new DataFrame pounds. This has been done for you, so hit ‘Submit Answer’ to see the results!.
1
2
3

# Read 'sp500.csv' into a DataFrame: sp500
sp500 = pd.read_csv(sp500_file, parse_dates=True, index_col='Date')
sp500.head()
OpenHighLowCloseAdj CloseVolumetkr
Date
1980-01-020.0108.430000105.290001105.760002105.76000240610000^gspc
1980-01-030.0106.080002103.260002105.220001105.22000150480000^gspc
1980-01-040.0107.080002105.089996106.519997106.51999739130000^gspc
1980-01-070.0107.800003105.800003106.809998106.80999844500000^gspc
1980-01-080.0109.290001106.290001108.949997108.94999753390000^gspc
1
2
3

# Read 'exchange.csv' into a DataFrame: exchange
exchange = pd.read_csv(exch_rates_file, parse_dates=True, index_col='Date')
exchange.head()
GBP/USD
Date
2015-01-020.65101
2015-01-050.65644
2015-01-060.65896
2015-01-070.66344
2015-01-080.66151
1
2
3
4
5

# Subset 'Open' & 'Close' columns from sp500: dollars
dollars = sp500[['Open', 'Close']]

# Print the head of dollars
dollars.head()
OpenClose
Date
1980-01-020.0105.760002
1980-01-030.0105.220001
1980-01-040.0106.519997
1980-01-070.0106.809998
1980-01-080.0108.949997
1
2
3
4
5

# Convert dollars to pounds: pounds
pounds = dollars.multiply(exchange['GBP/USD'], axis='rows')

# Print the head of pounds
pounds.head()
OpenClose
Date
1980-01-02NaNNaN
1980-01-03NaNNaN
1980-01-04NaNNaN
1980-01-07NaNNaN
1980-01-08NaNNaN
1

del january, february, feb_dict, jan_dict, weather, temps_f, temps_c, gdp, post2008, yearly, sp500, exchange, dollars, pounds

Concatenating Data

Having learned how to import multiple DataFrames and share information using Indexes, in this chapter you’ll learn how to perform database-style operations to combine DataFrames. In particular, you’ll learn about appending and concatenating DataFrames while working with a variety of real-world datasets.

Appending & concatenating Series

append()

  • .append(): Series & DataFrame method
  • Invocation:
  • s1.append(s2)
  • Stacks rows of s2 below s1
  • Method for Series & DataFrames

concat()

  • concat(): pandas module function
  • Invocation:
  • pd.concat([s1, s2, s3])
  • Can stack row-wise or column-wise

concat() & .append()

  • Equivalence of concat() & .append():
  • result1 = pd.concat([s1, s2, s3])
  • result2 = s1.append(s2).append(s3)
  • result1 == result2 elementwise

Series of US states

1
2
3
4
5

import pandas as pd
northeast = pd.Series(['CT', 'ME', 'MA', 'NH', 'RI', 'VT', 'NJ', 'NY', 'PA'])
south = pd.Series(['DE', 'FL', 'GA', 'MD', 'NC', 'SC', 'VA', 'DC', 'WV', 'AL', 'KY', 'MS', 'TN', 'AR', 'LA', 'OK', 'TX'])
midwest = pd.Series(['IL', 'IN', 'MN', 'MO', 'NE', 'ND', 'SD', 'IA', 'KS', 'MI', 'OH', 'WI'])
west = pd.Series(['AZ', 'CO', 'ID', 'MT',

Using .append()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

east = northeast.append(south)
print(east)
0 CT       7 DC
1 ME       8 WV
2 MA       9 AL
3 NH       10 KY
4 RI       11 MS
5 VT       12 TN
6 NJ       13 AR
7 NY       14 LA
8 PA       15 OK
0 DE       16 TX
1 FL       dtype: object
2 GA
3 MD
4 NC
5 SC
6 VA

The appended Index

1
2
3
4
5
6
7

print(east.index)
Int64Index([ 0, 1, 2, 3, 4, 5, 6, 7, 8, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16], dtype='int64')

print(east.loc[3])
3 NH
3 MD
dtype: object

Using .reset_index()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

new_east = northeast.append(south).reset_index(drop=True)
print(new_east.head(11))
0 CT
1 ME
2 MA
3 NH
4 RI
5 VT
6 NJ
7 NY
8 PA
9 DE
10 FL
dtype: object

print(new_east.index)
RangeIndex(start=0, stop=26, step=1)

Using concat()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

east = pd.concat([northeast, south])
print(east.head(11))
0 CT
1 ME
2 MA
3 NH
4 RI
5 VT
6 NJ
7 NY
8 PA
0 DE
1 FL
dtype: object
print(east.index)
Int64Index([ 0, 1, 2, 3, 4, 5, 6, 7, 8, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16], dtype='int64')

Using ignore_index

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

new_east = pd.concat([northeast, south], ignore_index=True)
print(new_east.head(11))
0 CT
1 ME
2 MA
3 NH
4 RI
5 VT
6 NJ
7 NY
8 PA
9 DE
10 FL
dtype: object
print(new_east.index)
RangeIndex(start=0, stop=26, step=1)

Exercises

Appending Series with nonunique Indices

The Series bronze and silver, which have been printed in the IPython Shell, represent the 5 countries that won the most bronze and silver Olympic medals respectively between 1896 & 2008. The Indexes of both Series are called Country and the values are the corresponding number of medals won.

If you were to run the command combined = bronze.append(silver), how many rows would combined have? And how many rows would combined.loc['United States'] return? Find out for yourself by running these commands in the IPython Shell.

Instructions

Possible Answers

  • combined has 5 rows and combined.loc[‘United States’] is empty (0 rows).
  • combined has 10 rows and combined.loc['United States'] has 2 rows.
  • combined has 6 rows and combined.loc[‘United States’] has 1 row.
  • combined has 5 rows and combined.loc[‘United States’] has 2 rows.
1
2

bronze = pd.read_csv(so_bronze5_file, index_col=0)
bronze
Total
Country
United States1052.0
Soviet Union584.0
United Kingdom505.0
France475.0
Germany454.0
1
2

silver = pd.read_csv(so_silver5_file, index_col=0)
silver
Total
Country
United States1195.0
Soviet Union627.0
United Kingdom591.0
France461.0
Italy394.0
1
2

combined = pd.concat([bronze, silver])
combined
Total
Country
United States1052.0
Soviet Union584.0
United Kingdom505.0
France475.0
Germany454.0
United States1195.0
Soviet Union627.0
United Kingdom591.0
France461.0
Italy394.0
1

combined.loc['United States']
Total
Country
United States1052.0
United States1195.0

Appending pandas Series

In this exercise, you’ll load sales data from the months January, February, and March into DataFrames. Then, you’ll extract Series with the 'Units' column from each and append them together with method chaining using .append().

To check that the stacking worked, you’ll print slices from these Series, and finally, you’ll add the result to figure out the total units sold in the first quarter.

Instructions

  • Read the files 'sales-jan-2015.csv', 'sales-feb-2015.csv' and 'sales-mar-2015.csv' into the DataFrames jan, feb, and mar respectively.
  • Use parse_dates=True and index_col='Date'.
  • Extract the 'Units' column of jan, feb, and mar to create the Series jan_units, feb_units, and mar_units respectively.
  • Construct the Series quarter1 by appending feb_units to jan_units and then appending mar_units to the result. Use chained calls to the .append() method to do this.
  • Verify that quarter1 has the individual Series stacked vertically. To do this:
  • Print the slice containing rows from jan 27, 2015 to feb 2, 2015.
  • Print the slice containing rows from feb 26, 2015 to mar 7, 2015.
  • Compute and print the total number of units sold from the Series quarter1. This has been done for you, so hit ‘Submit Answer’ to see the result!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

# Load 'sales-jan-2015.csv' into a DataFrame: jan
jan = pd.read_csv(sales_jan_2015_file, parse_dates=True, index_col='Date')

# Load 'sales-feb-2015.csv' into a DataFrame: feb
feb = pd.read_csv(sales_feb_2015_file, parse_dates=True, index_col='Date')

# Load 'sales-mar-2015.csv' into a DataFrame: mar
mar = pd.read_csv(sales_mar_2015_file, parse_dates=True, index_col='Date')

# Extract the 'Units' column from jan: jan_units
jan_units = jan['Units']

# Extract the 'Units' column from feb: feb_units
feb_units = feb['Units']

# Extract the 'Units' column from mar: mar_units
mar_units = mar['Units']

# Append feb_units and then mar_units to jan_units: quarter1
quarter1 = pd.concat([jan_units, feb_units, mar_units])

# Print the first slice from quarter1
display(quarter1.sort_index().loc['jan 27, 2015':'feb 2, 2015'])

# Print the second slice from quarter1
display(quarter1.sort_index().loc['feb 26, 2015':'mar 7, 2015'])

# Compute & print total sales in quarter1
display(quarter1.sum())

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

Date
2015-01-27 07:11:55    18
2015-02-02 08:33:01     3
2015-02-02 20:54:49     9
Name: Units, dtype: int64



Date
2015-02-26 08:57:45     4
2015-02-26 08:58:51     1
2015-03-06 02:03:56    17
2015-03-06 10:11:45    17
Name: Units, dtype: int64



642

Concatenating pandas Series along row axis

Having learned how to append Series, you’ll now learn how to achieve the same result by concatenating Series instead. You’ll continue to work with the sales data you’ve seen previously. This time, the DataFrames jan, feb, and mar have been pre-loaded.

Your job is to use pd.concat() with a list of Series to achieve the same result that you would get by chaining calls to .append().

You may be wondering about the difference between pd.concat() and pandas’ .append() method. One way to think of the difference is that .append() is a specific case of a concatenation, while pd.concat() gives you more flexibility, as you’ll see in later exercises.

Instructions

  • Create an empty list called units. This has been done for you.
    • Use a for loop to iterate over [jan, feb, mar]:
  • In each iteration of the loop, append the 'Units' column of each DataFrame to units.
    • Concatenate the Series contained in the list units into a longer Series called quarter1 using pd.concat().
  • Specify the keyword argument axis='rows' to stack the Series vertically.
  • Verify that quarter1 has the individual Series stacked vertically by printing slices. This has been done for you, so hit ‘Submit Answer’ to see the result!
1
2
3
4
5
6
7
8
9
10
11
12
13

# Initialize empty list: units
units = []

# Build the list of Series
for month in [jan, feb, mar]:
    units.append(month.Units)

# Concatenate the list: quarter1
quarter1 = pd.concat(units, axis='rows')

# Print slices from quarter1
print(quarter1.sort_index().loc['jan 27, 2015':'feb 2, 2015'])
print(quarter1.sort_index().loc['feb 26, 2015':'mar 7, 2015'])

1
2
3
4
5
6
7
8
9
10
11

Date
2015-01-27 07:11:55    18
2015-02-02 08:33:01     3
2015-02-02 20:54:49     9
Name: Units, dtype: int64
Date
2015-02-26 08:57:45     4
2015-02-26 08:58:51     1
2015-03-06 02:03:56    17
2015-03-06 10:11:45    17
Name: Units, dtype: int64

1

del bronze, silver, combined, jan, feb, mar, jan_units, feb_units, mar_units, quarter1

Appending & concatenating DataFrames

Loading population data

1
2
3

import pandas as pd
pop1 = pd.read_csv('population_01.csv', index_col=0)
pop2 = pd.read_csv('population_02.csv', index_col=0)

1
2
3
4
5
6
7

pop1_data = {'Zip Code ZCTA': [66407, 72732, 50579, 46421], '2010 Census Population': [479, 4716, 2405, 30670]}
pop2_data = {'Zip Code ZCTA': [12776, 76092, 98360, 49464], '2010 Census Population': [2180, 26669, 12221, 27481]}

pop1 = pd.DataFrame.from_dict(pop1_data)
pop1.set_index('Zip Code ZCTA', drop=True, inplace=True)
pop2 = pd.DataFrame.from_dict(pop2_data)
pop2.set_index('Zip Code ZCTA', drop=True, inplace=True)

Examining population data

1

pop1
2010 Census Population
Zip Code ZCTA
66407479
727324716
505792405
4642130670
1

pop2
2010 Census Population
Zip Code ZCTA
127762180
7609226669
9836012221
4946427481
1
2

print(type(pop1), pop1.shape)
print(type(pop2), pop2.shape)

1
2

<class 'pandas.core.frame.DataFrame'> (4, 1)
<class 'pandas.core.frame.DataFrame'> (4, 1)

Appending population DataFrames

1

pd.concat([pop1, pop2])
2010 Census Population
Zip Code ZCTA
66407479
727324716
505792405
4642130670
127762180
7609226669
9836012221
4946427481
1
2

print(pop1.index.name, pop1.columns)
print(pop2.index.name, pop2.columns)

1
2

Zip Code ZCTA Index(['2010 Census Population'], dtype='object')
Zip Code ZCTA Index(['2010 Census Population'], dtype='object')

Population & unemployment data

1
2

population = pd.read_csv('population_00.csv', index_col=0)
unemployment = pd.read_csv('unemployment_00.csv', index_col=0)

1
2
3
4
5
6
7

pop_data = {'Zip Code ZCTA': [57538, 59916, 37660, 2860], '2010 Census Population': [322, 130, 40038, 45199]}
emp_data = {'Zip': [2860, 46167, 1097, 80808], 'unemployment': [0.11, 0.02, 0.33, 0.07], 'participants': [34447, 4800, 42, 4310]}

population = pd.DataFrame.from_dict(pop_data)
population.set_index('Zip Code ZCTA', drop=True, inplace=True)
unemployment = pd.DataFrame.from_dict(emp_data)
unemployment.set_index('Zip', drop=True, inplace=True)

1

population
2010 Census Population
Zip Code ZCTA
57538322
59916130
3766040038
286045199
1

unemployment
unemploymentparticipants
Zip
28600.1134447
461670.024800
10970.3342
808080.074310

Appending population & unemployment

1

pd.concat([population, unemployment], sort=True)
2010 Census Populationparticipantsunemployment
57538322.0NaNNaN
59916130.0NaNNaN
3766040038.0NaNNaN
286045199.0NaNNaN
2860NaN34447.00.11
46167NaN4800.00.02
1097NaN42.00.33
80808NaN4310.00.07

Repeated index labels

1

pd.concat([population, unemployment], sort=True)
2010 Census Populationparticipantsunemployment
57538322.0NaNNaN
59916130.0NaNNaN
3766040038.0NaNNaN
286045199.0NaNNaN
2860NaN34447.00.11
46167NaN4800.00.02
1097NaN42.00.33
80808NaN4310.00.07

Concatenating rows

  • with axis=0, pd.concat is the same as population.append(unemployment, sort=True)
1

pd.concat([population, unemployment], axis=0, sort=True)
2010 Census Populationparticipantsunemployment
57538322.0NaNNaN
59916130.0NaNNaN
3766040038.0NaNNaN
286045199.0NaNNaN
2860NaN34447.00.11
46167NaN4800.00.02
1097NaN42.00.33
80808NaN4310.00.07

Concatenating column

  • outer join
1

pd.concat([population, unemployment], axis=1, sort=True)
2010 Census Populationunemploymentparticipants
1097NaN0.3342.0
286045199.00.1134447.0
3766040038.0NaNNaN
46167NaN0.024800.0
57538322.0NaNNaN
59916130.0NaNNaN
80808NaN0.074310.0
1

del pop1_data, pop2_data, pop1, pop2, pop_data, emp_data, population, unemployment

Exercises

Appending DataFrames with ignore_index

In this exercise, you’ll use the Baby Names Dataset (from data.gov) again. This time, both DataFrames names_1981 and names_1881 are loaded without specifying an Index column (so the default Indexes for both are RangeIndexes).

You’ll use the DataFrame .append() method to make a DataFrame combined_names. To distinguish rows from the original two DataFrames, you’ll add a 'year' column to each with the year (1881 or 1981 in this case). In addition, you’ll specify ignore_index=True so that the index values are not used along the concatenation axis. The resulting axis will instead be labeled 0, 1, ..., n-1, which is useful if you are concatenating objects where the concatenation axis does not have meaningful indexing information.

Instructions

  • Create a 'year' column in the DataFrames names_1881 and names_1981, with values of 1881 and 1981 respectively. Recall that assigning a scalar value to a DataFrame column broadcasts that value throughout.
  • Create a new DataFrame called combined_names by appending the rows of names_1981 underneath the rows of names_1881. Specify the keyword argument ignore_index=True to make a new RangeIndex of unique integers for each row.
  • Print the shapes of all three DataFrames. This has been done for you.
  • Extract all rows from combined_names that have the name 'Morgan'. To do this, use the .loc[] accessor with an appropriate filter. The relevant column of combined_names here is 'name'.
1
2

names_1881 = pd.read_csv(baby_1881_file, header=None, names=['name', 'gender', 'count'])
names_1981 = pd.read_csv(baby_1981_file, header=None, names=['name', 'gender', 'count'])

1

names_1981.head()
namegendercount
0JenniferF57032
1JessicaF42519
2AmandaF34370
3SarahF28162
4MelissaF28003
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# Add 'year' column to names_1881 and names_1981
names_1881['year'] = 1881
names_1981['year'] = 1981

# Append names_1981 after names_1881 with ignore_index=True: combined_names
combined_names = pd.concat([names_1881, names_1981], ignore_index=True, sort=False)

# Print shapes of names_1981, names_1881, and combined_names
print(names_1981.shape)
print(names_1881.shape)
print(combined_names.shape)

# Print all rows that contain the name 'Morgan'
combined_names[combined_names.name  == 'Morgan']

1
2
3

(19455, 4)
(1935, 4)
(21390, 4)
namegendercountyear
1283MorganM231881
2096MorganF17691981
14390MorganM7661981

Concatenating pandas DataFrames along column axis

The function pd.concat() can concatenate DataFrames horizontally as well as vertically (vertical is the default). To make the DataFrames stack horizontally, you have to specify the keyword argument axis=1 or axis='columns'.

In this exercise, you’ll use weather data with maximum and mean daily temperatures sampled at different rates (quarterly versus monthly). You’ll concatenate the rows of both and see that, where rows are missing in the coarser DataFrame, null values are inserted in the concatenated DataFrame. This corresponds to an outer join (which you will explore in more detail in later exercises).

The files 'quarterly_max_temp.csv' and 'monthly_mean_temp.csv' have been pre-loaded into the DataFrames weather_max and weather_mean respectively, and pandas has been imported as pd.

Instructions

  • Create a new DataFrame called weather by concatenating the DataFrames weather_max and weather_mean horizontally.
    • Pass the DataFrames to pd.concat() as a list and specify the keyword argument axis=1 to stack them horizontally.
  • Print the new DataFrame weather.
1
2
3
4
5
6
7
8

weather_mean_data = {'Mean TemperatureF': [53.1, 70., 34.93548387, 28.71428571, 32.35483871, 72.87096774, 70.13333333, 35., 62.61290323, 39.8, 55.4516129 , 63.76666667],
                     'Month': ['Apr', 'Aug', 'Dec', 'Feb', 'Jan', 'Jul', 'Jun', 'Mar', 'May', 'Nov', 'Oct', 'Sep']}
weather_max_data = {'Max TemperatureF': [68, 89, 91, 84], 'Month': ['Jan', 'Apr', 'Jul', 'Oct']}

weather_mean = pd.DataFrame.from_dict(weather_mean_data)
weather_mean.set_index('Month', inplace=True, drop=True)
weather_max = pd.DataFrame.from_dict(weather_max_data)
weather_max.set_index('Month', inplace=True, drop=True)

1

weather_max
Max TemperatureF
Month
Jan68
Apr89
Jul91
Oct84
1

weather_mean
Mean TemperatureF
Month
Apr53.100000
Aug70.000000
Dec34.935484
Feb28.714286
Jan32.354839
Jul72.870968
Jun70.133333
Mar35.000000
May62.612903
Nov39.800000
Oct55.451613
Sep63.766667
1
2
3
4
5

# Concatenate weather_max and weather_mean horizontally: weather
weather = pd.concat([weather_max, weather_mean], axis=1, sort=True)

# Print weather
weather
Max TemperatureFMean TemperatureF
Month
Apr89.053.100000
AugNaN70.000000
DecNaN34.935484
FebNaN28.714286
Jan68.032.354839
Jul91.072.870968
JunNaN70.133333
MarNaN35.000000
MayNaN62.612903
NovNaN39.800000
Oct84.055.451613
SepNaN63.766667

Reading multiple files to build a DataFrame

It is often convenient to build a large DataFrame by parsing many files as DataFrames and concatenating them all at once. You’ll do this here with three files, but, in principle, this approach can be used to combine data from dozens or hundreds of files.

Here, you’ll work with DataFrames compiled from The Guardian’s Olympic medal dataset.

pandas has been imported as pd and two lists have been pre-loaded: An empty list called medals, and medal_types, which contains the strings 'bronze', 'silver', and 'gold'.

Instructions

  • Iterate over medal_types in the for loop.
  • Inside the for loop:
    • Create file_name using string interpolation with the loop variable medal. This has been done for you. The expression "%s_top5.csv" % medal evaluates as a string with the value of medal replacing %s in the format string.
    • Create the list of column names called columns. This has been done for you.
    • Read file_name into a DataFrame called medal_df. Specify the keyword arguments header=0, index_col='Country', and names=columns to get the correct row and column Indexes.
    • Append medal_df to medals using the list .append() method.
  • Concatenate the list of DataFrames medals horizontally (using axis='columns') to create a single DataFrame called medals. Print it in its entirety.
1
2
3

top_five = data.glob('*_top5.csv')
for file in top_five:
    print(file)

1
2
3

D:\users\trenton\Dropbox\PythonProjects\DataCamp\data\merging-dataframes-with-pandas\summer_olympics_bronze_top5.csv
D:\users\trenton\Dropbox\PythonProjects\DataCamp\data\merging-dataframes-with-pandas\summer_olympics_gold_top5.csv
D:\users\trenton\Dropbox\PythonProjects\DataCamp\data\merging-dataframes-with-pandas\summer_olympics_silver_top5.csv

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

medal_types = ['bronze', 'silver', 'gold']
medal_list = list()

for medal in medal_types:

    # Create the file name: file_name
    file_name = data / f'summer_olympics_{medal}_top5.csv'
    
    # Create list of column names: columns
    columns = ['Country', medal]
    
    # Read file_name into a DataFrame: df
    medal_df = pd.read_csv(file_name, header=0, index_col='Country', names=columns)

    # Append medal_df to medals
    medal_list.append(medal_df)

# Concatenate medals horizontally: medals
medals = pd.concat(medal_list, axis='columns', sort=True)

# Print medals
medals
bronzesilvergold
Country
France475.0461.0NaN
Germany454.0NaN407.0
ItalyNaN394.0460.0
Soviet Union584.0627.0838.0
United Kingdom505.0591.0498.0
United States1052.01195.02088.0
1

del names_1881, names_1981, combined_names, weather_mean_data, weather_max_data, weather_mean, weather_max, weather, top_five, medals, medal_list

Concatenation, keys & MultiIndexes

Loading rainfall data

1
2
3
4
5

import pandas as pd
file1 = 'q1_rainfall_2013.csv'
rain2013 = pd.read_csv(file1, index_col='Month', parse_dates=True)
file2 = 'q1_rainfall_2014.csv'
rain2014 = pd.read_csv(file2, index_col='Month', parse_dates=True)

1
2
3
4
5
6
7

rain_2013_data = {'Month': ['Jan', 'Feb', 'Mar'], 'Precipitation': [0.096129, 0.067143, 0.061613]}
rain_2014_data = {'Month': ['Jan', 'Feb', 'Mar'], 'Precipitation': [0.050323, 0.082143, 0.070968]}

rain2013 = pd.DataFrame.from_dict(rain_2013_data)
rain2013.set_index('Month', inplace=True)
rain2014 = pd.DataFrame.from_dict(rain_2014_data)
rain2014.set_index('Month', inplace=True)

Examining rainfall data

1

rain2013
Precipitation
Month
Jan0.096129
Feb0.067143
Mar0.061613
1

rain2014
Precipitation
Month
Jan0.050323
Feb0.082143
Mar0.070968

Concatenating rows

1

pd.concat([rain2013, rain2014], axis=0)
Precipitation
Month
Jan0.096129
Feb0.067143
Mar0.061613
Jan0.050323
Feb0.082143
Mar0.070968

Using multi-index on rows

1
2

rain1314 = pd.concat([rain2013, rain2014], keys=[2013, 2014], axis=0)
rain1314
Precipitation
Month
2013Jan0.096129
Feb0.067143
Mar0.061613
2014Jan0.050323
Feb0.082143
Mar0.070968

Accessing a multi-index

1

rain1314.loc[2014]
Precipitation
Month
Jan0.050323
Feb0.082143
Mar0.070968

Concatenating columns

1
2

rain1314 = pd.concat([rain2013, rain2014], axis='columns')
rain1314
PrecipitationPrecipitation
Month
Jan0.0961290.050323
Feb0.0671430.082143
Mar0.0616130.070968

Using a multi-index on columns

1
2

rain1314 = pd.concat([rain2013, rain2014], keys=[2013, 2014], axis='columns')
rain1314
20132014
PrecipitationPrecipitation
Month
Jan0.0961290.050323
Feb0.0671430.082143
Mar0.0616130.070968
1

rain1314[2013]
Precipitation
Month
Jan0.096129
Feb0.067143
Mar0.061613

pd.concat() with dict

1
2
3

rain_dict = {2013: rain2013, 2014: rain2014}
rain1314 = pd.concat(rain_dict, axis='columns')
rain1314
20132014
PrecipitationPrecipitation
Month
Jan0.0961290.050323
Feb0.0671430.082143
Mar0.0616130.070968
1

del rain_2013_data, rain_2014_data, rain2013, rain2014, rain1314

Exercises

Concatenating vertically to get MultiIndexed rows

When stacking a sequence of DataFrames vertically, it is sometimes desirable to construct a MultiIndex to indicate the DataFrame from which each row originated. This can be done by specifying the keys parameter in the call to pd.concat(), which generates a hierarchical index with the labels from keys as the outermost index label. So you don’t have to rename the columns of each DataFrame as you load it. Instead, only the Index column needs to be specified.

Here, you’ll continue working with DataFrames compiled from The Guardian’s Olympic medal dataset. Once again, pandas has been imported as pd and two lists have been pre-loaded: An empty list called medals, and medal_types, which contains the strings 'bronze', 'silver', and 'gold'.

Instructions

  • Within the for loop:
    • Read file_name into a DataFrame called medal_df. Specify the index to be 'Country'.
    • Append medal_df to medals.
  • Concatenate the list of DataFrames medals into a single DataFrame called medals. Be sure to use the keyword argument keys=['bronze', 'silver', 'gold'] to create a vertically stacked DataFrame with a MultiIndex.
  • Print the new DataFrame medals. This has been done for you, so hit ‘Submit Answer’ to see the result!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

medal_types = ['bronze', 'silver', 'gold']
medal_list = list()

for medal in medal_types:

    # Create the file name: file_name
    file_name = data / f'summer_olympics_{medal}_top5.csv'
    
    # Read file_name into a DataFrame: medal_df
    medal_df = pd.read_csv(file_name, index_col='Country')
    
    # Append medal_df to medals
    medal_list.append(medal_df)
    
# Concatenate medals: medals
medals = pd.concat(medal_list, keys=['bronze', 'silver', 'gold'])

# Print medals in entirety
print(medals)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

                        Total
       Country               
bronze United States   1052.0
       Soviet Union     584.0
       United Kingdom   505.0
       France           475.0
       Germany          454.0
silver United States   1195.0
       Soviet Union     627.0
       United Kingdom   591.0
       France           461.0
       Italy            394.0
gold   United States   2088.0
       Soviet Union     838.0
       United Kingdom   498.0
       Italy            460.0
       Germany          407.0

Slicing MultiIndexed DataFrames

This exercise picks up where the last ended (again using The Guardian’s Olympic medal dataset).

You are provided with the MultiIndexed DataFrame as produced at the end of the preceding exercise. Your task is to sort the DataFrame and to use the pd.IndexSlice to extract specific slices. Check out this exercise from Manipulating DataFrames with pandas to refresh your memory on how to deal with MultiIndexed DataFrames.

pandas has been imported for you as pd and the DataFrame medals is already in your namespace.

Instructions

  • Create a new DataFrame medals_sorted with the entries of medals sorted. Use .sort_index(level=0) to ensure the Index is sorted suitably.
  • Print the number of bronze medals won by Germany and all of the silver medal data. This has been done for you.
  • Create an alias for pd.IndexSlice called idx. A slicer pd.IndexSlice is required when slicing on the inner level of a MultiIndex.
  • Slice all the data on medals won by the United Kingdom. To do this, use the .loc[] accessor with idx[:,'United Kingdom'], :.
1
2
3
4
5

# Sort the entries of medals: medals_sorted
medals_sorted = medals.sort_index(level=0)

# Print the number of Bronze medals won by Germany
print(medals_sorted.loc[('bronze','Germany')])

1
2

Total    454.0
Name: (bronze, Germany), dtype: float64

1
2

# Print data about silver medals
print(medals_sorted.loc['silver'])

1
2
3
4
5
6
7

                 Total
Country               
France           461.0
Italy            394.0
Soviet Union     627.0
United Kingdom   591.0
United States   1195.0

1
2
3
4
5

# Create alias for pd.IndexSlice: idx
idx = pd.IndexSlice

# Print all the data on medals won by the United Kingdom
medals_sorted.loc[idx[:, 'United Kingdom'], :]
Total
Country
bronzeUnited Kingdom505.0
goldUnited Kingdom498.0
silverUnited Kingdom591.0

Concatenating horizontally to get MultiIndexed columns

It is also possible to construct a DataFrame with hierarchically indexed columns. For this exercise, you’ll start with pandas imported and a list of three DataFrames called dataframes. All three DataFrames contain 'Company', 'Product', and 'Units' columns with a 'Date' column as the index pertaining to sales transactions during the month of February, 2015. The first DataFrame describes Hardware transactions, the second describes Software transactions, and the third, Service transactions.

Your task is to concatenate the DataFrames horizontally and to create a MultiIndex on the columns. From there, you can summarize the resulting DataFrame and slice some information from it.

Instructions

  • Construct a new DataFrame february with MultiIndexed columns by concatenating the list dataframes.
  • Use axis=1 to stack the DataFrames horizontally and the keyword argument keys=['Hardware', 'Software', 'Service'] to construct a hierarchical Index from each DataFrame.
  • Print summary information from the new DataFrame february using the .info() method. This has been done for you.
  • Create an alias called idx for pd.IndexSlice.
  • Extract a slice called slice_2_8 from february (using .loc[] & idx) that comprises rows between Feb. 2, 2015 to Feb. 8, 2015 from columns under 'Company'.
  • Print the slice_2_8. This has been done for you, so hit ‘Submit Answer’ to see the sliced data!
1
2
3
4
5
6

hw = pd.read_csv(sales_feb_hardware_file, index_col='Date')
sw = pd.read_csv(sales_feb_software_file, index_col='Date')
sv = pd.read_csv(sales_feb_service_file, index_col='Date')

dataframes = [hw, sw, sv]
dataframes

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

[                             Company   Product  Units
 Date                                                 
 2015-02-04 21:52:45  Acme Coporation  Hardware     14
 2015-02-07 22:58:10  Acme Coporation  Hardware      1
 2015-02-19 10:59:33        Mediacore  Hardware     16
 2015-02-02 20:54:49        Mediacore  Hardware      9
 2015-02-21 20:41:47            Hooli  Hardware      3,
                              Company   Product  Units
 Date                                                 
 2015-02-16 12:09:19            Hooli  Software     10
 2015-02-03 14:14:18          Initech  Software     13
 2015-02-02 08:33:01            Hooli  Software      3
 2015-02-05 01:53:06  Acme Coporation  Software     19
 2015-02-11 20:03:08          Initech  Software      7
 2015-02-09 13:09:55        Mediacore  Software      7
 2015-02-11 22:50:44            Hooli  Software      4
 2015-02-04 15:36:29        Streeplex  Software     13
 2015-02-21 05:01:26        Mediacore  Software      3,
                        Company  Product  Units
 Date                                          
 2015-02-26 08:57:45  Streeplex  Service      4
 2015-02-25 00:29:00    Initech  Service     10
 2015-02-09 08:57:30  Streeplex  Service     19
 2015-02-26 08:58:51  Streeplex  Service      1
 2015-02-05 22:05:03      Hooli  Service     10
 2015-02-19 16:02:58  Mediacore  Service     10]

1
2
3
4
5

# Concatenate dataframes: february
february = pd.concat(dataframes, axis=1, keys=['Hardware', 'Software', 'Service'], sort=True)

# Print february.info()
february.info()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

<class 'pandas.core.frame.DataFrame'>
Index: 20 entries, 2015-02-02 08:33:01 to 2015-02-26 08:58:51
Data columns (total 9 columns):
 #   Column               Non-Null Count  Dtype  
---  ------               --------------  -----  
 0   (Hardware, Company)  5 non-null      object 
 1   (Hardware, Product)  5 non-null      object 
 2   (Hardware, Units)    5 non-null      float64
 3   (Software, Company)  9 non-null      object 
 4   (Software, Product)  9 non-null      object 
 5   (Software, Units)    9 non-null      float64
 6   (Service, Company)   6 non-null      object 
 7   (Service, Product)   6 non-null      object 
 8   (Service, Units)     6 non-null      float64
dtypes: float64(3), object(6)
memory usage: 1.6+ KB

1

february
HardwareSoftwareService
CompanyProductUnitsCompanyProductUnitsCompanyProductUnits
Date
2015-02-02 08:33:01NaNNaNNaNHooliSoftware3.0NaNNaNNaN
2015-02-02 20:54:49MediacoreHardware9.0NaNNaNNaNNaNNaNNaN
2015-02-03 14:14:18NaNNaNNaNInitechSoftware13.0NaNNaNNaN
2015-02-04 15:36:29NaNNaNNaNStreeplexSoftware13.0NaNNaNNaN
2015-02-04 21:52:45Acme CoporationHardware14.0NaNNaNNaNNaNNaNNaN
2015-02-05 01:53:06NaNNaNNaNAcme CoporationSoftware19.0NaNNaNNaN
2015-02-05 22:05:03NaNNaNNaNNaNNaNNaNHooliService10.0
2015-02-07 22:58:10Acme CoporationHardware1.0NaNNaNNaNNaNNaNNaN
2015-02-09 08:57:30NaNNaNNaNNaNNaNNaNStreeplexService19.0
2015-02-09 13:09:55NaNNaNNaNMediacoreSoftware7.0NaNNaNNaN
2015-02-11 20:03:08NaNNaNNaNInitechSoftware7.0NaNNaNNaN
2015-02-11 22:50:44NaNNaNNaNHooliSoftware4.0NaNNaNNaN
2015-02-16 12:09:19NaNNaNNaNHooliSoftware10.0NaNNaNNaN
2015-02-19 10:59:33MediacoreHardware16.0NaNNaNNaNNaNNaNNaN
2015-02-19 16:02:58NaNNaNNaNNaNNaNNaNMediacoreService10.0
2015-02-21 05:01:26NaNNaNNaNMediacoreSoftware3.0NaNNaNNaN
2015-02-21 20:41:47HooliHardware3.0NaNNaNNaNNaNNaNNaN
2015-02-25 00:29:00NaNNaNNaNNaNNaNNaNInitechService10.0
2015-02-26 08:57:45NaNNaNNaNNaNNaNNaNStreeplexService4.0
2015-02-26 08:58:51NaNNaNNaNNaNNaNNaNStreeplexService1.0
1
2
3
4
5
6
7
8

# Assign pd.IndexSlice: idx
idx = pd.IndexSlice

# Create the slice: slice_2_8
slice_2_8 = february.loc['2015-02-02':'2015-02-08', idx[:, 'Company']]

# Print slice_2_8
slice_2_8
HardwareSoftwareService
CompanyCompanyCompany
Date
2015-02-02 08:33:01NaNHooliNaN
2015-02-02 20:54:49MediacoreNaNNaN
2015-02-03 14:14:18NaNInitechNaN
2015-02-04 15:36:29NaNStreeplexNaN
2015-02-04 21:52:45Acme CoporationNaNNaN
2015-02-05 01:53:06NaNAcme CoporationNaN
2015-02-05 22:05:03NaNNaNHooli
2015-02-07 22:58:10Acme CoporationNaNNaN

Concatenating DataFrames from a dict

You’re now going to revisit the sales data you worked with earlier in the chapter. Three DataFrames jan, feb, and mar have been pre-loaded for you. Your task is to aggregate the sum of all sales over the 'Company' column into a single DataFrame. You’ll do this by constructing a dictionary of these DataFrames and then concatenating them.

Instructions

  • Create a list called month_list consisting of the tuples ('january', jan), ('february', feb), and ('march', mar).
  • Create an empty dictionary called month_dict.
  • Inside the for loop:
    • Group month_data by 'Company' and use .sum() to aggregate.
  • Construct a new DataFrame called sales by concatenating the DataFrames stored in month_dict.
  • Create an alias for pd.IndexSlice and print all sales by 'Mediacore'. This has been done for you, so hit ‘Submit Answer’ to see the result!
1
2
3

jan = pd.read_csv(sales_jan_2015_file)
feb = pd.read_csv(sales_feb_2015_file)
mar = pd.read_csv(sales_mar_2015_file)

1

mar
DateCompanyProductUnits
02015-03-22 14:42:25MediacoreSoftware6
12015-03-12 18:33:06InitechService19
22015-03-22 03:58:28StreeplexSoftware8
32015-03-15 00:53:12HooliHardware19
42015-03-17 19:25:37HooliHardware10
52015-03-16 05:54:06MediacoreSoftware3
62015-03-25 10:18:10InitechHardware9
72015-03-25 16:42:42StreeplexHardware12
82015-03-26 05:20:04StreeplexSoftware3
92015-03-06 10:11:45MediacoreSoftware17
102015-03-22 21:14:39InitechHardware11
112015-03-17 19:38:12HooliHardware8
122015-03-28 19:20:38Acme CoporationService5
132015-03-13 04:41:32StreeplexHardware8
142015-03-06 02:03:56MediacoreSoftware17
152015-03-13 11:40:16InitechSoftware11
162015-03-27 08:29:45MediacoreSoftware6
172015-03-21 06:42:41MediacoreHardware19
182015-03-15 08:50:45InitechHardware18
192015-03-13 16:25:24StreeplexSoftware9
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

# Make the list of tuples: month_list
month_list = [('january', jan), ('february', feb), ('march', mar)]

# Create an empty dictionary: month_dict
month_dict = dict()

for month_name, month_data in month_list:

    # Group month_data: month_dict[month_name]
    month_dict[month_name] = month_data.groupby(['Company']).sum()

# Concatenate data in month_dict: sales
sales = pd.concat(month_dict)

# Print sales
display(sales)

# Print all sales by Mediacore
idx = pd.IndexSlice
display(sales.loc[idx[:, 'Mediacore'], :])
DateProductUnits
Company
januaryAcme Coporation2015-01-01 07:31:202015-01-20 19:49:242015-01-...SoftwareHardwareSoftwareServiceSoftware76
Hooli2015-01-02 09:51:062015-01-11 14:51:022015-01-...HardwareHardwareServiceServiceHardware70
Initech2015-01-06 17:19:342015-01-24 08:01:162015-01-...HardwareSoftwareServiceService37
Mediacore2015-01-15 15:33:402015-01-16 19:20:46HardwareService15
Streeplex2015-01-21 19:13:212015-01-09 05:23:512015-01-...HardwareServiceServiceSoftware50
februaryAcme Coporation2015-02-05 01:53:062015-02-04 21:52:452015-02-...SoftwareHardwareHardware34
Hooli2015-02-16 12:09:192015-02-02 08:33:012015-02-...SoftwareSoftwareSoftwareServiceHardware30
Initech2015-02-03 14:14:182015-02-25 00:29:002015-02-...SoftwareServiceSoftware30
Mediacore2015-02-09 13:09:552015-02-19 16:02:582015-02-...SoftwareServiceHardwareHardwareSoftware45
Streeplex2015-02-26 08:57:452015-02-09 08:57:302015-02-...ServiceServiceServiceSoftware37
marchAcme Coporation2015-03-28 19:20:38Service5
Hooli2015-03-15 00:53:122015-03-17 19:25:372015-03-...HardwareHardwareHardware37
Initech2015-03-12 18:33:062015-03-25 10:18:102015-03-...ServiceHardwareHardwareSoftwareHardware68
Mediacore2015-03-22 14:42:252015-03-16 05:54:062015-03-...SoftwareSoftwareSoftwareSoftwareSoftwareHardware68
Streeplex2015-03-22 03:58:282015-03-25 16:42:422015-03-...SoftwareHardwareSoftwareHardwareSoftware40
DateProductUnits
Company
januaryMediacore2015-01-15 15:33:402015-01-16 19:20:46HardwareService15
februaryMediacore2015-02-09 13:09:552015-02-19 16:02:582015-02-...SoftwareServiceHardwareHardwareSoftware45
marchMediacore2015-03-22 14:42:252015-03-16 05:54:062015-03-...SoftwareSoftwareSoftwareSoftwareSoftwareHardware68
1

del medal_types, medal_list, medal_df, medals, medals_sorted, idx, hw, sw, sv, dataframes, february, slice_2_8

Outer & inner joins

Using with arrays

1
2

A = np.arange(8).reshape(2, 4) + 0.1
A

1
2

array([[0.1, 1.1, 2.1, 3.1],
       [4.1, 5.1, 6.1, 7.1]])

1
2

B = np.arange(6).reshape(2,3) + 0.2
B

1
2

array([[0.2, 1.2, 2.2],
       [3.2, 4.2, 5.2]])

1
2

C = np.arange(12).reshape(3,4) + 0.3
C

1
2
3

array([[ 0.3,  1.3,  2.3,  3.3],
       [ 4.3,  5.3,  6.3,  7.3],
       [ 8.3,  9.3, 10.3, 11.3]])

Stacking arrays horizontally

1

np.hstack([B, A])

1
2

array([[0.2, 1.2, 2.2, 0.1, 1.1, 2.1, 3.1],
       [3.2, 4.2, 5.2, 4.1, 5.1, 6.1, 7.1]])

1

np.concatenate([B, A], axis=1)

1
2

array([[0.2, 1.2, 2.2, 0.1, 1.1, 2.1, 3.1],
       [3.2, 4.2, 5.2, 4.1, 5.1, 6.1, 7.1]])

Stacking arrays vertically

1

np.vstack([A, C])

1
2
3
4
5

array([[ 0.1,  1.1,  2.1,  3.1],
       [ 4.1,  5.1,  6.1,  7.1],
       [ 0.3,  1.3,  2.3,  3.3],
       [ 4.3,  5.3,  6.3,  7.3],
       [ 8.3,  9.3, 10.3, 11.3]])

1

np.concatenate([A, C], axis=0)

1
2
3
4
5

array([[ 0.1,  1.1,  2.1,  3.1],
       [ 4.1,  5.1,  6.1,  7.1],
       [ 0.3,  1.3,  2.3,  3.3],
       [ 4.3,  5.3,  6.3,  7.3],
       [ 8.3,  9.3, 10.3, 11.3]])

Incompatible array dimensions

1

np.concatenate([A, B], axis=0) # incompatible columns

1
2
3
4
5
6
7
8
9

---------------------------------------------------------------------------

ValueError                                Traceback (most recent call last)

Cell In[92], line 1
----> 1 np.concatenate([A, B], axis=0)


ValueError: all the input array dimensions except for the concatenation axis must match exactly, but along dimension 1, the array at index 0 has size 4 and the array at index 1 has size 3

1

np.concatenate([A, C], axis=1) # incompatible rows

1
2
3
4
5
6
7
8
9

---------------------------------------------------------------------------

ValueError                                Traceback (most recent call last)

Cell In[93], line 1
----> 1 np.concatenate([A, C], axis=1)


ValueError: all the input array dimensions except for the concatenation axis must match exactly, but along dimension 0, the array at index 0 has size 2 and the array at index 1 has size 3

Population & unemployment data

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

population = pd.read_csv('population_00.csv', index_col=0)

unemployment = pd.read_csv('unemployment_00.csv', index_col=0)
print(population)
2010 Census Population
Zip Code ZCTA
57538 322
59916 130
37660 40038
2860 45199

print(unemployment)
unemployment participants
Zip
2860 0.11 34447
46167 0.02 4800
1097 0.33 42
80808 0.07 4310

Converting to arrays

1
2
3
4
5
6
7
8
9
10
11
12
13

population_array = np.array(population)
print(population_array) # Index info is lost
[[ 322]
[ 130]
[40038]
[45199]]

unemployment_array = np.array(unemployment)
print(population_array)
[[ 1.10000000e-01 3.44470000e+04]
[ 2.00000000e-02 4.80000000e+03]
[ 3.30000000e-01 4.20000000e+01]
[ 7.00000000e-02 4.31000000e+03]]

Manipulating data as arrays

1
2
3
4
5

print(np.concatenate([population_array, unemployment_array], axis=1))
[[ 3.22000000e+02 1.10000000e-01 3.44470000e+04]
[ 1.30000000e+02 2.00000000e-02 4.80000000e+03]
[ 4.00380000e+04 3.30000000e-01 4.20000000e+01]
[ 4.51990000e+04 7.00000000e-02 4.31000000e+03]]

Joins

  • Joining tables: Combining rows of multiple tables
  • Outer join
    • Union of index sets (all labels, no repetition)
    • Missing fields filled with NaN
    • Preserves the indices in the original tables, filling null values for missing rows
    • Has all the indices of the original tables without repetiton (like a set union)
  • Inner join
    • Intersection of index sets (only common labels)
    • Has only labels common to both tables (like a set intersection)

Concatenation & inner join

  • only the row label present in both DataFrames is preserved
1
2
3
4

pd.concat([population, unemployment], axis=1, join='inner')

2010 Census Population unemployment participants
2860 45199 0.11 34447

Concatenation & outer join

  • All row indiecs from the original two indexes exist in the joind DataFrame index.
  • When a row occurs in one DataFrame, but not in the other, the missing column entries are filled with null values
1
2
3
4
5
6
7
8
9
10

pd.concat([population, unemployment], axis=1, join='outer')

2010 Census Population unemployment participants
1097 NaN 0.33 42.0
2860 45199.0 0.11 34447.0
37660 40038.0 NaN NaN
46167 NaN 0.02 4800.0
57538 322.0 NaN NaN
59916 130.0 NaN NaN
80808 NaN 0.07 4310.0

Inner join on other axis

  • The resulting DataFrame is empty becasue no column index label appears in both population and unemployment
1
2
3
4
5

pd.concat([population, unemployment], join='inner', axis=0)

Empty DataFrame
Columns: []
Index: [2860, 46167, 1097, 80808, 57538, 59916, 37660, 2860]

Exercises

Concatenating DataFrames with inner join

Here, you’ll continue working with DataFrames compiled from The Guardian’s Olympic medal dataset.

The DataFrames bronze, silver, and gold have been pre-loaded for you.

Your task is to compute an inner join.

Instructions

  • Construct a list of DataFrames called medal_list with entries bronze, silver, and gold.
  • Concatenate medal_list horizontally with an inner join to create medals.
    • Use the keyword argument keys=['bronze', 'silver', 'gold'] to yield suitable hierarchical indexing.
    • Use axis=1 to get horizontal concatenation.
    • Use join='inner' to keep only rows that share common index labels.
  • Print the new DataFrame medals.
1
2
3

bronze = pd.read_csv(so_bronze5_file)
silver = pd.read_csv(so_silver_file)
gold = pd.read_csv(so_gold_file)

1
2
3
4
5
6
7
8

# Create the list of DataFrames: medal_list
medal_list = [bronze, silver, gold]

# Concatenate medal_list horizontally using an inner join: medals
medals = pd.concat(medal_list, keys=['bronze', 'silver', 'gold'], axis=1, join='inner')

# Print medals
medals
bronzesilvergold
CountryTotalNOCCountryTotalNOCCountryTotal
0United States1052.0USAUnited States1195.0USAUnited States2088.0
1Soviet Union584.0URSSoviet Union627.0URSSoviet Union838.0
2United Kingdom505.0GBRUnited Kingdom591.0GBRUnited Kingdom498.0
3France475.0FRAFrance461.0FRAFrance378.0
4Germany454.0GERGermany350.0GERGermany407.0

Resampling & concatenating DataFrames with inner join

In this exercise, you’ll compare the historical 10-year GDP (Gross Domestic Product) growth in the US and in China. The data for the US starts in 1947 and is recorded quarterly; by contrast, the data for China starts in 1961 and is recorded annually.

You’ll need to use a combination of resampling and an inner join to align the index labels. You’ll need an appropriate offset alias for resampling, and the method .resample() must be chained with some kind of aggregation method (.pct_change() and .last() in this case).

pandas has been imported as pd, and the DataFrames china and us have been pre-loaded, with the output of china.head() and us.head() printed in the IPython Shell.

Instructions

  • Make a new DataFrame china_annual by resampling the DataFrame china with .resample('A').last() (i.e., with annual frequency) and chaining two method calls:
  • Chain .pct_change(10) as an aggregation method to compute the percentage change with an offset of ten years.
  • Chain .dropna() to eliminate rows containing null values.
  • Make a new DataFrame us_annual by resampling the DataFrame us exactly as you resampled china.
  • Concatenate china_annual and us_annual to construct a DataFrame called gdp. Use join='inner' to perform an inner join and use axis=1 to concatenate horizontally.
  • Print the result of resampling gdp every decade (i.e., using .resample('10A')) and aggregating with the method .last(). This has been done for you, so hit ‘Submit Answer’ to see the result!
1
2
3
4
5
6
7

china = pd.read_csv(gdp_china_file, parse_dates=['Year'])
china.rename(columns={'GDP': 'China'}, inplace=True)
china.set_index('Year', inplace=True)

us = pd.read_csv(gdp_usa_file, parse_dates=['DATE'])
us.rename(columns={'DATE': 'Year', 'VALUE': 'US'}, inplace=True)
us.set_index('Year', inplace=True)

1

china.head()
China
Year
1960-01-0159.184116
1961-01-0149.557050
1962-01-0146.685179
1963-01-0150.097303
1964-01-0159.062255
1

us.head()
US
Year
1947-01-01243.1
1947-04-01246.3
1947-07-01250.1
1947-10-01260.3
1948-01-01266.2
1
2
3

# Resample and tidy china: china_annual
china_annual = china.resample('YE').last().pct_change(10).dropna()
china_annual.head()
China
Year
1970-12-310.546128
1971-12-310.988860
1972-12-311.402472
1973-12-311.730085
1974-12-311.408556
1
2
3

# Resample and tidy us: us_annual
us_annual = us.resample('YE').last().pct_change(10).dropna()
us_annual.head()
US
Year
1957-12-310.827507
1958-12-310.782686
1959-12-310.953137
1960-12-310.689354
1961-12-310.630959
1
2
3
4
5

# Concatenate china_annual and us_annual: gdp
gdp = pd.concat([china_annual, us_annual], join='inner', axis=1)

# Resample gdp and print
gdp.resample('10YE').last()
ChinaUS
Year
1970-12-310.5461281.017187
1980-12-311.0725371.742556
1990-12-310.8928201.012126
2000-12-312.3575220.738632
2010-12-314.0110810.454332
2020-12-313.7899360.361780
1

del bronze, silver, gold, medal_list, medals, china, us, china_annual, us_annual, gdp

Merging Data

Here, you’ll learn all about merging pandas DataFrames. You’ll explore different techniques for merging, and learn about left joins, right joins, inner joins, and outer joins, as well as when to use which. You’ll also learn about ordered merging, which is useful when you want to merge DataFrames whose columns have natural orderings, like date-time columns.

  • merge() extends concat() with the ability to align rows using multiple columns

Merging DataFrames

1
2
3
4
5
6

pa_zipcode_population = {'Zipcode': [16855, 15681, 18657, 17307, 15635],
                         '2010 Census Population': [282, 5241, 11985, 5899, 220]}
pa_zipcode_city = {'Zipcode': [17545,18455, 17307, 15705, 16833, 16220, 18618, 16855, 16623, 15635, 15681, 18657, 15279, 17231, 18821],
                   'City': ['MANHEIM', 'PRESTON PARK', 'BIGLERVILLE', 'INDIANA', 'CURWENSVILLE', 'CROWN', 'HARVEYS LAKE', 'MINERAL SPRINGS',
                            'CASSVILLE', 'HANNASTOWN', 'SALTSBURG', 'TUNKHANNOCK', 'PITTSBURG', 'LEMASTERS', 'GREAT BEND'],
                   'State': ['PA', 'PA', 'PA', 'PA', 'PA', 'PA', 'PA', 'PA', 'PA', 'PA', 'PA', 'PA', 'PA', 'PA', 'PA']}

Population DataFrame

1
2

population = pd.DataFrame.from_dict(pa_zipcode_population)
population
Zipcode2010 Census Population
016855282
1156815241
21865711985
3173075899
415635220

Cities DataFrame

1
2

cities = pd.DataFrame.from_dict(pa_zipcode_city)
cities
ZipcodeCityState
017545MANHEIMPA
118455PRESTON PARKPA
217307BIGLERVILLEPA
315705INDIANAPA
416833CURWENSVILLEPA
516220CROWNPA
618618HARVEYS LAKEPA
716855MINERAL SPRINGSPA
816623CASSVILLEPA
915635HANNASTOWNPA
1015681SALTSBURGPA
1118657TUNKHANNOCKPA
1215279PITTSBURGPA
1317231LEMASTERSPA
1418821GREAT BENDPA

Merging

  • pd.merge() computes a merge on ALL columns that occur in both DataFrames
    • in the following case, the common column is Zipcode
    • for any row in which the Zipcode entry in cities matches a row in population, a new row is made in the merfed DataFrame.
    • by default, this is an inner join
      • it’s an inner join because it glues together only rows that match in the joining columns of BOTH DataFrames
1

pd.merge(population, cities)
Zipcode2010 Census PopulationCityState
016855282MINERAL SPRINGSPA
1156815241SALTSBURGPA
21865711985TUNKHANNOCKPA
3173075899BIGLERVILLEPA
415635220HANNASTOWNPA

Medal DataFrames

1
2

bronze = pd.read_csv(so_bronze_file)
bronze.head()
NOCCountryTotal
0USAUnited States1052.0
1URSSoviet Union584.0
2GBRUnited Kingdom505.0
3FRAFrance475.0
4GERGermany454.0
1

len(bronze)

1

138

1
2

gold = pd.read_csv(so_gold_file)
gold.head()
NOCCountryTotal
0USAUnited States2088.0
1URSSoviet Union838.0
2GBRUnited Kingdom498.0
3FRAFrance378.0
4GERGermany407.0
1

len(gold)

1

138

Merging all columns

  • by default, pd.merge() uses all columns common to both DataFrames to merge
  • the rows of the merged DataFrame consist of all rows where the NOC, Country, and Totals columns are identical in both DataFrames
1
2

so_merge = pd.merge(bronze, gold)
so_merge.head()
NOCCountryTotal
0ESPSpain92.0
1IRLIreland8.0
2SYRSyria1.0
3MOZMozambique1.0
4SURSuriname1.0
1

len(so_merge)

1

18

1

so_merge.columns

1

Index(['NOC', 'Country', 'Total'], dtype='object')

1

so_merge.index

1

RangeIndex(start=0, stop=18, step=1)

Merging on

1
2

so_merge = pd.merge(bronze, gold, on='NOC')
so_merge.head()
NOCCountry_xTotal_xCountry_yTotal_y
0USAUnited States1052.0United States2088.0
1URSSoviet Union584.0Soviet Union838.0
2GBRUnited Kingdom505.0United Kingdom498.0
3FRAFrance475.0France378.0
4GERGermany454.0Germany407.0
1

len(so_merge)

1

138

Merging on multiple columns

  • this is where merging extend concatenation in allowing matching on multiple columns
1
2

so_merge = pd.merge(bronze, gold, on=['NOC', 'Country'])
so_merge.head()
NOCCountryTotal_xTotal_y
0USAUnited States1052.02088.0
1URSSoviet Union584.0838.0
2GBRUnited Kingdom505.0498.0
3FRAFrance475.0378.0
4GERGermany454.0407.0

Using suffixes

1
2

so_merge = pd.merge(bronze, gold, on=['NOC', 'Country'], suffixes=['_bronze', '_gold'])
so_merge.head()
NOCCountryTotal_bronzeTotal_gold
0USAUnited States1052.02088.0
1URSSoviet Union584.0838.0
2GBRUnited Kingdom505.0498.0
3FRAFrance475.0378.0
4GERGermany454.0407.0

Counties DataFrame

1
2
3
4

pa_counties = {'CITY NAME': ['SALTSBURG', 'MINERAL SPRINGS', 'BIGLERVILLE', 'HANNASTOWN', 'TUNKHANNOCK'],
               'COUNTY NAME': ['INDIANA', 'CLEARFIELD', 'ADAMS', 'WESTMORELAND', 'WYOMING']}
counties = pd.DataFrame.from_dict(pa_counties)
counties
CITY NAMECOUNTY NAME
0SALTSBURGINDIANA
1MINERAL SPRINGSCLEARFIELD
2BIGLERVILLEADAMS
3HANNASTOWNWESTMORELAND
4TUNKHANNOCKWYOMING
1

cities.tail()
ZipcodeCityState
1015681SALTSBURGPA
1118657TUNKHANNOCKPA
1215279PITTSBURGPA
1317231LEMASTERSPA
1418821GREAT BENDPA

Specifying columns to merge

1

pd.merge(counties, cities, left_on='CITY NAME', right_on='City')
CITY NAMECOUNTY NAMEZipcodeCityState
0SALTSBURGINDIANA15681SALTSBURGPA
1MINERAL SPRINGSCLEARFIELD16855MINERAL SPRINGSPA
2BIGLERVILLEADAMS17307BIGLERVILLEPA
3HANNASTOWNWESTMORELAND15635HANNASTOWNPA
4TUNKHANNOCKWYOMING18657TUNKHANNOCKPA

Switching left/right DataFrames

1

pd.merge(cities, counties, left_on='City', right_on='CITY NAME')
ZipcodeCityStateCITY NAMECOUNTY NAME
017307BIGLERVILLEPABIGLERVILLEADAMS
116855MINERAL SPRINGSPAMINERAL SPRINGSCLEARFIELD
215635HANNASTOWNPAHANNASTOWNWESTMORELAND
315681SALTSBURGPASALTSBURGINDIANA
418657TUNKHANNOCKPATUNKHANNOCKWYOMING
1

del pa_zipcode_population, pa_zipcode_city, population, cities, bronze, gold, so_merge, pa_counties, counties

Exercises

Merging company DataFrames

Suppose your company has operations in several different cities under several different managers. The DataFrames revenue and managers contain partial information related to the company. That is, the rows of the city columns don’t quite match in revenue and managers (the Mendocino branch has no revenue yet since it just opened and the manager of Springfield branch recently left the company).

The DataFrames have been printed in the IPython Shell. If you were to run the command combined = pd.merge(revenue, managers, on='city'), how many rows would combined have?

1
2
3
4
5

rev = {'city': ['Austin', 'Denver', 'Springfield'], 'revenue': [100, 83, 4]}
man = {'city': ['Austin', 'Denver', 'Mendocino'], 'manager': ['Charles', 'Joel', 'Brett']}

revenue = pd.DataFrame.from_dict(rev)
managers = pd.DataFrame.from_dict(man)

1
2

combined = pd.merge(revenue, managers, on='city')
combined
cityrevenuemanager
0Austin100Charles
1Denver83Joel

Merging on a specific column

This exercise follows on the last one with the DataFrames revenue and managers for your company. You expect your company to grow and, eventually, to operate in cities with the same name on different states. As such, you decide that every branch should have a numerical branch identifier. Thus, you add a branch_id column to both DataFrames. Moreover, new cities have been added to both the revenue and managers DataFrames as well. pandas has been imported as pd and both DataFrames are available in your namespace.

At present, there should be a 1-to-1 relationship between the city and branch_id fields. In that case, the result of a merge on the city columns ought to give you the same output as a merge on the branch_id columns. Do they? Can you spot an ambiguity in one of the DataFrames?

Instructions

  • Using pd.merge(), merge the DataFrames revenue and managers on the 'city' column of each. Store the result as merge_by_city.
  • Print the DataFrame merge_by_city. This has been done for you.
  • Merge the DataFrames revenue and managers on the 'branch_id' column of each. Store the result as merge_by_id.
  • Print the DataFrame merge_by_id. This has been done for you, so hit ‘Submit Answer’ to see the result!
1
2
3
4
5

rev = {'city': ['Austin', 'Denver', 'Springfield', 'Mendocino'], 'revenue': [100, 83, 4, 200], 'branch_id': [10, 20, 30, 47]}
man = {'city': ['Austin', 'Denver', 'Mendocino', 'Springfield'], 'manager': ['Charles', 'Joel', 'Brett', 'Sally'], 'branch_id': [10, 20, 47, 31]}

revenue = pd.DataFrame.from_dict(rev)
managers = pd.DataFrame.from_dict(man)

1
2
3
4
5

# Merge revenue with managers on 'city': merge_by_city
merge_by_city = pd.merge(revenue, managers, on='city')

# Print merge_by_city
merge_by_city
cityrevenuebranch_id_xmanagerbranch_id_y
0Austin10010Charles10
1Denver8320Joel20
2Springfield430Sally31
3Mendocino20047Brett47
1
2
3
4
5

# Merge revenue with managers on 'branch_id': merge_by_id
merge_by_id = pd.merge(revenue, managers, on='branch_id')

# Print merge_by_id
merge_by_id
city_xrevenuebranch_idcity_ymanager
0Austin10010AustinCharles
1Denver8320DenverJoel
2Mendocino20047MendocinoBrett

Notice that when you merge on 'city', the resulting DataFrame has a peculiar result: In row 2, the city Springfield has two different branch IDs. This is because there are actually two different cities named Springfield - one in the State of Illinois, and the other in Missouri. The revenue DataFrame has the one from Illinois, and the managers DataFrame has the one from Missouri. Consequently, when you merge on 'branch_id', both of these get dropped from the merged DataFrame.

Merging on columns with non-matching labels

You continue working with the revenue & managers DataFrames from before. This time, someone has changed the field name 'city' to 'branch' in the managers table. Now, when you attempt to merge DataFrames, an exception is thrown:

1
2
3
4
5
6

>>> pd.merge(revenue, managers, on='city')
Traceback (most recent call last):
    ... <text deleted> ...
    pd.merge(revenue, managers, on='city')
    ... <text deleted> ...
KeyError: 'city'

Given this, it will take a bit more work for you to join or merge on the city/branch name. You have to specify the left_on and right_on parameters in the call to pd.merge().

As before, pandas has been pre-imported as pd and the revenue and managers DataFrames are in your namespace. They have been printed in the IPython Shell so you can examine the columns prior to merging.

Are you able to merge better than in the last exercise? How should the rows with Springfield be handled?

Instructions

  • Merge the DataFrames revenue and managers into a single DataFrame called combined using the 'city' and 'branch' columns from the appropriate DataFrames.
    • In your call to pd.merge(), you will have to specify the parameters left_on and right_on appropriately.
  • Print the new DataFrame combined.
1
2

state_rev = {'Austin': 'TX', 'Denver': 'CO', 'Springfield': 'IL', 'Mendocino': 'CA'}
state_man = {'Austin': 'TX', 'Denver': 'CO', 'Mendocino': 'CA', 'Springfield': 'MO'}

1
2

revenue['state'] = revenue['city'].map(state_rev)
managers['state'] = managers['city'].map(state_man)

1

managers.rename(columns={'city': 'branch'}, inplace=True)

1

revenue
cityrevenuebranch_idstate
0Austin10010TX
1Denver8320CO
2Springfield430IL
3Mendocino20047CA
1

managers
branchmanagerbranch_idstate
0AustinCharles10TX
1DenverJoel20CO
2MendocinoBrett47CA
3SpringfieldSally31MO
1
2

combined = pd.merge(revenue, managers, left_on='city', right_on='branch')
combined
cityrevenuebranch_id_xstate_xbranchmanagerbranch_id_ystate_y
0Austin10010TXAustinCharles10TX
1Denver8320CODenverJoel20CO
2Springfield430ILSpringfieldSally31MO
3Mendocino20047CAMendocinoBrett47CA

Merging on multiple columns

Another strategy to disambiguate cities with identical names is to add information on the states in which the cities are located. To this end, you add a column called state to both DataFrames from the preceding exercises. Again, pandas has been pre-imported as pd and the revenue and managers DataFrames are in your namespace.

Your goal in this exercise is to use pd.merge() to merge DataFrames using multiple columns (using 'branch_id', 'city', and 'state' in this case).

Are you able to match all your company’s branches correctly?

Instructions

  • Create a column called 'state' in the DataFrame revenue, consisting of the list ['TX','CO','IL','CA'].
  • Create a column called 'state' in the DataFrame managers, consisting of the list ['TX','CO','CA','MO'].
  • Merge the DataFrames revenue and managers using three columns :'branch_id', 'city', and 'state'. Pass them in as a list to the on paramater of pd.merge().
1

managers.rename(columns={'branch': 'city'}, inplace=True)

1
2
3
4
5
6
7
8
9
10
11

# Add 'state' column to revenue: revenue['state']
revenue['state'] = ['TX','CO','IL','CA']

# Add 'state' column to managers: managers['state']
managers['state'] = ['TX','CO','CA','MO']

# Merge revenue & managers on 'branch_id', 'city', & 'state': combined
combined = pd.merge(revenue, managers, on=['branch_id', 'city', 'state'])

# Print combined
print(combined)

1
2
3
4

        city  revenue  branch_id state  manager
0     Austin      100         10    TX  Charles
1     Denver       83         20    CO     Joel
2  Mendocino      200         47    CA    Brett

1

del rev, man, revenue, managers, merge_by_city, merge_by_id, combined

Joining DataFrames

  • Pandas has to search through DataFrame rows for matches when computing joins and merges
    • It’s useful to have different kinds of joins to mitigate costs

Medal DataFrames

1
2

bronze = pd.read_csv(so_bronze_file)
bronze.head()
NOCCountryTotal
0USAUnited States1052.0
1URSSoviet Union584.0
2GBRUnited Kingdom505.0
3FRAFrance475.0
4GERGermany454.0
1

len(bronze)

1

138

1
2

gold = pd.read_csv(so_gold_file)
gold.head()
NOCCountryTotal
0USAUnited States2088.0
1URSSoviet Union838.0
2GBRUnited Kingdom498.0
3FRAFrance378.0
4GERGermany407.0
1

len(gold)

1

138

Merging with inner join

  • merge() does an inner join by default
    • it extracts the rows that match in joining columns from both DataFrames and it glues them together in the joined DataFrame
    • the property how=innner is the default behavior
1
2

so_merge = pd.merge(bronze, gold, on=['NOC', 'Country'], suffixes=['_bronze', '_gold'], how='inner')
so_merge.head()
NOCCountryTotal_bronzeTotal_gold
0USAUnited States1052.02088.0
1URSSoviet Union584.0838.0
2GBRUnited Kingdom505.0498.0
3FRAFrance475.0378.0
4GERGermany454.0407.0

Merging with left join

  • using how=left keeps all rows of the left DataFrame in the merged DataFrame
  • Keeps all rows of the left DF in the merged DF
  • For rows in the left DF with matches in the right DF:
    • Non-joining columns of right DF are appended to left DF
  • For rows in the left DF with no matches in the right DF:
    • Non-joining columns are filled with nulls
1
2

bronze = pd.read_csv(so_bronze5_file)
gold = pd.read_csv(so_gold5_file)

1
2

g_noc = ['USA', 'URS', 'GBR', 'ITA', 'GER']
b_noc = ['USA', 'URS', 'GBR', 'FRA', 'GER']

1
2

gold['NOC'] = g_noc
bronze['NOC'] = b_noc

1

gold
CountryTotalNOC
0United States2088.0USA
1Soviet Union838.0URS
2United Kingdom498.0GBR
3Italy460.0ITA
4Germany407.0GER
1

bronze
CountryTotalNOC
0United States1052.0USA
1Soviet Union584.0URS
2United Kingdom505.0GBR
3France475.0FRA
4Germany454.0GER
1

pd.merge(bronze, gold, on=['NOC', 'Country'], suffixes=['_bronze', '_gold'], how='left')
CountryTotal_bronzeNOCTotal_gold
0United States1052.0USA2088.0
1Soviet Union584.0URS838.0
2United Kingdom505.0GBR498.0
3France475.0FRANaN
4Germany454.0GER407.0

Merging with right join

1

pd.merge(bronze, gold, on=['NOC', 'Country'], suffixes=['_bronze', '_gold'], how='right')
CountryTotal_bronzeNOCTotal_gold
0United States1052.0USA2088.0
1Soviet Union584.0URS838.0
2United Kingdom505.0GBR498.0
3ItalyNaNITA460.0
4Germany454.0GER407.0

Merging with outer join

1

pd.merge(bronze, gold, on=['NOC', 'Country'], suffixes=['_bronze', '_gold'], how='outer')
CountryTotal_bronzeNOCTotal_gold
0France475.0FRANaN
1United Kingdom505.0GBR498.0
2Germany454.0GER407.0
3ItalyNaNITA460.0
4Soviet Union584.0URS838.0
5United States1052.0USA2088.0

Population & unemployment data

1
2
3
4

population = pd.DataFrame.from_dict({'Zip Code ZCTA': [57538, 59916, 37660, 2860],
                                     '2010 Census Population': [322, 130, 40038, 45199]})
population.set_index('Zip Code ZCTA', inplace=True)
population
2010 Census Population
Zip Code ZCTA
57538322
59916130
3766040038
286045199
1
2
3
4
5

unemployment = pd.DataFrame.from_dict({'Zip': [2860, 46167, 1097],
                                       'unemployment': [0.11, 0.02, 0.33],
                                       'participants': [ 34447, 4800, 32]})
unemployment.set_index('Zip', inplace=True)
unemployment
unemploymentparticipants
Zip
28600.1134447
461670.024800
10970.3332

Using .join(how=’left’)

  • computes a left join using the Index by default
1

population.join(unemployment)
2010 Census Populationunemploymentparticipants
Zip Code ZCTA
57538322NaNNaN
59916130NaNNaN
3766040038NaNNaN
2860451990.1134447.0

Using .join(how=’right’)

1

population.join(unemployment, how='right')
2010 Census Populationunemploymentparticipants
Zip
286045199.00.1134447
46167NaN0.024800
1097NaN0.3332

Using .join(how=’inner’)

1

population.join(unemployment, how='inner')
2010 Census Populationunemploymentparticipants
2860451990.1134447

Using .join(how=’outer’)

1

population.join(unemployment, how='outer')
2010 Census Populationunemploymentparticipants
1097NaN0.3332.0
286045199.00.1134447.0
3766040038.0NaNNaN
46167NaN0.024800.0
57538322.0NaNNaN
59916130.0NaNNaN
1

del bronze, gold, so_merge, g_noc, b_noc, population, unemployment

Which should you use?

  • df1.append(df2): stacking vertically
  • pd.concat([df1, df2]):
    • stacking many horizontally or vertically
    • simple inner/outer joins on Indexes
  • df1.join(df2): inner/outer/left/right joins on Indexes
  • pd.merge([df1, df2]): many joins on multiple columns

Exercises

Data

1
2
3
4
5
6
7
8
9
10
11
12
13
14

rev = {'city': ['Austin', 'Denver', 'Springfield', 'Mendocino'],
       'state': ['TX','CO','IL','CA'],
       'revenue': [100, 83, 4, 200],
       'branch_id': [10, 20, 30, 47]}

man = {'city': ['Austin', 'Denver', 'Mendocino', 'Springfield'],
       'state': ['TX','CO','CA','MO'],
       'manager': ['Charles', 'Joel', 'Brett', 'Sally'],
       'branch_id': [10, 20, 47, 31]}

revenue = pd.DataFrame.from_dict(rev)
revenue.set_index('branch_id', inplace=True)
managers = pd.DataFrame.from_dict(man)
managers.set_index('branch_id', inplace=True)

1

revenue
citystaterevenue
branch_id
10AustinTX100
20DenverCO83
30SpringfieldIL4
47MendocinoCA200
1

managers
citystatemanager
branch_id
10AustinTXCharles
20DenverCOJoel
47MendocinoCABrett
31SpringfieldMOSally

Joining by Index

The DataFrames revenue and managers are displayed in the IPython Shell. Here, they are indexed by 'branch_id'.

Choose the function call below that will join the DataFrames on their indexes and return 5 rows with index labels [10, 20, 30, 31, 47]. Explore each of them in the IPython Shell to get a better understanding of their functionality.

1

revenue.join(managers, lsuffix='_rev', rsuffix='_mng', how='outer')
city_revstate_revrevenuecity_mngstate_mngmanager
branch_id
10AustinTX100.0AustinTXCharles
20DenverCO83.0DenverCOJoel
30SpringfieldIL4.0NaNNaNNaN
31NaNNaNNaNSpringfieldMOSally
47MendocinoCA200.0MendocinoCABrett

Choosing a joining strategy

Suppose you have two DataFrames: students (with columns 'StudentID', 'LastName', 'FirstName', and 'Major') and midterm_results (with columns 'StudentID', 'Q1', 'Q2', and 'Q3' for their scores on midterm questions).

You want to combine the DataFrames into a single DataFrame grades, and be able to easily spot which students wrote the midterm and which didn’t (their midterm question scores 'Q1', 'Q2', & 'Q3' should be filled with NaN values).

You also want to drop rows from midterm_results in which the StudentID is not found in students.

Which of the following strategies gives the desired result?

1
2

students = pd.DataFrame.from_dict({'StudentID': [], 'LastName': [], 'FirstName': [], 'Major': []})
midterm_results = pd.DataFrame.from_dict({'StudentID': [], 'Q1': [], 'Q2': [], 'Q3': []})

1

students
StudentIDLastNameFirstNameMajor
1

midterm_results
StudentIDQ1Q2Q3
1

grades = pd.merge(students, midterm_results, how='left')

Left & right merging on multiple columns

You now have, in addition to the revenue and managers DataFrames from prior exercises, a DataFrame sales that summarizes units sold from specific branches (identified by city and state but not branch_id).

Once again, the managers DataFrame uses the label branch in place of city as in the other two DataFrames. Your task here is to employ left and right merges to preserve data and identify where data is missing.

By merging revenue and sales with a right merge, you can identify the missing revenue values. Here, you don’t need to specify left_on or right_on because the columns to merge on have matching labels.

By merging sales and managers with a left merge, you can identify the missing manager. Here, the columns to merge on have conflicting labels, so you must specify left_on and right_on. In both cases, you’re looking to figure out how to connect the fields in rows containing Springfield.

pandas has been imported as pd and the three DataFrames revenue, managers, and sales have been pre-loaded. They have been printed for you to explore in the IPython Shell.

Instructions

  • Execute a right merge using pd.merge() with revenue and sales to yield a new DataFrame revenue_and_sales.
    • Use how='right' and on=['city', 'state'].
  • Print the new DataFrame revenue_and_sales. This has been done for you.
  • Execute a left merge with sales and managers to yield a new DataFrame sales_and_managers.
    • Use how='left', left_on=['city', 'state'], and right_on=['branch', 'state'].
  • Print the new DataFrame sales_and_managers. This has been done for you, so hit ‘Submit Answer’ to see the result!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

rev = {'city': ['Austin', 'Denver', 'Springfield', 'Mendocino'],
       'branch_id': [10, 20, 30, 47],
       'state': ['TX','CO','IL','CA'],
       'revenue': [100, 83, 4, 200]}

man = {'branch': ['Austin', 'Denver', 'Mendocino', 'Springfield'],
       'branch_id': [10, 20, 47, 31],
       'state': ['TX','CO','CA','MO'],
       'manager': ['Charles', 'Joel', 'Brett', 'Sally']}

sale = {'city': ['Mendocino', 'Denver', 'Austin', 'Springfield', 'Springfield'],
        'state': ['CA', 'CO', 'TX', 'MO', 'IL'],
        'units': [1, 4, 2, 5, 1]}

revenue = pd.DataFrame.from_dict(rev)
managers = pd.DataFrame.from_dict(man)
sales = pd.DataFrame.from_dict(sale)

1

revenue
citybranch_idstaterevenue
0Austin10TX100
1Denver20CO83
2Springfield30IL4
3Mendocino47CA200
1

managers
branchbranch_idstatemanager
0Austin10TXCharles
1Denver20COJoel
2Mendocino47CABrett
3Springfield31MOSally
1

sales
citystateunits
0MendocinoCA1
1DenverCO4
2AustinTX2
3SpringfieldMO5
4SpringfieldIL1
1
2
3
4
5

# Merge revenue and sales: revenue_and_sales
revenue_and_sales = pd.merge(revenue, sales, how='right', on=['city', 'state'])

# Print revenue_and_sales
revenue_and_sales
citybranch_idstaterevenueunits
0Mendocino47.0CA200.01
1Denver20.0CO83.04
2Austin10.0TX100.02
3SpringfieldNaNMONaN5
4Springfield30.0IL4.01
1
2
3
4

sales_and_managers = pd.merge(sales, managers, how='left', left_on=['city', 'state'], right_on=['branch', 'state'])

# Print sales_and_managers
sales_and_managers
citystateunitsbranchbranch_idmanager
0MendocinoCA1Mendocino47.0Brett
1DenverCO4Denver20.0Joel
2AustinTX2Austin10.0Charles
3SpringfieldMO5Springfield31.0Sally
4SpringfieldIL1NaNNaNNaN

Merging DataFrames with outer join

This exercise picks up where the previous one left off. The DataFrames revenue, managers, and sales are pre-loaded into your namespace (and, of course, pandas is imported as pd). Moreover, the merged DataFrames revenue_and_sales and sales_and_managers have been pre-computed exactly as you did in the previous exercise.

The merged DataFrames contain enough information to construct a DataFrame with 5 rows with all known information correctly aligned and each branch listed only once. You will try to merge the merged DataFrames on all matching keys (which computes an inner join by default). You can compare the result to an outer join and also to an outer join with restricted subset of columns as keys.

Instructions

  • Merge sales_and_managers with revenue_and_sales. Store the result as merge_default.
  • Print merge_default. This has been done for you.
  • Merge sales_and_managers with revenue_and_sales using how='outer'. Store the result as merge_outer.
  • Print merge_outer. This has been done for you.
  • Merge sales_and_managers with revenue_and_sales only on ['city','state'] using an outer join. Store the result as merge_outer_on and hit ‘Submit Answer’ to see what the merged DataFrames look like!
1
2
3
4
5

# Perform the first merge: merge_default
merge_default = pd.merge(sales_and_managers, revenue_and_sales)

# Print merge_default
merge_default
citystateunitsbranchbranch_idmanagerrevenue
0MendocinoCA1Mendocino47.0Brett200.0
1DenverCO4Denver20.0Joel83.0
2AustinTX2Austin10.0Charles100.0
1
2
3
4
5

# Perform the second merge: merge_outer
merge_outer = pd.merge(sales_and_managers, revenue_and_sales, how='outer')

# Print merge_outer
merge_outer
citystateunitsbranchbranch_idmanagerrevenue
0AustinTX2Austin10.0Charles100.0
1DenverCO4Denver20.0Joel83.0
2MendocinoCA1Mendocino47.0Brett200.0
3SpringfieldIL1NaN30.0NaN4.0
4SpringfieldIL1NaNNaNNaNNaN
5SpringfieldMO5Springfield31.0SallyNaN
6SpringfieldMO5NaNNaNNaNNaN
1
2
3
4
5

# Perform the third merge: merge_outer_on
merge_outer_on = pd.merge(sales_and_managers, revenue_and_sales, on=['city', 'state'], how='outer')

# Print merge_outer_on
merge_outer_on
citystateunits_xbranchbranch_id_xmanagerbranch_id_yrevenueunits_y
0AustinTX2Austin10.0Charles10.0100.02
1DenverCO4Denver20.0Joel20.083.04
2MendocinoCA1Mendocino47.0Brett47.0200.01
3SpringfieldIL1NaNNaNNaN30.04.01
4SpringfieldMO5Springfield31.0SallyNaNNaN5
1

del rev, man, revenue, managers, students, midterm_results, grades, sale, sales, revenue_and_sales, sales_and_managers, merge_default, merge_outer, merge_outer_on

Ordered merges

Software & hardware sales

1
2

software = pd.read_csv(sales_feb_software_file, parse_dates=['Date']).sort_values('Date')
software.head(10)
DateCompanyProductUnits
22015-02-02 08:33:01HooliSoftware3
12015-02-03 14:14:18InitechSoftware13
72015-02-04 15:36:29StreeplexSoftware13
32015-02-05 01:53:06Acme CoporationSoftware19
52015-02-09 13:09:55MediacoreSoftware7
42015-02-11 20:03:08InitechSoftware7
62015-02-11 22:50:44HooliSoftware4
02015-02-16 12:09:19HooliSoftware10
82015-02-21 05:01:26MediacoreSoftware3
1
2

hardware = pd.read_csv(sales_feb_hardware_file, parse_dates=['Date']).sort_values('Date')
hardware.head()
DateCompanyProductUnits
32015-02-02 20:54:49MediacoreHardware9
02015-02-04 21:52:45Acme CoporationHardware14
12015-02-07 22:58:10Acme CoporationHardware1
22015-02-19 10:59:33MediacoreHardware16
42015-02-21 20:41:47HooliHardware3

Using merge()

  • attempting to merge yields an empty DataFrame because it’s doing an INNER join on all columns with matching names by defaults
    • ‘Units’ and ‘Date’ columns have no overlapping values, so the result is empty
1
2

sales_merge = pd.merge(hardware, software)
sales_merge
DateCompanyProductUnits
1

sales_merge.info()

1
2
3
4
5
6
7
8
9
10
11

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 0 entries
Data columns (total 4 columns):
 #   Column   Non-Null Count  Dtype         
---  ------   --------------  -----         
 0   Date     0 non-null      datetime64[ns]
 1   Company  0 non-null      object        
 2   Product  0 non-null      object        
 3   Units    0 non-null      int64         
dtypes: datetime64[ns](1), int64(1), object(2)
memory usage: 132.0+ bytes

Using merge(how=’outer’)

1
2

sales_merge = pd.merge(hardware, software, how='outer')
sales_merge.head(14)
DateCompanyProductUnits
02015-02-02 08:33:01HooliSoftware3
12015-02-02 20:54:49MediacoreHardware9
22015-02-03 14:14:18InitechSoftware13
32015-02-04 15:36:29StreeplexSoftware13
42015-02-04 21:52:45Acme CoporationHardware14
52015-02-05 01:53:06Acme CoporationSoftware19
62015-02-07 22:58:10Acme CoporationHardware1
72015-02-09 13:09:55MediacoreSoftware7
82015-02-11 20:03:08InitechSoftware7
92015-02-11 22:50:44HooliSoftware4
102015-02-16 12:09:19HooliSoftware10
112015-02-19 10:59:33MediacoreHardware16
122015-02-21 05:01:26MediacoreSoftware3
132015-02-21 20:41:47HooliHardware3

Sorting merge(how=’outer’)

1
2

sales_merge = pd.merge(hardware, software, how='outer').sort_values('Date')
sales_merge.head(14)
DateCompanyProductUnits
02015-02-02 08:33:01HooliSoftware3
12015-02-02 20:54:49MediacoreHardware9
22015-02-03 14:14:18InitechSoftware13
32015-02-04 15:36:29StreeplexSoftware13
42015-02-04 21:52:45Acme CoporationHardware14
52015-02-05 01:53:06Acme CoporationSoftware19
62015-02-07 22:58:10Acme CoporationHardware1
72015-02-09 13:09:55MediacoreSoftware7
82015-02-11 20:03:08InitechSoftware7
92015-02-11 22:50:44HooliSoftware4
102015-02-16 12:09:19HooliSoftware10
112015-02-19 10:59:33MediacoreHardware16
122015-02-21 05:01:26MediacoreSoftware3
132015-02-21 20:41:47HooliHardware3

Using merge_ordered()

  • the default is an OUTER join
1
2

sales_merged = pd.merge_ordered(hardware, software)
sales_merged.head(14)
DateCompanyProductUnits
02015-02-02 08:33:01HooliSoftware3
12015-02-02 20:54:49MediacoreHardware9
22015-02-03 14:14:18InitechSoftware13
32015-02-04 15:36:29StreeplexSoftware13
42015-02-04 21:52:45Acme CoporationHardware14
52015-02-05 01:53:06Acme CoporationSoftware19
62015-02-07 22:58:10Acme CoporationHardware1
72015-02-09 13:09:55MediacoreSoftware7
82015-02-11 20:03:08InitechSoftware7
92015-02-11 22:50:44HooliSoftware4
102015-02-16 12:09:19HooliSoftware10
112015-02-19 10:59:33MediacoreHardware16
122015-02-21 05:01:26MediacoreSoftware3
132015-02-21 20:41:47HooliHardware3

Using on & suffixes

1
2

sales_merged = pd.merge_ordered(hardware, software, on=['Date', 'Company'], suffixes=['_hardware', '_software'])
sales_merged.head()
DateCompanyProduct_hardwareUnits_hardwareProduct_softwareUnits_software
02015-02-02 08:33:01HooliNaNNaNSoftware3.0
12015-02-02 20:54:49MediacoreHardware9.0NaNNaN
22015-02-03 14:14:18InitechNaNNaNSoftware13.0
32015-02-04 15:36:29StreeplexNaNNaNSoftware13.0
42015-02-04 21:52:45Acme CoporationHardware14.0NaNNaN

Stocks data

1

pwd()

1

'D:\\users\\trenton\\Dropbox\\PythonProjects\\DataCamp'

1
2
3
4
5
6
7
8
9
10

stocks_dir = Path.cwd() / 'data' / 'merging-dataframes-with-pandas'

tickers = ['^gspc', 'AAPL', 'CSCO', 'AMZN', 'MSFT', 'IBM']

for tk in tickers:
    print(tk)
    df = yf.download(tk, start='1980-01-01', end='2024-04-30', interval='1d').assign(tkr=tk)
    if tk == '^gspc':
        tk = 'SP500'
    df.to_csv( stocks_dir / f'{tk}.csv', index=True)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

^gspc


[*********************100%%**********************]  1 of 1 completed


AAPL


[*********************100%%**********************]  1 of 1 completed


CSCO


[*********************100%%**********************]  1 of 1 completed
[*********************100%%**********************]  1 of 1 completed

AMZN





MSFT


[*********************100%%**********************]  1 of 1 completed


IBM


[*********************100%%**********************]  1 of 1 completed

1
2
3
4
5
6

sp500_stocks = stocks_dir / 'SP500.csv'
aapl = stocks_dir / 'AAPL.csv'
csco = stocks_dir / 'CSCO.csv'
amzn = stocks_dir / 'AMZN.csv'
msft = stocks_dir / 'MSFT.csv'
ibm = stocks_dir / 'IBM.csv'

1
2
3
4
5
6

sp500_df = pd.read_csv(sp500_stocks, usecols=['Date', 'Close'], parse_dates=['Date'], index_col=['Date'])
aapl_df = pd.read_csv(aapl, usecols=['Date', 'Close'], parse_dates=['Date'], index_col=['Date'])
csco_df = pd.read_csv(csco, usecols=['Date', 'Close'], parse_dates=['Date'], index_col=['Date'])
amzn_df = pd.read_csv(amzn, usecols=['Date', 'Close'], parse_dates=['Date'], index_col=['Date'])
msft_df = pd.read_csv(msft, usecols=['Date', 'Close'], parse_dates=['Date'], index_col=['Date'])
ibm_df = pd.read_csv(ibm, usecols=['Date', 'Close'], parse_dates=['Date'], index_col=['Date'])

1
2
3
4
5
6

sp500_df.rename(columns={'Close': 'S&P'}, inplace=True)
aapl_df.rename(columns={'Close': 'AAPL'}, inplace=True)
csco_df.rename(columns={'Close': 'CSCO'}, inplace=True)
amzn_df.rename(columns={'Close': 'AMZN'}, inplace=True)
msft_df.rename(columns={'Close': 'MSFT'}, inplace=True)
ibm_df.rename(columns={'Close': 'IBM'}, inplace=True)

1

sp500_df
S&P
Date
1980-01-02105.760002
1980-01-03105.220001
1980-01-04106.519997
1980-01-07106.809998
1980-01-08108.949997
......
2024-04-235070.549805
2024-04-245071.629883
2024-04-255048.419922
2024-04-265099.959961
2024-04-295116.169922

11175 rows × 1 columns

1

stocks = pd.concat([sp500_df, aapl_df, csco_df, amzn_df, msft_df, ibm_df], axis=1)

1

stocks.head()
S&PAAPLCSCOAMZNMSFTIBM
Date
1980-01-02105.760002NaNNaNNaNNaN14.937859
1980-01-03105.220001NaNNaNNaNNaN15.176864
1980-01-04106.519997NaNNaNNaNNaN15.146989
1980-01-07106.809998NaNNaNNaNNaN15.087237
1980-01-08108.949997NaNNaNNaNNaN16.103010
1

stocks.tail()
S&PAAPLCSCOAMZNMSFTIBM
Date
2024-04-235070.549805166.89999448.320000179.539993407.570007182.190002
2024-04-245071.629883169.02000448.349998176.589996409.059998184.100006
2024-04-255048.419922169.88999948.099998173.669998399.040009168.910004
2024-04-265099.959961169.30000347.860001179.619995406.320007167.130005
2024-04-295116.169922173.50000047.779999180.960007402.250000167.429993
1

stocks.to_csv(stocks_dir / 'stocks.csv', index=True, index_label='Date')

GDP data

1
2
3
4
5

gdp = pd.read_csv(gdp_usa_file, parse_dates=['DATE'])
gdp.sort_values(by=['DATE'], ascending=False, inplace=True)
gdp.reset_index(inplace=True, drop=True)
gdp.rename(columns={'VALUE': 'GDP', 'DATE': 'Date'}, inplace=True)
gdp.head(8)
DateGDP
02016-04-0118436.5
12016-01-0118281.6
22015-10-0118222.8
32015-07-0118141.9
42015-04-0117998.3
52015-01-0117783.6
62014-10-0117692.2
72014-07-0117569.4

Ordered merge

1

gdp_2000_2015 = gdp[(gdp['Date'].dt.year >= 2000) & (gdp['Date'].dt.year <= 2015)]

1
2

stocks.reset_index(inplace=True)
stocks.head(5)
DateS&PAAPLCSCOAMZNMSFTIBM
01980-01-02105.760002NaNNaNNaNNaN14.937859
11980-01-03105.220001NaNNaNNaNNaN15.176864
21980-01-04106.519997NaNNaNNaNNaN15.146989
31980-01-07106.809998NaNNaNNaNNaN15.087237
41980-01-08108.949997NaNNaNNaNNaN16.103010
1

stocks_2000_2015 = stocks[(stocks['Date'].dt.year >= 2000) & (stocks['Date'].dt.year <= 2015)]

1
2

ordered_df = pd.merge_ordered(stocks_2000_2015, gdp_2000_2015, on='Date')
ordered_df.head()
DateS&PAAPLCSCOAMZNMSFTIBMGDP
02000-01-01NaNNaNNaNNaNNaNNaN10031.0
12000-01-031455.2199710.99944254.031254.46875058.28125110.898659NaN
22000-01-041399.4200440.91517951.000004.09687556.31250107.134323NaN
32000-01-051402.1099850.92857150.843753.48750056.90625110.898659NaN
42000-01-061403.4499510.84821450.000003.27812555.00000108.986618NaN

Ordered merge with ffill

1
2

ordered_df = pd.merge_ordered(stocks_2000_2015, gdp_2000_2015, on='Date', fill_method='ffill')
ordered_df.head()
DateS&PAAPLCSCOAMZNMSFTIBMGDP
02000-01-01NaNNaNNaNNaNNaNNaN10031.0
12000-01-031455.2199710.99944254.031254.46875058.28125110.89865910031.0
22000-01-041399.4200440.91517951.000004.09687556.31250107.13432310031.0
32000-01-051402.1099850.92857150.843753.48750056.90625110.89865910031.0
42000-01-061403.4499510.84821450.000003.27812555.00000108.98661810031.0
1
2
3

del software, hardware, sales_merge, sales_merged, stocks_dir, sp500_stocks, aapl
del csco, amzn, msft, ibm, sp500_df, aapl_df, csco_df, amzn_df, msft_df, ibm_df, stocks
del gdp, gdp_2000_2015, stocks_2000_2015, ordered_df

Exercises

Using merge_ordered()

This exercise uses pre-loaded DataFrames austin and houston that contain weather data from the cities Austin and Houston respectively. They have been printed in the IPython Shell for you to examine.

Weather conditions were recorded on separate days and you need to merge these two DataFrames together such that the dates are ordered. To do this, you’ll use pd.merge_ordered(). After you’re done, note the order of the rows before and after merging.

Instructions

  • Perform an ordered merge on austin and houston using pd.merge_ordered(). Store the result as tx_weather.
  • Print tx_weather. You should notice that the rows are sorted by the date but it is not possible to tell which observation came from which city.
  • Perform another ordered merge on austin and houston.
    • This time, specify the keyword arguments on='date' and suffixes=['_aus','_hus'] so that the rows can be distinguished. Store the result as tx_weather_suff.
  • Print tx_weather_suff to examine its contents. This has been done for you.
  • Perform a third ordered merge on austin and houston.
    • This time, in addition to the on and suffixes parameters, specify the keyword argument fill_method='ffill' to use forward-filling to replace NaN entries with the most recent non-null entry, and hit ‘Submit Answer’ to examine the contents of the merged DataFrames!
1
2

austin = pd.DataFrame.from_dict({'date': ['2016-01-01', '2016-02-08', '2016-01-17'], 'ratings': ['Cloudy', 'Cloudy', 'Sunny']})
houston = pd.DataFrame.from_dict({'date': ['2016-01-04', '2016-01-01', '2016-03-01'], 'ratings': ['Rainy', 'Cloudy', 'Sunny']})

1
2
3
4
5

# Perform the first ordered merge: tx_weather
tx_weather = pd.merge_ordered(austin, houston)

# Print tx_weather
tx_weather
dateratings
02016-01-01Cloudy
12016-01-04Rainy
22016-01-17Sunny
32016-02-08Cloudy
42016-03-01Sunny
1
2
3
4
5

# Perform the second ordered merge: tx_weather_suff
tx_weather_suff = pd.merge_ordered(austin, houston, on='date', suffixes=['_aus','_hus'])

# Print tx_weather_suff
tx_weather_suff
dateratings_ausratings_hus
02016-01-01CloudyCloudy
12016-01-04NaNRainy
22016-01-17SunnyNaN
32016-02-08CloudyNaN
42016-03-01NaNSunny
1
2
3
4
5

# Perform the third ordered merge: tx_weather_ffill
tx_weather_ffill = pd.merge_ordered(austin, houston, on='date', suffixes=['_aus','_hus'], fill_method='ffill')

# Print tx_weather_ffill
tx_weather_ffill
dateratings_ausratings_hus
02016-01-01CloudyCloudy
12016-01-04CloudyRainy
22016-01-17SunnyRainy
32016-02-08CloudyRainy
42016-03-01CloudySunny
1

del austin, houston, tx_weather, tx_weather_suff, tx_weather_ffill

Using merge_asof()

Similar to pd.merge_ordered(), the pd.merge_asof() function will also merge values in order using the on column, but for each row in the left DataFrame, only rows from the right DataFrame whose 'on' column values are less than the left value will be kept.

This function can be used to align disparate datetime frequencies without having to first resample.

Here, you’ll merge monthly oil prices (US dollars) into a full automobile fuel efficiency dataset. The oil and automobile DataFrames have been pre-loaded as oil and auto. The first 5 rows of each have been printed in the IPython Shell for you to explore.

These datasets will align such that the first price of the year will be broadcast into the rows of the automobiles DataFrame. This is considered correct since by the start of any given year, most automobiles for that year will have already been manufactured.

You’ll then inspect the merged DataFrame, resample by year and compute the mean 'Price' and 'mpg'. You should be able to see a trend in these two columns, that you can confirm by computing the Pearson correlation between resampled 'Price' and 'mpg'.

Instructions

  • Merge auto and oil using pd.merge_asof() with left_on='yr' and ight_on='Date'. Store the result as merged.
  • Print the tail of merged. This has been done for you.
  • Resample merged using 'A' (annual frequency), and on='Date'. Select [['mpg','Price']] and aggregate the mean. Store the result as yearly.
  • Hit Submit Answer to examine the contents of yearly and yearly.corr(), which shows the Pearson correlation between the resampled 'Price' and 'mpg'.
1
2

oil = pd.read_csv(oil_price_file, parse_dates=['Date'])
auto = pd.read_csv(auto_fuel_file, parse_dates=['yr'])

1

oil.head()
DatePrice
01970-01-013.35
11970-02-013.35
21970-03-013.35
31970-04-013.35
41970-05-013.35
1

auto.head()
mpgcyldisplhpweightaccelyroriginname
018.08307.0130350412.01970-01-01USchevrolet chevelle malibu
115.08350.0165369311.51970-01-01USbuick skylark 320
218.08318.0150343611.01970-01-01USplymouth satellite
316.08304.0150343312.01970-01-01USamc rebel sst
417.08302.0140344910.51970-01-01USford torino
1
2
3
4
5

# Merge auto and oil: merged
merged = pd.merge_asof(auto, oil, left_on='yr', right_on='Date')

# Print the tail of merged
merged.tail()
mpgcyldisplhpweightaccelyroriginnameDatePrice
38727.04140.086279015.61982-01-01USford mustang gl1982-01-0133.85
38844.0497.052213024.61982-01-01Europevw pickup1982-01-0133.85
38932.04135.084229511.61982-01-01USdodge rampage1982-01-0133.85
39028.04120.079262518.61982-01-01USford ranger1982-01-0133.85
39131.04119.082272019.41982-01-01USchevy s-101982-01-0133.85
1
2
3
4
5

# Resample merged: yearly
yearly = merged.resample('YE', on='Date')[['mpg','Price']].mean()

# Print yearly
yearly
mpgPrice
Date
1970-12-3117.6896553.35
1971-12-3121.1111113.56
1972-12-3118.7142863.56
1973-12-3117.1000003.56
1974-12-3122.76923110.11
1975-12-3120.26666711.16
1976-12-3121.57352911.16
1977-12-3123.37500013.90
1978-12-3124.06111114.85
1979-12-3125.09310314.85
1980-12-3133.80370432.50
1981-12-3130.18571438.00
1982-12-3132.00000033.85
1
2

# print yearly.corr()
yearly.corr()
mpgPrice
mpg1.0000000.948677
Price0.9486771.000000

Case Study - Summer Olympics

To cement your new skills, you’ll apply them by working on an in-depth study involving Olympic medal data. The analysis involves integrating your multi-DataFrame skills from this course and also skills you’ve gained in previous pandas courses. This is a rich dataset that will allow you to fully leverage your pandas data manipulation skills. Enjoy!

Medals in the Summer Olympics

Summer Olympic medalists 1896 to 2008 - IOC COUNTRY CODES.csv

1

pd.read_csv(so_ioc_codes_file).head(8)
CountryNOCISO code
0AfghanistanAFGAF
1AlbaniaALBAL
2AlgeriaALGDZ
3American Samoa*ASAAS
4AndorraANDAD
5AngolaANGAO
6Antigua and BarbudaANTAG
7ArgentinaARGAR

Summer Olympic medalists 1896 to 2008 - EDITIONS.tsv

1

pd.read_csv(so_editions_file, sep='\t').head(8)
EditionBronzeGoldSilverGrand TotalCityCountry
01896406447151AthensGreece
11900142178192512ParisFrance
21904123188159470St. LouisUnited States
31908211311282804LondonUnited Kingdom
41912284301300885StockholmSweden
519203554974461298AntwerpBelgium
61924285301298884ParisFrance
71928242229239710AmsterdamNetherlands

summer_1896.csv, summer_1900.csv, …, summer_2008.csv

1

pd.read_csv(so_all_medalists_file, sep='\t', header=4).head(8)
CityEditionSportDisciplineAthleteNOCGenderEventEvent_genderMedal
0Athens1896AquaticsSwimmingHAJOS, AlfredHUNMen100m freestyleMGold
1Athens1896AquaticsSwimmingHERSCHMANN, OttoAUTMen100m freestyleMSilver
2Athens1896AquaticsSwimmingDRIVAS, DimitriosGREMen100m freestyle for sailorsMBronze
3Athens1896AquaticsSwimmingMALOKINIS, IoannisGREMen100m freestyle for sailorsMGold
4Athens1896AquaticsSwimmingCHASAPIS, SpiridonGREMen100m freestyle for sailorsMSilver
5Athens1896AquaticsSwimmingCHOROPHAS, EfstathiosGREMen1200m freestyleMBronze
6Athens1896AquaticsSwimmingHAJOS, AlfredHUNMen1200m freestyleMGold
7Athens1896AquaticsSwimmingANDREOU, JoannisGREMen1200m freestyleMSilver

Reminder: loading & merging files

  • pd.read_csv() (& its many options)
  • Looping over files, e.g.,
    • [pd.read_csv(f) for f in glob(‘*.csv’)]
  • Concatenating & appending, e.g.,
    • pd.concat([df1, df2], axis=0)
    • df1.append(df2)

Case Study Explorations

Loading Olympic edition DataFrame

In this chapter, you’ll be using The Guardian’s Olympic medal dataset.

Your first task here is to prepare a DataFrame editions from a tab-separated values (TSV) file.

Initially, editions has 26 rows (one for each Olympic edition, i.e., a year in which the Olympics was held) and 7 columns: 'Edition', 'Bronze', 'Gold', 'Silver', 'Grand Total', 'City', and 'Country'.

For the analysis that follows, you won’t need the overall medal counts, so you want to keep only the useful columns from editions: 'Edition', 'Grand Total', City, and Country.

Instructions

  • Read file_path into a DataFrame called editions. The identifier file_path has been pre-defined with the filename 'Summer Olympic medallists 1896 to 2008 - EDITIONS.tsv'. You’ll have to use the option sep='\t' because the file uses tabs to delimit fields (pd.read_csv() expects commas by default).
  • Select only the columns 'Edition', 'Grand Total', 'City', and 'Country' from editions.
  • Print the final DataFrame editions in entirety (there are only 26 rows). This has been done for you, so hit ‘Submit Answer’ to see the result!
1
2
3

editions = pd.read_csv(so_editions_file, sep='\t')
editions = editions[['Edition', 'Grand Total', 'City', 'Country']]
editions.head()
EditionGrand TotalCityCountry
01896151AthensGreece
11900512ParisFrance
21904470St. LouisUnited States
31908804LondonUnited Kingdom
41912885StockholmSweden

Loading IOC codes DataFrames

Your task here is to prepare a DataFrame ioc_codes from a comma-separated values (CSV) file.

Initially, ioc_codes has 200 rows (one for each country) and 3 columns: 'Country', 'NOC', & 'ISO code'.

For the analysis that follows, you want to keep only the useful columns from ioc_codes: 'Country' and 'NOC' (the column 'NOC' contains three-letter codes representing each country).

Instructions

  • Read file_path into a DataFrame called ioc_codes. The identifier file_path has been pre-defined with the filename 'Summer Olympic medallists 1896 to 2008 - IOC COUNTRY CODES.csv'.
  • Select only the columns 'Country' and 'NOC' from ioc_codes.
  • Print the leading 5 and trailing 5 rows of the DataFrame ioc_codes (there are 200 rows in total). This has been done for you, so hit ‘Submit Answer’ to see the result!
1
2
3

ioc_codes = pd.read_csv(so_ioc_codes_file)
ioc_codes = ioc_codes[['Country', 'NOC']]
ioc_codes.head()
CountryNOC
0AfghanistanAFG
1AlbaniaALB
2AlgeriaALG
3American Samoa*ASA
4AndorraAND

Building medals DataFrame

Here, you’ll start with the DataFrame editions from the previous exercise.

You have a sequence of files summer_1896.csv, summer_1900.csv, …, summer_2008.csv, one for each Olympic edition (year).

You will build up a dictionary medals_dict with the Olympic editions (years) as keys and DataFrames as values.

The dictionary is built up inside a loop over the year of each Olympic edition (from the Index of editions).

Once the dictionary of DataFrames is built up, you will combine the DataFrames using pd.concat().

Instructions

  • Within the for loop:
    • Create the file path. This has been done for you.
    • Read file_path into a DataFrame. Assign the result to the year key of medals_dict.
    • Select only the columns ‘Athlete’, ‘NOC’, and ‘Medal’ from medals_dict[year].
    • Create a new column called ‘Edition’ in the DataFrame medals_dict[year] whose entries are all year.
  • Concatenate the dictionary of DataFrames medals_dict into a DataFame called medals. Specify the keyword argument ignore_index=True to prevent repeated integer indices.

  • Print the first and last 5 rows of medals. This has been done for you, so hit ‘Submit Answer’ to see the result!

  • Following is the code used to combine all of the editions by year
    • the individual files are not available
    • the combined dataset is provided
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

for year in editions['Edition']:

    # Create the file path: file_path
    file_path = 'summer_{:d}.csv'.format(year)
    
    # Load file_path into a DataFrame: medals_dict[year]
    medals_dict[year] = pd.read_csv(file_path)
    
    # Extract relevant columns: medals_dict[year]
    medals_dict[year] = medals_dict[year][['Athlete', 'NOC', 'Medal']]
    
    # Assign year to column 'Edition' of medals_dict
    medals_dict[year]['Edition'] = year
    
# Concatenate medals_dict: medals
medals = pd.concat(medals_dict, ignore_index=True)

1
2
3

medals = pd.read_csv(so_all_medalists_file, sep='\t', header=4)
medals = medals[['Athlete', 'NOC', 'Medal', 'Edition']]
medals.head()
AthleteNOCMedalEdition
0HAJOS, AlfredHUNGold1896
1HERSCHMANN, OttoAUTSilver1896
2DRIVAS, DimitriosGREBronze1896
3MALOKINIS, IoannisGREGold1896
4CHASAPIS, SpiridonGRESilver1896

Quantifying Performance

Constructing a pivot table

  • Apply DataFrame pivot_table() method
    • index: column to use as index of pivot table
    • values: column(s) to aggregate
    • aggfunc: function to apply for aggregation
    • columns: categories as columns of pivot table

Case Study Explorations

Counting medals by country/edition in a pivot table

Here, you’ll start with the concatenated DataFrame medals from the previous exercise.

You can construct a pivot table to see the number of medals each country won in each year. The result is a new DataFrame with the Olympic edition on the Index and with 138 country NOC codes as columns. If you want a refresher on pivot tables, it may be useful to refer back to the relevant exercises in Manipulating DataFrames with pandas.

Instructions

  • Construct a pivot table from the DataFrame medals, aggregating by count (by specifying the aggfunc parameter). Use 'Edition' as the index, 'Athlete' for the values, and 'NOC' for the columns.
  • Print the first & last 5 rows of medal_counts. This has been done for you, so hit ‘Submit Answer’ to see the results!
1
2
3
4
5

# Construct the pivot_table: medal_counts
medal_counts = medals.pivot_table(index='Edition', columns='NOC', values='Athlete', aggfunc='count')

# Print the first & last 5 rows of medal_counts
medal_counts.head()
NOCAFGAHOALGANZARGARMAUSAUTAZEBAHBARBDIBELBERBLRBOHBRABULBWICANCHICHNCIVCMRCOLCRCCROCUBCZEDENDJIDOMECUEGYERIESPESTETHEUAEUNFINFRAFRGGBRGDRGEOGERGHAGREGUYHAIHKGHUNINAINDIOPIRIIRLIRQISLISRISVITAJAMJPNKAZKENKGZKORKSAKUWLATLIBLTULUXMARMASMDAMEXMGLMKDMOZMRINAMNEDNGRNIGNORNZLPAKPANPARPERPHIPOLPORPRKPURQATROURSARU1RUSSCGSENSINSLOSRBSRISUDSUISURSVKSWESYRTANTCHTGATHATJKTOGTPETRITUNTURUAEUGAUKRURSURUUSAUZBVENVIEYUGZAMZIMZZX
Edition
1896NaNNaNNaNNaNNaNNaN2.05.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN6.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN11.0NaN7.0NaNNaN33.0NaN52.0NaNNaNNaN6.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN3.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN20.0NaNNaNNaNNaNNaNNaN6.0
1900NaNNaNNaNNaNNaNNaN5.06.0NaNNaNNaNNaN39.0NaNNaN2.0NaNNaNNaN2.0NaNNaNNaNNaNNaNNaNNaN2.0NaN6.0NaNNaNNaNNaNNaN2.0NaNNaNNaNNaNNaN185.0NaN78.0NaNNaN40.0NaNNaNNaNNaNNaN5.0NaN2.0NaNNaNNaNNaNNaNNaNNaN4.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN20.0NaNNaN9.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN15.0NaNNaN1.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN55.0NaNNaNNaNNaNNaNNaN34.0
1904NaNNaNNaNNaNNaNNaNNaN1.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN35.0NaNNaNNaNNaNNaNNaNNaN9.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN2.0NaNNaN13.0NaN2.0NaNNaNNaN4.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN2.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN394.0NaNNaNNaNNaNNaNNaN8.0
1908NaNNaNNaN19.0NaNNaNNaN1.0NaNNaNNaNNaN31.0NaNNaN5.0NaNNaNNaN51.0NaNNaNNaNNaNNaNNaNNaNNaNNaN15.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN29.035.0NaN347.0NaNNaN22.0NaN3.0NaNNaNNaN18.0NaNNaNNaNNaNNaNNaNNaNNaNNaN7.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN11.0NaNNaN44.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN2.03.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN98.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN63.0NaNNaNNaNNaNNaNNaNNaN
1912NaNNaNNaN10.0NaNNaNNaN14.0NaNNaNNaNNaN19.0NaNNaNNaNNaNNaNNaN8.0NaNNaNNaNNaNNaNNaNNaNNaNNaN84.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN67.025.0NaN160.0NaNNaN52.0NaN2.0NaNNaNNaN30.0NaNNaNNaNNaNNaNNaNNaNNaNNaN21.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN22.0NaNNaN76.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN7.014.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN173.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN101.0NaNNaNNaNNaNNaNNaNNaN
1

medal_counts.tail()
NOCAFGAHOALGANZARGARMAUSAUTAZEBAHBARBDIBELBERBLRBOHBRABULBWICANCHICHNCIVCMRCOLCRCCROCUBCZEDENDJIDOMECUEGYERIESPESTETHEUAEUNFINFRAFRGGBRGDRGEOGERGHAGREGUYHAIHKGHUNINAINDIOPIRIIRLIRQISLISRISVITAJAMJPNKAZKENKGZKORKSAKUWLATLIBLTULUXMARMASMDAMEXMGLMKDMOZMRINAMNEDNGRNIGNORNZLPAKPANPARPERPHIPOLPORPRKPURQATROURSARU1RUSSCGSENSINSLOSRBSRISUDSUISURSVKSWESYRTANTCHTGATHATJKTOGTPETRITUNTURUAEUGAUKRURSURUUSAUZBVENVIEYUGZAMZIMZZX
Edition
1992NaNNaN2.0NaN2.0NaN57.06.0NaN1.0NaNNaN3.0NaNNaNNaN14.017.0NaN44.0NaN83.0NaNNaN1.0NaN15.071.0NaN14.0NaNNaNNaNNaNNaN66.03.03.0NaN223.07.057.0NaN50.0NaNNaN198.013.02.0NaNNaNNaN45.06.0NaN3.03.02.0NaNNaN2.0NaN46.04.047.0NaN8.0NaN49.0NaNNaN3.0NaN13.0NaN3.02.0NaN1.02.0NaNNaNNaN2.033.011.0NaN23.015.016.0NaNNaN1.01.042.0NaN10.01.01.053.03.0NaNNaNNaNNaNNaN6.0NaNNaNNaN1.01.0NaN35.0NaNNaN8.0NaN1.0NaNNaN20.0NaNNaN6.0NaNNaNNaNNaNNaN224.0NaNNaNNaNNaNNaNNaNNaN
1996NaNNaN3.0NaN20.02.0132.03.01.05.0NaN1.06.0NaN23.0NaN64.021.0NaN51.0NaN110.0NaNNaNNaN1.029.057.013.024.0NaNNaN1.0NaNNaN67.0NaN3.0NaNNaN4.051.0NaN26.0NaN2.0124.0NaN8.0NaNNaN1.043.06.01.0NaN3.04.0NaNNaN1.0NaN71.016.043.011.08.0NaN66.0NaNNaN1.0NaN12.0NaN2.03.03.01.01.0NaN1.0NaN2.073.026.0NaN25.09.0NaNNaNNaNNaN1.021.03.05.01.0NaN38.05.0NaN115.0NaNNaNNaN2.0NaNNaNNaN11.0NaN3.031.01.0NaNNaN1.02.0NaNNaN1.02.01.06.0NaN1.034.0NaNNaN260.02.0NaNNaN26.01.0NaNNaN
2000NaNNaN5.0NaN20.01.0183.04.03.06.01.0NaN7.0NaN22.0NaN48.013.0NaN31.018.079.0NaN18.01.02.010.069.09.025.0NaNNaNNaNNaNNaN43.03.08.0NaNNaN5.066.0NaN55.0NaN6.0119.0NaN18.0NaNNaNNaN53.08.01.0NaN4.01.0NaN1.01.0NaN65.023.043.07.07.01.073.02.01.03.0NaN17.0NaN5.0NaN2.06.0NaN1.01.0NaNNaN79.08.0NaN44.04.0NaNNaNNaNNaNNaN24.02.04.0NaN1.046.05.0NaN188.0NaNNaNNaN3.0NaN1.0NaN14.0NaN6.032.0NaNNaNNaNNaN3.0NaNNaN5.02.0NaN5.0NaNNaN35.0NaN1.0248.04.0NaN1.026.0NaNNaNNaN
2004NaNNaNNaNNaN47.0NaN157.08.05.02.0NaNNaN3.0NaN17.0NaN40.017.0NaN17.04.094.0NaN1.02.0NaN20.061.012.029.0NaN1.0NaN5.01.027.03.07.0NaNNaN2.053.0NaN57.0NaN4.0149.0NaN31.0NaNNaN2.040.05.01.0NaN6.0NaNNaNNaN2.0NaN102.013.094.08.07.0NaN52.0NaNNaN4.0NaN3.0NaN3.0NaNNaN4.01.0NaNNaNNaNNaN76.08.0NaN7.06.0NaNNaN17.0NaNNaN12.03.05.0NaNNaN39.010.0NaN192.014.0NaNNaN5.0NaNNaNNaN7.0NaN10.012.01.0NaNNaNNaN8.0NaNNaN9.01.0NaN10.01.0NaN48.0NaNNaN264.05.02.0NaNNaNNaN3.0NaN
20081.0NaN2.0NaN51.06.0149.03.07.05.0NaNNaN5.0NaN30.0NaN75.05.0NaN34.01.0184.0NaN1.02.0NaN5.047.07.018.0NaN2.01.01.0NaN71.03.07.0NaNNaN5.076.0NaN77.0NaN6.0101.0NaN7.0NaNNaNNaN27.07.03.0NaN2.03.0NaN14.01.0NaN42.017.051.013.014.02.078.0NaNNaN3.0NaN5.0NaN2.01.01.04.04.0NaNNaN1.0NaN62.024.0NaN22.014.0NaN1.0NaNNaNNaN20.02.06.0NaNNaN22.01.0NaN143.0NaNNaN3.05.015.0NaN1.011.0NaN10.07.0NaNNaNNaNNaN4.02.01.04.05.01.08.0NaNNaN31.0NaNNaN315.06.01.01.0NaNNaN4.0NaN

Computing fraction of medals per Olympic edition

In this exercise, you’ll start with the DataFrames editions, medals, & medal_counts from prior exercises.

You can extract a Series with the total number of medals awarded in each Olympic edition.

The DataFrame medal_counts can be divided row-wise by the total number of medals awarded each edition; the method .divide() performs the broadcast as you require.

This gives you a normalized indication of each country’s performance in each edition.

Instructions

  • Set the index of the DataFrame editions to be 'Edition' (using the method .set_index()). Save the result as totals.
  • Extract the 'Grand Total' column from totals and assign the result back to totals.
  • Divide the DataFrame medal_counts by totals along each row. You will have to use the .divide() method with the option axis='rows'. Assign the result to fractions.
  • Print first & last 5 rows of the DataFrame fractions. This has been done for you, so hit ‘Submit Answer’ to see the results!
1
2
3

# Set Index of editions: totals
totals = editions.set_index('Edition')
totals.head()
Grand TotalCityCountry
Edition
1896151AthensGreece
1900512ParisFrance
1904470St. LouisUnited States
1908804LondonUnited Kingdom
1912885StockholmSweden
1
2
3

# Reassign totals['Grand Total']: totals
totals = totals['Grand Total']
totals.head()

1
2
3
4
5
6
7

Edition
1896    151
1900    512
1904    470
1908    804
1912    885
Name: Grand Total, dtype: int64

1
2
3
4
5

# Divide medal_counts by totals: fractions
fractions = medal_counts.divide(totals, axis='rows')

# Print first & last 5 rows of fractions
fractions.head()
NOCAFGAHOALGANZARGARMAUSAUTAZEBAHBARBDIBELBERBLRBOHBRABULBWICANCHICHNCIVCMRCOLCRCCROCUBCZEDENDJIDOMECUEGYERIESPESTETHEUAEUNFINFRAFRGGBRGDRGEOGERGHAGREGUYHAIHKGHUNINAINDIOPIRIIRLIRQISLISRISVITAJAMJPNKAZKENKGZKORKSAKUWLATLIBLTULUXMARMASMDAMEXMGLMKDMOZMRINAMNEDNGRNIGNORNZLPAKPANPARPERPHIPOLPORPRKPURQATROURSARU1RUSSCGSENSINSLOSRBSRISUDSUISURSVKSWESYRTANTCHTGATHATJKTOGTPETRITUNTURUAEUGAUKRURSURUUSAUZBVENVIEYUGZAMZIMZZX
Edition
1896NaNNaNNaNNaNNaNNaN0.0132450.033113NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.039735NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.072848NaN0.046358NaNNaN0.218543NaN0.344371NaNNaNNaN0.039735NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.019868NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.132450NaNNaNNaNNaNNaNNaN0.039735
1900NaNNaNNaNNaNNaNNaN0.0097660.011719NaNNaNNaNNaN0.076172NaNNaN0.003906NaNNaNNaN0.003906NaNNaNNaNNaNNaNNaNNaN0.003906NaN0.011719NaNNaNNaNNaNNaN0.003906NaNNaNNaNNaNNaN0.361328NaN0.152344NaNNaN0.078125NaNNaNNaNNaNNaN0.009766NaN0.003906NaNNaNNaNNaNNaNNaNNaN0.007812NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.039062NaNNaN0.017578NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.029297NaNNaN0.001953NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.107422NaNNaNNaNNaNNaNNaN0.066406
1904NaNNaNNaNNaNNaNNaNNaN0.002128NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.074468NaNNaNNaNNaNNaNNaNNaN0.019149NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.004255NaNNaN0.027660NaN0.004255NaNNaNNaN0.008511NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.004255NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.838298NaNNaNNaNNaNNaNNaN0.017021
1908NaNNaNNaN0.023632NaNNaNNaN0.001244NaNNaNNaNNaN0.038557NaNNaN0.006219NaNNaNNaN0.063433NaNNaNNaNNaNNaNNaNNaNNaNNaN0.018657NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.0360700.043532NaN0.431592NaNNaN0.027363NaN0.003731NaNNaNNaN0.022388NaNNaNNaNNaNNaNNaNNaNNaNNaN0.008706NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.013682NaNNaN0.054726NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.0024880.003731NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.121891NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.078358NaNNaNNaNNaNNaNNaNNaN
1912NaNNaNNaN0.011299NaNNaNNaN0.015819NaNNaNNaNNaN0.021469NaNNaNNaNNaNNaNNaN0.009040NaNNaNNaNNaNNaNNaNNaNNaNNaN0.094915NaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.0757060.028249NaN0.180791NaNNaN0.058757NaN0.002260NaNNaNNaN0.033898NaNNaNNaNNaNNaNNaNNaNNaNNaN0.023729NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.024859NaNNaN0.085876NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.0079100.015819NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.195480NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.114124NaNNaNNaNNaNNaNNaNNaN
1

fractions.tail()
NOCAFGAHOALGANZARGARMAUSAUTAZEBAHBARBDIBELBERBLRBOHBRABULBWICANCHICHNCIVCMRCOLCRCCROCUBCZEDENDJIDOMECUEGYERIESPESTETHEUAEUNFINFRAFRGGBRGDRGEOGERGHAGREGUYHAIHKGHUNINAINDIOPIRIIRLIRQISLISRISVITAJAMJPNKAZKENKGZKORKSAKUWLATLIBLTULUXMARMASMDAMEXMGLMKDMOZMRINAMNEDNGRNIGNORNZLPAKPANPARPERPHIPOLPORPRKPURQATROURSARU1RUSSCGSENSINSLOSRBSRISUDSUISURSVKSWESYRTANTCHTGATHATJKTOGTPETRITUNTURUAEUGAUKRURSURUUSAUZBVENVIEYUGZAMZIMZZX
Edition
1992NaNNaN0.001173NaN0.001173NaN0.0334310.003519NaN0.000587NaNNaN0.001760NaNNaNNaN0.0082110.009971NaN0.025806NaN0.048680NaNNaN0.000587NaN0.0087980.041642NaN0.008211NaNNaNNaNNaNNaN0.0387100.0017600.001760NaN0.1307920.0041060.033431NaN0.029326NaNNaN0.1161290.0076250.001173NaNNaNNaN0.0263930.003519NaN0.001760.0017600.001173NaNNaN0.001173NaN0.0269790.0023460.027566NaN0.004692NaN0.028739NaNNaN0.001760NaN0.007625NaN0.0017600.001173NaN0.0005870.001173NaNNaNNaN0.0011730.0193550.006452NaN0.0134900.0087980.009384NaNNaN0.0005870.0005870.024633NaN0.0058650.0005870.0005870.0310850.001760NaNNaNNaNNaNNaN0.003519NaNNaNNaN0.0005870.000587NaN0.020528NaNNaN0.004692NaN0.000587NaNNaN0.011730NaNNaN0.003519NaNNaNNaNNaNNaN0.131378NaNNaNNaNNaNNaNNaNNaN
1996NaNNaN0.001614NaN0.0107580.0010760.0710060.0016140.0005380.002690NaN0.0005380.003228NaN0.012372NaN0.0344270.011296NaN0.027434NaN0.059172NaNNaNNaN0.0005380.0156000.0306620.0069930.012910NaNNaN0.000538NaNNaN0.036041NaN0.001614NaNNaN0.0021520.027434NaN0.013986NaN0.0010760.066703NaN0.004303NaNNaN0.0005380.0231310.0032280.000538NaN0.0016140.002152NaNNaN0.000538NaN0.0381930.0086070.0231310.0059170.004303NaN0.035503NaNNaN0.000538NaN0.006455NaN0.0010760.0016140.0016140.0005380.000538NaN0.000538NaN0.0010760.0392680.013986NaN0.0134480.004841NaNNaNNaNNaN0.0005380.0112960.0016140.0026900.000538NaN0.0204410.002690NaN0.061861NaNNaNNaN0.001076NaNNaNNaN0.005917NaN0.0016140.0166760.000538NaNNaN0.0005380.001076NaNNaN0.0005380.0010760.0005380.003228NaN0.0005380.018289NaNNaN0.1398600.001076NaNNaN0.0139860.000538NaNNaN
2000NaNNaN0.002481NaN0.0099260.0004960.0908190.0019850.0014890.0029780.000496NaN0.003474NaN0.010918NaN0.0238210.006452NaN0.0153850.0089330.039206NaN0.0089330.0004960.0009930.0049630.0342430.0044670.012407NaNNaNNaNNaNNaN0.0213400.0014890.003970NaNNaN0.0024810.032754NaN0.027295NaN0.0029780.059057NaN0.008933NaNNaNNaN0.0263030.0039700.000496NaN0.0019850.000496NaN0.0004960.000496NaN0.0322580.0114140.0213400.0034740.0034740.0004960.0362280.0009930.0004960.001489NaN0.008437NaN0.002481NaN0.0009930.002978NaN0.0004960.000496NaNNaN0.0392060.003970NaN0.0218360.001985NaNNaNNaNNaNNaN0.0119110.0009930.001985NaN0.0004960.0228290.002481NaN0.093300NaNNaNNaN0.001489NaN0.000496NaN0.006948NaN0.0029780.015881NaNNaNNaNNaN0.001489NaNNaN0.0024810.000993NaN0.002481NaNNaN0.017370NaN0.0004960.1230770.001985NaN0.0004960.012903NaNNaNNaN
2004NaNNaNNaNNaN0.023524NaN0.0785790.0040040.0025030.001001NaNNaN0.001502NaN0.008509NaN0.0200200.008509NaN0.0085090.0020020.047047NaN0.0005010.001001NaN0.0100100.0305310.0060060.014515NaN0.000501NaN0.0025030.0005010.0135140.0015020.003504NaNNaN0.0010010.026527NaN0.028529NaN0.0020020.074575NaN0.015516NaNNaN0.0010010.0200200.0025030.000501NaN0.003003NaNNaNNaN0.001001NaN0.0510510.0065070.0470470.0040040.003504NaN0.026026NaNNaN0.002002NaN0.001502NaN0.001502NaNNaN0.0020020.000501NaNNaNNaNNaN0.0380380.004004NaN0.0035040.003003NaNNaN0.008509NaNNaN0.0060060.0015020.002503NaNNaN0.0195200.005005NaN0.0960960.007007NaNNaN0.002503NaNNaNNaN0.003504NaN0.0050050.0060060.000501NaNNaNNaN0.004004NaNNaN0.0045050.000501NaN0.0050050.000501NaN0.024024NaNNaN0.1321320.0025030.001001NaNNaNNaN0.001502NaN
20080.00049NaN0.000979NaN0.0249760.0029380.0729680.0014690.0034280.002449NaNNaN0.002449NaN0.014691NaN0.0367290.002449NaN0.0166500.0004900.090108NaN0.0004900.000979NaN0.0024490.0230170.0034280.008815NaN0.0009790.0004900.000490NaN0.0347700.0014690.003428NaNNaN0.0024490.037218NaN0.037708NaN0.0029380.049461NaN0.003428NaNNaNNaN0.0132220.0034280.001469NaN0.0009790.001469NaN0.0068560.000490NaN0.0205680.0083250.0249760.0063660.0068560.0009790.038198NaNNaN0.001469NaN0.002449NaN0.0009790.0004900.0004900.0019590.001959NaNNaN0.00049NaN0.0303620.011753NaN0.0107740.006856NaN0.00049NaNNaNNaN0.0097940.0009790.002938NaNNaN0.0107740.000490NaN0.070029NaNNaN0.0014690.0024490.007346NaN0.000490.005387NaN0.0048970.003428NaNNaNNaNNaN0.0019590.0009790.000490.0019590.0024490.0004900.003918NaNNaN0.015181NaNNaN0.1542610.0029380.0004900.000490NaNNaN0.001959NaN

Computing percentage change in fraction of medals won

Here, you’ll start with the DataFrames editions, medals, medal_counts, & fractions from prior exercises.

To see if there is a host country advantage, you first want to see how the fraction of medals won changes from edition to edition.

The expanding mean provides a way to see this down each column. It is the value of the mean with all the data available up to that point in time. If you are interested in learning more about pandas’ expanding transformations, this section of the pandas documentation has additional information.

Instructions

  • Create mean_fractions by chaining the methods .expanding().mean() to fractions.
  • Compute the percentage change in mean_fractions down each column by applying .pct_change() and multiplying by 100. Assign the result to fractions_change.
  • Reset the index of fractions_change using the .reset_index() method. This will make 'Edition' an ordinary column.
  • Print the first and last 5 rows of the DataFrame fractions_change. This has been done for you, so hit ‘Submit Answer’ to see the results!
1
2
3

# Apply the expanding mean: mean_fractions
mean_fractions = fractions.expanding().mean()
mean_fractions.head()
NOCAFGAHOALGANZARGARMAUSAUTAZEBAHBARBDIBELBERBLRBOHBRABULBWICANCHICHNCIVCMRCOLCRCCROCUBCZEDENDJIDOMECUEGYERIESPESTETHEUAEUNFINFRAFRGGBRGDRGEOGERGHAGREGUYHAIHKGHUNINAINDIOPIRIIRLIRQISLISRISVITAJAMJPNKAZKENKGZKORKSAKUWLATLIBLTULUXMARMASMDAMEXMGLMKDMOZMRINAMNEDNGRNIGNORNZLPAKPANPARPERPHIPOLPORPRKPURQATROURSARU1RUSSCGSENSINSLOSRBSRISUDSUISURSVKSWESYRTANTCHTGATHATJKTOGTPETRITUNTURUAEUGAUKRURSURUUSAUZBVENVIEYUGZAMZIMZZX
Edition
1896NaNNaNNaNNaNNaNNaN0.0132450.033113NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.039735NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.072848NaN0.046358NaNNaN0.218543NaN0.344371NaNNaNNaN0.039735NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.019868NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.132450NaNNaNNaNNaNNaNNaN0.039735
1900NaNNaNNaNNaNNaNNaN0.0115050.022416NaNNaNNaNNaN0.076172NaNNaN0.003906NaNNaNNaN0.003906NaNNaNNaNNaNNaNNaNNaN0.003906NaN0.025727NaNNaNNaNNaNNaN0.003906NaNNaNNaNNaNNaN0.217088NaN0.099351NaNNaN0.148334NaN0.344371NaNNaNNaN0.024750NaN0.003906NaNNaNNaNNaNNaNNaNNaN0.007812NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.039062NaNNaN0.017578NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.024582NaNNaN0.001953NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.119936NaNNaNNaNNaNNaNNaN0.053071
1904NaNNaNNaNNaNNaNNaN0.0115050.015653NaNNaNNaNNaN0.076172NaNNaN0.003906NaNNaNNaN0.039187NaNNaNNaNNaNNaNNaNNaN0.011528NaN0.025727NaNNaNNaNNaNNaN0.003906NaNNaNNaNNaNNaN0.217088NaN0.067652NaNNaN0.108109NaN0.174313NaNNaNNaN0.019337NaN0.003906NaNNaNNaNNaNNaNNaNNaN0.007812NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.039062NaNNaN0.017578NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.017807NaNNaN0.001953NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.359390NaNNaNNaNNaNNaNNaN0.041054
1908NaNNaNNaN0.023632NaNNaN0.0115050.012051NaNNaNNaNNaN0.057365NaNNaN0.005063NaNNaNNaN0.047269NaNNaNNaNNaNNaNNaNNaN0.011528NaN0.023370NaNNaNNaNNaNNaN0.003906NaNNaNNaNNaN0.0360700.159236NaN0.158637NaNNaN0.087923NaN0.117453NaNNaNNaN0.020100NaN0.003906NaNNaNNaNNaNNaNNaNNaN0.008259NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.026372NaNNaN0.036152NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.0024880.003731NaNNaNNaNNaNNaNNaNNaNNaN0.017807NaNNaN0.061922NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.289132NaNNaNNaNNaNNaNNaN0.041054
1912NaNNaNNaN0.017466NaNNaN0.0115050.012804NaNNaNNaNNaN0.045399NaNNaN0.005063NaNNaNNaN0.037712NaNNaNNaNNaNNaNNaNNaN0.011528NaN0.041256NaNNaNNaNNaNNaN0.003906NaNNaNNaNNaN0.0558880.126489NaN0.163068NaNNaN0.082090NaN0.088654NaNNaNNaN0.022860NaN0.003906NaNNaNNaNNaNNaNNaNNaN0.013416NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.025868NaNNaN0.052727NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.0051990.009775NaNNaNNaNNaNNaNNaNNaNNaN0.017807NaNNaN0.106441NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.254131NaNNaNNaNNaNNaNNaN0.041054
1
2
3

# Compute the percentage change: fractions_change
fractions_change = mean_fractions.pct_change()*100
fractions_change.head()
NOCAFGAHOALGANZARGARMAUSAUTAZEBAHBARBDIBELBERBLRBOHBRABULBWICANCHICHNCIVCMRCOLCRCCROCUBCZEDENDJIDOMECUEGYERIESPESTETHEUAEUNFINFRAFRGGBRGDRGEOGERGHAGREGUYHAIHKGHUNINAINDIOPIRIIRLIRQISLISRISVITAJAMJPNKAZKENKGZKORKSAKUWLATLIBLTULUXMARMASMDAMEXMGLMKDMOZMRINAMNEDNGRNIGNORNZLPAKPANPARPERPHIPOLPORPRKPURQATROURSARU1RUSSCGSENSINSLOSRBSRISUDSUISURSVKSWESYRTANTCHTGATHATJKTOGTPETRITUNTURUAEUGAUKRURSURUUSAUZBVENVIEYUGZAMZIMZZX
Edition
1896NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
1900NaNNaNNaNNaNNaNNaN-13.134766-32.304688NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN-35.253906NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN198.002486NaN114.313616NaNNaN-32.125947NaN0.000000NaNNaNNaN-37.711589NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN23.730469NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN-9.448242NaNNaNNaNNaNNaNNaN33.561198
1904NaNNaNNaNNaNNaNNaN0.000000-30.169386NaNNaNNaNNaN0.000000NaNNaN0.00000NaNNaNNaN903.191489NaNNaNNaNNaNNaNNaNNaN195.106383NaN0.000000NaNNaNNaNNaNNaN0.0NaNNaNNaNNaNNaN0.000000NaN-31.905623NaNNaN-27.117728NaN-49.382160NaNNaNNaN-21.871362NaN0.0NaNNaNNaNNaNNaNNaNNaN0.000000NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.000000NaNNaN0.000000NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN-27.563146NaNNaN0.000000NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN199.651245NaNNaNNaNNaNNaNNaN-22.642384
1908NaNNaNNaNNaNNaNNaN0.000000-23.013510NaNNaNNaNNaN-24.690649NaNNaN29.60199NaNNaNNaN20.623816NaNNaNNaNNaNNaNNaNNaN0.000000NaN-9.160582NaNNaNNaNNaNNaN0.0NaNNaNNaNNaNNaN-26.649046NaN134.489218NaNNaN-18.672328NaN-32.619801NaNNaNNaN3.944407NaN0.0NaNNaNNaNNaNNaNNaNNaN5.721393NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN-32.487562NaNNaN105.666114NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.000000NaNNaN3070.398010NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN-19.549222NaNNaNNaNNaNNaNNaN0.000000
1912NaNNaNNaN-26.092774NaNNaN0.0000006.254438NaNNaNNaNNaN-20.858191NaNNaN0.00000NaNNaNNaN-20.219099NaNNaNNaNNaNNaNNaNNaN0.000000NaN76.534540NaNNaNNaNNaNNaN0.0NaNNaNNaNNaN54.944477-20.564982NaN2.793012NaNNaN-6.634382NaN-24.518979NaNNaNNaN13.729899NaN0.0NaNNaNNaNNaNNaNNaNNaN62.430575NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN-1.912744NaNNaN45.846353NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN108.983051161.977401NaNNaNNaNNaNNaNNaNNaNNaN0.000000NaNNaN71.896226NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN-12.105733NaNNaNNaNNaNNaNNaN0.000000
1
2
3
4
5

# Reset the index of fractions_change: fractions_change
fractions_change = fractions_change.reset_index()

# Print first & last 5 rows of fractions_change
fractions_change.head()
NOCEditionAFGAHOALGANZARGARMAUSAUTAZEBAHBARBDIBELBERBLRBOHBRABULBWICANCHICHNCIVCMRCOLCRCCROCUBCZEDENDJIDOMECUEGYERIESPESTETHEUAEUNFINFRAFRGGBRGDRGEOGERGHAGREGUYHAIHKGHUNINAINDIOPIRIIRLIRQISLISRISVITAJAMJPNKAZKENKGZKORKSAKUWLATLIBLTULUXMARMASMDAMEXMGLMKDMOZMRINAMNEDNGRNIGNORNZLPAKPANPARPERPHIPOLPORPRKPURQATROURSARU1RUSSCGSENSINSLOSRBSRISUDSUISURSVKSWESYRTANTCHTGATHATJKTOGTPETRITUNTURUAEUGAUKRURSURUUSAUZBVENVIEYUGZAMZIMZZX
01896NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
11900NaNNaNNaNNaNNaNNaN-13.134766-32.304688NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN-35.253906NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN198.002486NaN114.313616NaNNaN-32.125947NaN0.000000NaNNaNNaN-37.711589NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN23.730469NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN-9.448242NaNNaNNaNNaNNaNNaN33.561198
21904NaNNaNNaNNaNNaNNaN0.000000-30.169386NaNNaNNaNNaN0.000000NaNNaN0.00000NaNNaNNaN903.191489NaNNaNNaNNaNNaNNaNNaN195.106383NaN0.000000NaNNaNNaNNaNNaN0.0NaNNaNNaNNaNNaN0.000000NaN-31.905623NaNNaN-27.117728NaN-49.382160NaNNaNNaN-21.871362NaN0.0NaNNaNNaNNaNNaNNaNNaN0.000000NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.000000NaNNaN0.000000NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN-27.563146NaNNaN0.000000NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN199.651245NaNNaNNaNNaNNaNNaN-22.642384
31908NaNNaNNaNNaNNaNNaN0.000000-23.013510NaNNaNNaNNaN-24.690649NaNNaN29.60199NaNNaNNaN20.623816NaNNaNNaNNaNNaNNaNNaN0.000000NaN-9.160582NaNNaNNaNNaNNaN0.0NaNNaNNaNNaNNaN-26.649046NaN134.489218NaNNaN-18.672328NaN-32.619801NaNNaNNaN3.944407NaN0.0NaNNaNNaNNaNNaNNaNNaN5.721393NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN-32.487562NaNNaN105.666114NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN0.000000NaNNaN3070.398010NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN-19.549222NaNNaNNaNNaNNaNNaN0.000000
41912NaNNaNNaN-26.092774NaNNaN0.0000006.254438NaNNaNNaNNaN-20.858191NaNNaN0.00000NaNNaNNaN-20.219099NaNNaNNaNNaNNaNNaNNaN0.000000NaN76.534540NaNNaNNaNNaNNaN0.0NaNNaNNaNNaN54.944477-20.564982NaN2.793012NaNNaN-6.634382NaN-24.518979NaNNaNNaN13.729899NaN0.0NaNNaNNaNNaNNaNNaNNaN62.430575NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN-1.912744NaNNaN45.846353NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN108.983051161.977401NaNNaNNaNNaNNaNNaNNaNNaN0.000000NaNNaN71.896226NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN-12.105733NaNNaNNaNNaNNaNNaN0.000000
1

fractions_change.tail()
NOCEditionAFGAHOALGANZARGARMAUSAUTAZEBAHBARBDIBELBERBLRBOHBRABULBWICANCHICHNCIVCMRCOLCRCCROCUBCZEDENDJIDOMECUEGYERIESPESTETHEUAEUNFINFRAFRGGBRGDRGEOGERGHAGREGUYHAIHKGHUNINAINDIOPIRIIRLIRQISLISRISVITAJAMJPNKAZKENKGZKORKSAKUWLATLIBLTULUXMARMASMDAMEXMGLMKDMOZMRINAMNEDNGRNIGNORNZLPAKPANPARPERPHIPOLPORPRKPURQATROURSARU1RUSSCGSENSINSLOSRBSRISUDSUISURSVKSWESYRTANTCHTGATHATJKTOGTPETRITUNTURUAEUGAUKRURSURUUSAUZBVENVIEYUGZAMZIMZZX
211992NaN0.0-7.2140760.0-6.767308NaN2.754114-3.034840NaN-24.111659NaNNaN-4.8428050.0NaN0.0-0.744112-5.6704090.00.1755220.0000004.2406300.00.000000-13.6155100.000000NaN31.154706NaN-2.9659920.00.000000NaN0.000000NaN32.943248-15.2724030.3130860.0NaN-4.446724-2.2463450.0-2.7311000.0NaN2.172297167.686073-7.4006720.00.0NaN-2.26096740.6744870.000000NaN-4.863231-5.7799110.00.000000NaN0.0-2.358676-2.946081-0.484390NaN-3.902657NaN14.789187NaNNaN-3.3460810.0NaN0.04.687834NaNNaN-6.282029-5.830225NaNNaNNaNNaN-1.54105966.9734430.0-2.8455412.032222-2.4827330.00000NaN-20.453817-11.275721-0.3014510.000000-0.281514-11.929985NaN0.676152-6.3087570.0NaNNaN0.00.000000NaNNaN0.000000NaN-4.856259-4.662757NaN-2.9921700.0000000.0-4.855929NaN-4.038382NaNNaN290.4275990.0000000.000000-2.767259NaN0.000000NaN0.00.000000-1.329330NaN0.000000NaN0.0000000.0000000.0000000.0
221996NaN0.08.9592110.01.306696NaN10.743275-3.876773NaN16.793717NaNNaN-4.2302120.0NaN0.020.111002-4.3635180.00.4688670.0000007.8602470.00.0000000.000000-8.41850538.65877711.355483NaN-1.7677500.00.000000NaN0.000000NaN19.2831230.000000-0.9789650.00.0-4.574592-2.5296960.0-3.4969010.0NaN-2.8700810.000000-6.0699720.00.0NaN-2.4228476.078215-7.4347300.0-4.5890290.5182000.00.000000-27.0710060.0-1.11483417.777438-1.362980NaN-3.843949NaN15.161867NaNNaN-17.8861910.0-7.6695490.0-6.12841718.786982NaN-5.880390-9.762539NaNNaNNaN-4.1420122.28635375.5280000.0-2.612566-1.8516080.0000000.00000NaN0.000000-9.509095-3.505201-4.506051-10.906079-9.1111250.000000-2.067262-4.6432420.0NaNNaN0.00.000000-34.714004NaN0.000000NaN-3.3103010.000000NaN-3.160616-10.7584720.00.000000NaN12.054737NaNNaN-17.036096-11.549636-16.531330-2.865545NaN-15.722487NaN0.00.000000-1.010378NaN0.000000NaN-2.667732-10.7584720.0000000.0
232000NaN0.019.7624880.00.515190-26.93548412.554986-3.46422188.38709711.693273NaN0.0-3.9379480.0-5.8765780.07.987043-5.7454910.0-1.75852044.530287-3.8512780.0326.433237-11.07892622.518245-19.77240410.379886-18.064516-1.7485050.00.0000000.0000000.000000NaN5.576306-12.73222016.4284810.00.0-4.272276-1.9860480.0-2.5175160.088.387097-3.1353340.000000-4.4537130.00.00.000000-1.9434929.276908-6.9044720.0-3.012862-7.5984250.0-15.108035-13.9959430.0-1.57194719.343853-1.580262-20.645161-4.829604NaN11.365816NaNNaN-0.8176600.06.6140830.011.7357300.000000-19.247312-1.9146680.000000NaN-3.870968NaN0.0000002.011575-3.7923550.0-0.614563-4.1529200.0000000.00000NaN0.0000000.000000-3.059593-6.171254-10.3887410.000000-7.692308-1.264420-4.4035860.025.410940NaN0.00.000000-11.732125NaN-29.801489NaN-2.8627640.00000042.258065-3.0422890.0000000.00.0000000.016.322880NaNNaN-2.933583-9.3215470.000000-3.697468NaN0.000000-2.5142310.0-12.025323-1.34184242.2580650.000000NaN-2.6964450.0000000.0000000.0
242004NaN0.00.0000000.09.6253650.0000008.161162-2.18692248.982144-8.7175820.00.0-4.1915780.0-8.9784510.04.441670-4.3499060.0-2.847128-8.3281170.1288630.0-21.4548220.1965630.0000000.5703406.0833541.607119-1.2006410.0-13.4884880.000000-4.340613NaN0.634407-10.3884278.3784250.00.0-4.348500-2.2965040.0-2.3025510.0-0.407142-1.4373040.000000-2.4786350.00.043.043043-2.415933-4.183048-6.4256390.0-0.0320260.0000000.00.0000009.0133770.00.2252804.1162863.545940-4.909245-3.8860270.0000004.0375710.00.05.0055980.0-19.9986500.0-1.1017220.0000000.000000-3.085448-8.4191700.00.000000NaN0.0000001.603836-2.7180090.0-4.287069-2.9776200.0000000.00000NaN0.0000000.000000-4.168815-3.479153-6.7157050.0000000.000000-1.864104-1.2723100.07.955310NaN0.00.0000005.850695NaN0.000000NaN-3.5122950.00000039.338230-3.786998-6.0579060.00.0000000.051.089437NaNNaN7.728559-10.7846700.0000000.626526NaN0.00000011.5808750.00.000000-1.03192221.170339-1.6159690.0000000.0000000.000000-43.4919290.0
252008NaN0.0-8.1978070.08.58855591.2664086.086870-3.38983631.7644363.9726960.00.0-3.7716810.09.6509500.011.793640-6.2090670.0-1.269829-12.37855313.2513320.0-16.466962-0.1707140.000000-15.0245051.764921-10.279513-2.2497250.021.726437-4.480901-10.7198380.010.683945-8.7765455.8358330.00.0-3.866047-1.4217920.0-1.6379680.011.391970-3.2344010.000000-5.0781860.00.00.000000-2.9136282.177068-5.5411700.0-4.822164-2.0962800.0197.441939-7.7884810.0-2.4049366.430486-0.84256410.6453283.23941148.6777678.2227470.00.0-1.3035810.0-11.8441650.0-4.891225-21.618165-20.806995-2.8886674.3910100.00.000000NaN0.0000000.27308518.0410390.0-2.5804210.4837670.000000-40.034280.00.0000000.000000-3.027550-4.856871-4.1628640.0000000.000000-3.560689-5.7487990.0-4.0963360.00.014.7894222.813976NaN0.000000NaN-2.8906930.00000013.273239-3.8478290.0000000.00.0000000.06.022364NaNNaN-4.7243134.298707-12.610665-1.0258680.00.000000-5.9227640.00.000000-0.45003114.610625-6.987342-0.6611170.0000000.000000-23.3165330.0

Reshaping and plotting

Case Study Explorations

Building hosts DataFrame

Your task here is to prepare a DataFrame hosts by left joining editions and ioc_codes.

Once created, you will subset the Edition and NOC columns and set Edition as the Index.

There are some missing NOC values; you will set those explicitly.

Finally, you’ll reset the Index & print the final DataFrame.

Instructions

  • Create the DataFrame hosts by doing a left join on DataFrames editions and ioc_codes (using pd.merge()).
  • Clean up hosts by subsetting and setting the Index.
    • Extract the columns 'Edition' and 'NOC'.
    • Set 'Edition' column as the Index.
  • Use the .loc[] accessor to find and assign the missing values to the 'NOC' column in hosts. This has been done for you.
  • Reset the index of hosts using .reset_index(), which you’ll need to save as the hosts DataFrame.
1
2
3

# Left join editions and ioc_codes: hosts
hosts = pd.merge(editions, ioc_codes, how='left')
hosts.head()
EditionGrand TotalCityCountryNOC
01896151AthensGreeceGRE
11900512ParisFranceFRA
21904470St. LouisUnited StatesUSA
31908804LondonUnited KingdomGBR
41912885StockholmSwedenSWE
1
2
3

# Extract relevant columns and set index: hosts
hosts = hosts[['Edition', 'NOC']].set_index('Edition')
hosts.head()
NOC
Edition
1896GRE
1900FRA
1904USA
1908GBR
1912SWE
1
2

# Fix missing 'NOC' values of hosts
hosts.loc[hosts.NOC.isnull()]
NOC
Edition
1972NaN
1980NaN
1988NaN
1
2
3

hosts.loc[1972, 'NOC'] = 'FRG'
hosts.loc[1980, 'NOC'] = 'URS'
hosts.loc[1988, 'NOC'] = 'KOR'

1
2

# Reset Index of hosts: hosts
hosts.reset_index(inplace=True)

1

hosts.head()
EditionNOC
01896GRE
11900FRA
21904USA
31908GBR
41912SWE

Reshaping for analysis

This exercise starts off with fractions_change and hosts already loaded.

Your task here is to reshape the fractions_change DataFrame for later analysis.

Initially, fractions_change is a wide DataFrame of 26 rows (one for each Olympic edition) and 139 columns (one for the edition and 138 for the competing countries).

On reshaping with pd.melt(), as you will see, the result is a tall DataFrame with 3588 rows and 3 columns that summarizes the fractional change in the expanding mean of the percentage of medals won for each country in blocks.

Instructions

  • Create a DataFrame reshaped by reshaping the DataFrame fractions_change with pd.melt().
  • You’ll need to use the keyword argument id_vars='Edition' to set the identifier variable.
  • You’ll also need to use the keyword argument value_name='Change' to set the measured variables.
  • Print the shape of the DataFrames reshaped and fractions_change. This has been done for you.
  • Create a DataFrame chn by extracting all the rows from reshaped in which the three letter code for each country ('NOC') is 'CHN'.
  • Print the last 5 rows of the DataFrame chn using the .tail() method.
1
2
3
4
5

# Reshape fractions_change: reshaped
reshaped = pd.melt(fractions_change, id_vars='Edition', value_name='Change')

# Print reshaped.shape and fractions_change.shape
reshaped.shape

1

(3588, 3)

1

fractions_change.shape

1

(26, 139)

1
2
3
4
5

# Extract rows from reshaped where 'NOC' == 'CHN': chn
chn = reshaped[reshaped.NOC == 'CHN']

# Print last 5 rows of chn with .tail()
chn.tail()
EditionNOCChange
5671992CHN4.240630
5681996CHN7.860247
5692000CHN-3.851278
5702004CHN0.128863
5712008CHN13.251332

On looking at the hosting countries from the last 5 Olympic editions and the fractional change of medals won by China the last 5 editions, you can see that China fared significantly better in 2008 (i.e., when China was the host country).

Merging to compute influence

This exercise starts off with the DataFrames reshaped and hosts in the namespace.

Your task is to merge the two DataFrames and tidy the result.

The end result is a DataFrame summarizing the fractional change in the expanding mean of the percentage of medals won for the host country in each Olympic edition.

Instructions

  • Merge reshaped and hosts using an inner join. Remember, how='inner' is the default behavior for pd.merge().
  • Print the first 5 rows of the DataFrame merged. This has been done for you. You should see that the rows are jumbled chronologically.
  • Set the index of merged to be 'Edition' and sort the index.
  • Print the first 5 rows of the DataFrame influence.
1
2
3
4

# Merge reshaped and hosts: merged
merged = pd.merge(reshaped, hosts, how='inner')
# Print first 5 rows of merged
merged.head()
EditionNOCChange
01956AUS54.615063
12000AUS12.554986
21920BEL54.757887
31976CAN-2.143977
42008CHN13.251332
1
2
3
4
5

# Set Index of merged and sort it: influence
influence = merged.set_index('Edition').sort_index()

# Print first 5 rows of influence
influence.head()
NOCChange
Edition
1896GRENaN
1900FRA198.002486
1904USA199.651245
1908GBR134.489218
1912SWE71.896226

Plotting influence of host country

This final exercise starts off with the DataFrames influence and editions in the namespace. Your job is to plot the influence of being a host country.

Instructions

  • Create a Series called change by extracting the 'Change' column from influence.
  • Create a bar plot of change using the .plot() method with kind='bar'. Save the result as ax to permit further customization.
  • Customize the bar plot of change to improve readability:
  • Apply the method .set_ylabel("% Change of Host Country Medal Count") to ax.
  • Apply the method .set_title("Is there a Host Country Advantage?") to ax.
  • Apply the method .set_xticklabels(editions['City']) to ax.
  • Reveal the final plot using plt.show().
1
2
3
4
5
6
7
8
9
10
11
12
13

# Extract influence['Change']: change
change = influence['Change']

# Make bar plot of change: ax
ax = change.plot(kind='bar')

# Customize the plot to improve readability
ax.set_ylabel("% Change of Host Country Medal Count")
ax.set_title("Is there a Host Country Advantage?")
ax.set_xticklabels(editions['City'])

# Display the plot
plt.show()

png

Certificate

Python, Data Manipulation, Data Analysis, Data Science
python pandas data merging data analysis data science
This post is licensed under CC BY 4.0 by the author.