Visualizing House of Representative Data Using Power Query

Quote of the Day

Success is never due to one thing, but failure can be. Sleeping well won’t make you successful, but not sleeping enough will hold you back. Hard work is rarely enough without good strategy, but even the best strategy is useless without hard work. Many things are necessary, but not sufficient for success.

James Clear


Introduction

Figure 1: Pivot Table Showing How Minnesota representatives for House Ammendment 789 to BIll HR 2546.

Figure 1: Pivot Table Showing How Minnesota representatives for House Amendment 789 to Bill HR 2546.

I have been following certain bills through the US House of Representatives and wanted to know how the voting varied by US regions and political parties. I also want to generate tables that show how my state's representatives vote (Figure 1). Fortunately, the votes are documented online and Power Query was able to easily grab and process the data.

The government does a decent job of graphically presenting the data but I wanted to view the data a bit differently than how they did because I want to:

  • Quickly be able to look at how my state's congressional representatives voted
  • See how the voting varied US region and party
  • See the votes of only congressional representative – the online data includes delegates from US territories if the votes will not change the pass/fail results

This post provides a workbook that can be used to:

  • graphical view the voting results by US region and party
  • filter the data to view the votes by state delegation
  • graphical view of the voting without including the territorial delegates

Background

The online data is well-formatted:

  • Votes of the US House and Senate are listed here
  • US House data includes the district number and party of each representative
  • Territory delegates are also included if their voting does not affect the vote outcome

There is one minor issue involving how votes are recorded. An affirmative vote may be recorded as "Yes" or "Aye." A negative vote may be recorded as "No" or "Nay." Also, some representatives choose not to vote, either through absence or simply deciding not to vote.

Because the US House and Senate data is formatted differently, I have chosen to restrict my workbook to processing only House data. My processing plan is straightforward:

  • Use Power Query to download the data
  • Filter the data to remove territorial delegates (if needed)
  • Augment the data with US region information using region designations from the US Census Department

The workbook is available here.  It does contain a small, button-activated macro that updates all the queries and pivot tables.

To use the workbook, you must:

  • go to the list of votes
  • copy the link to the US House vote (NOT Senate) you are interested in
  • paste the link into the vote link location on the Report tab of the workbook.

This processing sequence leaves me with a tidy table that I then use to generate pivot tables and charts.

Analysis

I randomly chose a bill that went through the House recently that involved adding some wilderness lands (HR  2546, Amendment 789). I wanted to see how the vote results varied by party and region of the US.

Figure 2 shows that the bill passed but the voting was along party lines. The graph in Figure 2 is my duplication of a similar graph on the government website. The only difference in my plot is that I removed the non-voting delegates.

Figure 2: House Vote Totals Summarized By Party.

Figure 2: House Vote Results Summarized By Party.

Figure 3 shows how the voting results varied by region. Again, it is very clear that the voting was along party lines in all regions of the US.

Figure 3: Vote Summary By Region and Party.

Figure 3: Vote Summary By Region and Party.

Conclusion

I was able to view the data for a US House bill that I was interested in. Figure 3 shows how polarized the US is nationally right now. Figure 4 shows that the Colorado House delegation was also polarized.

Figure 4: Colorado Voting Breakdown By Party.

Figure 4: Colorado Voting Breakdown By Party.

My home state of Minnesota's House delegation was also polarized (Figure 5).

Figure 5: Minnesota Delegation was also Polarized.

Figure 5: Minnesota Delegation was also Polarized.

This entry was posted in Civics Through Spreadsheets, Excel. Bookmark the permalink.