Embedded Files
The Congress.gov homepage
About Congress.gov
About Congress.gov
Congress.gov is the official website of the United States Congress. It is presented by the Library of Congress. The site provides up-to-date information for Members of Congress and other U.S. Government officials, as well as the public.
The site provides this information based on data from various agencies, including
the Office of the Clerk of the U.S. House of Representatives,
the Office of the Secretary of the Senate
the Congressional Budget Office
the Government Publishing office
the Library of Congress' Congressional Research Services
The site's homepage includes several useful sections, including recent news about Congress, where to view the House and Senate live when they are in session, and a search feature that allows users to find their Congress members.
The site is updated the morning after a session adjourns.
Searching Congress.gov for Legislation, Records, and More
Searching Congress.gov for Legislation, Records, and More
Users who are familiar with online databases like EBSCOHost or ProQuest will likely find Congress.gov approachable. The Help Center (linked from the Support flyout menu at the top right of the page) also contains a quick start guide, and the Search Tools link to the left of the Support menu also provides more detailed information.
The fastest way to get started searching Congress.gov is to use the standard search bar at the top of the home page:
The dropdown menu, for search categories, defaults to Current Congress. The other options are:
Search by Congress
Current Congress
All Congresses
Search by Source
Legislation
Committee Materials
Congressional Record
Members
Nominations
Clicking on the More Options toggle expands the search panel to show more advanced options.
The full Advanced Search page of Congress.gov
However, if a user needs even more specific search options, there is a full Advanced Searches page, accessible from the top navigation bar (to the right of the Congress.gov logo).
Exporting Data from Congress.gov
Exporting Data from Congress.gov
For the purposes of this example, the search will be relatively simple: we will look at instances of legislation related to the LGBTQIA+ community that have been proposed from 1973-2022 (the 93rd to 117th Congresses).
Here, we have used the search term "lgbt*" with an asterisk/wildcard so that records that contain variations like LGBT, LGBTQ, or LGBTQIA+ will be included in the results (a test search for just "lgbt" only returns 55 results, as opposed to 143 for "lgbt*"), checked the box for 1973-2022 under Congress (Years), and selected 93-117 (1973-2022) from the listed sessions.
A search for the term "lgbt*" on Congress.gov
This search returns 143 results:
The search results for the term "lgbt*" on Congress.gov
After double-checking that the search terms and other filters are correct, we can then choose to:
save this search (recommended if the user will need to refer to these results in the future)
download the search results as a CSV
Clicking Download Results will prompt a modal window, which informs the user that they will download a CSV with the first 1,000 results. For searches with over 1,000 results, the user will need to refine their search to limit the amount. The most effective way to do this is to change the time period by adjusting the session of Congress.
A modal window letting the user know they will only be able to download 1,000 results at a time
Clicking OK results in the download of a CSV file.
Note:
This file will be named
search_results_yyyy-mm-dd_HHMMtt.csv
Where "yyyy-mm-dd_HHMMtt" is Year-Month-Day_HourMinuteAM/PM that the user downloaded the file.
Working with CSV Data from Congress.gov
Working with CSV Data from Congress.gov
Checking the Contents of the CSV
Checking the Contents of the CSV
With any data analysis or visualization project, it's important to verify the data before getting started.
Opening this CSV in Excel, we can see that the first two rows show information about the search, including the download date and time, and the URL for the search, which can be used to refer back to the search if needed.
A CSV downloaded from Congress.gov, open in Microsoft Excel
The columns in the CSV are:
Legislation Number
URL: a link to the full record for this legislation on Congress.gov
Congress: the session of Congress that the legislation was proposed in
Amends Bill: the Legislation Number of any bill that this legislation amends
Title: the title of the legislation
Sponsor: the Senator or Representative that introduced the legislation
Date of Introduction: the date that the legislation was introduced to Congress
Date Offered, Date Submitted, Date Proposed: additional dates in the progression of legislation through Congress
Number of Cosponsors: how many Senators or Representatives agreed to cosponsor the legislation
Committees: the committee(s) in the House or Senate where the legislation was proposed
Latest Action Date: the date when the most recent action was taken on the legislation
Latest Action: a summary of the last action that was taken on the legislation
Depending on the format of the CSV and what program it is opened in, there can be issues with character encoding. We can see this in the name of Rep. Raúl Grijalva, where the ú has been replaced with ú (Microsoft Excel, Windows 10). There is also an instance of an apostrophe that has been replaced with ’.
Because these appear to be the only instances of this type of error, it can be fixed using Find & Replace.
An example of a character encoding error
For more on text encoding in Excel: Choose text encoding when you open and save files
i18nqa.com has a useful UTF-8 Encoding Debugging Chart
Tracking Down Missing Information
Tracking Down Missing Information
Referring to the CSV above, we can see that rows 65, 66, and 126 are missing data for the columns Date of Introduction and Committees. In two of these cases, it appears to be because they are amendments to prior legislation.
For amendments, Date Offered is used instead of Date of Introduction.
The entry for House Amendment 681 on Congress.gov
Using either the URL for the search in the CSV, or the link in the URL column for the legislation, we can check to see if we are able to get this information.
For example, row 66 is House Amendment (H.Amdt.) 681, which amends House Resolution (H.R.) 4863.
Clicking through to H.R.4863 shows that this legislation was introduced in the House Financial Services Committee and the Senate Committee on Banking, Housing, and Urban Affairs.
The entry for House Resolution 4863 on Congress.gov
In some cases, we will not be able to replace/add missing data. For larger datasets, manually finding and adding missing data is largely impractical. Entries with missing data may be discarded.
Once the contents of the CSV have been verified and updated as needed, the data can be converted to a table.
Converting to a Table and Saving
Converting to a Table and Saving
To convert the CSV content to a table in Excel, and save as an xlxs file:
Click on one of the cells in the table area and type CTRL+T (Windows) or CMD+T (MacOS)
OR, select all of the cells in the table and click Format as Table in the Home ribbon menu
Make sure that "My table has headers" is checked and click OK
Use the Save As menu to save the file as an xlxs file.
The Create Table dialog in Excel
Arraigning Data for Use in Tableau
Arraigning Data for Use in Tableau
While this file could be used in Tableau as is, it is a good idea to separate some of data out for ease of reading and flexibility. Here, we will create separate columns for:
Chamber: was the legislation introduced in the House or the Senate?
Party: was the legislation sponsored by a Republican, Democrat, or Independent?
State: what state is the sponsor of this legislation from?
Became Law: was this legislation passed and made law?
The information for these new columns will come from the Sponsor, Committees, and Latest Action Columns.
Adding Chamber Data
Adding Chamber Data
What chamber the legislation was introduced in is already provided in the Committees column. We can create a separate column for it by using Excel's LEFT and FIND functions.
Insert a new column next to Committees, and add the header Chamber
The text of every cell in the column Committees starts with either House or Senate, followed by a space
The LEFT function in Excel allows the user to extract/copy a set number of characters from the LEFT of the string in that cell. Since House and Senate have different numbers of characters, we will add the FIND function to use the first space in the string as a delimiter.
The formula (in the first cell under the Chamber header) will look like this:
=LEFT(T2,(FIND(" ",T2,1)-1))
Where T2 is the cell in the Committees column we are extracting text from.
Next, we will add the Party and State columns using VLOOKUP.
Adding Party and State
Adding Party and State
The Sponsor column has the sponsor's state and party affiliation listed after their name. As an example, [D-CA-41] means Democrat, California, 41st District. We can use this information to get the Party and State.
We can then use the MID and FIND functions in Excel to copy all of the text between the brackets into a column called Code.
=MID(I2,FIND("[",I2)+1,FIND("]",I2)-FIND("[",I2)-1)
Where I2 is the cell that has the Sponsor text.
This will take everything inside the brackets and put it into the Code column.
Then, we can create a column called Party Code and use LEFT to take the first letter of the string:
=LEFT([@Code],1)
This will fill the cell with the first letter in the string from Party Code. The cells under Party Code will show D, R, or I.
We will then make a new sheet called Lists, and make a table that matches the letter to the party name.
A table in the Lists sheet
We can now use VLOOKUP to create a column with the full party name:
=VLOOKUP([@[Party Code]],Party[#All],2,FALSE)
For more on VLOOKUP, check out ExcelJet - VLOOKUP
The same method (creating a table in the Lists sheet and using VLOOKUP) can be used to create the State Code and State columns.
congress_lgbtq_stats.xlsxBringing Data into Tableau
Bringing Data into Tableau
Open Tableau, and from the sidebar select Connect > To a File > Microsoft Excel, and then select the Excel file to use.
From here, we can use the data to look at things like where the most legislation was proposed, how often certain congresspeople proposed legislation, and more.
References
References
Congress.gov | Library of Congress. (n.d.). Retrieved May 4, 2022, from https://www.congress.gov/
Excel VLOOKUP function. Exceljet. (n.d.). Retrieved May 9, 2022, from https://exceljet.net/excel-functions/excel-vlookup-function
Microsoft. Microsoft Support. (n.d.). Retrieved May 8, 2022, from https://support.microsoft.com/en-us/office/choose-text-encoding-when-you-open-and-save-files-60d59c21-88b5-4006-831c-d536d42fd861
Texin, T. (n.d.). UTF-8 Encoding Debugging Chart. UTF-8 Character Debug Tool. Retrieved May 10, 2022, from https://www.i18nqa.com/debug/utf8-debug.html
VLOOKUP. Microsoft Support. (n.d.). Retrieved May 9, 2022, from https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1
Page updated
Report abuse