Multidimensional data in pandas

Files needed = ('dogs.csv', '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 country
  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 is panel data. Panel data is sometimes called longitudinal data. It follows the same firm/person/country over time.

In [ ]:
import pandas as pd                 # load pandas and shorten it to pd
import datetime as dt               # load datetime and shorten it to dt
import matplotlib.pyplot as plt     # for making figures
In [ ]:
soccer = {'team' : ['Man City', 'Man City', 'Man City', 'Man City', 'Chelsea', 'Chelsea'], 
          'player' : ['Walker', 'Stones', 'Foden', 'Jesus', 'Cahill', 'Pedro'],
          'pos' : ['D', 'D', 'M', 'F', 'D', 'F'],
          'goals' : [1, 0, 0, 1, 0, 3],
          'assists': [0,0,0,0,0,0]
         }

prem = pd.DataFrame(soccer)
prem

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 team and position as the indexes.

In [ ]:
prem.set_index(['team', 'pos'], inplace=True)
prem

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

prem.set_index(['team', 'pos'], inplace=True)

In the output, the highest level of the index is team (we passed it 'team' first in the list) and the second level is position. The output does not repeat the team name for each observation. The 'missing' team name just means that the team 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

In [ ]:
print(prem.index)

Subsetting with multiple indexes

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

In [ ]:
# All the defenders on Man City
prem.loc[('Man City', 'D'),:] 

It's always a good idea to pay attention to warnings, particularly 'PerformanceWarning'. Pandas is telling us that we are asking for something in the second index, but the second index is not ordered. If the index was big, this could slow down our program. Let's fix that with sort_index().

Important Sort your mulitIndex.

In [ ]:
prem = prem.sort_index(axis=0)   # tell pandas which axis to sort. Could sort the columns, too...
                                 # returns a DataFrame unless we use inplace=True
prem
In [ ]:
# Now let's ask for all the defenders on Man City
prem.loc[('Man City', 'D'), :]

No warnings.

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 subset identify the data we want.

We can use loc[] like we do with a single index if we want to index on the top level index.

In [ ]:
print('All the Chelsea players:')
print(prem.loc['Chelsea',:])               # All the 'Chelsea' observations

print('\n\nAll the Man City players:')
print(prem.loc['Man City',:])              # All the 'Man City' observations

Note that this kind of notation does not work if we want to index on the second index. Suppose we wanted all the defense, regardless of team. It seems like this should work:

prem.loc[:,'D']

...but it does not. That brings us to our next way to query a multiIndex.

The xs( ) method

We can also 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.

In [ ]:
print(prem.xs('Chelsea', level = 0) )              # All the 'Chelsea' observations
print('\n')
print(prem.xs('Man City', level = 'team'))              # All the 'Man City' observations

This pretty works the same way that loc[] worked for the outer index.

With xs(), we can partially index on the 'inner index' as well. Suppose we want all the defenders, regardless of team.

In [ ]:
prem.xs('D', level=1)

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

In [ ]:
prem.reset_index(inplace=True)    # this moves the indexes back to columns
prem

Who says we two indexes are enough...let's try three levels of indexes!

In [ ]:
prem.set_index(['team', 'player', 'pos'], inplace=True)
prem

A multiIndex in columns

There is nothing that says you can't have multiple indexes in the axis=1 dimension. Here is quick way to see this: transpose the DataFrame.

In [ ]:
prem = prem.transpose()           # this swaps the rows for columns
print(prem)                       # print() lines up the columns well
prem

Now the rows are named 'goals' and 'assists' and the columns are ('team', 'player', 'pos'). I'm not sure this is a very useful way to look at this particular dataset, but multiIndex columns can come in handy. [Transpose is handy, too.] Let's change it back.

In [ ]:
prem = prem.transpose()
prem

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.

In [ ]:
# When subsetting by the upppermost level, I can use xs or loc

print('Chelsea avg. goals', prem.xs('Chelsea', level='team')['goals'].mean())   # average goals for Chelsea players
print('Chelsea avg. goals', prem.loc['Chelsea','goals'].mean())   # average goals for Chelsea players

# When subsetting on the inner levels, I use xs 
print('Defender avg. goals {0:.2f}.'.format( prem.xs('D', level='pos')['goals'].mean() ) )          # average goals for defenders

[Did something go wrong? Go back and fix it up!]

Notice the syntax with xs.

prem.xs('Chelsea', level='team')['goals']

The prem.xs('Chelsea', level='team') is returning a DataFrame with all the columns. [Try it!]

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

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.

In [ ]:
# Multiple indexes on rows
prem.to_csv('prem.csv')

# Multiple indexes on columns
prem = prem.transpose()
prem.to_csv('prem_transposed.csv')

Practice

Let's take data from the Current Population Survey, which surveys about 60,000 households each month. We will compute some average wages. We will need to clean up a bit, then work with a multiIndex. Think of this as a mini-project.

  1. Load the march cps data, 'CPS_March_2016.csv'. Note: the missing values are '.'
  1. Keep only those with fulltimely == 1
  2. Keep only those with 5 <= hrwage <= 200
  1. Rename 'female' to 'gender'
  2. In column 'gender' replace 0 with 'male' and 1 with 'female'
  1. Set the index to 'gender' and 'educ', in that order.
  2. Sort the index.
  1. Report the average wage for males and females. Try it with the loc[] method.
  1. Report the average wage for HS diploma/GED and for College degree, regardless of gender. Use the xs() method.