<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/congress-over-time/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://kieranhealy.org/blog/archives/2018/11/17/congress-over-time/"> R on kieranhealy.org</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>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.</p>
<p>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:</p>
<div>
<pre><code data-lang="text">
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,,,,,,,
</code></pre>
</div>
<p>The bottom of each file looks like this:</p>
<div>
<pre><code data-lang="text">
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;
</code></pre>
</div>
<p>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 <code>sed</code> command is one. We loop it over each CSV file, telling it to delete (<code>d</code>) lines 1 and 2:</p>
<div>
<pre><code data-lang="sh">
<span>## Remove first two lines from each csv file
</span><span></span><span>for</span> i in *.csv<span>;</span> <span>do</span>
    sed -i.orig <span>'1,2d'</span> <span>$i</span>
<span>done</span>
</code></pre>
</div>
<p>The <code>-i.orig</code> option makes a copy of the original file first, appending a <code>.orig</code> extension to the filename.</p>
<p>We do the same thing to delete the last three lines of each file. You can use some versions of <code>head</code> to do this quite easily, because they accept a negative number to their <code>-n</code> argument. Thus, while <code>head -n 3</code> usually returns the first three lines of a file, <code>head -n -3</code> will show you all but the last three lines. But the version of <code>head</code> 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:</p>
<div>
<pre><code data-lang="sh">
<span>## Remove last three lines from each csv file
</span><span></span><span>for</span> i in *.csv<span>;</span> <span>do</span>
    sed -i.orig -e :a -e <span>'1,3!{P;N;D;};N;ba'</span> <span>$i</span>
<span>done</span>
</code></pre>
</div>
<p>The <code>-e :a</code> is a label for the expression, and the <code>'1,3!{P;N;D;};N;ba'</code> is where the work gets done, streaming through the file till it locates the end, deletes that line, and then branches (<code>b</code>) back to the labeled script again (<code>a</code>) until it’s done it three times. Gross.</p>
<p>You could also do this using a combination of <code>wc</code> (to get a count of the number of lines in the file) and <code>awk</code>, like this:</p>
<div>
<pre><code data-lang="sh">
awk -v <span>n</span><span>=</span><span>$(($(</span>wc -l &lt; file<span>)</span> <span>-</span> <span>3</span><span>))</span> <span>'NR&lt;n'</span> file
</code></pre>
</div>
<p>There’s a reason people used to say <code>sed</code> and <code>awk</code> had those names because of the sounds people made when forced to use them.</p>
<p>Anyway, now we have a folder full of clean CSV files. Time to fire up R and get to the fun part.</p>
<p>Inside R, we get a vector of our filenames:</p>
<div>
<pre><code data-lang="r">
filenames <span>&lt;-</span> <span>dir</span><span>(</span>path <span>=</span> <span>&quot;data/clean&quot;</span><span>,</span>
                 pattern <span>=</span> <span>&quot;*.csv&quot;</span><span>,</span>
                 full.names <span>=</span> <span>TRUE</span><span>)</span>

filenames

<span>#> [1] &quot;data/clean/01_79_congress.csv&quot; &quot;data/clean/02_80_congress.csv&quot; </span>
<span>#> [3] &quot;data/clean/03_81_congress.csv&quot; &quot;data/clean/04_82_congress.csv&quot; </span>
<span>#> [5] &quot;data/clean/05_83_congress.csv&quot; &quot;data/clean/06_84_congress.csv&quot; </span>
<span>#> [7] &quot;data/clean/07_85_congress.csv&quot; &quot;data/clean/08_86_congress.csv&quot; </span>
<span>#> [9] &quot;data/clean/09_87_congress.csv&quot; &quot;data/clean/10_88_congress.csv&quot; </span>
<span>#> [11] &quot;data/clean/11_89_congress.csv&quot; &quot;data/clean/12_90_congress.csv&quot; </span>
<span>#> [13] &quot;data/clean/13_91_congress.csv&quot; &quot;data/clean/14_92_congress.csv&quot; </span>
<span>#> [15] &quot;data/clean/15_93_congress.csv&quot; &quot;data/clean/16_94_congress.csv&quot; </span>
<span>#> [17] &quot;data/clean/17_95_congress.csv&quot; &quot;data/clean/18_96_congress.csv&quot; </span>
<span>#> [19] &quot;data/clean/19_97_congress.csv&quot; &quot;data/clean/20_98_congress.csv&quot; </span>
<span>#> [21] &quot;data/clean/21_99_congress.csv&quot; &quot;data/clean/22_100_congress.csv&quot;</span>
<span>#> [23] &quot;data/clean/23_101_congress.csv&quot; &quot;data/clean/24_102_congress.csv&quot;</span>
<span>#> [25] &quot;data/clean/25_103_congress.csv&quot; &quot;data/clean/26_104_congress.csv&quot;</span>
<span>#> [27] &quot;data/clean/27_105_congress.csv&quot; &quot;data/clean/28_106_congress.csv&quot;</span>
<span>#> [29] &quot;data/clean/29_107_congress.csv&quot; &quot;data/clean/30_108_congress.csv&quot;</span>
<span>#> [31] &quot;data/clean/31_109_congress.csv&quot; &quot;data/clean/32_110_congress.csv&quot;</span>
<span>#> [33] &quot;data/clean/33_111_congress.csv&quot; &quot;data/clean/34_112_congress.csv&quot;</span>
<span>#> [35] &quot;data/clean/35_113_congress.csv&quot; &quot;data/clean/36_114_congress.csv&quot;</span>
<span>#> [37] &quot;data/clean/37_115_congress.csv&quot; &quot;data/clean/38_116_congress.csv&quot;</span>
</code></pre>
</div>
<p>Than, instead of writing a <code>for</code> loop and doing a bunch of <code>rbind</code>-ing, we can pipe our vector of filenames to the <code>map_dfr()</code> function and we’re off to the races:</p>
<div>
<pre><code data-lang="r">
data <span>&lt;-</span> filenames <span>%>%</span> map_dfr<span>(</span>read_csv<span>,</span> <span>.i</span>d <span>=</span> <span>&quot;congress&quot;</span><span>)</span>

<span>colnames</span><span>(</span>data<span>)</span> <span>&lt;-</span> to_snake_case<span>(</span><span>colnames</span><span>(</span>data<span>))</span>

data 

<span>#> # A tibble: 20,111 x 26</span>
<span>#> congress last first middle suffix nickname born death sex position party</span>
<span>#> </span></code></pre></div>