Homework 1-5

Senators' Political Alignment

Due February 17, 2017 at 11:59pm


For ACT 1-2, using the data on first 10 Senate roll call votes for the 114th Congress - 1st session (2015), we calculated which senators agreed or disagreed the most with Senator Elizabeth Warren.

For this homework, we will be calculating all of the pairwise alignment values across Senators (i.e. comparing all the Senators to each other, not just each Senator to Warren). We will use the results of the Senate roll call votes on bills for the 114th Congress - 2nd session (2016).

Your alignment values should be displayed in this format:

Task 1

There were 163 Senator roll call votes for the 114th Congress - 2nd session. You can see a brief description of all the votes here. You should see that each vote is associated with a “question” category, for example, “On the Motion” or “On the Conference Report.” If you navigate to the XML version of the page (you should see the link for XML in the top right hand corner), you will see the <question> element.

We would like to limit our analysis to votes on the question “On the Amendment.” Thus, we need to determine this specific set of vote numbers. We will use Google Sheets’ IMPORTXML and filter functions to help with this task.

Create a new spreadsheet. In the first column, import all the <vote_number> elements. The resulting list of vote numbers should start at 163 and then decrease to 1. Then, in the second column, import all the <question> elements.

Filter your sheet so that you are only left with the rows for the question “On the Amendment.” You should see that there are 35 votes of this type.

Task 2

On a new sheet, obtain the Senators’ results for the 35 “On the Amendment” roll call votes.

The XML file for vote number 1 has the format


In place of the “00001” of this URL, you should go through each of the 35 vote numbers obtained earlier.

For each Senator, import the <member_full>, <party>, and <state> elements, placing each element in a separate column. Then in the adjacent columns, import the Senators’ votes (‘Yea’, ‘Nay’, or ‘Not Voting’).

Once you have imported all your data, copy and paste the values only to a new sheet to prevent the IMPORTXML command from recomputing. On this sheet, sort the data so that Democrats and Republicans are grouped together and so that within each party, they are ordered by state.

Then create another new sheet. Here, code each vote as 1=’Yea’, -1=’Nay’, 0=’Not Voting’.

Because we are using so many sheets in this assignment, please give each sheet a descriptive name.

Task 3

Create a new sheet. As in the example image shown above, the Senators’ names should serve both as row headers and column headers. This format will allow you to display the alignment values for all pairs of Senators. Also include each Senator’s party and state.

Once you have set the layout of the sheet, you can begin calculating the alignment values.

As a reminder, the formula for the alignment of two Senators is

This formula is too complex to complete in one function call. Thus, you likely will want to break this formula down into a series of steps. We suggest that you create an additional sheet for each step:

  • Calculate "num_agree - num_disagree." For comparing just 2 Senators, this value is equivalent to the sum-product of 2 Senators’ votes. This sum-product works in the following way:
    • +1 point: both are 1 or both are -1
    • -1 point: one is -1 and the other is 1
    • 0 points: at least one did not vote
    Since we want to calculate "num_agree - num_disagree" for all pairwise comparisons of Senators, you should use MMULT to speed up these calculations. Call MMULT on the matrix of votes. As a hint, think about if you need to use the TRANSPOSE function.

  • Calculate "num_agree + num_disagree." For comparing just 2 Senators, this value is equivalent to the sum-product of the absolute values of the 2 Senators’ votes. (Thus, you may want to also create a sheet for these absolute values). This sum-product works in the following way:
    • +1 point: both votes are either 1 or -1
    • 0 points: at least one senator did not vote
    Again, you should use MMULT and TRANSPOSE to calculate these values for all pairs of Senators.

  • Determine your final alignment values using your previous calculations.

One way to check the validity of your results is to ensure that the alignment is always “1” on the diagonal (Boxer vs. Boxer, Feinstein vs. Feinstein, etc.), since this compares a person with himself or herself.

Task 4

To better visualize your alignment values, apply conditional formatting to your final sheet. Create a color scale that depicts the range from -1 to 1.

Task 5

On another sheet, calculate the average alignment values for the following:

  • Within the Democratic party
  • Within the Republican party
  • For all pairs in which one Senator is a Democrat and the other is a Republican

Then interpret your findings.

Extra Credit

We would like you to explore the data some more on your own, investigating any hypotheses about Senators’ alignment that you come up with. You can either comment on trends you find in your existing spreadsheet, or you can go one step further and reformat the spreadsheet or apply additional formulas (max, min, etc.) to manipulate the data even further.

On a separate sheet, please discuss your findings and any methods you used. We will be awarding points based on the effort you put in. You can earn up to an extra 10% on this homework grade.


On a separate sheet, please put down how many hours you worked on this assignment, people you worked with, and whether you went to TA hours for help or clarification on this assignment.

Once you're done, share your file with cs0030handin@gmail.com by midnight, 2/17.

Make sure your submission has your name in the filename: FirstLast_HW1-5. “FirstLast” should be replaced with your first and last name or we will take off points. Make sure every task has been completed.