Many data sets involve some sort of hierarchical structure. The American Community Survey is an example of one of the most common hierarchical data structures: individuals grouped into households. Another common hierarchical data structure is panel or longitudinal data and repeated measures, where things are observed multiple times. These structures may seem very different, but the same concepts apply to both and often even the same code.
6.1 Hierarchical Data Concepts
In this section we’ll introduce some of the core concepts and vocabulary for thinking carefully about hierarchical data.
6.1.1 Levels
Hierarchical data can be described in terms of levels (note that these are not the same as the levels of a categorical variable). A level one unit is the smallest unit in the data set. A level two unit is then a group of level one units. Some examples:
EXAMPLE DATA STRUCTURE
LEVEL ONE UNIT
LEVEL TWO UNIT
Individuals living in households
Individuals
Households
States grouped into regions
States
Regions
Students in classes
Students
Classes
Tests taken by students
Tests
Students
Monthly observations of individuals
Monthly observations
Individuals
Visits to the doctor by patients
Doctor visits
Patients
Social media posts by individuals
Posts
Individuals
If the hierarchy has more than two levels, simply keep counting up: if you have students grouped into classes grouped into schools grouped into districts, students are level one, classes are level two, schools are level three, and districts are level four.
Each variable is associated with a specific level. A variable is a level two variable if all the level one units within a given level two unit have the same value for the variable. For data structures where a level two unit is observed over time, level two variables are variables that do not change over time.
EXAMPLE DATA STRUCTURE
LEVEL ONE VARIABLES
LEVEL TWO VARIABLES
Individuals living in households
Age, Sex
Household income, Address
States grouped into regions
State population
Regional income per capita
Students in classes
Student’s race
Teacher’s race, Class size
Tests taken by students
Test Score
Free lunch eligibility
Monthly observations of individuals
Employment Status
Highest degree earned
Visits to the doctor by patients
BMI, Diagnosis
Race
Social media posts by individuals
Length, Topic
Sex
Of course all of these depend on the details of the actual data set. In a study that observes individuals for a few months, it’s unlikely that their highest degree earned will change. But it might! And if it does, highest degree earned becomes a level one variable. It does not meet the definition of a level two variable because different level one units (monthly observations) have different values for it.
6.1.2 Data Forms
With a hierarchical data set, an observation (row) can represent either a level one unit or a level two unit. Consider observing two people for three months:
person_id
month
years_edu
employed
1
1
16
True
1
2
16
True
1
3
16
True
2
1
12
False
2
2
12
True
2
3
12
True
Exercise
Identify the level one units, level two units, level one variable(s), and level two variable(s) in the above data set.
In this form, each observation represents a month (or more precisely, a person-month combination). Now consider the exact same data in a different form:
person_id
years_edu
employed_1
employed_2
employed_3
1
16
True
True
True
2
12
False
True
True
In this form, each observation represents a person. Because years_edu is a level two variable, there’s just one value per person and thus one variable (column). However, employed is a level one variable with three (potentially) different values per person. Thus it needs three variables (columns).
We call the first form the long form (or occasionally the tall form) and the second the wide form. The long form is longer because it has more rows; the wide form is wider because it has more columns. In most cases the long form is easier to work with, so we’ll do most of our examples in this form.
Now consider the indexes in this data set. In the long form, person_id and month are a compound identifier and could be easily turned into a MultiIndex, while the variable names are a simple column identifier. In the wide form, person_id is a simple row identifier, but now the variable names for the level one variables are a compound identifier with two parts: the variable name (employed) and the month in which the variable was observed. To identify a specific value in the data set we still need to know the person_id, month, and a variable name, but month has been converted from a row identifier to part of a compound column identifier. We’ll learn how to actually carry out that transformation in the next chapter.
6.1.3 Setting Up
Start up Jupyter Lab if you haven’t already and navigate to the folder where you put the example files. Then create a new Python Notebook and call it Hierarchical_Data_Practice.ipynb. Have it import Pandas and load acs_clean.pickle:
import pandas as pdacs = pd.read_pickle('acs_clean.pickle')acs
age
race
marital_status
edu
income
female
hispanic
household
person
37
1
20
White
Never married
Some college, >=1 year
10000.0
True
False
2
19
White
Never married
Some college, >=1 year
5300.0
True
False
3
19
Black
Never married
Some college, >=1 year
4700.0
True
False
241
1
50
White
Never married
Master's degree
32500.0
True
False
242
1
29
White
Never married
Bachelor's degree
30000.0
True
False
...
...
...
...
...
...
...
...
...
1236624
1
29
White
Now married
Some college, >=1 year
50100.0
False
False
2
26
White
Now married
High School graduate
12000.0
True
False
1236756
1
58
White
Now married
Master's degree
69800.0
True
False
2
61
White
Now married
Master's degree
40800.0
False
False
1236779
1
30
American Indian
Divorced
High School graduate
22110.0
False
False
27410 rows × 7 columns
Exercise
Load the example extract from the National Longitudinal Survey of Youth contained in nlsy.pickle. Identify the level one and level two units in the data and the level one and level two variables. (We’re not so worried about having a streamlined data cleaning workflow in this chapter, so just use your Hierarchical_Data_Practice.ipynb Notebook.)
6.2 Long Form Data
For some purposes, if a hierarchical data set is in long form you don’t have to worry about the hierarchical structure. On the other hand, a very common data wrangling task is creating level two variables based on the level one variables in the data set, and then the hierarchical structure is critical. In long form the critical tool for doing so is groupby(), so that the calculations are carried out separately for each level two unit.
6.2.1 Using groupby()
The groupby() function creates a new object which contains the DataFrame split up into groups. Functions act on that object, carry out calculations on the groups, and can return results in various forms. For example, if you apply basic summary statistic functions, you’ll get one row per group:
acs.groupby('household').mean()
/tmp/ipykernel_1582033/3152870129.py:1: FutureWarning: The default value of numeric_only in DataFrameGroupBy.mean is deprecated. In a future version, numeric_only will default to False. Either specify numeric_only or select only columns which should be valid for the function.
acs.groupby('household').mean()
age
income
female
hispanic
household
37
19.333333
6666.666667
1.0
0.0
241
50.000000
32500.000000
1.0
0.0
242
29.000000
30000.000000
1.0
0.0
377
69.000000
51900.000000
1.0
0.0
418
59.000000
12200.000000
1.0
0.0
...
...
...
...
...
1236119
51.000000
46600.000000
0.5
0.0
1236287
27.500000
17800.000000
0.5
0.0
1236624
27.500000
31050.000000
0.5
0.0
1236756
59.500000
55300.000000
0.5
0.0
1236779
30.000000
22110.000000
0.0
0.0
10565 rows × 4 columns
Note how the boolean (True/False) variables female and hispanic were converted, or coerced, into numeric variables before the mean was calculated. This is very useful and we’ll talk more about it shortly. The categorical variables race, edu, and marital_status were ignored, as means would be meaningless for them.
mean() is an example of an aggregate function: it takes multiple values and returns a single value. Useful aggregate functions include:
mean()
sum() (total)
max()
min()
count() (number of rows with non-missing values)
size() (number of rows)
first() (value for the first observation in the group)
last() (value for the last observation in the group)
nth() (value for the nth observation in the group)
The first five functions listed, mean() through count(), can be applied to a DataFrame, a Series, or the result of a groupby(). However, the last four, size() through nth(), only work with groupby().
With the transform() function, you pass in the name of an aggregate function which is then applied to the groups, but the result is copied or broadcast to all the observations in the group. This is the primary tool you’ll use to add level two variables to a DataFrame. The result is a Series with the same number of rows as the original DataFrame, but the value for level one units in the same level two unit is always the same.
We’ll start with examples of calculating continuous (or quantitative) level two variables based on continuous level one variables.
For many purposes, the total income of a household is more relevant than individual incomes. Create a column called household_income containing the total income of everyone in the household with:
Now calculate the household size, or the number of people in the household. Since there is one row per person, we can use the size() function. You still need to have it act on a single column, but it makes no difference which column you choose:
Create a variable mean_age containg the mean age of the household.
Now calculate the income per person, spot-checking household 484. Since income per person is the same as the mean income for the household, you might think you can use:
This does not give the right answer because of the way mean() handled missing values: it took the mean of the two observed values of income and ignored the two people with NaN. However, we know that NaN for income means the person is under 15 years old, and almost certainly their income is zero or close to it. What we want is the total income of the household divided by the number of people in the household, whether they have observed values of income or not.
Since you already created household_income and household_size, you can just use them:
Note that we did not need to use groupby()! groupby() is relevant when you’re using an aggregation function and need to control which rows are aggregated (e.g. the people in the same household). This code does not use an aggregation function: income_per_person for each row depends solely on household_income and household_size for the same row.
What if you hadn’t already created household_income and household_size? Is there a way to calculate income_per_person directly? Absolutely. We just need a function to pass to transform() that first calculates the sum and then divides by the size. No such function exists–but we can create one.
6.2.3 Lambda Functions
Python allows you to create and name your own functions, but you most likely won’t need to for data wrangling. You’ll probably be able to do everything you need to do with temporary, unnamed functions, known as lambda functions. You define a lambda function with:
lamba x: (f(x))
where f(x) should be replaced by the function you need. x is a parameter that is being passed in to the function–you can actually give it any name you want.
What seems like the obvious f(x) here is:
lambda x: (x.sum()/x.size())
but that doesn’t work. The reason is that the passed in x is no longer a groupby() object so you can’t use the size() function. However, Series and DataFrames have a size attribute which gives the total number of elements they contain. For a DataFrame this is the number of rows times the number of columns and rarely useful. However, for a Series which is one column from a DataFrame size is the number of rows, and that is exactly what you need:
Suppose you’re interested in the income children contribute to their household (household 8787 is a good example), so you want to create a child_income containing the household’s total income earned by children. You might think this would be a straightforward process of adding up the income of everyone that’s younger than 18:
The trouble is that in excluding the adults from the sum(), you also excluded them from getting the result. Thus child_income is not a proper household-level variable, as it is not the same for all the observations in the same household–and you’d really be in trouble if your research question involved the effect of children’s contributions on the adults in the household. The solution is to create a column that only contains the quantities you want to add up, but can be added up for everyone:
For children income_if_child contains their income, while for adults it contains NaN. If we now pass income_if_child to sum(), it will add up just the children’s incomes without us having to exclude the adults. Thus the result will be stored in the rows for the household’s adults too:
Since all the aggregate functions ignore missing values, you can use this approach any time you need to use an aggregate function on a subset of the data:
Create a new column containing the value of interest for the observations to be included and NaN for the values to be excluded
Aggregate that column with no subsetting
Exercise
Find the mean age of the adults in each household.
Again, just to keep things neat drop the variables created in this section:
As we’ve seen, if you do math with an indicator variable, it will be coerced into a number with True converted to 1 and False converted to 0. Aggregate functions take on new and very useful meanings when applied to indicator variables.
Start by creating an indicator variable for ‘this person is a child’:
acs['child'] = acs['age']<18acs.loc[[37, 8787]]
age
race
marital_status
edu
income
female
hispanic
child
household
person
37
1
20
White
Never married
Some college, >=1 year
10000.0
True
False
False
2
19
White
Never married
Some college, >=1 year
5300.0
True
False
False
3
19
Black
Never married
Some college, >=1 year
4700.0
True
False
False
8787
1
45
White
Divorced
Some college, >=1 year
38000.0
True
False
False
2
16
White
Never married
9th grade
3600.0
False
False
True
3
15
White
Never married
7th-8th grade
200.0
True
False
True
Just to see the conversion in action, use the astype() function and pass in int32 (32-bit integer).
This merits some explanation. If a household has no children, everyone in the household has a 0 (False) for child and the maximum value is 0. If a household has children, then at least one person in the household has 1 (True) for child, and the maximum value is 1. Python is clever enough to realize that the max() of a boolean variable is also a boolean variable, so it stores True/False in has_children rather than 1/0.
The minimum value of child tells you if the household consists entirely of children:
If everyone in the household is a child, everyone has a 1 (True) for child and the minimum value is 1 (True). If anyone in the household is not a child, then at least one person has a 0 (False) for child and the minimum is 0 (False).
Exercise
Create a variable containing the number of people in the household with income greater than zero and an indicator variable for ‘this household has someone with income>0’.
6.3 Wide Form Data
Up to this point we’ve worked with the American Community Survey in long form, with one observation per person. Some tasks are easier to do in long form than in wide form, but others are just as easy either way–once you know the tricks for working in wide form.
The file acs_wide.pickle contains the same ACS data we’ve been working with, but in wide form. Read it in as our new acs:
acs = pd.read_pickle('acs_wide.pickle')acs
age_1
age_2
age_3
age_4
age_5
age_6
age_7
age_8
age_9
age_10
...
female_7
female_8
female_9
female_10
female_11
female_12
female_13
female_14
female_15
female_16
household
37
20.0
19.0
19.0
NaN
NaN
NaN
NaN
NaN
NaN
NaN
...
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
241
50.0
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
...
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
242
29.0
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
...
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
377
69.0
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
...
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
418
59.0
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
...
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
1236119
51.0
51.0
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
...
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
1236287
41.0
42.0
23.0
4.0
NaN
NaN
NaN
NaN
NaN
NaN
...
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
1236624
29.0
26.0
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
...
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
1236756
58.0
61.0
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
...
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
1236779
30.0
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
...
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
10565 rows × 112 columns
Before proceeding, let’s review what we’re seeing here. In wide form, there is one row per household rather than one row per person as in long form. Each person is described by a set of columns. For example, person 1 is described by age_1, race_1, etc. There are 16 people in one household, so there are 16 of all the columns (i.e. they go up to age_16, race_16, etc.). Smaller households will have NaN in the columns for the people they don’t have. For example, household 37 has three people, so age_1, age_2, and age_3 have values while all the others have NaN.
In long form, household and person made up a MultiIndex row index, while the variable names (age, race, etc.) made up the column index. In wide form household is the row index. We could use the variable names plus person as a MultiIndex column index, and you’ll see what that looks like in the next chapter. However, we have not found a good way to have a MultiIndex describe data that contain both level 1 and level 2 variables. Thus rather than using an official MultiIndex, the column names contain both the variable name and the person number. This seems to be the most common approach.
6.3.1 Subsetting in Wide Form
If we were using a MultiIndex for the columns we could use loc() for subsetting just like we did in long form. However, since the information we need is embedded in the column names, we’ll use filter() instead.
The filter() function allows you to subset based on patterns in the column names. A very simple pattern is like=. This will select all the columns that contain the string you pass in. Select all the income columns by passing in like='income':
We’ll learn shortly how to use this to create level two variables like household_income. But that raises a problem: once you create a household_income variable, like='income' will select it too.
Now consider trying to select all the variables that describe person 1. You might think like='_1' would do the job, but try it:
Instead we got the variables for persons 10 through 16 as well, because they also contain ’_1’.
What we need is the ability to be much more specific about the columns to be selected. The solution is regular expressions. Regular expressions are a syntax for describing a pattern in text so that a computer can detect text that matches it. They’ve been around for more than 50 years and most programming languages and statistical packages implement them. They can be used for a variety of purposes; we won’t discuss working with text data (strings) but we’re laying the foundation by introducing you to regular expressions. Regular expressions can be extremely complex. We’ll introduce you to just a few of the most important features but you can find many, many tutorials online.
You use a regular expression with filter() by passing in regex=. The simplest regular expression is just some text, and a string matches it if it contains that text. In other words, it does the same thing as like=:
But a regular expression can do much more. Here are the tools we’ll use:
If a regular expression starts with ^, then the string only matches if it starts with the pattern.
If a regular expression ends with $, then the string only matches if it ends with the pattern.
\d matches any digit (number).
A * after something means the string can contain any number of that thing at that point. So \d* means the string can contain any number of digits at that point.
Putting this all together, the regular expression ^income_\d*$ will match strings that start with ‘income_’ followed by any number of digits and then end. This will allow us to match the columns containing the income for each person (income_1, income_2, etc.) without also matching columns like household_income:
The regular expression _1$ will match strings that end with ’_1’. This excludes variables that end with ’_10’, ’_11’, etc. and thus allows us to select just the columns describing person 1:
Use a regular expression to select all the race variables. Then use one to select all the variables for person 5.
6.3.2 Creating Level Two Variables
When we used the sum() function in the last section to add up household incomes, it was adding all the values in a column. However, with the axis=1 argument it will add up values across a row. You can use this along with filter() to calculate household income in wide form:
If you want to be able to see both the individual incomes and household_income so you can spot-check the results, go ahead and use like='income' (and scroll right):
acs.filter(like='income')
income_1
income_2
income_3
income_4
income_5
income_6
income_7
income_8
income_9
income_10
income_11
income_12
income_13
income_14
income_15
income_16
household_income
household
37
10000.0
5300.0
4700.0
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
20000.0
241
32500.0
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
32500.0
242
30000.0
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
30000.0
377
51900.0
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
51900.0
418
12200.0
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
12200.0
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
1236119
62200.0
31000.0
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
93200.0
1236287
15000.0
27000.0
11400.0
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
53400.0
1236624
50100.0
12000.0
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
62100.0
1236756
69800.0
40800.0
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
110600.0
1236779
22110.0
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
22110.0
10565 rows × 17 columns
This works for all of the aggregate functions from the last section, except those that only work with groupby(). Thus the code to carry out the tasks from the last section is almost the same: just use filter() instead of loc to select the right subsample, don’t bother with transform(), and pass axis=1 to the aggregate function itself.
How can we find the size of each household (number of people)? Recall that count() gives the number of non-missing values. So as long as there’s at least one variable with no missing values in the data set, you can use count() to find the number of people. This data set has multiple candidates, but use age:
What about income per person? That’s actually easier than in long form. There’s no need to broadcast the result to all the rows in the same household, so you don’t need transform() or a lambda function. Just add up the household income and divide by the number of people in the household:
income_1 16800.0
income_2 18000.0
income_3 NaN
income_4 NaN
income_5 NaN
income_6 NaN
income_7 NaN
income_8 NaN
income_9 NaN
income_10 NaN
income_11 NaN
income_12 NaN
income_13 NaN
income_14 NaN
income_15 NaN
income_16 NaN
household_income 34800.0
income_per_person 8700.0
Name: 484, dtype: float64
Recall that household 484 has two children who have missing values for income because they are under 15.
Exercise
Find the mean age for each household. Then create an indicator variable for ‘everyone in this household is female’.
6.3.3 Creating Level One Variables
Creating level one variables in long form is straightforward. For example, to create an indicator for ‘this person is black’ you’d just run:
acs['black'] = (acs['race']=='Black')
But in wide form there are 16 race variables, and there will need to be 16 indicators for Black as well. If you need to do a lot of work with level one variables, this is a pretty good reason to convert your data to long form. We’ll learn how in the next chapter. But it’s not too hard to make all 16 variables as long as you use a for loop.
Once again, we’ll start by considering what it would take to create just one of the 16 new indicator variables:
acs['black_1'] = (acs['race_1']=='Black')
That would take care of person 1. What would need to change in order to take care of person 2? The number at the end of the variable names. That tells us that this time we need to loop over a range of numbers. You can do that with the range() function. In its simplest form it takes two arguments, a starting number and an ending number. In true Python style the ending number is not included. To see how this works in a loop, try:
for i inrange(1, 17): print(i)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
So far so good, but how do we get those numbers into the variable names? If you add two strings together, they’ll be concatenated:
'Hello '+'world!'
'Hello world!'
The only wrinkle is that i is a number, But you can convert it to a string with the str() function and then add it:
'black_'+str(i)
'black_16'
This allows us to build the variable names we need, so now you’re ready to write the loop. One last complication: remember that any comparison involving NaN returns False. If a race variable is NaN the corresponding black variable should also be NaN, so select only the rows where race is not NaN:
for i inrange(1,17): acs.loc[ acs['race_'+str(i)].notna(),'black_'+str(i)] = (acs['race_'+str(i)]=='Black')acs.filter(like='black')
/tmp/ipykernel_1582033/611018561.py:1: FutureWarning: In a future version, `df.iloc[:, i] = newvals` will attempt to set the values inplace instead of always setting a new array. To retain the old behavior, use either `df[df.columns[i]] = newvals` or, if columns are non-unique, `df.isetitem(i, newvals)`
for i in range(1,17): acs.loc[
black_1
black_2
black_3
black_4
black_5
black_6
black_7
black_8
black_9
black_10
black_11
black_12
black_13
black_14
black_15
black_16
household
37
False
False
True
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
241
False
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
242
False
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
377
False
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
418
False
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
1236119
False
False
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
1236287
False
False
False
False
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
1236624
False
False
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
1236756
False
False
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
1236779
False
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
NaN
10565 rows × 16 columns
Exercise
Create a set of indicator variables for ‘this person has experienced the end of a marriage’. This would include anyone who is divorced, widowed, or separated (we’ll call that an ‘end’, though you could argue otherwise), and not anyone who is currently married or has never been married.
6.4 Panel Data
Panel data or longitudinal data is just another form of hierarchical data, with subjects as level two units and times they were observed as level one units. With panel data, the timing of the observations or at least their order is important. If it’s not, then we refer to it as repeated measures data. Since panel data is just hierarchical data, a lot of the code we’ve already learned applies directly to panel data, but there are some panel-specific tricks too.
You should already have the NLSY extract in memory from the second exercise. If not, load it with:
nlsy = pd.read_pickle('nlsy.pickle')nlsy
birth_year
edu
income
age
id
year
1.0
1979
58.0
12TH GRADE
4620.0
21.0
1980
58.0
NaN
NaN
22.0
1981
58.0
12TH GRADE
5000.0
23.0
1982
58.0
NaN
NaN
24.0
1983
58.0
NaN
NaN
25.0
...
...
...
...
...
...
12686.0
1993
60.0
12TH GRADE
31000.0
33.0
1994
60.0
NaN
NaN
34.0
1996
60.0
NaN
NaN
36.0
1998
60.0
NaN
NaN
38.0
2000
60.0
NaN
NaN
40.0
241034 rows × 4 columns
6.4.1 Creating Level Two Variables
The National Longitudinal Survey of Youth tracks young people for many years, making it classic panel data. In the NLSY a level two unit is a year and a level one unit is a person-year combination. You can create level two variables using the same tools we used with the ACS. For example, create max_edu containing the person’s highest level of education with:
Note that this relies on edu being an ordered categorical variable.
The first(), last() and nth() functions are particularly useful with panel data. But their meaning depends on the order the data are sorted in. This data set appears to be in chronological order, but you shouldn’t trust that if you’re going to write code that will give you the wrong answer if it is not. So before using the first() function to find the person’s age at the start of the study, sort by id and year:
The nth() function requires an argument specifying ‘n’, the desired observation (starting with zero). If you were using it on its own, that would just go in parentheses as usual:
nlsy['edu'].groupby('id').nth(2)
id
1.0 12TH GRADE
2.0 9TH GRADE
3.0 NaN
4.0 9TH GRADE
5.0 3RD YEAR COLLEGE
...
12682.0 12TH GRADE
12683.0 12TH GRADE
12684.0 12TH GRADE
12685.0 1ST YEAR COLLEGE
12686.0 12TH GRADE
Name: edu, Length: 12686, dtype: category
Categories (21, object): ['NONE' < '1ST GRADE' < '2ND GRADE' < '3RD GRADE' ... '5TH YEAR COLLEGE' < '6TH YEAR COLLEGE' < '7TH YEAR COLLEGE' < '8TH YEAR COLLEGE OR MORE']
With transform(), the arguments that would normally be passed in to the function are instead passed in to transform() as additional arguments. So the transform() equivalent is:
The NLSY frequently has missing data as subjects were frequently not located in a given year. Somewhat unusually for panel data, they often were able to find people in later years so there are gaps as well as attrition. Consider what we know about the education of person 5:
nlsy.loc[11]
birth_year
edu
income
age
year
1979
59.0
1ST YEAR COLLEGE
2900.0
20.0
1980
59.0
NaN
NaN
21.0
1981
59.0
3RD YEAR COLLEGE
2000.0
22.0
1982
59.0
4TH YEAR COLLEGE
4002.0
23.0
1983
59.0
4TH YEAR COLLEGE
8000.0
24.0
1984
59.0
4TH YEAR COLLEGE
17000.0
25.0
1985
59.0
4TH YEAR COLLEGE
20000.0
26.0
1986
59.0
NaN
NaN
27.0
1987
59.0
NaN
NaN
28.0
1988
59.0
NaN
NaN
29.0
1989
59.0
4TH YEAR COLLEGE
46000.0
30.0
1990
59.0
4TH YEAR COLLEGE
74283.0
31.0
1991
59.0
4TH YEAR COLLEGE
81481.0
32.0
1992
59.0
4TH YEAR COLLEGE
55000.0
33.0
1993
59.0
4TH YEAR COLLEGE
60000.0
34.0
1994
59.0
4TH YEAR COLLEGE
101653.0
35.0
1996
59.0
4TH YEAR COLLEGE
60000.0
37.0
1998
59.0
4TH YEAR COLLEGE
25000.0
39.0
2000
59.0
4TH YEAR COLLEGE
55000.0
41.0
They were not interviewed in 1986, 1987, and 1988, but given that their education level both before and after that gap is ‘4TH YEAR COLLEGE’ we know it was ‘4TH YEAR COLLEGE’ in those years as well. Meanwhile, while we might think that in 1980 their education level was ‘2ND YEAR COLLEGE’ that is less certain–and if that were a two-year gap we’d have no idea. So let’s fill in gaps only where the edu is the same before and after the gap.
To actually do that, we’ll fill in all the gaps in two ways: first by carrying past values forward, and then by carrying future values back. If the two methods agree, then that is a gap we can fill.
The fillna() function allows you to fill in missing values. If you pass in method='ffill' it will do a forward fill, and if you pass in method='bfill' it will do a backwards fill. While fillna() is not an aggregate function, it’s still important to use groupby('id') because that ensures each person is considered separately and no values are carried from one person to another.
Now we’ll replace edu if edu is missing and edu_forward and edu_backward are the same. Since they are the same it doesn’t matter which of them we set edu to:
Note that we will not attempt to fill in other gaps where we are not perfectly certain what the missing value should be. There are more or less valid methods for filling in missing values like multiple imputation, but they are complicated and a lot of work. Methods that are less complicated and less work, like filling in the mean or linear interpolation, can create serious problems with any statistical analysis. It’s unusual to be able to fill in gaps like we were able to here but worth doing when you can.
6.4.3 Variables Based On An Observations Neighbors
The shift() function can shift observations forward or backwards: forward if you pass in a positive number, backwards if you pass in a negative number. For easy viewing, let’s build a DataFrame by passing a dictionary to the Pandas DataFrame function. The dictionary must contain the column names as keys and Series (which we’ll create based on the edu Series in nsly) as values. Our DataFrame will have one column for the original edu, one for shifting forward, and one for shifting backward:
Note that when shifting forward you’ll have NaN at the beginning of the series, and when shifting backwards at the end. In this case we did not need groupby() because we extracted just one person, but in general you’ll need it to prevent values from being shifted between people.
Now consider trying to identify the year in which a person graduates from high school. In that year their edu will be ‘12TH GRADE’ or higher and the year before their edu will be less than ‘12TH GRADE’. Note that for whatever reason the NLSY frequently records people as having gained multiple years of education in one year, and we won’t worry about that. For person 2, the year in which they graduated is 1985:
nlsy.loc[2]
birth_year
edu
income
age
edu_forward
edu_backward
year
1979
59.0
9TH GRADE
4000.0
20.0
9TH GRADE
9TH GRADE
1980
59.0
9TH GRADE
5000.0
21.0
9TH GRADE
9TH GRADE
1981
59.0
9TH GRADE
6000.0
22.0
9TH GRADE
9TH GRADE
1982
59.0
9TH GRADE
10000.0
23.0
9TH GRADE
9TH GRADE
1983
59.0
9TH GRADE
11000.0
24.0
9TH GRADE
9TH GRADE
1984
59.0
9TH GRADE
11500.0
25.0
9TH GRADE
9TH GRADE
1985
59.0
12TH GRADE
11000.0
26.0
12TH GRADE
12TH GRADE
1986
59.0
12TH GRADE
14000.0
27.0
12TH GRADE
12TH GRADE
1987
59.0
12TH GRADE
16000.0
28.0
12TH GRADE
12TH GRADE
1988
59.0
12TH GRADE
20000.0
29.0
12TH GRADE
12TH GRADE
1989
59.0
12TH GRADE
19000.0
30.0
12TH GRADE
12TH GRADE
1990
59.0
12TH GRADE
20000.0
31.0
12TH GRADE
12TH GRADE
1991
59.0
12TH GRADE
20000.0
32.0
12TH GRADE
12TH GRADE
1992
59.0
12TH GRADE
22000.0
33.0
12TH GRADE
12TH GRADE
1993
59.0
12TH GRADE
25000.0
34.0
12TH GRADE
12TH GRADE
1994
59.0
12TH GRADE
0.0
35.0
12TH GRADE
12TH GRADE
1996
59.0
12TH GRADE
0.0
37.0
12TH GRADE
12TH GRADE
1998
59.0
12TH GRADE
0.0
39.0
12TH GRADE
12TH GRADE
2000
59.0
12TH GRADE
0.0
41.0
12TH GRADE
12TH GRADE
Now create an indicator variable for ‘graduated this year’ with:
Note that if either edu or shifted edu is NaN, the result will be false. Thus this indicator variable is really for ‘we know the person graduated this year.’ We could set it to NaN for years with missing values if we wanted to, using the techniques we’ve used before.
Logically each person should only graduate from high school once, but it’s not hard to image data entry errors that would make it look like they graduated multiple times. Recall that the sum of an indicator variable is the number of times it is true, so check with:
A little over half the subjects have True for grad zero times: either they never graduated from high school, they graduated outside the study period, or we could not identify their graduation year due to missing data. But no one graduated more than once.
Now let’s find their age at graduation. This is just a matter of subsetting with an aggregate function, except that the desired subset has just one observation per group. So start by creating age_if_grad containing the person’s age for the year they graduated and NaN for all others.
and that would absolutely work. But let’s learn an alternative method. The numpy where() function takes three arguments: a condition, the result to return if the condition is true, and the result to return if the condition is false. Build age_if_grad by importing numpy as np and then passing to where() the three arguments grad, age, and NaN:
import numpy as npnlsy['age_if_grad'] = np.where( nlsy['grad'], nlsy['age'], np.NaN)nlsy.loc[2]
birth_year
edu
income
age
edu_forward
edu_backward
grad
age_if_grad
year
1979
59.0
9TH GRADE
4000.0
20.0
9TH GRADE
9TH GRADE
False
NaN
1980
59.0
9TH GRADE
5000.0
21.0
9TH GRADE
9TH GRADE
False
NaN
1981
59.0
9TH GRADE
6000.0
22.0
9TH GRADE
9TH GRADE
False
NaN
1982
59.0
9TH GRADE
10000.0
23.0
9TH GRADE
9TH GRADE
False
NaN
1983
59.0
9TH GRADE
11000.0
24.0
9TH GRADE
9TH GRADE
False
NaN
1984
59.0
9TH GRADE
11500.0
25.0
9TH GRADE
9TH GRADE
False
NaN
1985
59.0
12TH GRADE
11000.0
26.0
12TH GRADE
12TH GRADE
True
26.0
1986
59.0
12TH GRADE
14000.0
27.0
12TH GRADE
12TH GRADE
False
NaN
1987
59.0
12TH GRADE
16000.0
28.0
12TH GRADE
12TH GRADE
False
NaN
1988
59.0
12TH GRADE
20000.0
29.0
12TH GRADE
12TH GRADE
False
NaN
1989
59.0
12TH GRADE
19000.0
30.0
12TH GRADE
12TH GRADE
False
NaN
1990
59.0
12TH GRADE
20000.0
31.0
12TH GRADE
12TH GRADE
False
NaN
1991
59.0
12TH GRADE
20000.0
32.0
12TH GRADE
12TH GRADE
False
NaN
1992
59.0
12TH GRADE
22000.0
33.0
12TH GRADE
12TH GRADE
False
NaN
1993
59.0
12TH GRADE
25000.0
34.0
12TH GRADE
12TH GRADE
False
NaN
1994
59.0
12TH GRADE
0.0
35.0
12TH GRADE
12TH GRADE
False
NaN
1996
59.0
12TH GRADE
0.0
37.0
12TH GRADE
12TH GRADE
False
NaN
1998
59.0
12TH GRADE
0.0
39.0
12TH GRADE
12TH GRADE
False
NaN
2000
59.0
12TH GRADE
0.0
41.0
12TH GRADE
12TH GRADE
False
NaN
You may find that syntax clearer. where() would definitely be easier to use than the first syntax if you needed to set the rows where grad was false to something other than NaN.
age_if_grad for each person is a single number plus a bunch of NaNs. Many aggregate functions have the property that if you give them a single number they’ll return that number, so we could use any of them. We’ll go with mean:
Assume for the moment that if someone’s income is zero they are unemployed and if their income is positive they are employed. Create an indicator variable for ‘person lost their job’ which is true if someone is unemployed (income is zero) in the current year and was employed (income is positive) in the previous year.