How to get Twitter bio information for U.S. Congress legislators in CSV format using t, csvkit, Unix tools, and Sunlight Foundation data

quickie-t-congress.md

Use public data, t, and csvkit to find Congressmembers on Twitter from the command-line

This snippet uses:

• t, a command-line tool for accessing the Twitter API
• csvcut from the invaluable csvkit suite
• a CSV of U.S. congressmember data compiled by the Sunlight Foundation, from the public unitedstates/congress-legislators Github project.

First, download the CSV (included below in this gist as sunlight-legislators.csv:

   curl -O http://unitedstates.sunlightfoundation.com/legislators/legislators.csv

Then:

• use csvcut to select the twitter_id column (the -x flag trims blank lines
• use tail to select all lines except the header (probably more efficient, imperceptably so, to use sed)
• pass each Twitter handle via xargs to the t users subcommand. The --csv flag returns the data as CSV

       csvcut -x -c twitter_id  legislators.csv | 
           tail -n+2 | 
           xargs t users --csv > twitter-legislators.csv

You can see the results of that data query (as of 2016-03-18) in the attached file: twitter-legislators-csv.

At this point, you can throw it all into a database and do some table joins. Or, just stick to csvkit and pipes; the snippet below will:

1. Join Congressmembers to their respective Twitter info, via csvjoin on the twitter_id and Screen name columns, respectively
2. Filter the list via csvgrep to select only legislators currently in office (via the in_office column)
3. Use csvsort to do a reverse sort on the Followers column
4. Use csvcut to prune the columns to get just the title, name, party, twitter name, followers, tweets count, and last tweeted at values.
5. Use head to trim the list to top 25
6. Use csvlook to pretty print the output

       csvjoin -c 'twitter_id,Screen name' legislators.csv twitter-legislators.csv | 
            csvgrep -c 'in_office' -m '1' | 
            csvsort -r -c 'Followers' | 
            csvcut -c 'title,firstname,lastname,party,state,twitter_id,Followers,Tweets,Last tweeted at' | 
            head -n 25 | csvlook

Behold, your Twitter overlords:

(note: Rep. Kevin McCarthy, aka the current holder of @GOPLeader, shows up twice because GOPLeader shows up twice in Sunlight's spreadssheet: once for McCarthy and once for former Rep. Eric Cantor. So we actually need to filter the original legislators.csv before joining it. Oh well, you can figure it out)

title	firstname	lastname	party	state	twitter_id	Followers	Tweets	Last tweeted at
Sen	John	McCain	R	AZ	SenJohnMcCain	1996392	10831	2016-03-18T16:26:24+00:00
Sen	Bernard	Sanders	I	VT	SenSanders	1710683	13916	2016-03-18T16:43:27+00:00
Rep	Nancy	Pelosi	D	CA	NancyPelosi	760161	3791	2016-03-18T12:44:56+00:00
Sen	Ted	Cruz	R	TX	SenTedCruz	747063	3955	2016-03-17T21:04:05+00:00
Rep	Paul	Ryan	R	WI	SpeakerRyan	598907	3993	2016-03-18T16:40:32+00:00
Sen	Elizabeth	Warren	D	MA	SenWarren	515662	871	2016-03-18T16:37:08+00:00
Sen	Harry	Reid	D	NV	SenatorReid	319748	3659	2016-03-17T20:10:40+00:00
Rep	Trey	Gowdy	R	SC	TGowdySC	265294	1366	2016-03-17T20:35:20+00:00
Sen	Mike	Lee	R	UT	SenMikeLee	220186	3716	2016-03-18T14:08:27+00:00
Rep	Darrell	Issa	R	CA	DarrellIssa	216015	19797	2016-03-18T15:25:30+00:00
Sen	Kirsten	Gillibrand	D	NY	SenGillibrand	166858	12042	2016-03-17T21:00:52+00:00
Sen	Charles	Schumer	D	NY	SenSchumer	132427	7238	2016-03-18T16:39:17+00:00
Sen	Tim	Scott	R	SC	SenatorTimScott	117092	4486	2016-03-17T18:41:41+00:00
Sen	Lindsey	Graham	R	SC	GrahamBlog	98665	4243	2016-02-18T15:59:03+00:00
Sen	Charles	Grassley	R	IA	ChuckGrassley	97332	4391	2016-03-18T00:14:09+00:00
Rep	Ileana	Ros-Lehtinen	R	FL	RosLehtinen	77581	19575	2016-03-18T16:23:02+00:00
Sen	Barbara	Boxer	D	CA	SenatorBoxer	73129	2038	2016-03-18T15:44:03+00:00
Sen	John	Cornyn	R	TX	JohnCornyn	73105	18912	2016-03-18T16:39:30+00:00
Rep	Kevin	McCarthy	R	CA	GOPLeader	70686	7341	2016-03-17T18:28:39+00:00
Rep	Kevin	McCarthy	R	CA	GOPLeader	70686	7341	2016-03-17T18:28:39+00:00
Sen	Dianne	Feinstein	D	CA	SenFeinstein	67282	3037	2016-03-18T15:33:00+00:00
Sen	Richard	Durbin	D	IL	SenatorDurbin	64730	6216	2016-03-18T14:56:24+00:00
Sen	Mitch	McConnell	R	KY	McConnellPress	63203	7941	2016-03-17T16:01:43+00:00
Sen	Mark	Warner	D	VA	MarkWarner	57082	4635	2016-03-18T14:19:29+00:00

sunlight-legislators.csv

twitter-legislators.csv