We will create tables of growth rates for counties and cities of the WA state, plot all WA cities on a map and create a page with a map, pie chart and tables using the Google Visualization API. To get there, please install the googleVis package and then follow the steps below:
Re-create the dt
object from the class by reading in the OFM data. It contains population for all cities in WA.
dt <- fread("ofm_april1_population_final.csv")
Extract all valid city records (i.e. where Filter is 4) and 4 columns, namely County, Jurisdiction and the two columns containing the 2018 and 2019 population. Store the object in citypop
. Rename the 2018 column to “PopBase” and the 2019 column to “Pop”. After this step, dim(citypop)
should give 289 rows and 4 columns.
Convert the PopBase and Pop columns into numeric type.
Create a dataset countypop
containing the sums of the PopBase and Pop columns by County. The aggregated columns should have the same names, namely PopBase and Pop. After this step, you should see
head(countypop)
County PopBase Pop
1: Adams 10800 10880
2: Asotin 8480 8485
3: Benton 162020 165525
4: Chelan 45070 45310
5: Clallam 30445 30950
6: Clark 257080 261610
dim(countypop)
[1] 39 3
In citypop
, create a column Growth as (Pop - PopBase)/PopBase * 100 rounded to 2 decimals. Do the same for countypop
.
citypop
, add two columns in one go (using the ':='()
syntax):
frank()
function. Ranking in decreasing order can be achieved by passing negative values of the original column, here Growth
.Do the same for countypop
.
In citypop
, add a column CountyShare computed as the share of Pop within its county (as percentage). Hint: For this you can join citypop
and countypop
using the i
syntax. It should have the form (fill in ...
)
citypop[countypop, CountyShare := ..., on = ...]
In citypop
, create a column City which takes values of Jurisdiction but removes any text containing " (part)". Hint: For replacing text use the function gsub(" (part)", "", Jurisdiction, fixed = TRUE)
on the j
dimension (just assign that call to City). Remove the Jurisdiction column by chaining the removal right after the replacement.
To move the City and County columns to the front of the dataset, use
setcolorder(citypop, c("City", "County"))
Create interactive (sortable) tables for cities and counties by
library(googleVis)
gtbl.county <- gvisTable(countypop)
plot(gtbl.county)
gtbl.city <- gvisTable(citypop, options = list(page='enable', pageSize = 40))
plot(gtbl.city)
(Note that to view the tables there needs to be an internet access.)
The file “citycenters.csv” provided with the class data contains the latitude and longitude of cities in WA, which we will need for the map. Read the file into R using fread
and store it in an object called cc
.
Rename the column “NAME” in the cc
dataset to “City”.
Join the datasets citypop
and cc
on the “City” column, so that columns “lat” and “long” appear as a new character column (called “latlong”) of citypop
in the form lat:long. Hint: Use the paste
function to combine “lat” and “long” with sep=":"
. In the joining command, don’t forget to use the prefix i.
for those two columns. After this step, you should see
head(citypop)
City County PopBase Pop Growth Rank StateShare CountyShare latlong
1: Hatton Adams 110 115 4.55 14.0 0.00 1.06 46.7747984582264:-118.828249712648
2: Lind Adams 550 550 0.00 247.5 0.01 5.06 46.9718705700471:-118.615626494366
3: Othello Adams 8270 8345 0.91 121.5 0.17 76.70 46.8262050796596:-119.175365734618
4: Ritzville Adams 1660 1660 0.00 247.5 0.03 15.26 47.127504898941:-118.379872032013
5: Washtucna Adams 210 210 0.00 247.5 0.00 1.93 46.7531092188871:-118.313575039108
6: Asotin Asotin 1275 1280 0.39 185.5 0.03 15.09 46.3400565321523:-117.051767869215
Can you extract records where there was no match? Hint: the “latlong” column is NA for those records. Remove those records from citypop
.
Now we neeed to create a column with the info that will be displayed when clicking on the city icons. Say we want to see the city name, population and growth. Thus, create a column “Tip” constructed via paste0(City, ": ", Pop, ", Gr: ", Growth)
.
Plot points on a google map using:
plot(gvisMap(citypop, "latlong", "City", tipvar = "Tip",
options = list(height = 800, width = 800)))
Another types of google charts are a Geo Chart and a Pie Chart. To combine such charts with the tables created previously on one page, use the following code:
gchart <- gvisGeoChart(citypop, "latlong", hovervar = "Tip", colorvar = "Pop",
options = list(region="US-WA", resolution = "metros",
height = 800, width = 800))
gpiechart <- gvisPieChart(countypop[, .(County, StateShare)],
options = list(width=500, height=800))
plot(gvisMerge(
gvisMerge(gchart, gpiechart, horizontal = TRUE),
gvisMerge(gtbl.county, gtbl.city, horizontal = TRUE,
tableOptions = "cellspacing=\"20\" border=\"5\"")
)
)
That’s it! Did you produce a page like this?