Activity 1-3

Adapted from Brown University CSCI0931. Used with Permission.

Task 1: Compare Any Legislator

  1. Make a copy of Activity 1-3. It contains pretty much the stuff that we did in Activities 1-1 and 1-2, but in this spreadsheet, we have kept the whole set of Legco records. The exception is the PivotTable, which is currently blank, and StarryCompare, which is currently zeros (because PivotTable
  2. Take a look at PivotTableFlipped. Google Sheets is not able to make Pivot Tables that have too many columns, and there are far too many vote-ids for Google to work with. Therefore, we have flipped the rows and columns to make PivotTableFlipped.
  3. In the PivotTable sheet, use TRANSPOSE to recreate the Pivot Table that we made in Activity 1-2. Check to make sure that none of the cells are colored red (i.e. none of them are blank).
  4. Check StarryCompare. The table should have been repopulated -- i.e. the calculations should be back in the sheet, now that PivotTable has data.
  • Now we will start our work on the sheet GeneralCompareTemplate. Right click on the tab and rename it GeneralCompare.
  • New Rule We want to reference as many cells as possible (using functions). This helps you (and us!) understand where everything is coming from. Use functions that reference PivotTable (like =PivotTable!A5) to fill the row and column headers (cells G10:G79 and cells H9:JG9, starting in the blue boxes). Your spreadsheet should now look something like this (click to see it bigger):
  • Use Data Validation to allow a list of names in cell B2. These names can be referenced from the column A in PivotTable.
  • In cell B3, use the MATCH function to get the legislator's alphabetic rank. Reference cells from Column A in PivotTable. The first (in PivotTable!A2) should be 何俊仁 Albert HO and the last person (in PivotTable!A71) should be 黃碧雲 Dr Helena WONG (Legco is horribly inconsistent about the formatting of our legislators' English names -- sometimes they put the surname first, sometimes last -- which is why we are using both English and Chinese names.)
  • In cell B7, use the OFFSET function to re-display the name of the legislator in cell B3). You should offset a cell from the PivotTable sheet (which should be sorted alphabetically). The number of rows to offset should be based on the alphabetic rank we just computed (in cell B3).
  • In cells H7:JG7 (the green cells), modify the function in B7 to display the votes of the legislator whose name appears in B2 and B7. How many rows should you offset? How many columns? You will need to use an absolute reference ($) to make sure you always use cell B3.
  • We can now fill the table. Copy the function from cell StarryCompare!B2 to cell H10 and modify it. Remember that the name of the legislator that we want to compare to is B7 on this sheet, and his/her votes are in H7:JG7. (You will also need to use an absolute reference somewhere.)
  • Use the COUNTIF function to again compute num_agree,num_disagree,and num_not_voting in columns C:E (the orange cells).
  • In column F, compute the score of each legislator into that column using this forumla:$$\frac{\text{num_agree}-\text{num_disagree}}{\text{num_agree}+\text{num_disagree}}$$
  • In column B, use the rank() function to calculate the rank of each legislator, given the current comparison function.
  • You will see that the rank() function gives an error because one of the legislators gives a "divide by zero" error. Therefore, we will need to override his score so that we can rank the others. The IFERROR() function checks if a cell contains a valid number. Modify the score function in column F to set the score to some default value (use -100) if a division by zero occurs.
  • Now we're ready to sort this sheet. Put the cursor in B9, and click on View -> Freeze -> Up to current row(9). This should freeze the first 9 rows. Now we can sort by Column B.
  • Use the drop-down menu in B2 to pick a few other legislators and rank the rest against them. Then sort the table by rankings. Make sure that things work.
  • There are two more tabs in the spreadsheet: ElectionData and Translations. In A10, write a formula using LEFT, MATCH (you will probably have to use MATCH with 0 because the ElectionTable is unsorted) and OFFSET that will pull out the party (政治聯繫) for each legislator from ElectionTable. This should be quite straightforward:
    1. You can use the three leftmost characters to pull out the Chinese name of most of the legislators, except for 方剛 Vincent FANG and 葉劉淑儀 Regina IP, who will need special handling.
    2. 郭偉强 KWOK Wai-keung is a problem -- we will not be able to handle him with our current methods (can you figure out why?). For the time being, just treat him as an atypical case.
    Check to make sure that the results make sense. One easy way to check is to compare against Starry, and then sort by rank. The legislators who are the most similar to her should come from the same party. (The election table is in Chinese because Wikipedia uses a different English naming system than Legco does, which makes matching against the English names very painful. If you really are not comfortable with Chinese, we have provided a EnglishTranslations sheet, and you can use a similar MATCH and OFFSET to translate the Chinese to English if you wish.)

    We now have a nice spreadsheet that is also an application -- i.e. other people can use it to make their own conclusions about the data. Before we go on, a few things to keep in mind:

    1. We didn't have to have the green cells in this sheet. We could have entered a very large and complicated function in cell D10. But doing it step-by-step is a good idea because (1) it breaks the large problem into manageable pieces and (2) it will be easier to figure out what we did later.
    2. Nice formatting with cells and text boxes also help with understanding and readability.

    • Task 2: Diving Deeper and Making Observations

    For this part, the whole group will work together -- more people notice more patterns. I want you to dive deeper into the data and come up with some interesting observations. Some examples to get you started:
    1. Who is least like Starry? What happens if we rank against that person? Do we get exactly the same result? What if we rank against a more moderate legislator (try somebody like 田北俊 James TIEN or 莫乃光 Charles Peter MOK)?
    2. Rank against Starry again, but this time, look at the scores (as opposed to the rank). Do you notice anything interesting about the scores? Can we extrapolate from this and make some observations about rankings, in general?
    3. Our legislators come from a wide range of backgrounds (i.e. we have lots of political parties, etc). Each of these backgrounds have their own claims (e.g. the functional constituencies are there to represent the interests of their profession, the political parties all have their own visions of how HK should be governed, etc). Are their claims backed up by data (i.e. do their actions -- the voting records -- reflect their claims)?
    Write down any interesting patterns that you see. This will be the basis of your homework in a couple of weeks.