Part 27After word of the Toriemander reaches the Columbia County Board of Supervisors, we are tasked with preparing various options for reform of the board of supervisors.
We first decide to consider a 13-member board. We add two columns to our xlsx spreadsheet. RELPOP is the population of each town relative to the quota. To calculate the quota, we sum the town/city populations for the entire county and divide by 13.
E21: =SUM(E2:E20)/13
The RELPOP column is calculated as
I2: =E2/$E$21
...
I20 =E20/$E$21
We also add a DIST column for storing district numbers. We leave those cells empty for now. We also sort the rows by population. We decide this will help our analysis, and since the join is performed based on COUSUB, the two tables don't have to be in any particular order.
We copy the xlsx file to our csv file (paste values only), and save the csv file.
The .csv file looks like this.
We then decide to strip the file xlsx and csv files to the bare essentials, removing the extraneous columns.
We also remove the extraneous columns from the .csvt file:
We are now ready to make changes in our spreadsheet, and then quickly move them into GQIS:
The process is quite straightforward:
(1) Remove the current .csv layer from the Project
(a) right click on layer,
(b) select Remove,
(c) confirm.
(2) Add csv file:
(a) click on comma icon.
(b) browse to csv file
(c) click on radio button indicating no coordinates
(d) OK
(3) Join the csv file.
(a) right click on layer to join too
(b) select properties
(c) select join
(d) click on green +
(e) browse to file to join
(f) select field to join with
(g) select field to join to
(h) OK
We can now create an initial display. We have not assigned any districts yet, this is more of an analytical plan.
We notice that several towns have population enough for a single district, so we will start assigning district numbers to those towns. Kinderhook will be District 1. We also realize that when larger towns are assigned a single district, that the population per district for the remainder of the county declines.
Back to the xlsx spreadsheet.
We create a minispreadsheet beginning in Column I
Column I is simply a series of district numbers 1...13
Columns J and K are filled with the following equations.
J1: =SUMIF(E$2:E$20,I1,C$2:C$20)
K1: =COUNTIF(E$2:E$20,I1)
J1 will contain the sum of the population of the towns assigned the district number in I1
K1 will contain the count of such towns.
J14: =SUMIF(J1:J13)
K14 =SUMIF(K1:K13)
We can use those cells to keep track whether all towns have been assigned a district.
We assign districts 1 to 6 for the six largest towns.
We then update QGIS:
(1) Copy and paste from xlsx file to csv file
(2) Save csv file.
(3) Remove csv layer
(4) Add csv layer
(5) Join csv layer to shapefile layer
Now that some districts have been assigned, we can categorize our towns based on district number. Towns without a district are not categorized, but there is a color assigned to non-categorized towns. We change that color to a neutral beige.
We notice that if we assign a district to Livingston, then the remaining towns can be paired in an obvious fashion to form the final 6 districts of our total of 13.
We go back to the xlsx file and assign the district numbers, and then bring them back into QGIS. When we first do the join, the new districts are not colored. We need to go back in Style, and classify districts 7 to 13.