Since the U.S. midterm elections I’ve been playing around with some Congressional Quarterly data about the composition of the House and Senate since 1945. Unfortunately I’m not allowed to share the data, but here are two or three things I had to do with it that you might find useful.
The data comes as a set of CSV files, one for each congressional session. You download the data by repeatedly querying CQ’s main database by year. In its initial form, the top of each file looks like this:
Results for 79th Congress
,
Last,First,Middle,Suffix,Nickname,Born,Death,Sex,Position,Party,State,District,Start,End,Religion,Race,Educational Attainment,JobType1,JobType2,JobType3,JobType4,JobType5,Mil1,Mil2,Mil3
Abernethy,Thomas,Gerstle,,,05/16/1903,01/23/1953,M,U.S. Representative,Democrat,MS,4,01/03/1945,01/03/1953,Methodist,White,Professional degree,Law,,,,,Did not serve,,
Adams,Sherman,,,,01/08/1899,10/27/1986,M,U.S. Representative,Republican,NH,2,01/03/1945,01/03/1947,Not specified,White,Bachelor's degree,Construction/building trades,,,,,,,
The bottom of each file looks like this:
Young,Milton,Ruben,,,12/06/1897,05/31/1983,M,U.S. Senator,Republican,ND,,03/19/1945,01/03/1981,Mormon,White,Unknown,Agriculture,,,,,Did not serve,,
Zimmerman,Orville,,,,12/31/1880,04/07/1948,M,U.S. Representative,Democrat,MO,10,01/03/1945,04/07/1948,Methodist,White,Professional degree,Education,Law,,,,Army,,
,
,
&quot;Export list of members by biographical characteristics. Washington: CQ Press. Dynamically generated November 10, 2018, from CQ Press Electronic Library, CQ Press Congress Collection: http://library.cqpress.com/congress/export.php?which=memberbioadv&amp;congress=198&amp;yearlimit=0&quot;
To make the files readable in R, the first thing we’ll want to do is strip the first two lines of each file and the last three lines of each file. (Of course I checked first to make sure each file was the same in this regard.) There are several ways to get rid of specific lines from files. The venerable sed command is one. We loop it over each CSV file, telling it to delete (d) lines 1 and 2:
## Remove first two lines from each csv file
for i in *.csv; do
sed -i.orig '1,2d' $i
done
The -i.orig option makes a copy of the original file first, appending a .orig extension to the filename.
We do the same thing to delete the last three lines of each file. You can use some versions of head to do this quite easily, because they accept a negative number to their -n argument. Thus, while head -n 3 usually returns the first three lines of a file, head -n -3 will show you all but the last three lines. But the version of head that ships with macOS won’t do this. So I used sed again, this time taking advantage of Stack Overflow to find the following grotesque incantation:
## Remove last three lines from each csv file
for i in *.csv; do
sed -i.orig -e :a -e '1,3!{P;N;D;};N;ba' $i
done
The -e :a is a label for the expression, and the '1,3!{P;N;D;};N;ba' is where the work gets done, streaming through the file till it locates the end, deletes that line, and then branches (b) back to the labeled script again (a) until it’s done it three times. Gross.
You could also do this using a combination of wc (to get a count of the number of lines in the file) and awk, like this:
awk -v n=$(($(wc -l __ file) - 3)) 'NR &quot;1945-01-01&quot;) %__%
group_by(pid) %__% nest() %__%
mutate(data = map(data, ~ mutate(.x, term_id = 1 + congress - first(congress)))) %__%
unnest() %__% filter(term_id == 1)
first_terms
#__ __ # A tibble: 2,998 x 39
#__ pid congress last first middle suffix nickname born death sex #__ #__ 1 1 79 Aber… Thom… Gerst… NA NA 1903-05-16 1953-01-23 M #__ 2 2 79 Adams Sher… NA NA NA 1899-01-08 1986-10-27 M #__ 3 4 79 Allen Asa Leona… NA NA 1891-01-05 1969-01-05 M #__ 4 5 79 Allen Leo Elwood NA NA 1898-10-05 1973-01-19 M #__ 5 6 79 Almo… J. Linds… Jr. NA 1898-06-15 1986-04-14 M #__ 6 7 79 Ande… Herm… Carl NA NA 1897-01-27 1978-07-26 M #__ 7 9 79 Ande… John Zuing… NA NA 1904-03-22 1981-02-09 M #__ 8 10 79 Andr… Augu… Herman NA NA 1890-10-11 1958-01-14 M #__ 9 13 79 Andr… Walt… Gresh… NA NA 1889-07-16 1949-03-05 M #__ 10 14 79 Ange… Homer Daniel NA NA 1875-01-12 1968-03-31 M #__ # ... with 2,988 more rows, and 29 more variables: position , party ,
#__ # state , district , start , end , religion ,
#__ # race , educational_attainment , job_type_1 ,
#__ # job_type_2 , job_type_3 , job_type_4 , job_type_5 ,
#__ # mil_1 , mil_2 , mil_3 , start_year , end_year ,
#__ # name_dob , start_age , poc , days_old ,
#__ # months_old , full_name , end_career , entry_age ,
#__ # yr_fac , term_id The trick here is that mutate(data = map(data, ~ mutate(.x, term_id = 1 + congress - first(congress)))) line, which nests one mutate call inside another. We group the data by pid and nest() it so it’s as if we have a separate table for each representative. Then we use map() to add a term_id column to each subtable. Once we have a per-person term_id, and we grab everyone’s first term, we can e.g. take a look at the breakdown of freshman representatives by gender for every session since 1945:
First-term representatives by gender, 1945-2019
And also to break that out by Party:
First-term representatives by gender and party, 1945-2019