This week, I thought it would be interesting to find out just how old American politicians are. President Joe Biden (81) announced that he won't run for re-election, but Donald Trump (78) isn't a youngster, either. Nancy Pelosi (84) seems to have been central to coordinating Biden's decision to pull out of the race.
We do see some inching toward younger talent and candidates: Vice president Kamala Harris isn't even 60 years yet, which makes her seem quite young compared with all of them — until you compare her with Republican vice presidential candidate JD Vance (39), who was clearly selected in part because of his youth.
Just how old are American politicians? I certainly have an image of them as very old, especially in the Senate, but maybe that's unfair or wrong? The only way to find out is to examine the data, and that's what we do here at Bamboo Weekly.
Data and eight questions
I looked around for a data source that could tell me the ages of US politicians. I was delighted to find a project that has been going on for some time, a GitHub repository with more than 100 collaborators. The project is here:
https://github.com/unitedstates/congress-legislators
The good news is that they have a lot of data, and seem to update it on a very regular basis. (For example, they already included the death of Sheila Jackson Lee, who passed away on July 19th.) The data comes from a wide variety of sources and formats, and the source data thus includes a number of unique identifiers, each from a distinct system.
We'll look at two of the files, legislators-historical.yaml and legislators-current.yaml, both (as the suffix indicates) in YAML format. The first file contains all legislators who previously served, and the second contains all those who are currently serving.
While the age of legislators is the topical focus for this week's questions, I found that reading the data from YAML into a useful Pandas data frame was a fairly big task. I thus broke it up (to some degree) into multiple questions.
As usual, a link to the Jupyter notebook I used to ask and answer these questions is at the bottom of this post.
Read the two YAML files (legislators-historical and legislators-current) into a single data frame.
Before doing anything else, I loaded Pandas. But because we're also going to be working with YAML, I also installed the pyyaml package from PYPI, which we import with the name yaml:
import pandas as pd
from pandas import DataFrame
import yamlWe have two YAML files that we need to read into Pandas data frames. Then we'll need to combine them.
I hadn't worked with YAML in some time, and was under the (mistaken) assumption that Pandas would have a read_yaml method, much as it has read_csv and read_json. But it turns out that no such method exists. We'll thus need to use pyyaml to read the YAML files into Python dictionaries, and then use those dicts to build a data frame. A bit roundabout, but we can do it.
I also assumed that reading a YAML file with pyyaml would be similar to reading a JSON or pickle file, with a load method. That turns out to be true, but it's not enough to run yaml.load on a file. You also need to provide a value to the Loader keyword argument; fortunately, you can pass yaml.Loader as an argument:
historical_filename = 'legislators-historical.yaml'
historical_data = yaml.load(open(historical_filename), Loader=yaml.Loader)
But wait: It turns out that YAML has some security issues, namely that a malicious file can force your computer to execute arbitrary code upon loading the file. So it's far better to use safe_load, which has the added benefit of not requiring another argument:
historical_data = yaml.safe_load(open(historical_filename))
historical_df = DataFrame(historical_data)
That defines historical_data as a list of dicts. We can turn that into a data frame by invoking DataFrame on the data:
historical_df = DataFrame(historical_data)
To load the current data, I did roughly the same thing:
current_filename = 'legislators-current.yaml'
current_data = yaml.safe_load(open(current_filename))
current_df = DataFrame(current_data)
I now had two data frames with the same column names, and wanted to stack them on top of one another. To do that, I used pd.concat, which takes a list of data frames as an argument:
df = pd.concat([historical_df, current_df])I've used pd.concat many times, but this was the first time I got an error saying that the data frames couldn't be combined because the index was duplicated, and let to conflicts. That's fine; I added the ignore_index keyword argument, which solved the problem:
df = pd.concat([historical_df, current_df], ignore_index=True)At this point, we have a data frame with 12,684 rows and 7 columns. However, three of those columns contain Python dicts, and one contains a list of dicts in each cell. We'll work to clean that up and make a better data frame in the next few questions.
Three columns (id, bio, and name) contain Python dicts. Expand each dict to be new columns in the row, and then remove the original columns. Then take the "terms" column, which contains a list of dicts, and expand it such that you have multiple rows per legislator, one term per row. (The rest of the data for the legislator will be duplicated.) Finally, set the "bioguide" column to be the index.
In some ways, a dict is like a row in a data frame: The keys are the column names, and the values are the cell values. So it seems reasonable to assume that we can somehow expand a series of dicts into a data frame.
One not-very-obvious way to do this is with the pd.json_normalize method. It does exactly what I described, namely takes a series of dicts and returns a data frame:
pd.json_normalize(df['id'])
The above returns a new data frame with 17 columns (one for each dict key) and 12,684 rows – one for each in the original data frame. We can do this for each of the three columns that contains a dict, but then how do we merge it back into the original data frame?
We can use pd.concat, but this time, we'll pass axis='columns', so that the concatenation happens side-to-side, rather than top-to-bottom:
(pd.concat([df,
pd.json_normalize(df['id']),
pd.json_normalize(df['bio']),
pd.json_normalize(df['name'])],
axis='columns')
) I also asked you to remove the original columns. We can do that with drop, specifying that we want to drop columns, rather than (its default of) rows:
df = (pd.concat([df,
pd.json_normalize(df['id']),
pd.json_normalize(df['bio']),
pd.json_normalize(df['name'])],
axis='columns')
.drop(['id', 'bio', 'name'], axis='columns')
)Next, I asked you to use the "bioguide" column as the index. We can do that with set_index:
(pd.concat([df,
pd.json_normalize(df['id']),
pd.json_normalize(df['bio']),
pd.json_normalize(df['name'])],
axis='columns')
.drop(['id', 'bio', 'name'], axis='columns')
.set_index('bioguide')
)At this point, we've expanded all of the dicts into columns. But there is still the terms column, with each cell containing a list of dicts. Each dict in that list represents a single term in which the legislator served. So if someone served two terms, there will be a two-element list of dicts, and if they served 10 terms, there will be a 10-element list of dicts.
There isn't an obvious way to handle data like this. But Pandas does provide an explode method, which takes a series containing lists and moves each list into its own element. So given a series, a two-element list would be turned into two elements, and the 10-element list would be turned into 10 rows. The index would be preserved, though.
We can also run explode on a data frame, specifying which column contains a list. When you do this, each of the rows is duplicated, once for each list element. The list elements themselves are spread across the rows. This will basically solve our problem, although it will mean that there will be a separate row not for each legislator, but rather for each term that they served. Note that the index will continue to contain the unique "bioguide" ID code for each legislator:
df = (pd.concat([df,
pd.json_normalize(df['id']),
pd.json_normalize(df['bio']),
pd.json_normalize(df['name'])],
axis='columns')
.drop(['id', 'bio', 'name'], axis='columns')
.set_index('bioguide')
.explode('terms')
)The resulting data frame has 45,035 rows and 28 columns. We definitely won't need all of these columns, but I decided against trimming them down for now.
Now expand the "terms" column to be new columns in the row, and remove the original "terms" column. The "bioguide" column should remain the index.
We've solved the problem of the terms column containing a list of dicts, but now the term column contains dicts. We can again use pd.json_normalize in order to turn each dict into multiple columns, and again use pd.concat to merge the new columns into the data frame and drop to remove the original terms column.
I found that in order for this to work correctly, I had to use reset_index to temporarily remove the index, and then set_index at the end of the query to put it back:
df = (pd.concat([df.reset_index(),
pd.json_normalize(df['terms'])],
axis='columns')
.drop('terms', axis='columns')
.set_index('bioguide')
)With this done, we still have 45,035 rows, but we also have 46 columns. If I were doing real work with this data set, I would definitely trim these columns down. But for now, I'll just ignore the ones I don't need.
As you can see, it can sometimes take a while just to get the data into a form that we can then use! Let's just hope — cue ominous music — that the data will also be clean, and that we can rely on it.
Turn the birthday, start, and end columns into datetimes.
If we're going to calculate legislators' ages, then we'll need to perform some calculations with dates. To do that, I asked you to turn the birthday, start, and end columns into datetime dtypes.
Fortunately, that's fairly straightforward with pd.to_datetime, which takes a series of strings as inputs and returns a series of datetime objects. Of course, this assumes that the strings contain date or datetime data in a format that pd.to_datetime will recognize – which is indeed the case here.
I was thinking of using apply along with lambda and pd.to_datetime, but decided in the end to use apply, running pd.datetime on three columns and then assigning back to those same columns. Note that by putting a list inside of the square brackets, we can both set and retrieve multiple columns:
columns = ['birthday', 'start', 'end']
df[columns] = df[columns].apply(pd.to_datetime)
What is the greatest number of terms a legislator has served in Congress? How old were they at the start of their first term, and how old at the end of their final term?
First, let's figure out the greatest number of terms that a legislator has served. Since we know that we have one row per term, one solution is to use value_counts on the index (which represents individual legislators) and take the highest value:
(
df
.reset_index()
['bioguide']
.value_counts()
.head()
)The winner, by the way, is John Dingell, a Michigan congressman who served 30 terms of office.
How old was Dingell when he started his first term, and how old he was at the end of his last? We can calculate that, using the D000355 unique identifier to retrieve all of his rows, then calculate the age at the start of each term and at the end of each term:
(
df
.loc['D000355']
.assign(age_at_start = lambda df_: (df_['start'] -
df_['birthday']).dt.days / 365,
age_at_end = lambda df_: (df_['end'] -
df_['birthday']).dt.days / 365)
)Here, we're taking advantage of date arithmetic, where subtracting one date from another gives us a "timedelta" object, one which represents the distance between two points in time. I then extracted the number of days in this span with dt.days, and divided it by 365 to give an approximate age in years. I used assign to add new columns to our data frame.
After executing this query, we now have age_at_start and age_at_end, two columns that tell us how old Dingell was, in years, at the start and end of each term. I then restricted the data frame to those two columns and used agg to retrieve the min and max for each column:
(
df
.loc['D000355']
.assign(age_at_start = lambda df_: (df_['start'] -
df_['birthday']).dt.days / 365,
age_at_end = lambda df_: (df_['end'] -
df_['birthday']).dt.days / 365)
[['age_at_start', 'age_at_end']]
.agg(['min', 'max'])
)
The result:
age_at_start age_at_end
min 28.515068 30.512329
max 86.550685 88.550685In other words, Dingell was less than 29 years old when he was first elected to Congress, and was more than 88 years old when he finished his final term.
Calculate how old each legislator was at the start of each term, and show the 10 eldest starts to a legislative term. Did any legislators make the list more than once? Display their ages in years.
Let's apply this same logic to all of the legislators, calculating their age at the start of each term:
(
df
.assign(age_at_start = lambda df_: (df_['start'] -
df_['birthday']).dt.days / 365)
.sort_values('age_at_start', ascending=False)
.head(10)
[['wikipedia', 'start',
'birthday', 'age_at_start', 'type']]
)Once again, we use assign to add a new column, containing the legislator's age in years. We can then use sort_values to sort the entire data frame according to the value in the age_at_start column. We got the 10 oldest starts, and then printed a number of useful columns:
wikipedia start birthday age_at_start type
bioguide
T000254 Strom Thurmond 1997-01-07 1902-12-05 94.156164 sen
H000067 Ralph Hall 2013-01-03 1923-05-03 89.734247 rep
G000386 Chuck Grassley 2023-01-03 1933-09-17 89.356164 sen
B001210 Robert Byrd 2007-01-04 1917-11-20 89.183562 sen
P000218 Claude Pepper 1989-01-03 1900-09-08 88.380822 rep
S000355 Isaac R. Sherwood 1923-12-03 1835-08-13 88.364384 rep
S000827 Charles Manly Stedman 1929-04-15 1841-01-29 88.265753 rep
T000254 Strom Thurmond 1991-01-03 1902-12-05 88.139726 sen
H000067 Ralph Hall 2011-01-05 1923-05-03 87.736986 rep
C000714 John Conyers 2017-01-03 1929-05-16 87.695890 repStrom Thurmond was 94 when he started his final term in office — but he was on the list more than once, when he was 88 and also when he was 94, As a senator, whose term is six years, that's pretty impressive. Ralph Hall was on the list in both 2013 and 2011, thanks in no small part to the fact that he was in the House of Representatives, where elections take place every two years.
Create a pivot table showing, for Democrat and Republican legislators that started their terms in 1990 or after, the mean age (in years) of the members of each party.
Who is older, on average, Democrats or Republicans? I thought it might be interesting to find out.
First, let's create our pivot table, which is basically a 2D groupby. We choose two categorical columns, one value column, and an aggregation method (which is mean by default). We run those through pivot_table and get a data frame in which one categorical column's unique values are the columns, and the second is the values. Before creating the pivot table, though, we create the age_at_start column, calculated as we've already done before:
(
df
.assign(age_at_start = lambda df_: (df_['start'] -
df_['birthday']).dt.days / 365)
.pivot_table(index=df['start'].dt.year,
columns='party',
values='age_at_start')
)The good news is that this gave us a lot of answers. But there was too much information, with too many parties and too many years. I thus decided to keep only politicians since 1990 (still not that recent) and only people whose party was Democrat or Republican:
(
df
.assign(age_at_start = lambda df_: (df_['start'] -
df_['birthday']).dt.days / 365)
.pivot_table(index=df['start'].dt.year,
columns='party',
values='age_at_start')
[['Democrat', 'Republican']]
.loc[1990:]
.assign(democrat_is_older = lambda df_: (df_['Democrat'] >
df_['Republican']))
)
Notice how I can use loc to retrieve only those years since 1990, thanks to the fact that our pivot table used years on the index.
Finally, just to make it easier to compare, I created a democrat_is_older column, indicating whether the Democrats who started that year were, on average, older. And quite a lot of the time, they were:
(
df
.assign(age_at_start = lambda df_: (df_['start'] -
df_['birthday']).dt.days / 365)
.pivot_table(index=df['start'].dt.year,
columns='party',
values='age_at_start')
[['Democrat', 'Republican']]
.loc[1990:]
.assign(democrat_is_older = lambda df_: (df_['Democrat'] >
df_['Republican']))
)
How many legislators in the current congress are older than Joe Biden? Get their names, birthdates, party affiliation, and gender. How many such people are in each party? How does it break down by gender?
Finally, people have been worried about Joe Biden's age and health. How any members of the current Congress are older than Joe Biden?
He was born on November 20, 1942. We can retrieve those legislators whose birthday is after that with a simple > comparison inside of loc:
(
df
.loc[lambda df_: df_['birthday'] <= '1942-11-20']
)Next, we'll keep only those legislators who started after January 1st, 2023:
(
df
.loc[lambda df_: df_['birthday'] <= '1942-11-20']
.loc[lambda df_: df_['start'] > '2023-01-01']
)Finally, we can keep the party and gender columns, and then run value_counts on both of them:
(
df
.loc[lambda df_: df_['birthday'] <= '1942-11-20']
.loc[lambda df_: df_['start'] > '2023-01-01']
[['party', 'gender']]
.value_counts()
)The result:
party gender
Democrat F 5
M 4
Republican M 3In other words: Among Democrats, there are 5 female and 4 male legislators who are older than Joe Biden. Among Republications, it's only three.
That's it for this week's analysis! Here's a link to my notebook: https://drive.google.com/file/d/1l0LXddGM1Zv0FuFCyFtEGiv-HmiBfubg/view?usp=drive_link
I'll be back next week with more puzzlers and questions for you to answer with Python and Pandas.
Reuven