download notebook
view notebook w/o solutions

Multidimensional data in pandas

Files needed = ('nipa.xlsx', 'CPS_March_2016.csv')

We have covered some pandas basics and learned how to plot. Now let's sort out how to deal with more complex data. We will often find ourselves with data in which the unit of observation is complex. Pandas helps us deal with this by allowing for many index variables. So far, we have only used single indexing, but that is about to change.

Some examples that could use a multiIndex

1. State and county
2. Team and player
3. Industry and firm
4. Country (or person, firm,...) and time

That last one is important, and one that shows up a lot in economics. We call this panel data. Panel data is sometimes called longitudinal data. It follows the same firm/person/country over time.

MultiIndexes are important. Here are a few applications.

1. When the multiIndex is set correctly, we can use methods such as .loc[] in more powerful ways to retrieve subsets of data.
2. The multiIndex is important when we want to "reshape" DataFrames to create Dataframes with observations as rows and variables as columns.
3. The multiIndex helps us keep our data neat and organized.

import pandas as pd                 # load pandas and shorten it to pd
import matplotlib.pyplot as plt     # for making figures

m = {'name':['madison', 'madison', 'madison', 'seattle', 'seattle', 'seattle', 'phoenix', 'phoenix', 'phoenix'],
     'year':[2020, 2021, 2022,2020, 2021, 2022,2020, 2021, 2022 ],
     'temp-aug':[80,84,86,68,66,70,108,109,110],
     'pop':[267,268,269,735,734,733,4850,4900,4948] 
}

city = pd.DataFrame(m)
city

Multiple indexing

The key to working with more complex datasets is getting the index right. So far, we have considered a single index, but pandas allows for multiple indexes that nest each other.

Key concept: Hierarchical indexing takes multiple levels of indexes.

Let's set up the DataFrame to take name and year as the indexes.

city.set_index(['name', 'year'], inplace=True)
city

Wow.

Notice that the set_index() method is the same one we used earlier with single indexes. In this case, we passed it a list of variables to make the indexes

city.set_index(['name', 'year'], inplace=True)

In the output, the highest level of the index is name (we passed it 'name' first in the list) and the second level is year. The output does not repeat the city name for each observation. The 'missing' city name just means that the city is the same as above. [A very Tufte-esque removal of unnecessary ink.]

Let's take a look under the hood. What's our index? A new kind of object: the MultiIndex

print(city.index)

MultiIndex([('madison', 2020),
            ('madison', 2021),
            ('madison', 2022),
            ('seattle', 2020),
            ('seattle', 2021),
            ('seattle', 2022),
            ('phoenix', 2020),
            ('phoenix', 2021),
            ('phoenix', 2022)],
           names=['name', 'year'])

Subsetting with multiple indexes

With a multiIndex, we need two arguments to reference observations. Notice that I am using a tuple to pass the two values of the multiIndex.

# All the Madison data
city.loc[('madison', 2022),:] 

temp-aug     86
pop         269
Name: (madison, 2022), dtype: int64

Partial indexing

With the indexes set, we can easily subset the data using only one of the indexes. In pandas, this is called partial indexing because we are only using part of the index to identify the data we want.

We use the xs() method of DataFrame. Here we specify which level we are looking into. Note that I can reference the levels either by an integer or by its name.

# Get all of the 2021 observations.
city.xs(2021, level = 1)  

# Get all of the 2022 observations.
city.xs(2022, level = 'year')

We just took a slice from our data for just one year. This is called a cross section. The function we used is called xs for "x section" or "cross section."

Notice that .xs() dropped the index level that we chose from. If we want to keep that level we use the drop_level option.

# Get all of the 2022 observations. Keep the year variable.
city.xs(2022, level = 'year', drop_level=False)

With xs(), we can partially index on the 'outer index' as well. Suppose we want all the Madison observations.

# Get all of the Madison observations.
city.xs('madison', level = 'name', drop_level=False)

Limits to .xs()

.xs() can only search for one value in a level. This code, which looks reasonable, will not work.

city.xs(['madison', 'seattle'], level='name')

will return an error. I'm not sure why this is not allowed.

Resetting the multiIndex

As with a single index, we can get rid of the multIndex and replace it with a generic list of integers. This adds the index levels back into the DataFrame as columns.

# I'm intentionally using inplace=False because I do not want to reset the index permanently. 
# This code will just display a copy of the dataFrame.

city.reset_index(inplace=False) 

Saving multiIndex DataFrames

Saving a multiIndexed DataFrame works like before. Pandas fills in all the repeated labels, so the output is ready to go. Run the following code and then open the csv files.

# Multiple indexes on rows
city.to_csv('city.csv', index=True)

Reading multiIndex DataFrames

We can set up the multiIndex as we read in a file, too.

If the multiIndex is on the rows, pass index_col a list of column names.

city_readin = pd.read_csv('city.csv', index_col=['name', 'year'])
city_readin

Practice:

Use the city_readin DataFrame from above to answer these questions.

1. What was the August temperature in Phoenix in 2022? Use .loc[] to return the temperature.

city_readin.loc[('phoenix', 2022), 'temp-aug']

110

We can use .loc[] to partially index only the outermost row.

Unlike .xs() we can pass it lists.

1. Try city_readin.loc['madison'] what does it return?

city_readin.loc['madison']

1. Return all of the observations for Madison and Seattle. Use .loc[].

city_readin.loc[['madison', 'seattle']]

1. Return a DataFrame containing only the 2021 observations.

city_readin.xs(2021, level='year')

Summary statistics by level

MultiIndexes provide a quick way to summarize data. We will see many different ways to do this — getting statistics by groups — and not all will involve a multiIndex.

# Compute average August temperature for Madison. Using xs.
'Madison\'s average August temperature: {:3.2f}'.format(city_readin.xs('madison', level='name')['temp-aug'].mean())

"Madison's average August temperature: 83.33"

Notice the syntax with xs.

 city_readin.xs('madison', level='name')['temp-aug']

The city_readin.xs('madison', level='name') is returning a DataFrame with all the columns. [Try it!]

We then use the usual square-bracket syntax to pick off just the column 'temp-aug' and then hit it with .mean().

# Compute average August temperature for Madison. Using loc, since name is the outermost index.
'Madison\'s average August temperature: {:3.2f}'.format(city_readin.loc['madison','temp-aug'].mean())

"Madison's average August temperature: 83.33"

# Compute average August temperature (across all cities) for 2021.
'2021 average August temperature: {:3.2f}'.format(city_readin.xs(2021, level='year')['temp-aug'].mean())

'2021 average August temperature: 86.33'

A multiIndex in columns

There is nothing that says you can't have multiple indexes in the axis=1 dimension. It can be a bit more confusing, especially when we are reading in a file.

Open up "nipa.xlsx" in Excel and take a look.

If the multiIndex is on the columns, pass header a list of line numbers (integers). We also need to set the (row) index at the same time.

# Do not set the index. What is the name of the first column?
nipa = pd.read_excel('nipa.xlsx', header=[0,1])
nipa

nipa.columns

MultiIndex([(   'Unit', 'Var'),
            ('Nominal', 'GDP'),
            ('Nominal', 'INV'),
            (   'Real', 'GDP'),
            (   'Real', 'INV')],
           )

nipa = pd.read_excel('nipa.xlsx', header=[0,1], index_col=0)
nipa

nipa.axes

[Int64Index([1990, 2000, 2010, 2020], dtype='int64'),
 MultiIndex([('Nominal', 'GDP'),
             ('Nominal', 'INV'),
             (   'Real', 'GDP'),
             (   'Real', 'INV')],
            names=['Unit', 'Var'])]

We ask for columns with a multiIndex the same way we ask for rows.

Let's get nominal GDP.

nipa[('Nominal', 'GDP')]

1990     5963.14450
2000    10250.95200
2010    15048.97000
2020    21060.47425
Name: (Nominal, GDP), dtype: float64

Let's get only the nominal variables.

nipa['Nominal']

Partial indexing with .xs() works, too. But we have to be careful.

Uncomment the code below and run it. What is the code trying to do? What is wrong? How can we fix it?

# nipa.xs('Nominal', level=0)

For many methods of DataFrame the default axis is 0. You can always check the documentation.

Practice

Let's take data from the Current Population Survey, which surveys about 60,000 households each month. We will compute some average wages. This is the survey used to produce the official unemployment rate measures for the United States and many more labor-market indicators.

We will need to clean up a bit, then work with a multiIndex. Think of this as a mini-project.

The unit of observation is a person. The variables are:

• hrwage: hourly wage
• educ: education level
• female: 1 if female, 0 if not

• fulltimely: 1 if worked full time, 0 if not

• Load the March CPS data, 'CPS_March_2016.csv'. Note: the missing values are '.'

cps = pd.read_csv('CPS_March_2016.csv', na_values = '.')
cps.head(20)

1. Keep only those with fulltimely == 1
2. Keep only those with 5 <= hrwage <= 200

# Keep individuals who worked full time last year.
cps = cps[ cps['fulltimely'] == 1 ]

# Keep individuals with wages between $5 and $200.
cps = cps[cps['hrwage'] <= 200]
cps = cps[cps['hrwage'] >= 5]

# or
# cps = cps[ (cps['hrwage']<=200) & (cps['hrwage']>=5) & (cps['fulltimely'] == 1) ]

cps.sample(10)

1. Set the index to 'female' and 'educ', in that order.
2. Sort the index.

cps.set_index(['female', 'educ'], inplace=True)
cps.sort_index(axis=0, inplace = True)

cps.sample(10)

1. Report the average wage for males and females.

# Using xs()

avg_wage_f = cps.xs(1, level='female')['hrwage'].mean()
avg_wage_m = cps.xs(0, level='female')['hrwage'].mean()

print('Average wage of females is ${0:.2f} and males is ${1:.2f}.'.format(avg_wage_f, avg_wage_m) )

Average wage of females is $22.75 and males is $28.31.

# We can partial index using .loc[] ONLY on the outermost index. We can always use .xs().

avg_wage_f = cps.loc[1, 'hrwage'].mean() # Looking for 1 in the outermost index
avg_wage_m = cps.loc[0, 'hrwage'].mean() # Looking for 0 in the outermost index

print('Average wage of females is ${0:.2f} and males is ${1:.2f}.'.format(avg_wage_f, avg_wage_m) )

Average wage of females is $22.75 and males is $28.31.

1. Report the average wage for HS diploma/GED and for College degree, regardless of gender.

# We cannot use the .loc[] approach here because 'educ' is not the outermost index.

avg_wage_hs = cps.xs('HS diploma/GED', level='educ')['hrwage'].mean()
avg_wage_cd = cps.xs('College degree', level='educ')['hrwage'].mean()

print('Average wage of high school is ${0:.2f} and college is ${1:.2f}.'.format(avg_wage_hs, avg_wage_cd) )

Average wage of high school is $19.11 and college is $31.96.