Re-odering columns without specfying all of them

When working with datasets with many columns, sometimes you just want to have the key row identification variables together and in the beginning. For example if you are dealing with patient data, you may want the id, dob, and gender as the first 3 rows, respectively, and not in arbitrary column positions in the data

To arrange columns by specfying all of them and/or just a subset, please see the cookbook link and the dplyr vignette on select()

# Create a dataframe example
df <- as.data.frame(matrix(c(1:20), ncol=10))
names(df)[c(3, 5, 7)] <- c("gender", "id", "dob")
df

##   V1 V2 gender V4 id V6 dob V8 V9 V10
## 1  1  3      5  7  9 11  13 15 17  19
## 2  2  4      6  8 10 12  14 16 18  20

# we want to bring "id", "dob", and "gender" to the beginning, and keep
# everything else the way it is
toBeginning <- c("id", "dob", "gender")
df <- cbind(df[, toBeginning],
            df[, !names(df) %in% toBeginning])
df

##   id dob gender V1 V2 V4 V6 V8 V9 V10
## 1  9  13      5  1  3  7 11 15 17  19
## 2 10  14      6  2  4  8 12 16 18  20

---

## Create a connection file to include in all of your database code (saves some time)

• description: basic connection to and query of the PostgreSQL database, use this as a source file R source code files.
• created: 9/19/2014
• author: Aaron D. Schroeder

Create database driver

library(RPostgreSQL) # load the database connection library

drv <- dbDriver("PostgreSQL") # create instance of database driver

Close all existing connections

all_cons <- dbListConnections(drv)
for(con in all_cons)
   {dbDisconnect(con)}

Using the driver instance, create a new connection to the database and set the dbname, user and password variables

con <- dbConnect(drv, dbname="sampleDb", host="localhost", port=5432
    , user="aaron", password="aaron")

---

## View for all tables in the connected database

connect to database

source(file="~/R/pg_connect.R")

dbname <- dbGetQuery(con, "SELECT table_catalog FROM information_schema.tables LIMIT 1")
View(dbname)

build query

sql = paste("SELECT table_schema, table_name
             FROM information_schema.tables
             WHERE table_catalog = '", dbname, "'
             AND table_schema NOT IN ('pg_catalog', 'files', 'information_schema')
             ORDER BY table_schema, table_name", sep="")

run query

dbTables <- dbGetQuery(con, sql)

display query results

View(dbTables)

---

## View all columns in a given table

connect to database

source(file="pg_connect.R")

set schema and table variables

schemaName = "sample_schema"
tableName = "sample_table"

build query

sql = paste("SELECT column_name, data_type
             FROM information_schema.columns
             WHERE table_schema = '",schemaName,"'
             AND table_name = '",tableName,"'", sep="")

run query

tableColumns <- dbGetQuery(con, sql)

display query results

View(tableColumns)

---

## A simple query

connect to database (set database in pg_connect.R, see above)

source(file="pg_connect.R")

build and run query

myTable <- dbGetQuery(con, "SELECT  id, item, description
                            FROM sample_schema.sample_table")

display query results

View(myTable)

myDataFrame <- read.csv("GradeRetentionData.csv")

The read.csv function assumes that your file has a header row, so row 1 is the name of each column. If that's not the case, you can add header=FALSE to the command:

mydata <- read.csv("filename.txt", header=FALSE)

If your data use another character to separate the fields, not a comma, R also has the more general read.table function. So if your separator is a tab, for instance, this would work:

mydata <- read.table("filename.txt", sep="\t", header=TRUE)

Note that when using read.table you need to specify header=TRUE if the first row is column names (default is FALSE which is the opposite of read.csv where default is TRUE).

author: "Aaron D. Schroeder" description: Takes treatment data that is in long-format, transforms and casts the data to wide-format, then finds all consecutive-month treatment runs (spells).

1. Add Libraries and Import Data

Sample data includes client id and service/treatment date.

library(reshape2)                                                    # Load reshape2 library
reshapeData <- read.csv("R dosage test data.csv")                     # Import csv data

##    clientid servicedate
## 1  Client A      1/1/14
## 2  Client A      2/1/14
## 3  Client A      3/1/14
## 4  Client A      5/1/14
## 5  Client B      2/1/14
## 6  Client B      3/1/14
## 7  Client B      4/1/14
## 8  Client B      6/1/14
## 9  Client B      7/1/14
## 10 Client C      1/1/14
## 11 Client C      2/1/14
## 12 Client C      5/1/14
## 13 Client C      7/1/14

2. Create New Transposed Data Frame

Transform service date into month_year. Add a received column to be used in the cast step to show months receiving service.

clientid <- reshapeData$clientid                                       # Create vectors for new df
monthYear <- format(as.Date(reshapeData$servicedate, "%m/%d/%y"), "%m_%Y")
received <- ifelse(!is.null(reshapeData$servicedate), 1, 0)
newDF <- data.frame(clientid, monthYear, received)                           # Create data frame

##    clientid monthYear received
## 1  Client A   01_2014        1
## 2  Client A   02_2014        1
## 3  Client A   03_2014        1
## 4  Client A   05_2014        1
## 5  Client B   02_2014        1
## 6  Client B   03_2014        1
## 7  Client B   04_2014        1
## 8  Client B   06_2014        1
## 9  Client B   07_2014        1
## 10 Client C   01_2014        1
## 11 Client C   02_2014        1
## 12 Client C   05_2014        1
## 13 Client C   07_2014        1

3. Cast to wide data frame

Creates a single row per client id with "1" indicating service received, otherwise "0".

castDF <- dcast(newDF, clientid ~ monthYear, value.var="received", fill="0") # Reshape df with dcast

##   clientid 01_2014 02_2014 03_2014 04_2014 05_2014 06_2014 07_2014
## 1 Client A       1       1       1       0       1       0       0
## 2 Client B       0       1       1       1       0       1       1
## 3 Client C       1       1       0       0       1       0       1

4. Build data frame to be filled

Include columns for spell count, spell lengths (a comma delimited string of spell lengths), max spell length, and min spell length.

vars <- c("spell count", "spell lengths (mos)", "max spell length", "min spell length")                                    # Build vector (column)
finalDF <- data.frame(vars)                                            # Add vector (column) to df

5. Get all runs (dosage spells) per id

Loops through wide data frame and uses the R Run Length Encoding (RLE) function to get runs of consecutive integers (in this case "1"). Would be faster using vectorization but looping is easier to understand and generally fast enough.

for (i in 1:NROW(castDF) ) {
  currentRow <- castDF[i,]                                           # Get row i from wide data frame
  currentId <- currentRow[[1]]                                       # Get client id from row i
  runs <- rle(currentRow)                                            # Use RLE to get run lengths
  lengths <- sort(as.character(runs$length[runs$values == 1]))       # Get and sort runs with value 1
  numspells <- length(lengths)                                       # Length of vector
  maxspell <- tail(lengths, n=1)                                     # Last item in vector
  minspell <- lengths[1]                                             # First item in vector
  finalDF[as.character(currentId)] <- NA                             # Create new df column
  newVector <- c(numspells, toString(lengths), maxspell, minspell)   # Create new values vector
  finalDF[[as.character(currentId)]] <- newVector                    # Add vector to finalDF
}

Final dosage spells data frame

##                  vars Client A Client B Client C
## 1         spell count        2        2        3
## 2 spell lengths (mos)     1, 3     2, 3  1, 1, 2
## 3    max spell length        3        3        2
## 4    min spell length        1        2        1

Workshop designed by Emily Molfe

Good Cheat Sheet: http://www.maths.lancs.ac.uk/~rowlings/Teaching/UseR2012/cheatsheet.html

Source for some exercises: https://rpubs.com/cengel248/59418

Load Libraries

library(maps)  # For creating geographical maps
library(mapdata)  # Contains basic data for ’maps’
library(maptools)  # Tools for handling spatial objects
library(mapproj)  # For creating projected maps
library(raster)  # Tools to deal with raster maps
library(ggplot2)  # To create maps
library(gpclib)  # general polygon clipper
library(RColorBrewer)  # Color packes
library(classInt)  # Choose univariate class intervals
library(acs)  # Package to handle ACS data
library(choroplethr)  # Simplify the creation of Choropleth Maps in R
library(rgdal)  # Bindings for the Geospatial Data Abstraction
library(sp)  # Classes and mehods for spatial data
library(hexbin)  # Hexagonal Binning Routines
library(ggmap)  # Google maps and OpenStreetMap
library(XML)  # Tools for parsing and generating XL
library(dplyr)  # Data Manipulation
library(rgeos)

Example 1: Arlington

Load data

Single Point in Arlington

# Example 1: Arlington
# -----------------------------------------------------------------------

# Load Data ---------------------------------------------------------------

# County shapfiles come from: http://gisdata.arlgis.opendata.arcgis.com/

# Arlington County Border loads shape file
Ar <- readShapePoly("Data/County Line/Arlington_Boundaries_and_Facilities.shp")

# projects the data make sure that all your objects are on the same
# projection
proj4string(Ar) <- CRS("+proj=longlat +datum=NAD83")

# Roads
Roads <- readShapeLines("Data/Roads/Roads.shp")

# VT Building (plotting 1 point)
VT <- as.data.frame(cbind(-77.116258, 38.88143))

# Change col names to pinpoint x and y
colnames(VT) <- c("x", "y")

# Make into coordinates
coordinates(VT) <- ~x + y

# Project the VT data
proj4string(VT) <- CRS("+proj=longlat +datum=NAD83")

Get census block group

# Census Blocks read the projection into the polygon in 1 step
geodat <- readShapePoly("Data/Block Group/tl_2014_51_bg.shp", proj4string = CRS("+proj=longlat +datum=NAD83"))

Plot Virginia

# Plot to check
plot(geodat)

# Get Block Groups for Arlington County Only
geodat <- geodat[which(geodat$COUNTYFP == "013"), ]

# Order block groups to help data merging
geodat <- geodat[order(geodat$GEOID), ]

Plot Arlington

# Plot to check
plot(geodat)

ACS Data

# Load ACS Data
myacs <- read.acs("Data/ACS SNAP/ACS_13_5YR_B22010_with_ann.csv", geocols = 3:1,
    endyear = 2013)

# See column names
myacs@acs.colnames

[1] "HD01_VD01.Estimate; Total:"
[2] "HD01_VD02.Estimate; Household received Food Stamps/SNAP in the past 12 months:"
[3] "HD01_VD03.Estimate; Household received Food Stamps/SNAP in the past 12 months: - Households with 1 or more persons with a disability"
[4] "HD01_VD04.Estimate; Household received Food Stamps/SNAP in the past 12 months: - Households with no persons with a disability"
[5] "HD01_VD05.Estimate; Household did not receive Food Stamps/SNAP in the past 12 months:"
[6] "HD01_VD06.Estimate; Household did not receive Food Stamps/SNAP in the past 12 months: - Households with 1 or more persons with a disability"
[7] "HD01_VD07.Estimate; Household did not receive Food Stamps/SNAP in the past 12 months: - Households with no persons with a disability"

# Pull out columns of interest
snapt <- myacs@estimate[, 1]  # Total
snap <- myacs@estimate[, 2]  #  Household received Food Stamps/SNAP in the past 12 months

# Extracts attribute data from shape file
mdat <- geodat@data

# Binds ACS data to attributes (previous ordering makes this easy)
mdat <- cbind(mdat, snap, snapt)

# Connect attributes back to shapefile
geodat@data <- mdat

plot(geodat)

Need to tell it to plot with specific variables and colors

# General Plot to map SNAP
# --------------------------------------------------------------------

# Decide on colors from brewer.pal
display.brewer.all(type = "seq")

# Set Colors
nclr <- 4  # number of breaks for colors and legend
plotclr <- brewer.pal(nclr, "Reds")  # palette from brewer

# Set breaks for different colors (... can use quantiles too with quantile()
# command
class <- classIntervals(geodat$snap, nclr, style = "fixed", fixedBreaks = c(0,
    20, 50, 75, 100, 300))

# Set colors
colcode <- findColours(class, plotclr)

# Check names of breaks to set names
names(attr(colcode, "table"))

[1] "[0,20)"    "[20,50)"   "[50,75)"   "[75,100)"  "[100,300]"

# Set names of breaks
names <- c("0 - 20", "20 - 50", "50-75", "75 - 100", "100 - 300")

# Plot
par(mai = c(1, 0, 1, 2.8))
plot(geodat, col = colcode)

# Add title
mtext("Households within Arlington Recieving SNAP", 3, line = 1, cex = 1.5,
    adj = -1.3)

# add = TRUE to add another layer to the same plot
plot(Roads, add = TRUE, col = "grey", lwd = 0.3)
plot(geodat, add = TRUE, lwd = 1)
plot(Ar, add = TRUE, lwd = 2.5)
plot(VT, add = TRUE, pch = 19, cex = 2, col = "blue")

legend(-77.03, 38.91635, xpd = TRUE, legend = names, fill = attr(colcode, "palette"),
    cex = 1.5, bty = "n", title = "Count of Households\nReceiving SNAP")
legend(-77.03, 38.86, c("Census Block Group", "Virginia Tech"), lty = c(1, -1),
    pch = c(-1, 19), col = c("black", "blue"), cex = 1.3, , seg.len = 1, y.intersp = 0.8,
    bty = "n", xpd = TRUE)

# spplot ------------------------------------------------------------------

spplot(geodat,"snap")

##this is ugly, let's chose our own colors

# Choose own colors
pal <- brewer.pal(7, "OrRd")  # select 7 colors from the palette OrRd
# Number of colors should be one higher than number of cuts
spplot(geodat, "snap", col.regions = pal, cuts = 6)

# ggplot and hexbins ------------------------------------------------------

# create a unique ID for the later join
geodat@data$id = rownames(geodat@data)

# turn SpatialPolygonsDataframe into a data.frame
geodat.pts <- fortify(geodat, region="id") # Only has the coordinates
geodat.df <- left_join(geodat.pts, geodat@data, by="id") # Add the attributes back in

# calculate quantile breaks
geodat.df$qt <- cut(geodat.df$snap,
                    breaks = c(0,20,50,75,100,300),include.lowest = TRUE)

# Make VT into a data.frame
VT<-as.data.frame(VT)

# plot
ggplot(geodat.df, aes(long,lat,group=group, fill=qt)) + # the data
  ggtitle("Households Recieving SNAP in Arlington") +
  geom_polygon() + # make polygons
  scale_fill_brewer("Homicide Rate", palette = "OrRd") + # fill with brewer colors
  theme(line = element_blank(),  # remove the background, tickmarks, etc
        axis.text=element_blank(),
        axis.title=element_blank(),
        panel.background = element_blank()) +
  geom_point(aes(x, y,fill = NULL,group = NULL), size = 3,data=VT,col="blue")+
  scale_alpha(guide = 'none')+
  coord_equal()

# spplot ------------------------------------------------------------------

spplot(geodat, "snap")

## this is ugly, let's chose our own colors

# Choose own colors
pal <- brewer.pal(7, "OrRd")  # select 7 colors from the palette OrRd
# Number of colors should be one higher than number of cuts
spplot(geodat, "snap", col.regions = pal, cuts = 6)

# ggplot and hexbins ------------------------------------------------------

# create a unique ID for the later join
geodat@data$id = rownames(geodat@data)

# turn SpatialPolygonsDataframe into a data.frame
geodat.pts <- fortify(geodat, region = "id")  # Only has the coordinates
geodat.df <- left_join(geodat.pts, geodat@data, by = "id")  # Add the attributes back in

# calculate quantile breaks
geodat.df$qt <- cut(geodat.df$snap, breaks = c(0, 20, 50, 75, 100, 300), include.lowest = TRUE)

# Make VT into a data.frame
VT <- as.data.frame(VT)

# plot
ggplot(geodat.df, aes(long, lat, group = group, fill = qt)) +
  ggtitle("Households Recieving SNAP in Arlington") +
  geom_polygon() + # make polygons
  scale_fill_brewer("Homicide Rate", palette = "OrRd") + # fill with brewer colors
  theme(line = element_blank(),  # remove the background, tickmarks, etc
        axis.text = element_blank(),
        axis.title = element_blank(),
        panel.background = element_blank()) +
  geom_point(aes(x, y, fill = NULL, group = NULL),
             size = 3, data = VT, col = "blue") +
  scale_alpha(guide = 'none') +
  coord_equal()

Example 2: Philly

# Example 2: Philly ------------------------

# Load Data ---------------------------------------------------------------

# Philly Shapefile

philly <- readShapePoly("Data/Philly2/Philly2.shp")

# N_HOMIC: Number of homicides (since 2006)
# HOMIC_R: homicide rate per 100,000 (Philadelphia Open Data)
# PCT_COL: % 25 years and older with college or higher degree1 (ACS 2006-2010)
# mdHHnc: estimated median household income (ACS 2006-2010)

# spplot ------------------------------------------------------------------

spplot(philly) # sp plot (general)

spplot(philly, c("HOMIC_R", "PCT_COL")) # Just those

# Using color.brewer
pal <- brewer.pal(7, "OrRd")  # we select 7 colors from the palette
spplot(philly, "HOMIC_R", col.regions = pal, cuts = 6)

# ggplot hexbins ------------------------------------------------------

homicides<-read.csv("PhillyHomicides.csv")
head(homicides) # X and Y at end are coordinates

  DC_DIST SECTOR  DISPATCH_DATE_TIME DISPATCH_DATE DISPATCH_TIME HOUR
1      22      1 2014-09-14 16:00:00    2014-09-14      16:00:00   NA
2       1      B 2006-01-14 00:00:00    2006-01-14      00:00:00   NA
3       1      B 2006-04-01 16:05:00    2006-04-01      16:05:00   NA
4       1      B 2006-05-10 11:13:00    2006-05-10      11:13:00   NA
5       1      E 2006-07-01 12:42:00    2006-07-01      12:42:00   NA
6       1      F 2006-07-09 19:13:00    2006-07-09      19:13:00   NA
        DC_KEY          LOCATION_BLOCK UCR_GENERAL OBJECTID
1 199822061421 1800 BLOCK W MONTGOMERY         100 44774978
2 200601001669   2000 BLOCK MIFFLIN ST         100 44746630
3 200601011408   S 22ND ST /SNYDER AVE         100 44746625
4 200601016399   2100 BLOCK MC KEAN ST         100 44771721
5 200601023411   2100 BLOCK S HICKS ST         100 44746632
6 200601024451   1800 BLOCK SNYDER AVE         100 44843467
     TEXT_GENERAL_CODE   POINT_X  POINT_Y    SHAPE
1  Homicide - Criminal -75.15680 39.98804 44714107
2  Homicide - Criminal -75.17873 39.92801 44685759
3  Homicide - Criminal -75.18275 39.92607 44685754
4  Homicide - Criminal -75.18092 39.92704 44710850
5  Homicide - Criminal -75.17204 39.92463 44685761
6 Homicide - Criminal  -75.17612 39.92517 44782596

ggplot(homicides, aes(POINT_X, POINT_Y)) +
    stat_binhex() +
    scale_fill_gradientn(colours = c("white", "red"), name = "Frequency")

# ggplot ------------------------------------------------------

# create a unique ID for the later join
philly@data$id = rownames(philly@data)

# turn SpatialPolygonsDataframe into a data frame
philly.pts <- fortify(philly, region="id") #this only has the coordinates
philly.df <- left_join(philly.pts, philly@data, by="id") # add the attributes back in

# calculate quantile breaks
philly.df$qt <- cut(philly.df$HOMIC_R,
                    breaks = quantile(philly.df$HOMIC_R, probs = 0:7/7, na.rm = TRUE),
                    include.lowest = TRUE)

# plot
ggplot(philly.df, aes(long,lat,group=group, fill=qt)) + # the data
  ggtitle("Homicide Rate in Philidelphia by Census Tract") +
  geom_polygon() + # make polygons
  scale_fill_brewer("Homicide Rate", palette = "OrRd") + # fill with brewer colors
  theme(line = element_blank(),  # remove the background, tickmarks, etc
        axis.text=element_blank(),
        axis.title=element_blank(),
        panel.background = element_blank()) +
  coord_equal()

# Event Data with Coordinates ---------------------------------------------

# Basemap
phBasemap <- get_map(location="Philadelphia, PA", zoom=12, maptype = 'satellite')
ggmap(phBasemap)

# Try out these different backgrounds (see library information)

#phBasemap <- get_map(location="Philadelphia, PA", zoom=12, maptype = 'terrain')
#ggmap(phBasemap)

#phBasemap <- get_map(location="Philadelphia, PA", zoom=12, maptype = 'toner')
#ggmap(phBasemap)

#phBasemap <- get_map(location="Philadelphia, PA", zoom=12, maptype = 'watercolor')
#ggmap(phBasemap)


# plot with heatmap
ggmap(phBasemap) +
  # make the heatmap
  stat_density2d(aes(x = POINT_X,
                     y = POINT_Y,
                     fill = ..level.., # value corresponding to discretized density estimates
                     alpha = ..level..),
                 bins = 25,  # number of bands
                 data = homicides,
                 geom = "polygon") +  # creates the bands of differenc dolors
  ## Configure the colors, transparency and panel
  scale_fill_gradient(low = "yellow", high = "red") +
  scale_alpha(range = c(.25, .55)) +
  theme(legend.position="none")

Example 3: Web Scrapping

# Example 3: Web Scrapping ------------------------------------------------

# read in the data
url <- "http://en.wikipedia.org/wiki/List_of_United_States_cities_by_crime_rate"
# we want the first table: which=1
citiesCR <- readHTMLTable(url, which = 1, stringsAsFactors = FALSE)

# clean up (with mutate_each function from dplyr): remove the comma in 1,000
# and above and convert numbers from strings to numeric
citiesCRclean <- mutate_each(citiesCR, funs(as.numeric(gsub(",", "", .))), -(State:City))

# geocode loations
latlon <- geocode(paste(citiesCRclean$City, citiesCRclean$State, sep = ", "))

# combine into a new dataframe
citiesCRll <- data.frame(citiesCRclean, latlon)

# get basmap
map_us <- get_map(location = "United States", zoom = 4, color = "bw")

# plot
ggmap(map_us, legend = "bottomright", extent = "device") + geom_point(data = citiesCRll,
    aes(x = lon, y = lat, color = Violent.Crime, size = Population)) + scale_colour_gradient(low = "white",
    high = "red") + scale_size_continuous(range = c(4, 12))

author: "Aaron D. Schroeder" description: Takes yearly student record data in long-format, transforms and casts the data to wide-format, then finds number of times each grade was attended (times attended > 1 = retention).

1. Add Libraries and Import Data

Sample data includes client id, grade year (e.g. KG, 1, 2), and entry date (first day of school)

library(reshape2)
retentionData <- read.csv("GradeRetentionData.csv")

##    clientid gradeyear entrydate
## 1   ClientA         1  8/1/2010
## 2   ClientA         1  8/1/2011
## 3   ClientA         2  8/1/2012
## 4   ClientC         4  8/1/2008
## 5   ClientC         5  8/1/2009
## 6   ClientC         5  8/1/2010
## 7   ClientC         6  8/1/2011
## 8   ClientB        PK  8/1/2003
## 9   ClientB        KG  8/1/2004
## 10  ClientB        KG  8/1/2004
## 11  ClientB         1  8/1/2005

2. Transform column data where necessary and build new data frame

Reduce date to just year

yearattend <- format(as.Date(retentionData$entrydate, "%m/%d/%Y"), "%Y")

Custom function gradeNum is added to convert text grade number (e.g. "KG") to numeric (e.g. "0") to help with sorting. The function is applied using mapply, a vectorized approach (as opposed to looping).

gradeNum <- function(x){ switch(x, "KG" = 0, "K"  = 0, "PK" = -1, "P"  = -1, x) }
gradeyear <- mapply(gradeNum, x = as.character(retentionData$gradeyear))

Construct new data frame (here we use same name to replace old data frame).

retentionData <- data.frame(clientid = retentionData$clientid, gradeyear, yearattend)

##    clientid gradeyear yearattend
## 1   ClientA         1       2010
## 2   ClientA         1       2011
## 3   ClientA         2       2012
## 4   ClientC         4       2008
## 5   ClientC         5       2009
## 6   ClientC         5       2010
## 7   ClientC         6       2011
## 8   ClientB        -1       2003
## 9   ClientB         0       2004
## 10  ClientB         0       2004
## 11  ClientB         1       2005

3. Eliminate duplicate records

Two records with identical clientid, gradeyear, and yearattend are duplicates, not a grade retention. Notice record 10 was eliminated as a duplicate.

retentionData <- unique(retentionData[,c("clientid","gradeyear","yearattend")])

##    clientid gradeyear yearattend
## 1   ClientA         1       2010
## 2   ClientA         1       2011
## 3   ClientA         2       2012
## 4   ClientC         4       2008
## 5   ClientC         5       2009
## 6   ClientC         5       2010
## 7   ClientC         6       2011
## 8   ClientB        -1       2003
## 9   ClientB         0       2004
## 11  ClientB         1       2005

4. Cast to wide data frame

Create a single row for each clientid + gradeyear combination. Order by clientid and gradeyear.

castDF <- dcast(retentionData, clientid + gradeyear ~ yearattend, value.var="yearattend", fun.aggregate=length)
castDF <- castDF[order(castDF$clientid, castDF$gradeyear),]

##   clientid gradeyear 2003 2004 2005 2008 2009 2010 2011 2012
## 1  ClientA         1    0    0    0    0    0    1    1    0
## 2  ClientA         2    0    0    0    0    0    0    0    1
## 3  ClientB        -1    1    0    0    0    0    0    0    0
## 4  ClientB         0    0    1    0    0    0    0    0    0
## 5  ClientB         1    0    0    1    0    0    0    0    0
## 6  ClientC         4    0    0    0    1    0    0    0    0
## 7  ClientC         5    0    0    0    0    1    1    0    0
## 8  ClientC         6    0    0    0    0    0    0    1    0

5. Create summary column

Create row sums column and add to new wide data frame

rsums <- rowSums(castDF[c(3:ncol(castDF))])
castDF$times_attended <- rsums

##   clientid gradeyear 2003 2004 2005 2008 2009 2010 2011 2012 times_attended
## 1  ClientA         1    0    0    0    0    0    1    1    0              2
## 2  ClientA         2    0    0    0    0    0    0    0    1              1
## 3  ClientB        -1    1    0    0    0    0    0    0    0              1
## 4  ClientB         0    0    1    0    0    0    0    0    0              1
## 5  ClientB         1    0    0    1    0    0    0    0    0              1
## 6  ClientC         4    0    0    0    1    0    0    0    0              1
## 7  ClientC         5    0    0    0    0    1    1    0    0              2
## 8  ClientC         6    0    0    0    0    0    0    1    0              1

for

for(number in 1:3){
  print(number * number)
}

## [1] 1
## [1] 4
## [1] 9

For consistency, I've changed the above code to use similar variables as the rest of the example

for(i in 1:3){
  print(i * i)
}

## [1] 1
## [1] 4
## [1] 9

lapply

fun_lappy <- function(x){
  # function that takes in a value, and returns
  # the value, the value squared, and the value cubed
  c(x, x^2, x^3)
}
fun_lappy(3) # returns: 3, 3^2 = 9, 3^3 = 27

## [1]  3  9 27

1:3

## [1] 1 2 3

lapply(X = 1:3, FUN = fun_lappy)

## [[1]]
## [1] 1 1 1
##
## [[2]]
## [1] 2 4 8
##
## [[3]]
## [1]  3  9 27

foreach do

library(foreach)

foreach(i = 1:3) %do% {
  i * i
}

## [[1]]
## [1] 1
##
## [[2]]
## [1] 4
##
## [[3]]
## [1] 9

# capture the output of foreach into a variable
foreach_list <- foreach(i = 1:3) %do% {
  i * i
}

# getting things out of a list needs double square brackets
foreach_list[[1]]

## [1] 1

foreach_list[[2]]

## [1] 4

foreach_list[[3]]

## [1] 9

Appending things to a vector in a regular for loop

Sometimes you want to loop through something and add the output to a vector or list. You can do this using a regular for loop, but this is usually when people will advise you not to use loops in R. This is especially the case with cbind and rbind when you have to append values to a dataframe using a loop.

vector <- c()
for(i in 1:3){
  sq <- i * i
  print(sq)
  vector <- c(vector, sq)
}

## [1] 1
## [1] 4
## [1] 9

vector

## [1] 1 4 9

foreach dopar

library(doParallel)

## Loading required package: iterators
## Loading required package: parallel

foreach_list <- foreach(i = 1:3) %dopar% {
  i * i
}

## Warning: executing %dopar% sequentially: no parallel backend registered

# create clusters
cl <- makeCluster(4)
registerDoParallel(cl)

# same as above
foreach_list <- foreach(i = 1:3) %dopar% {
  i * i
}
foreach_list

## [[1]]
## [1] 1
##
## [[2]]
## [1] 4
##
## [[3]]
## [1] 9

# close clusters
stopCluster(cl)
registerDoSEQ()

parallel apply family

1:3

## [1] 1 2 3

lapply(X = 1:3, FUN = fun_lappy)

## [[1]]
## [1] 1 1 1
##
## [[2]]
## [1] 2 4 8
##
## [[3]]
## [1]  3  9 27

cl <- makeCluster(4)
registerDoParallel(cl)

parSapply(cl = cl, X = 1:3, FUN = fun_lappy)

##      [,1] [,2] [,3]
## [1,]    1    2    3
## [2,]    1    4    9
## [3,]    1    8   27

parLapply(cl = cl, X = 1:3, fun = fun_lappy)

## [[1]]
## [1] 1 1 1
##
## [[2]]
## [1] 2 4 8
##
## [[3]]
## [1]  3  9 27

stopCluster(cl)
registerDoSEQ()

Benchmark with small values

library(microbenchmark)
vector <- c(1:1000)
empty <- c()

cl <- makeCluster(4)
registerDoParallel(cl)

# separate each thing you want to time, with a comma
microbenchmark(
  # apply
  sapply(X = vector, FUN = function(x){x * x}),
  lapply(X = vector, FUN = function(x){x * x}),

  # regular for loop
  for(i in vector){
    empty <- c(empty, i * i)
  },
  # foreach loop
  foreach_list <- foreach(i = vector) %do% {
    i * i
  },

  # foreach loop with parallel backend
  foreach_list <- foreach(i = vector) %dopar% {
    i * i
  },

  # parallel sapply
  parSapply(cl, vector, function(x){x * x}),

  # parallel lapply
  parLapply(cl = cl, X = vector, fun = function(x){x * x}),

  # onyl test each loop 10 times, instead of default value
  times = 10)

## Unit: microseconds
##                                                             expr
##              sapply(X = vector, FUN = function(x) {     x * x })
##              lapply(X = vector, FUN = function(x) {     x * x })
##               for (i in vector) {     empty <- c(empty, i * i) }
##           foreach_list <- foreach(i = vector) %do% {     i * i }
##        foreach_list <- foreach(i = vector) %dopar% {     i * i }
##                 parSapply(cl, vector, function(x) {     x * x })
##  parLapply(cl = cl, X = vector, fun = function(x) {     x * x })
##         min         lq        mean      median         uq        max neval
##     742.861    868.628    956.4620    903.1240   1097.101   1303.277    10
##     495.295    595.710    765.4985    659.1065    930.858   1193.816    10
##    1562.566   8733.582  16339.6369  17027.2665  22505.735  30457.564    10
##  239166.682 245262.464 247710.1623 247072.6320 248961.145 261592.290    10
##  346741.386 364317.662 379288.0742 369792.7685 380320.741 452426.713    10
##   40031.085  40296.467  40880.2206  40837.6970  41370.241  42223.376    10
##   38770.815  39704.909  40254.9182  40162.3770  40530.919  42081.794    10

stopCluster(cl)
registerDoSEQ()

Benchmark with larger values

print_difftime_prompt <- function(str_what_did_you_time, diff_time, sep=':'){
    parse_time <- unclass(diff_time)[1]
    parse_units <- attr(unclass(diff_time), 'units')
    prompt_string <- sprintf('%s took: %s %s', str_what_did_you_time, parse_time, parse_units)
    cat(prompt_string, '\n')
    # return(prompt_string)
}

vector <- c(1:100000)

cl <- makeCluster(4)
registerDoParallel(cl)

# sapply
strt <- Sys.time()
output <- sapply(X = vector, FUN = function(x){x})
print_difftime_prompt('sapply', Sys.time() - strt)

## sapply took: 0.155578851699829 secs

rm(output)
gc()

##          used (Mb) gc trigger (Mb) max used (Mb)
## Ncells 506772 27.1     899071 48.1   874656 46.8
## Vcells 821585  6.3    1467557 11.2  1451443 11.1

# lapply
strt <- Sys.time()
output <- lapply(X = vector, FUN = function(x){x})
print_difftime_prompt('lapply', Sys.time() - strt)

## lapply took: 0.0575652122497559 secs

rm(output)
gc()

##          used (Mb) gc trigger (Mb) max used (Mb)
## Ncells 506821 27.1     899071 48.1   899071 48.1
## Vcells 821649  6.3    1467557 11.2  1451443 11.1

# regular for loop
fill <- c()
strt <- Sys.time()
for(i in vector){
  fill <- c(fill, i)
}
print_difftime_prompt('regular for loop', Sys.time() - strt)

## regular for loop took: 17.0807673931122 secs

# foreach loop
strt <- Sys.time()
foreach_list <- foreach(i = vector) %do% {
  i
}
print_difftime_prompt('foreach do', Sys.time() - strt)

## foreach do took: 44.5841858386993 secs

rm(foreach_list)
gc()

##           used (Mb) gc trigger (Mb) max used (Mb)
## Ncells  802903 42.9    1476915 78.9  1476915 78.9
## Vcells 1067737  8.2    2128632 16.3  2128595 16.3

# foreach loop with parallel backend
strt <- Sys.time()
foreach_list <- foreach(i = vector) %dopar% {
  i
}
print_difftime_prompt('foreach dopar', Sys.time() - strt)

## foreach dopar took: 1.18145899772644 mins

rm(foreach_list)
gc()

##           used (Mb) gc trigger (Mb) max used (Mb)
## Ncells  802952 42.9    1835812 98.1  1835812 98.1
## Vcells 1067798  8.2    2932173 22.4  2932173 22.4

# parallel sapply
strt <- Sys.time()
output <- parSapply(cl, vector, function(x){x})
print_difftime_prompt('parSapply', Sys.time() - strt)

## parSapply took: 0.325727939605713 secs

rm(output)
gc()

##           used (Mb) gc trigger (Mb) max used (Mb)
## Ncells  803004 42.9    1835812 98.1  1835812 98.1
## Vcells 1067867  8.2    2932173 22.4  2932173 22.4

# parallel lapply
strt <- Sys.time()
output <- parLapply(cl = cl, X = vector, fun = function(x){x})
print_difftime_prompt('parLapply', Sys.time() - strt)

## parLapply took: 0.216223001480103 secs

rm(output)
gc()

##           used (Mb) gc trigger (Mb) max used (Mb)
## Ncells  803050 42.9    1835812 98.1  1835812 98.1
## Vcells 1067926  8.2    2932173 22.4  2932173 22.4

stopCluster(cl)
registerDoSEQ()

list to df

library(plyr)
foreach_list <- foreach(i = 1:3) %do% {
  i * i
}
foreach_list

## [[1]]
## [1] 1
##
## [[2]]
## [1] 4
##
## [[3]]
## [1] 9

ldply(foreach_list, .fun = data.frame)

##   X..1L.. X..2L.. X..3L..
## 1       1      NA      NA
## 2      NA       4      NA
## 3      NA      NA       9

as.data.frame(foreach_list)

##   X1L X4L X9L
## 1   1   4   9

Splitting up 100,000,000 records to be processed 100,000 at a time

myDataTable <- <100,000,000 records>

chunk <- 100000
iterator <- idiv(nrow(myDataTable), chunkSize=chunk)

startRow <- 1
endRow <- chunk

try(
  for (i in 1:10000) {
    myChunkOfData <- myDataTable[startRow:endRow]

    startRow <- endRow + 1
    endRow <- endRow + nextElem(iterator)
  }
  , silent = TRUE)

#Import packages
library(foreach)
library(doParallel)
library(data.table)
library(dplyr)
library(Matrix)
library(igraph)

setwd("~/R/ForEach/HealthIT")
data <- fread("ili_data.csv", sep=",")
data_pt_hosp <- data %>%
  select(studyid,DMISID)

#Setup parallel backend to use 20 processors
cl<-makeCluster(8)
registerDoParallel(cl)

chunk <- 100000
it <- idiv(nrow(data_pt_hosp), chunkSize=chunk)
startRow <- 1
endRow <- chunk
edgeTableFinal <- data.table(a=character(),b=character())
try(
for (i in 1:10000) {
  #start time
  strt<-Sys.time()

  data_pt_hosp_cast <- dcast.data.table(data_pt_hosp[startRow:endRow],studyid~DMISID,fun=length)

  #head(data_pt_hosp_cast)
  #set rownames:
  setkey(data_pt_hosp_cast,studyid)
  #data table without the study id column:
  data_pt_hosp_cast[,studyid:=NULL]

  hospitals <- colnames(data_pt_hosp_cast)

  hospital_adj <- Matrix(0, length(hospitals), length(hospitals), sparse=TRUE)
  row.names(hospital_adj) <- hospitals
  colnames(hospital_adj) <- hospitals

  data_pt_hosp_cast$non_zero <- apply(data_pt_hosp_cast,1,nnzero)
  data_pt_hosp_cast <- data_pt_hosp_cast[data_pt_hosp_cast$non_zero > 1,]
  data_pt_hosp_cast[,non_zero:=NULL]

  num_rows <- nrow(data_pt_hosp_cast)

# Parallel loop
  ls<-foreach(i=1:num_rows) %dopar% {

    row_matrix <- as.matrix(data_pt_hosp_cast[i, ])
    rows_of_hospitals <- row_matrix[,row_matrix != 0]
    hospital_ids <- names(rows_of_hospitals)

    if(length(hospital_ids) > 1){
      to.ls <- combn(hospital_ids, 2, simplify = FALSE)
    }
  }

  list_col1 <- list()
  for (i in seq(ls)) {
    for (j in seq(ls[[i]])) {
      list_col1 <- c(list_col1, ls[[i]][[j]][[1]])
    }
  }

  list_col2 <- list()
  for (i in seq(ls)) {
    for (j in seq(ls[[i]])) {
      list_col2 <- c(list_col2, ls[[i]][[j]][[2]])
    }
  }

  edgeTable <- data.table(a=list_col1,b=list_col2)
  edgeTableFinal <- rbind(edgeTableFinal, edgeTable)

  print(Sys.time()-strt)

  startRow <- endRow + 1
  endRow <- endRow + nextElem(it)
}
, silent = TRUE)
stopCluster(cl)
registerDoSEQ()
# print(edgeTableFinal)
# write.table(as.matrix(edgeTableFinal), "/home/aschroed/R/ForEach/HealthIT/edgeTable.csv", sep=",")

Keeping/dropping columns by name

Taken from here and here

# Creating the data frame
fname <- c('Bob', 'Sally', 'John', 'Jane')
lname <- c('Dole', 'Doe', 'Doe', 'Smith')
age <- c(32, 24, 28, 25)
df <- data.frame(fname, lname, age)
df

##   fname lname age
## 1   Bob  Dole  32
## 2 Sally   Doe  24
## 3  John   Doe  28
## 4  Jane Smith  25

# keeping columns by name
keep <- c('fname', 'age')
df[, names(df) %in% keep]

##   fname age
## 1   Bob  32
## 2 Sally  24
## 3  John  28
## 4  Jane  25

# dropping columns by name
drop <- c('lname')
df[, !(names(df) %in% drop)]

##   fname age
## 1   Bob  32
## 2 Sally  24
## 3  John  28
## 4  Jane  25

Work on a subset of data frame using regular expression

Sometimes you want to do some kind of calculation or munge on a aubset of a data frame. For example, survey data might be coded q1a - q1k and you want to average the values, sum the values, look at the values, etc...

# Create a data frame
q13 <- c(1:5)
q14a <- c(2:6)
q14b <- c(3:7)
q14c <- c(4:8)
q14z <- c(5:9)
df <- data.frame(q13, q14a, q14b, q14c, q14z)
df

##   q13 q14a q14b q14c q14z
## 1   1    2    3    4    5
## 2   2    3    4    5    6
## 3   3    4    5    6    7
## 4   4    5    6    7    8
## 5   5    6    7    8    9

# regular expresstion for only sum q14a - q14c, do not include q14z
q14pattern <- '^q14[a-cA-C]'

# search the column names for a pattern match, return vector of column indices
q14columns <- grep(pattern = q14pattern, x = names(df))

# do something with the subset data, here I am summing the rows
df$q14.sum <- rowSums(x = df[, q14columns])
df

##   q13 q14a q14b q14c q14z q14.sum
## 1   1    2    3    4    5       9
## 2   2    3    4    5    6      12
## 3   3    4    5    6    7      15
## 4   4    5    6    7    8      18
## 5   5    6    7    8    9      21

We can also do a similar process to only look at the q14 variables

q14patternAll <- '^q14'
q14columnsAll <- grep(pattern = q14patternAll, x = names(df))
head(df[, q14columnsAll], n = 2)

##   q14a q14b q14c q14z q14.sum
## 1    2    3    4    5       9
## 2    3    4    5    6      12

Single-line timing

A common way to time a line of R code is to save Sys.time() to a variable before the code you want to evaluate, and then find the difference of Sys.time() after. This will have R output text something along the lines of Time difference of 5.335534 secs

However, if you are running a script that has multiple of these timings, the printed results are not useful. A hack would be to do a print() statement before the time difference calculation, but that just looks ugly, especially in knitr documents.

Below is a function that will take a string of what you are timing, and the difftime calculation, and cats back a nice prompt for you.

print_difftime_prompt <- function(str_what_did_you_time, diff_time, sep=':'){
    parse_time <- unclass(diff_time)[1]
    parse_units <- attr(unclass(diff_time), 'units')
    prompt_string <- sprintf('%s took: %s %s', str_what_did_you_time, parse_time, parse_units)
    cat(prompt_string, '\n')
    # return(prompt_string)
}

For example: strt <- Sys.time() print_difftime_prompt('add simulation dataframes to list', Sys.time() - strt) will return: add simulation dataframes to list took: 36.1213629245758 secs

Microbenchmark

Additionally, do get an accurate time, use the microbenchmark() function in the microbenchmark package. See Hadley's post about it's use.

A procedure for adjusting a table of data cells such that they add up to selected totals for both the columns and rows of the table
Aaron D. Schroeder

Starting table and target margins

seedTable = matrix(c(6,6,3,8,10,10,9,10,9,3,14,8),ncol=3,byrow=TRUE)
goalRowMargs = c(20,30,35,15)
goalColMargs = c(35,40,25)

Support Functions

Equal Margins

Function to check for equality of margins

equalMarginals = function(rowMs, colMs){sum(rowMs) == sum(colMs)}

No Zeros

Function to fill cells with vlaue 0 with 0.001

noZeros = function(aMatrix){replace(aMatrix, aMatrix == 0, .0001)}

Cell Functions

Functions to calculate cell values during an iteration

rowCellAdj = function(prevCellVal, prevRowSum, goalRowSum){prevCellVal/prevRowSum*goalRowSum}
colCellAdj = function(prevCellVal, prevColSum, goalColSum){prevCellVal/prevColSum*goalColSum}

Function for a single iteration of IPF

Returns a list of two tables, a row adjusted table and a column adjusted table

iterTable = function(inTable, goalRowMargs, goalColMargs){
    # adjust rows
    rowAdjTable = matrix(nrow = nrow(inTable), ncol = ncol(inTable))
    for (r in 1:nrow(inTable)){
        prevRowSum = sum(inTable[r,])
        goalRowSum = goalRowMargs[r]
        for (c in 1:ncol(inTable)){
            prevCellVal = inTable[r,c]
            newCellVal = rowCellAdj(prevCellVal, prevRowSum, goalRowSum)
            rowAdjTable[r,c] = newCellVal
        }
    }
    # adjust columns
    colAdjTable = matrix(nrow = nrow(rowAdjTable), ncol = ncol(rowAdjTable))
    for (c in 1:ncol(rowAdjTable)){
        prevColSum = sum(rowAdjTable[,c])
        goalColSum = goalColMargs[c]
        for (r in 1:nrow(rowAdjTable)){
            prevCellVal = rowAdjTable[r,c]
            newCellVal = rowCellAdj(prevCellVal, prevColSum, goalColSum)
            colAdjTable[r,c] = newCellVal
        }
    }
    resultTables = list()
    resultTables[[1]] = rowAdjTable
    resultTables[[2]] = colAdjTable
    resultTables
}

IPF function

Sets intial values and iteration parameters - returns a proportionally-fitted table

ipf = function(seedTable, goalRowMargs, goalColMargs, accuracy = .0001, maxiter = 50){
    if (equalMarginals(goalRowMargs, goalColMargs)){
        seedTable = noZeros(seedTable)
        iter = 0
        checkDif = 1
        i = iterTable(seedTable, goalRowMargs, goalColMargs)
        while((checkDif > accuracy) && (iter < maxiter)){
            i = iterTable(i[[2]], goalRowMargs, goalColMargs)
            rowMaxDif = max(abs(rowSums(i[[2]]) - goalRowMargs))
            colMaxDif = max(abs(colSums(i[[1]]) - goalColMargs))
            checkDif = max(rowMaxDif, colMaxDif)
            iter = iter + 1
        }
        round(addmargins(i[[2]]), 2)
    }
}

ipf(seedTable, goalRowMargs, goalColMargs)

9.14	7.75	3.11	20.00
10.30	10.92	8.77	30.00
13.34	12.57	9.09	35.00
2.21	8.76	4.02	15.00
35	40	25	100

Quality Profile of Data Field 'exit_date'

Aaron D. Schroeder 2/3/2016

# set working directory
setwd("~/sdal/projects/resinfra/")

# connect to database
source(file="analysis/aerogers/pg_connect.R")

# load libraries
library(descr)
library(plyr)

COMPLETENESS

get records with missing values for exit_date

missing_exit_date = dbGetQuery(con, "SELECT count(*)
                                      FROM student_mobility_fields_2005_2015
                                      WHERE exit_date IS NULL")
print(missing_exit_date)

##      count
## 1 38947070

VALUE VALIDITY

get records with invalid values for exit_date

invalid_exit_date = dbGetQuery(con, "SELECT unique_id, exit_date, division_number__serving_school_number
                                      FROM student_mobility_fields_2005_2015
                                      WHERE exit_date IS NOT NULL
                                      AND EXTRACT('YEAR' FROM to_date(exit_date, 'YYYY-MM-DD')) NOT IN (SELECT CAST(value as integer) FROM valid_values WHERE fieldname = 'school_year')")

get number of rows with invlaid values for exit_date

print(nrow(invalid_exit_date))

## [1] 0

generate two-way table and plot of invalid_exit_date vs school_division

crosstab(substr(invalid_exit_date$division_number__serving_school_number, 1, 3), invalid_exit_date$exit_date,
         prop.t = FALSE,
         prop.r = TRUE,
         prop.c = TRUE,
         prop.chisq = FALSE,
         format=c("SPSS"),
         dnn = c("School Division", "Invalid Exit Date"))

UNIQUENESS

get values for exit_date

values_exit_date = dbGetQuery(con, "SELECT to_date(exit_date, 'YYYY-MM-DD') exit_date
                                   FROM student_mobility_fields_2005_2015
                                   WHERE exit_date IS NOT NULL")

frequency distribution table of exit_date values

exit_date_frequencies = table(values_exit_date$exit_date)
head(as.data.frame(exit_date_frequencies))

##         Var1 Freq
## 1 2005-01-01    4
## 2 2005-01-02  746
## 3 2005-01-03 6520
## 4 2005-01-04 3353
## 5 2005-01-05 1618
## 6 2005-01-06 1599

frequency distribution plot of exit_date values

barplot(exit_date_frequencies, main="Exit Date Value Distribution", horiz=TRUE)

RECORD CONSISTENCY

get records with inconsistent relationship between exit_date and active_status

inconsistent_exit_date_record = dbGetQuery(con, "SELECT unique_id, school_year
                                                 FROM student_mobility_fields_2005_2015
                                                 WHERE active_status IN ('I', 'N')
                                                 AND exit_date IS NULL")

frequency distribution table of inconsistent exit_date values

inconsistent_exit_date_freq_record = table(inconsistent_exit_date_record$school_year)
as.data.frame(inconsistent_exit_date_freq_record)

##    Var1  Freq
## 1  2005 10021
## 2  2006 14607
## 3  2007 47480
## 4  2008 49303
## 5  2009 88819
## 6  2010 92280
## 7  2011 83438
## 8  2012 21380
## 9  2013  2702
## 10 2014  2903
## 11 2015   233

frequency distribution plot of inconsistent exit_date values

barplot(inconsistent_exit_date_freq_record, main="Exit Date Inconsistent - Empty", horiz=TRUE)

count and list of unique_ids with inconsistent exit_date values

inconsistent_exit_date_unqid = unique(inconsistent_exit_date_record$unique_id)
length(inconsistent_exit_date_unqid)

## [1] 236609

head(inconsistent_exit_date_unqid)

## [1] "3D3UAQQ" "NSDNQQQ" "Z3SRQQQ" "2DDUAQQ" "WVWUAQQ" "45T6QQQ"

LONGITUDINAL CONSISTENCY

get distinct unique_ids for inconsistent exit_date

inconsistent_exit_date_long = dbGetQuery(con, "SELECT distinct a.unique_id
                                                FROM student_mobility_fields_2005_2015 a
                                                JOIN student_mobility_fields_2005_2015 b
                                                ON a.unique_id = b.unique_id
                                                WHERE a.exit_date <> b.exit_date")

nrow(inconsistent_exit_date_long)

## [1] 376321

convert dataframe column to a single-quoted, comma-delimited string for use in next SQL query

unique_id_list = paste0("'", paste0(inconsistent_exit_date_long$unique_id, collapse = "','"), "'")

build query for getting record details

sql = paste0("SELECT unique_id, exit_date, extract(year from to_date(exit_date, 'YYYY-MM-DD')) exit_year, school_year, division_number__serving_school_number FROM student_mobility_fields_2005_2015 WHERE unique_id IN (", unique_id_list, ")")

run query

inconsistent_exit_date_details = dbGetQuery(con, sql)

frequency distribution of inconsistent exit_dates

head(as.data.frame(table(inconsistent_exit_date_details$exit_date)))

##                  Var1 Freq
## 1 2005-01-01 00:00:00    4
## 2 2005-01-02 00:00:00  370
## 3 2005-01-03 00:00:00 3085
## 4 2005-01-04 00:00:00 1811
## 5 2005-01-05 00:00:00  847
## 6 2005-01-06 00:00:00  805

generate two-way table and plot of inconsistent exit_date vs school_year

crosstab(inconsistent_exit_date_details$school_year,
         inconsistent_exit_date_details$exit_date,
         prop.t = FALSE,
         prop.r = TRUE,
         prop.c = TRUE,
         prop.chisq = FALSE,
         format=c("SPSS"),
         dnn = c("School Year", "Exit"))

generate two-way table and plot of exit_date vs school_division

crosstab(substr(inconsistent_exit_date_details$division_number__serving_school_number, 1, 3), inconsistent_exit_date_details$exit_year,
         prop.t = FALSE,
         prop.r = TRUE,
         prop.c = TRUE,
         prop.chisq = FALSE,
         format=c("SPSS"),
         dnn = c("School Division", "Exit Date"))

##    Cell Contents
## |-------------------------|
## |                   Count |
## |             Row Percent |
## |          Column Percent |
## |-------------------------|
##
## ================================================================================================================================
##            Exit Date
## Schl Dv       2005      2006      2007      2008      2009      2010      2011      2012      2013      2014      2015     Total
## --------------------------------------------------------------------------------------------------------------------------------
## 001           678       711       380      1015      1046       714       632       726       917       789       287      7895
##               8.6%      9.0%      4.8%     12.9%     13.2%      9.0%      8.0%      9.2%     11.6%     10.0%      3.6%      0.4%
##               0.5%      0.5%      0.3%      0.5%      0.6%      0.4%      0.3%      0.3%      0.4%      0.4%      0.3%
## --------------------------------------------------------------------------------------------------------------------------------
## 002           833       867      1269      1760      1705      2478      1500      1666      1797      1436       647     15958
##               5.2%      5.4%      8.0%     11.0%     10.7%     15.5%      9.4%     10.4%     11.3%      9.0%      4.1%      0.8%
##               0.6%      0.6%      1.1%      0.9%      1.0%      1.5%      0.8%      0.7%      0.8%      0.6%      0.6%
## --------------------------------------------------------------------------------------------------------------------------------
## 003           207       222       326       366       190       152       206       276       405       340       156      2846
##               7.3%      7.8%     11.5%     12.9%      6.7%      5.3%      7.2%      9.7%     14.2%     11.9%      5.5%      0.1%
##               0.2%      0.2%      0.3%      0.2%      0.1%      0.1%      0.1%      0.1%      0.2%      0.2%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 004           164       217       181       356       166       280       156       250       299       228       101      2398
##               6.8%      9.0%      7.5%     14.8%      6.9%     11.7%      6.5%     10.4%     12.5%      9.5%      4.2%      0.1%
##               0.1%      0.1%      0.2%      0.2%      0.1%      0.2%      0.1%      0.1%      0.1%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 005           342       299       476       812       425       406       476       562      1192       631       328      5949
##               5.7%      5.0%      8.0%     13.6%      7.1%      6.8%      8.0%      9.4%     20.0%     10.6%      5.5%      0.3%
##               0.3%      0.2%      0.4%      0.4%      0.3%      0.2%      0.3%      0.2%      0.5%      0.3%      0.3%
## --------------------------------------------------------------------------------------------------------------------------------
## 006           160       179       158       341       183       140       174       380       319       257       114      2405
##               6.7%      7.4%      6.6%     14.2%      7.6%      5.8%      7.2%     15.8%     13.3%     10.7%      4.7%      0.1%
##               0.1%      0.1%      0.1%      0.2%      0.1%      0.1%      0.1%      0.2%      0.1%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 007          1473      1541       958      1690      1660      1930      1908      1904      2042      1865       666     17637
##               8.4%      8.7%      5.4%      9.6%      9.4%     10.9%     10.8%     10.8%     11.6%     10.6%      3.8%      0.9%
##               1.1%      1.0%      0.8%      0.9%      1.0%      1.1%      1.0%      0.8%      0.9%      0.8%      0.6%
## --------------------------------------------------------------------------------------------------------------------------------
## 008           752       786       868      1141      1242      1129      1118      1122      1066      2089       529     11842
##               6.4%      6.6%      7.3%      9.6%     10.5%      9.5%      9.4%      9.5%      9.0%     17.6%      4.5%      0.6%
##               0.6%      0.5%      0.8%      0.6%      0.7%      0.7%      0.6%      0.5%      0.5%      0.9%      0.5%
## --------------------------------------------------------------------------------------------------------------------------------
## 009            16        33        84       115        51        36        29        70        90        74        54       652
##               2.5%      5.1%     12.9%     17.6%      7.8%      5.5%      4.4%     10.7%     13.8%     11.3%      8.3%      0.0%
##               0.0%      0.0%      0.1%      0.1%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 010           686       823      1052      1302       984       882       932      1011      1819      2199       914     12604
##               5.4%      6.5%      8.3%     10.3%      7.8%      7.0%      7.4%      8.0%     14.4%     17.4%      7.3%      0.7%
##               0.5%      0.6%      0.9%      0.7%      0.6%      0.5%      0.5%      0.4%      0.8%      1.0%      0.9%
## --------------------------------------------------------------------------------------------------------------------------------
## 011            87        67        55        71        78        80        86       100        93        56        47       820
##              10.6%      8.2%      6.7%      8.7%      9.5%      9.8%     10.5%     12.2%     11.3%      6.8%      5.7%      0.0%
##               0.1%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 012           270       350       303       627       382       275       311       441       464       464       324      4211
##               6.4%      8.3%      7.2%     14.9%      9.1%      6.5%      7.4%     10.5%     11.0%     11.0%      7.7%      0.2%
##               0.2%      0.2%      0.3%      0.3%      0.2%      0.2%      0.2%      0.2%      0.2%      0.2%      0.3%
## --------------------------------------------------------------------------------------------------------------------------------
## 013           395       381       396       681       788       443       354       312       386       456       250      4842
##               8.2%      7.9%      8.2%     14.1%     16.3%      9.1%      7.3%      6.4%      8.0%      9.4%      5.2%      0.3%
##               0.3%      0.3%      0.3%      0.3%      0.5%      0.3%      0.2%      0.1%      0.2%      0.2%      0.2%
## --------------------------------------------------------------------------------------------------------------------------------
## 014           295       520       201       379       318       268       243       279       375       432       133      3443
##               8.6%     15.1%      5.8%     11.0%      9.2%      7.8%      7.1%      8.1%     10.9%     12.5%      3.9%      0.2%
##               0.2%      0.4%      0.2%      0.2%      0.2%      0.2%      0.1%      0.1%      0.2%      0.2%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 015           183       209       283       363       227       250       201       523       334       301       236      3110
##               5.9%      6.7%      9.1%     11.7%      7.3%      8.0%      6.5%     16.8%     10.7%      9.7%      7.6%      0.2%
##               0.1%      0.1%      0.2%      0.2%      0.1%      0.1%      0.1%      0.2%      0.1%      0.1%      0.2%
## --------------------------------------------------------------------------------------------------------------------------------
## 016           755       914       453      1499      1809      2426      1772      2168      2309      2231      3573     19909
##               3.8%      4.6%      2.3%      7.5%      9.1%     12.2%      8.9%     10.9%     11.6%     11.2%     17.9%      1.0%
##               0.6%      0.6%      0.4%      0.8%      1.1%      1.4%      0.9%      0.9%      1.0%      1.0%      3.4%
## --------------------------------------------------------------------------------------------------------------------------------
## 017           341       538       621       708       708       489       611      1047      1029       803       333      7228
##               4.7%      7.4%      8.6%      9.8%      9.8%      6.8%      8.5%     14.5%     14.2%     11.1%      4.6%      0.4%
##               0.3%      0.4%      0.5%      0.4%      0.4%      0.3%      0.3%      0.4%      0.4%      0.4%      0.3%
## --------------------------------------------------------------------------------------------------------------------------------
## 018           342       350       234       457       377       634       562       581       494       406       241      4678
##               7.3%      7.5%      5.0%      9.8%      8.1%     13.6%     12.0%     12.4%     10.6%      8.7%      5.2%      0.2%
##               0.3%      0.2%      0.2%      0.2%      0.2%      0.4%      0.3%      0.2%      0.2%      0.2%      0.2%
## --------------------------------------------------------------------------------------------------------------------------------
## 019            58        98        85       213        86        95        68       115       151       109        50      1128
##               5.1%      8.7%      7.5%     18.9%      7.6%      8.4%      6.0%     10.2%     13.4%      9.7%      4.4%      0.1%
##               0.0%      0.1%      0.1%      0.1%      0.1%      0.1%      0.0%      0.0%      0.1%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 020           189       180       245       338       211       252       268       278       211       239       125      2536
##               7.5%      7.1%      9.7%     13.3%      8.3%      9.9%     10.6%     11.0%      8.3%      9.4%      4.9%      0.1%
##               0.1%      0.1%      0.2%      0.2%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 021          5604      7091      6909     10027      9716      9596     15435     15253     14301     11585      6683    112200
##               5.0%      6.3%      6.2%      8.9%      8.7%      8.6%     13.8%     13.6%     12.7%     10.3%      6.0%      5.9%
##               4.3%      4.8%      6.0%      5.1%      5.8%      5.7%      8.2%      6.5%      6.2%      5.2%      6.3%
## --------------------------------------------------------------------------------------------------------------------------------
## 022            73       128       173       287        91       156        80       240       214       218       103      1763
##               4.1%      7.3%      9.8%     16.3%      5.2%      8.8%      4.5%     13.6%     12.1%     12.4%      5.8%      0.1%
##               0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.0%      0.1%      0.1%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 023            54        56        72       141        60        48        70       119        88       148        36       892
##               6.1%      6.3%      8.1%     15.8%      6.7%      5.4%      7.8%     13.3%      9.9%     16.6%      4.0%      0.0%
##               0.0%      0.0%      0.1%      0.1%      0.0%      0.0%      0.0%      0.1%      0.0%      0.1%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 024           834      1169       796      1700       810       760       857      1313      1269      1144       574     11226
##               7.4%     10.4%      7.1%     15.1%      7.2%      6.8%      7.6%     11.7%     11.3%     10.2%      5.1%      0.6%
##               0.6%      0.8%      0.7%      0.9%      0.5%      0.4%      0.5%      0.6%      0.6%      0.5%      0.5%
## --------------------------------------------------------------------------------------------------------------------------------
## 025           181       234       146       315       160       210       201       406       225       317       121      2516
##               7.2%      9.3%      5.8%     12.5%      6.4%      8.3%      8.0%     16.1%      8.9%     12.6%      4.8%      0.1%
##               0.1%      0.2%      0.1%      0.2%      0.1%      0.1%      0.1%      0.2%      0.1%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 026           224       222       176       338       342       254       339       405       353       249       148      3050
##               7.3%      7.3%      5.8%     11.1%     11.2%      8.3%     11.1%     13.3%     11.6%      8.2%      4.9%      0.2%
##               0.2%      0.2%      0.2%      0.2%      0.2%      0.1%      0.2%      0.2%      0.2%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 027           494       554       397       797      1224      1255      1257      1129      1161      1089       490      9847
##               5.0%      5.6%      4.0%      8.1%     12.4%     12.7%     12.8%     11.5%     11.8%     11.1%      5.0%      0.5%
##               0.4%      0.4%      0.3%      0.4%      0.7%      0.7%      0.7%      0.5%      0.5%      0.5%      0.5%
## --------------------------------------------------------------------------------------------------------------------------------
## 028           132       132       267       341       121       122       162       440       281       235       104      2337
##               5.6%      5.6%     11.4%     14.6%      5.2%      5.2%      6.9%     18.8%     12.0%     10.1%      4.5%      0.1%
##               0.1%      0.1%      0.2%      0.2%      0.1%      0.1%      0.1%      0.2%      0.1%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 029         11604     11566      5757     12761     12535     12665     15335     19955     18005     16441      7872    144496
##               8.0%      8.0%      4.0%      8.8%      8.7%      8.8%     10.6%     13.8%     12.5%     11.4%      5.4%      7.6%
##               9.0%      7.8%      5.0%      6.5%      7.5%      7.5%      8.2%      8.4%      7.8%      7.4%      7.4%
## --------------------------------------------------------------------------------------------------------------------------------
## 030           604       703       684      1410      1322      1018      1044      1043       976      2004       520     11328
##               5.3%      6.2%      6.0%     12.4%     11.7%      9.0%      9.2%      9.2%      8.6%     17.7%      4.6%      0.6%
##               0.5%      0.5%      0.6%      0.7%      0.8%      0.6%      0.6%      0.4%      0.4%      0.9%      0.5%
## --------------------------------------------------------------------------------------------------------------------------------
## 031           152       263       221       307       352       383       394       385       323       312       106      3198
##               4.8%      8.2%      6.9%      9.6%     11.0%     12.0%     12.3%     12.0%     10.1%      9.8%      3.3%      0.2%
##               0.1%      0.2%      0.2%      0.2%      0.2%      0.2%      0.2%      0.2%      0.1%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 032           292      1261       388       771       282       236       315       752       598       450       213      5558
##               5.3%     22.7%      7.0%     13.9%      5.1%      4.2%      5.7%     13.5%     10.8%      8.1%      3.8%      0.3%
##               0.2%      0.9%      0.3%      0.4%      0.2%      0.1%      0.2%      0.3%      0.3%      0.2%      0.2%
## --------------------------------------------------------------------------------------------------------------------------------
## 033           612       721       526       683       651       639       788      1349      1145       906       492      8512
##               7.2%      8.5%      6.2%      8.0%      7.6%      7.5%      9.3%     15.8%     13.5%     10.6%      5.8%      0.4%
##               0.5%      0.5%      0.5%      0.3%      0.4%      0.4%      0.4%      0.6%      0.5%      0.4%      0.5%
## --------------------------------------------------------------------------------------------------------------------------------
## 034           905       967       630      1192      1079      1114      1065      1417      1323      1212       682     11586
##               7.8%      8.3%      5.4%     10.3%      9.3%      9.6%      9.2%     12.2%     11.4%     10.5%      5.9%      0.6%
##               0.7%      0.7%      0.5%      0.6%      0.6%      0.7%      0.6%      0.6%      0.6%      0.5%      0.6%
## --------------------------------------------------------------------------------------------------------------------------------
## 035           179       203       131       329       173       171       239       337       375       241       174      2552
##               7.0%      8.0%      5.1%     12.9%      6.8%      6.7%      9.4%     13.2%     14.7%      9.4%      6.8%      0.1%
##               0.1%      0.1%      0.1%      0.2%      0.1%      0.1%      0.1%      0.1%      0.2%      0.1%      0.2%
## --------------------------------------------------------------------------------------------------------------------------------
## 036           512       883       566      1003       654       520       555       858       848       881       388      7668
##               6.7%     11.5%      7.4%     13.1%      8.5%      6.8%      7.2%     11.2%     11.1%     11.5%      5.1%      0.4%
##               0.4%      0.6%      0.5%      0.5%      0.4%      0.3%      0.3%      0.4%      0.4%      0.4%      0.4%
## --------------------------------------------------------------------------------------------------------------------------------
## 037            84       140       146       186       155       163       198       322       267       275       121      2057
##               4.1%      6.8%      7.1%      9.0%      7.5%      7.9%      9.6%     15.7%     13.0%     13.4%      5.9%      0.1%
##               0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 038           217       168       271       324       275       187       239       329       362       292       159      2823
##               7.7%      6.0%      9.6%     11.5%      9.7%      6.6%      8.5%     11.7%     12.8%     10.3%      5.6%      0.1%
##               0.2%      0.1%      0.2%      0.2%      0.2%      0.1%      0.1%      0.1%      0.2%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 039           235       213       284       354       242       242       270       481       365       455       173      3314
##               7.1%      6.4%      8.6%     10.7%      7.3%      7.3%      8.1%     14.5%     11.0%     13.7%      5.2%      0.2%
##               0.2%      0.1%      0.2%      0.2%      0.1%      0.1%      0.1%      0.2%      0.2%      0.2%      0.2%
## --------------------------------------------------------------------------------------------------------------------------------
## 040           216       301       265       477       328       325       315       335       392       386       186      3526
##               6.1%      8.5%      7.5%     13.5%      9.3%      9.2%      8.9%      9.5%     11.1%     10.9%      5.3%      0.2%
##               0.2%      0.2%      0.2%      0.2%      0.2%      0.2%      0.2%      0.1%      0.2%      0.2%      0.2%
## --------------------------------------------------------------------------------------------------------------------------------
## 041           408       650       923      1106      1574      1666      1778      1824      1611      3404       665     15609
##               2.6%      4.2%      5.9%      7.1%     10.1%     10.7%     11.4%     11.7%     10.3%     21.8%      4.3%      0.8%
##               0.3%      0.4%      0.8%      0.6%      0.9%      1.0%      0.9%      0.8%      0.7%      1.5%      0.6%
## --------------------------------------------------------------------------------------------------------------------------------
## 042          1069      1362       893      1422      1414      1479      1496      1561      1877      1462       823     14858
##               7.2%      9.2%      6.0%      9.6%      9.5%     10.0%     10.1%     10.5%     12.6%      9.8%      5.5%      0.8%
##               0.8%      0.9%      0.8%      0.7%      0.8%      0.9%      0.8%      0.7%      0.8%      0.7%      0.8%
## --------------------------------------------------------------------------------------------------------------------------------
## 043          4313      4523      3610      6888      7470      6320      4836      5968      6563      7129      3135     60755
##               7.1%      7.4%      5.9%     11.3%     12.3%     10.4%      8.0%      9.8%     10.8%     11.7%      5.2%      3.2%
##               3.3%      3.1%      3.1%      3.5%      4.4%      3.7%      2.6%      2.5%      2.9%      3.2%      3.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 044          1603      1067       974      1104       692       759       720      1676      1274      1408       540     11817
##              13.6%      9.0%      8.2%      9.3%      5.9%      6.4%      6.1%     14.2%     10.8%     11.9%      4.6%      0.6%
##               1.2%      0.7%      0.8%      0.6%      0.4%      0.4%      0.4%      0.7%      0.6%      0.6%      0.5%
## --------------------------------------------------------------------------------------------------------------------------------
## 045            22        14        24        26         8         7        17        31        32        39        12       232
##               9.5%      6.0%     10.3%     11.2%      3.4%      3.0%      7.3%     13.4%     13.8%     16.8%      5.2%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 046           362       461       556      1062       383       432       469       808       706       754       404      6397
##               5.7%      7.2%      8.7%     16.6%      6.0%      6.8%      7.3%     12.6%     11.0%     11.8%      6.3%      0.3%
##               0.3%      0.3%      0.5%      0.5%      0.2%      0.3%      0.2%      0.3%      0.3%      0.3%      0.4%
## --------------------------------------------------------------------------------------------------------------------------------
## 048           423       442       583       690       368       349       329       809       675       598       292      5558
##               7.6%      8.0%     10.5%     12.4%      6.6%      6.3%      5.9%     14.6%     12.1%     10.8%      5.3%      0.3%
##               0.3%      0.3%      0.5%      0.3%      0.2%      0.2%      0.2%      0.3%      0.3%      0.3%      0.3%
## --------------------------------------------------------------------------------------------------------------------------------
## 049           100       112        90       191        90        97       104       238       215       277       119      1633
##               6.1%      6.9%      5.5%     11.7%      5.5%      5.9%      6.4%     14.6%     13.2%     17.0%      7.3%      0.1%
##               0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 050            81       145       205       233       137       139       135       332       227       261       115      2010
##               4.0%      7.2%     10.2%     11.6%      6.8%      6.9%      6.7%     16.5%     11.3%     13.0%      5.7%      0.1%
##               0.1%      0.1%      0.2%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 051           152       135       156       165        85       103       123       203       197       207        66      1592
##               9.5%      8.5%      9.8%     10.4%      5.3%      6.5%      7.7%     12.8%     12.4%     13.0%      4.1%      0.1%
##               0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 052           388       648       339       551       413       343       435       790       465       439       221      5032
##               7.7%     12.9%      6.7%     10.9%      8.2%      6.8%      8.6%     15.7%      9.2%      8.7%      4.4%      0.3%
##               0.3%      0.4%      0.3%      0.3%      0.2%      0.2%      0.2%      0.3%      0.2%      0.2%      0.2%
## --------------------------------------------------------------------------------------------------------------------------------
## 053          2399      2574      3156      4941      4832      4836      5221      6258      7043      8613      4685     54558
##               4.4%      4.7%      5.8%      9.1%      8.9%      8.9%      9.6%     11.5%     12.9%     15.8%      8.6%      2.9%
##               1.9%      1.7%      2.7%      2.5%      2.9%      2.8%      2.8%      2.6%      3.1%      3.9%      4.4%
## --------------------------------------------------------------------------------------------------------------------------------
## 054           458       507       484       841       560       657       611       588       713       678       316      6413
##               7.1%      7.9%      7.5%     13.1%      8.7%     10.2%      9.5%      9.2%     11.1%     10.6%      4.9%      0.3%
##               0.4%      0.3%      0.4%      0.4%      0.3%      0.4%      0.3%      0.2%      0.3%      0.3%      0.3%
## --------------------------------------------------------------------------------------------------------------------------------
## 055           198       319       246       449       185       177       223       422       294       299       155      2967
##               6.7%     10.8%      8.3%     15.1%      6.2%      6.0%      7.5%     14.2%      9.9%     10.1%      5.2%      0.2%
##               0.2%      0.2%      0.2%      0.2%      0.1%      0.1%      0.1%      0.2%      0.1%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 056           102       146       295       237       146       139        97       263       172       309       132      2038
##               5.0%      7.2%     14.5%     11.6%      7.2%      6.8%      4.8%     12.9%      8.4%     15.2%      6.5%      0.1%
##               0.1%      0.1%      0.3%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 057            56        77       115       196        67        73        77       137       164       126        59      1147
##               4.9%      6.7%     10.0%     17.1%      5.8%      6.4%      6.7%     11.9%     14.3%     11.0%      5.1%      0.1%
##               0.0%      0.1%      0.1%      0.1%      0.0%      0.0%      0.0%      0.1%      0.1%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 058           471       506       331       566       520       570       514       610       827       611       302      5828
##               8.1%      8.7%      5.7%      9.7%      8.9%      9.8%      8.8%     10.5%     14.2%     10.5%      5.2%      0.3%
##               0.4%      0.3%      0.3%      0.3%      0.3%      0.3%      0.3%      0.3%      0.4%      0.3%      0.3%
## --------------------------------------------------------------------------------------------------------------------------------
## 059           195       173       181       254       114       112       123       195       167       133        86      1733
##              11.3%     10.0%     10.4%     14.7%      6.6%      6.5%      7.1%     11.3%      9.6%      7.7%      5.0%      0.1%
##               0.2%      0.1%      0.2%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 060           912       858      1704      1518      1151      1195      1239      1257      1510      1431       666     13441
##               6.8%      6.4%     12.7%     11.3%      8.6%      8.9%      9.2%      9.4%     11.2%     10.6%      5.0%      0.7%
##               0.7%      0.6%      1.5%      0.8%      0.7%      0.7%      0.7%      0.5%      0.7%      0.6%      0.6%
## --------------------------------------------------------------------------------------------------------------------------------
## 062           120       152        60       271       155       169       148       376       361       302        97      2211
##               5.4%      6.9%      2.7%     12.3%      7.0%      7.6%      6.7%     17.0%     16.3%     13.7%      4.4%      0.1%
##               0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.2%      0.2%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 063           226       215       251       384       263       224       202       498       449       384       156      3252
##               6.9%      6.6%      7.7%     11.8%      8.1%      6.9%      6.2%     15.3%     13.8%     11.8%      4.8%      0.2%
##               0.2%      0.1%      0.2%      0.2%      0.2%      0.1%      0.1%      0.2%      0.2%      0.2%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 065           212       292       233       390       165       200       211       472       445       290       102      3012
##               7.0%      9.7%      7.7%     12.9%      5.5%      6.6%      7.0%     15.7%     14.8%      9.6%      3.4%      0.2%
##               0.2%      0.2%      0.2%      0.2%      0.1%      0.1%      0.1%      0.2%      0.2%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 066            59       198       211       253        95        84        99       209       246       169       113      1736
##               3.4%     11.4%     12.2%     14.6%      5.5%      4.8%      5.7%     12.0%     14.2%      9.7%      6.5%      0.1%
##               0.0%      0.1%      0.2%      0.1%      0.1%      0.0%      0.1%      0.1%      0.1%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 067           366       353       288       452       279       298       341       265       327       429       166      3564
##              10.3%      9.9%      8.1%     12.7%      7.8%      8.4%      9.6%      7.4%      9.2%     12.0%      4.7%      0.2%
##               0.3%      0.2%      0.2%      0.2%      0.2%      0.2%      0.2%      0.1%      0.1%      0.2%      0.2%
## --------------------------------------------------------------------------------------------------------------------------------
## 068           290       503       760      1136       721       439       532      1204       781       809       343      7518
##               3.9%      6.7%     10.1%     15.1%      9.6%      5.8%      7.1%     16.0%     10.4%     10.8%      4.6%      0.4%
##               0.2%      0.3%      0.7%      0.6%      0.4%      0.3%      0.3%      0.5%      0.3%      0.4%      0.3%
## --------------------------------------------------------------------------------------------------------------------------------
## 069           179       251       237       416       316       227       235       531       418       405       202      3417
##               5.2%      7.3%      6.9%     12.2%      9.2%      6.6%      6.9%     15.5%     12.2%     11.9%      5.9%      0.2%
##               0.1%      0.2%      0.2%      0.2%      0.2%      0.1%      0.1%      0.2%      0.2%      0.2%      0.2%
## --------------------------------------------------------------------------------------------------------------------------------
## 070           207       247       197       300       263       179       180       359       491       570       237      3230
##               6.4%      7.6%      6.1%      9.3%      8.1%      5.5%      5.6%     11.1%     15.2%     17.6%      7.3%      0.2%
##               0.2%      0.2%      0.2%      0.2%      0.2%      0.1%      0.1%      0.2%      0.2%      0.3%      0.2%
## --------------------------------------------------------------------------------------------------------------------------------
## 071           693       736       766      1219       737       816       822      1303      1492      1395       725     10704
##               6.5%      6.9%      7.2%     11.4%      6.9%      7.6%      7.7%     12.2%     13.9%     13.0%      6.8%      0.6%
##               0.5%      0.5%      0.7%      0.6%      0.4%      0.5%      0.4%      0.6%      0.6%      0.6%      0.7%
## --------------------------------------------------------------------------------------------------------------------------------
## 072           169       152       177       299       258       331       380       300       284       481       174      3005
##               5.6%      5.1%      5.9%     10.0%      8.6%     11.0%     12.6%     10.0%      9.5%     16.0%      5.8%      0.2%
##               0.1%      0.1%      0.2%      0.2%      0.2%      0.2%      0.2%      0.1%      0.1%      0.2%      0.2%
## --------------------------------------------------------------------------------------------------------------------------------
## 073           268       329       204       609       351       327       356       547       617       551       189      4348
##               6.2%      7.6%      4.7%     14.0%      8.1%      7.5%      8.2%     12.6%     14.2%     12.7%      4.3%      0.2%
##               0.2%      0.2%      0.2%      0.3%      0.2%      0.2%      0.2%      0.2%      0.3%      0.2%      0.2%
## --------------------------------------------------------------------------------------------------------------------------------
## 074           643       699       595      1098      1116      1068      1160      1343      1240      1009       537     10508
##               6.1%      6.7%      5.7%     10.4%     10.6%     10.2%     11.0%     12.8%     11.8%      9.6%      5.1%      0.6%
##               0.5%      0.5%      0.5%      0.6%      0.7%      0.6%      0.6%      0.6%      0.5%      0.5%      0.5%
## --------------------------------------------------------------------------------------------------------------------------------
## 075          7102      8053      4793     10344      8631      8616     11765     16997     16024     15143      7429    114897
##               6.2%      7.0%      4.2%      9.0%      7.5%      7.5%     10.2%     14.8%     13.9%     13.2%      6.5%      6.0%
##               5.5%      5.4%      4.1%      5.2%      5.1%      5.1%      6.3%      7.2%      7.0%      6.8%      7.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 077           407       459       567       442       424       357       451       659       627       552       229      5174
##               7.9%      8.9%     11.0%      8.5%      8.2%      6.9%      8.7%     12.7%     12.1%     10.7%      4.4%      0.3%
##               0.3%      0.3%      0.5%      0.2%      0.3%      0.2%      0.2%      0.3%      0.3%      0.2%      0.2%
## --------------------------------------------------------------------------------------------------------------------------------
## 078            60        75        26        46        39        57        92       136       125       121        53       830
##               7.2%      9.0%      3.1%      5.5%      4.7%      6.9%     11.1%     16.4%     15.1%     14.6%      6.4%      0.0%
##               0.0%      0.1%      0.0%      0.0%      0.0%      0.0%      0.0%      0.1%      0.1%      0.1%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 079           122        73       200       160       105        90        89       192       131       183        46      1391
##               8.8%      5.2%     14.4%     11.5%      7.5%      6.5%      6.4%     13.8%      9.4%     13.2%      3.3%      0.1%
##               0.1%      0.0%      0.2%      0.1%      0.1%      0.1%      0.0%      0.1%      0.1%      0.1%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 080           800       916       833      1292      1355      1326      1445      1557      1260      2411      1264     14459
##               5.5%      6.3%      5.8%      8.9%      9.4%      9.2%     10.0%     10.8%      8.7%     16.7%      8.7%      0.8%
##               0.6%      0.6%      0.7%      0.7%      0.8%      0.8%      0.8%      0.7%      0.5%      1.1%      1.2%
## --------------------------------------------------------------------------------------------------------------------------------
## 081           206       246       302       277       291       189       223       405       370       402       147      3058
##               6.7%      8.0%      9.9%      9.1%      9.5%      6.2%      7.3%     13.2%     12.1%     13.1%      4.8%      0.2%
##               0.2%      0.2%      0.3%      0.1%      0.2%      0.1%      0.1%      0.2%      0.2%      0.2%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 082           705       640       935       997       801       700       865      1066      1003       950       487      9149
##               7.7%      7.0%     10.2%     10.9%      8.8%      7.7%      9.5%     11.7%     11.0%     10.4%      5.3%      0.5%
##               0.5%      0.4%      0.8%      0.5%      0.5%      0.4%      0.5%      0.5%      0.4%      0.4%      0.5%
## --------------------------------------------------------------------------------------------------------------------------------
## 083           361       413       453       744       524       495       624       845       758       603       308      6128
##               5.9%      6.7%      7.4%     12.1%      8.6%      8.1%     10.2%     13.8%     12.4%      9.8%      5.0%      0.3%
##               0.3%      0.3%      0.4%      0.4%      0.3%      0.3%      0.3%      0.4%      0.3%      0.3%      0.3%
## --------------------------------------------------------------------------------------------------------------------------------
## 084           319       405       318       464       315       335       336       481       619       527       194      4313
##               7.4%      9.4%      7.4%     10.8%      7.3%      7.8%      7.8%     11.2%     14.4%     12.2%      4.5%      0.2%
##               0.2%      0.3%      0.3%      0.2%      0.2%      0.2%      0.2%      0.2%      0.3%      0.2%      0.2%
## --------------------------------------------------------------------------------------------------------------------------------
## 085           306       403       541       932       370       408       422       929       836       866       391      6404
##               4.8%      6.3%      8.4%     14.6%      5.8%      6.4%      6.6%     14.5%     13.1%     13.5%      6.1%      0.3%
##               0.2%      0.3%      0.5%      0.5%      0.2%      0.2%      0.2%      0.4%      0.4%      0.4%      0.4%
## --------------------------------------------------------------------------------------------------------------------------------
## 086           355       456       274       530       512       410       400       688       645       528       278      5076
##               7.0%      9.0%      5.4%     10.4%     10.1%      8.1%      7.9%     13.6%     12.7%     10.4%      5.5%      0.3%
##               0.3%      0.3%      0.2%      0.3%      0.3%      0.2%      0.2%      0.3%      0.3%      0.2%      0.3%
## --------------------------------------------------------------------------------------------------------------------------------
## 087           176       255       149       615       248       255       303       544       502       475       246      3768
##               4.7%      6.8%      4.0%     16.3%      6.6%      6.8%      8.0%     14.4%     13.3%     12.6%      6.5%      0.2%
##               0.1%      0.2%      0.1%      0.3%      0.1%      0.2%      0.2%      0.2%      0.2%      0.2%      0.2%
## --------------------------------------------------------------------------------------------------------------------------------
## 088          2734      4418      3596      5668      5453      5350      5171      4963      4754      4938      2846     49891
##               5.5%      8.9%      7.2%     11.4%     10.9%     10.7%     10.4%      9.9%      9.5%      9.9%      5.7%      2.6%
##               2.1%      3.0%      3.1%      2.9%      3.2%      3.2%      2.8%      2.1%      2.1%      2.2%      2.7%
## --------------------------------------------------------------------------------------------------------------------------------
## 089          1861      2431      1139      2697      2246      2190      2258      3377      4729      4176      2763     29867
##               6.2%      8.1%      3.8%      9.0%      7.5%      7.3%      7.6%     11.3%     15.8%     14.0%      9.3%      1.6%
##               1.4%      1.6%      1.0%      1.4%      1.3%      1.3%      1.2%      1.4%      2.1%      1.9%      2.6%
## --------------------------------------------------------------------------------------------------------------------------------
## 090           163       121       122       160       122        89       100       123       139       199       108      1446
##              11.3%      8.4%      8.4%     11.1%      8.4%      6.2%      6.9%      8.5%      9.6%     13.8%      7.5%      0.1%
##               0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 091           283       391       408       411       401       384       386       351       358       256       150      3779
##               7.5%     10.3%     10.8%     10.9%     10.6%     10.2%     10.2%      9.3%      9.5%      6.8%      4.0%      0.2%
##               0.2%      0.3%      0.4%      0.2%      0.2%      0.2%      0.2%      0.1%      0.2%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 092           778       890       800      1003      1217       912       880       986       919      1148       370      9903
##               7.9%      9.0%      8.1%     10.1%     12.3%      9.2%      8.9%     10.0%      9.3%     11.6%      3.7%      0.5%
##               0.6%      0.6%      0.7%      0.5%      0.7%      0.5%      0.5%      0.4%      0.4%      0.5%      0.3%
## --------------------------------------------------------------------------------------------------------------------------------
## 093           374       528       515      1023       476       497       544       910       976       746       423      7012
##               5.3%      7.5%      7.3%     14.6%      6.8%      7.1%      7.8%     13.0%     13.9%     10.6%      6.0%      0.4%
##               0.3%      0.4%      0.4%      0.5%      0.3%      0.3%      0.3%      0.4%      0.4%      0.3%      0.4%
## --------------------------------------------------------------------------------------------------------------------------------
## 094           651       768       682      1074       712       788       643      1264      1155      1153       669      9559
##               6.8%      8.0%      7.1%     11.2%      7.4%      8.2%      6.7%     13.2%     12.1%     12.1%      7.0%      0.5%
##               0.5%      0.5%      0.6%      0.5%      0.4%      0.5%      0.3%      0.5%      0.5%      0.5%      0.6%
## --------------------------------------------------------------------------------------------------------------------------------
## 095           248       196       335       461       240       180       202       381       340       262       186      3031
##               8.2%      6.5%     11.1%     15.2%      7.9%      5.9%      6.7%     12.6%     11.2%      8.6%      6.1%      0.2%
##               0.2%      0.1%      0.3%      0.2%      0.1%      0.1%      0.1%      0.2%      0.1%      0.1%      0.2%
## --------------------------------------------------------------------------------------------------------------------------------
## 096           540       728       427       776       775       677       778      1082      1348      1404       498      9033
##               6.0%      8.1%      4.7%      8.6%      8.6%      7.5%      8.6%     12.0%     14.9%     15.5%      5.5%      0.5%
##               0.4%      0.5%      0.4%      0.4%      0.5%      0.4%      0.4%      0.5%      0.6%      0.6%      0.5%
## --------------------------------------------------------------------------------------------------------------------------------
## 097           420       423       294       723       456       425       539       773       917       890       395      6255
##               6.7%      6.8%      4.7%     11.6%      7.3%      6.8%      8.6%     12.4%     14.7%     14.2%      6.3%      0.3%
##               0.3%      0.3%      0.3%      0.4%      0.3%      0.3%      0.3%      0.3%      0.4%      0.4%      0.4%
## --------------------------------------------------------------------------------------------------------------------------------
## 098           659       869       386       877      1027       971       976      1395      1311      1251       751     10473
##               6.3%      8.3%      3.7%      8.4%      9.8%      9.3%      9.3%     13.3%     12.5%     11.9%      7.2%      0.5%
##               0.5%      0.6%      0.3%      0.4%      0.6%      0.6%      0.5%      0.6%      0.6%      0.6%      0.7%
## --------------------------------------------------------------------------------------------------------------------------------
## 10              0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 100             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 101          1712      1739      1165      1391      1298      1295      1875      1698      1735      1485       894     16287
##              10.5%     10.7%      7.2%      8.5%      8.0%      8.0%     11.5%     10.4%     10.7%      9.1%      5.5%      0.9%
##               1.3%      1.2%      1.0%      0.7%      0.8%      0.8%      1.0%      0.7%      0.8%      0.7%      0.8%
## --------------------------------------------------------------------------------------------------------------------------------
## 102           514       767       753       826       739       768      1951       812      1083       893       451      9557
##               5.4%      8.0%      7.9%      8.6%      7.7%      8.0%     20.4%      8.5%     11.3%      9.3%      4.7%      0.5%
##               0.4%      0.5%      0.7%      0.4%      0.4%      0.5%      1.0%      0.3%      0.5%      0.4%      0.4%
## --------------------------------------------------------------------------------------------------------------------------------
## 103            56        65        41       126        62        65       126       373       132       158        87      1291
##               4.3%      5.0%      3.2%      9.8%      4.8%      5.0%      9.8%     28.9%     10.2%     12.2%      6.7%      0.1%
##               0.0%      0.0%      0.0%      0.1%      0.0%      0.0%      0.1%      0.2%      0.1%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 104           443       506       272       435       381       369       391       576       530       577       298      4778
##               9.3%     10.6%      5.7%      9.1%      8.0%      7.7%      8.2%     12.1%     11.1%     12.1%      6.2%      0.3%
##               0.3%      0.3%      0.2%      0.2%      0.2%      0.2%      0.2%      0.2%      0.2%      0.3%      0.3%
## --------------------------------------------------------------------------------------------------------------------------------
## 105             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 106           320       303       168       522       456       504       482       560       476       471       234      4496
##               7.1%      6.7%      3.7%     11.6%     10.1%     11.2%     10.7%     12.5%     10.6%     10.5%      5.2%      0.2%
##               0.2%      0.2%      0.1%      0.3%      0.3%      0.3%      0.3%      0.2%      0.2%      0.2%      0.2%
## --------------------------------------------------------------------------------------------------------------------------------
## 107            85        78       154       171        95        79       100       128       199       152        80      1321
##               6.4%      5.9%     11.7%     12.9%      7.2%      6.0%      7.6%      9.7%     15.1%     11.5%      6.1%      0.1%
##               0.1%      0.1%      0.1%      0.1%      0.1%      0.0%      0.1%      0.1%      0.1%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 108          1106      1200       963      1564      1540      1567      1681      1876      1809      1816       870     15992
##               6.9%      7.5%      6.0%      9.8%      9.6%      9.8%     10.5%     11.7%     11.3%     11.4%      5.4%      0.8%
##               0.9%      0.8%      0.8%      0.8%      0.9%      0.9%      0.9%      0.8%      0.8%      0.8%      0.8%
## --------------------------------------------------------------------------------------------------------------------------------
## 109            71        92       151       289        66        92       108       256       259       248       137      1769
##               4.0%      5.2%      8.5%     16.3%      3.7%      5.2%      6.1%     14.5%     14.6%     14.0%      7.7%      0.1%
##               0.1%      0.1%      0.1%      0.1%      0.0%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 11              0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 110           399       445       515       756       714      1051      1074      1243      1120      1034       415      8766
##               4.6%      5.1%      5.9%      8.6%      8.1%     12.0%     12.3%     14.2%     12.8%     11.8%      4.7%      0.5%
##               0.3%      0.3%      0.4%      0.4%      0.4%      0.6%      0.6%      0.5%      0.5%      0.5%      0.4%
## --------------------------------------------------------------------------------------------------------------------------------
## 111           133        84       185       199       145       100       122       221       242       237        85      1753
##               7.6%      4.8%     10.6%     11.4%      8.3%      5.7%      7.0%     12.6%     13.8%     13.5%      4.8%      0.1%
##               0.1%      0.1%      0.2%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 112          3226      3849      4000      6423      5972      7014      5723      6328      5753      5076      2400     55764
##               5.8%      6.9%      7.2%     11.5%     10.7%     12.6%     10.3%     11.3%     10.3%      9.1%      4.3%      2.9%
##               2.5%      2.6%      3.5%      3.3%      3.6%      4.1%      3.0%      2.7%      2.5%      2.3%      2.3%
## --------------------------------------------------------------------------------------------------------------------------------
## 113           419       570       393       849       923      1039       875      1009       968       963       354      8362
##               5.0%      6.8%      4.7%     10.2%     11.0%     12.4%     10.5%     12.1%     11.6%     11.5%      4.2%      0.4%
##               0.3%      0.4%      0.3%      0.4%      0.5%      0.6%      0.5%      0.4%      0.4%      0.4%      0.3%
## --------------------------------------------------------------------------------------------------------------------------------
## 114           814       759       564      1253       706       964      1109      1500      1535      1532       647     11383
##               7.2%      6.7%      5.0%     11.0%      6.2%      8.5%      9.7%     13.2%     13.5%     13.5%      5.7%      0.6%
##               0.6%      0.5%      0.5%      0.6%      0.4%      0.6%      0.6%      0.6%      0.7%      0.7%      0.6%
## --------------------------------------------------------------------------------------------------------------------------------
## 115          1014      1209       646      1238      1408      1557      1902      2151      1940      2490       760     16315
##               6.2%      7.4%      4.0%      7.6%      8.6%      9.5%     11.7%     13.2%     11.9%     15.3%      4.7%      0.9%
##               0.8%      0.8%      0.6%      0.6%      0.8%      0.9%      1.0%      0.9%      0.8%      1.1%      0.7%
## --------------------------------------------------------------------------------------------------------------------------------
## 116           337       277       277       424       339       241       275       446       447       440       245      3748
##               9.0%      7.4%      7.4%     11.3%      9.0%      6.4%      7.3%     11.9%     11.9%     11.7%      6.5%      0.2%
##               0.3%      0.2%      0.2%      0.2%      0.2%      0.1%      0.1%      0.2%      0.2%      0.2%      0.2%
## --------------------------------------------------------------------------------------------------------------------------------
## 117          6572      7479      4473      7937      6683      6594      7218      7664      7624      8268      4010     74522
##               8.8%     10.0%      6.0%     10.7%      9.0%      8.8%      9.7%     10.3%     10.2%     11.1%      5.4%      3.9%
##               5.1%      5.1%      3.9%      4.0%      4.0%      3.9%      3.8%      3.2%      3.3%      3.7%      3.8%
## --------------------------------------------------------------------------------------------------------------------------------
## 118          6796      7963      9410     11808      6637     11577     11381     14721     10835      9412      3787    104327
##               6.5%      7.6%      9.0%     11.3%      6.4%     11.1%     10.9%     14.1%     10.4%      9.0%      3.6%      5.5%
##               5.3%      5.4%      8.1%      6.0%      3.9%      6.8%      6.1%      6.2%      4.7%      4.2%      3.6%
## --------------------------------------------------------------------------------------------------------------------------------
## 119            58        70        59       115        78        97        76       211       144       142        57      1107
##               5.2%      6.3%      5.3%     10.4%      7.0%      8.8%      6.9%     19.1%     13.0%     12.8%      5.1%      0.1%
##               0.0%      0.0%      0.1%      0.1%      0.0%      0.1%      0.0%      0.1%      0.1%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 120           967      1311      1099      1683      1715      1461      1381      1465      1852      1784       751     15469
##               6.3%      8.5%      7.1%     10.9%     11.1%      9.4%      8.9%      9.5%     12.0%     11.5%      4.9%      0.8%
##               0.8%      0.9%      1.0%      0.9%      1.0%      0.9%      0.7%      0.6%      0.8%      0.8%      0.7%
## --------------------------------------------------------------------------------------------------------------------------------
## 121          3498      3664      2547      6596      7126      3796      3752      6714      7481      6507      2973     54654
##               6.4%      6.7%      4.7%     12.1%     13.0%      6.9%      6.9%     12.3%     13.7%     11.9%      5.4%      2.9%
##               2.7%      2.5%      2.2%      3.3%      4.2%      2.2%      2.0%      2.8%      3.3%      2.9%      2.8%
## --------------------------------------------------------------------------------------------------------------------------------
## 122           104       145       161       355       172       133       235       319       240       190       128      2182
##               4.8%      6.6%      7.4%     16.3%      7.9%      6.1%     10.8%     14.6%     11.0%      8.7%      5.9%      0.1%
##               0.1%      0.1%      0.1%      0.2%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 123          7232      7931      5032      7671      6474      6564      7865     12218      9947      9423      3562     83919
##               8.6%      9.5%      6.0%      9.1%      7.7%      7.8%      9.4%     14.6%     11.9%     11.2%      4.2%      4.4%
##               5.6%      5.4%      4.4%      3.9%      3.8%      3.9%      4.2%      5.2%      4.3%      4.2%      3.4%
## --------------------------------------------------------------------------------------------------------------------------------
## 124          2988      3049      2062      3946      2603      2536      3883      5027      4946      4584      2075     37699
##               7.9%      8.1%      5.5%     10.5%      6.9%      6.7%     10.3%     13.3%     13.1%     12.2%      5.5%      2.0%
##               2.3%      2.1%      1.8%      2.0%      1.5%      1.5%      2.1%      2.1%      2.2%      2.1%      2.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 126           262       526       254       586       333       315       363       862       685       412       298      4896
##               5.4%     10.7%      5.2%     12.0%      6.8%      6.4%      7.4%     17.6%     14.0%      8.4%      6.1%      0.3%
##               0.2%      0.4%      0.2%      0.3%      0.2%      0.2%      0.2%      0.4%      0.3%      0.2%      0.3%
## --------------------------------------------------------------------------------------------------------------------------------
## 127          1652      1743       868      1751      1620      1763      1778      3588      3849      3814      1694     24120
##               6.8%      7.2%      3.6%      7.3%      6.7%      7.3%      7.4%     14.9%     16.0%     15.8%      7.0%      1.3%
##               1.3%      1.2%      0.8%      0.9%      1.0%      1.0%      0.9%      1.5%      1.7%      1.7%      1.6%
## --------------------------------------------------------------------------------------------------------------------------------
## 128          8987      9997      6983     12173     11376     12163     14663     14278     13800     12213      4953    121586
##               7.4%      8.2%      5.7%     10.0%      9.4%     10.0%     12.1%     11.7%     11.3%     10.0%      4.1%      6.4%
##               7.0%      6.8%      6.0%      6.2%      6.8%      7.2%      7.8%      6.0%      6.0%      5.5%      4.7%
## --------------------------------------------------------------------------------------------------------------------------------
## 129             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 130           351       282       370       471       339       388       474       998       796       741       397      5607
##               6.3%      5.0%      6.6%      8.4%      6.0%      6.9%      8.5%     17.8%     14.2%     13.2%      7.1%      0.3%
##               0.3%      0.2%      0.3%      0.2%      0.2%      0.2%      0.3%      0.4%      0.3%      0.3%      0.4%
## --------------------------------------------------------------------------------------------------------------------------------
## 131           620       931       795      1359      1326      1338      1339      1254      1413      1813       778     12966
##               4.8%      7.2%      6.1%     10.5%     10.2%     10.3%     10.3%      9.7%     10.9%     14.0%      6.0%      0.7%
##               0.5%      0.6%      0.7%      0.7%      0.8%      0.8%      0.7%      0.5%      0.6%      0.8%      0.7%
## --------------------------------------------------------------------------------------------------------------------------------
## 132           535       506       558       981       506       603       598       585       955       809       351      6987
##               7.7%      7.2%      8.0%     14.0%      7.2%      8.6%      8.6%      8.4%     13.7%     11.6%      5.0%      0.4%
##               0.4%      0.3%      0.5%      0.5%      0.3%      0.4%      0.3%      0.2%      0.4%      0.4%      0.3%
## --------------------------------------------------------------------------------------------------------------------------------
## 135           222       361       290       387       131       142       163       289       258       312       128      2683
##               8.3%     13.5%     10.8%     14.4%      4.9%      5.3%      6.1%     10.8%      9.6%     11.6%      4.8%      0.1%
##               0.2%      0.2%      0.3%      0.2%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 136          3360      4187      2119      6757      4605      4662      4854      6365      7302      8357      3105     55673
##               6.0%      7.5%      3.8%     12.1%      8.3%      8.4%      8.7%     11.4%     13.1%     15.0%      5.6%      2.9%
##               2.6%      2.8%      1.8%      3.4%      2.7%      2.7%      2.6%      2.7%      3.2%      3.8%      2.9%
## --------------------------------------------------------------------------------------------------------------------------------
## 137            14        27        26        39        24        40        47       164       122        58        32       593
##               2.4%      4.6%      4.4%      6.6%      4.0%      6.7%      7.9%     27.7%     20.6%      9.8%      5.4%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.1%      0.1%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 139           237       266       118       263       296       385       347       309       459       433       271      3384
##               7.0%      7.9%      3.5%      7.8%      8.7%     11.4%     10.3%      9.1%     13.6%     12.8%      8.0%      0.2%
##               0.2%      0.2%      0.1%      0.1%      0.2%      0.2%      0.2%      0.1%      0.2%      0.2%      0.3%
## --------------------------------------------------------------------------------------------------------------------------------
## 140             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 142           104       128       135       125        80        97        84       159       171       100        75      1258
##               8.3%     10.2%     10.7%      9.9%      6.4%      7.7%      6.7%     12.6%     13.6%      7.9%      6.0%      0.1%
##               0.1%      0.1%      0.1%      0.1%      0.0%      0.1%      0.0%      0.1%      0.1%      0.0%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 143          1714      1864       793      1521       975       822       877      1627      1654      1226       638     13711
##              12.5%     13.6%      5.8%     11.1%      7.1%      6.0%      6.4%     11.9%     12.1%      8.9%      4.7%      0.7%
##               1.3%      1.3%      0.7%      0.8%      0.6%      0.5%      0.5%      0.7%      0.7%      0.6%      0.6%
## --------------------------------------------------------------------------------------------------------------------------------
## 144           364       382       369       579       366       288       308       597       550       512       220      4535
##               8.0%      8.4%      8.1%     12.8%      8.1%      6.4%      6.8%     13.2%     12.1%     11.3%      4.9%      0.2%
##               0.3%      0.3%      0.3%      0.3%      0.2%      0.2%      0.2%      0.3%      0.2%      0.2%      0.2%
## --------------------------------------------------------------------------------------------------------------------------------
## 151             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 161             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 166             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 168             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 170             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 172             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 173             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 180             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 190             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 196             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 199             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 20              0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 200             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 201             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 202            79        81        62       160        76        77        76       133       154       141        52      1091
##               7.2%      7.4%      5.7%     14.7%      7.0%      7.1%      7.0%     12.2%     14.1%     12.9%      4.8%      0.1%
##               0.1%      0.1%      0.1%      0.1%      0.0%      0.0%      0.0%      0.1%      0.1%      0.1%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 204             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 207            71        93        77       128        73        96        64       103       122       104        53       984
##               7.2%      9.5%      7.8%     13.0%      7.4%      9.8%      6.5%     10.5%     12.4%     10.6%      5.4%      0.1%
##               0.1%      0.1%      0.1%      0.1%      0.0%      0.1%      0.0%      0.0%      0.1%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 210             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 218             1         9         6        12         5         8        10        22        22        28        30       153
##               0.7%      5.9%      3.9%      7.8%      3.3%      5.2%      6.5%     14.4%     14.4%     18.3%     19.6%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 219             3         1         0        21         0         0         0         0         0         0         0        25
##              12.0%      4.0%      0.0%     84.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 22              0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 220             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 221             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 222             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 224             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 230             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 231             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 240             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 250             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 260             0         0         0         0         0         0         0         0         0         2         1         3
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%     66.7%     33.3%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 261             0         0         0         0         0         0         3         3         0         0         2         8
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%     37.5%     37.5%      0.0%      0.0%     25.0%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 262             0         0         0         0         1         0         0         3         3         2         2        11
##               0.0%      0.0%      0.0%      0.0%      9.1%      0.0%      0.0%     27.3%     27.3%     18.2%     18.2%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 263             3         2         0         0         0         0         5         4         0         5         0        19
##              15.8%     10.5%      0.0%      0.0%      0.0%      0.0%     26.3%     21.1%      0.0%     26.3%      0.0%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 264             0         0         0         1         0         0         0         2         0         0         0         3
##               0.0%      0.0%      0.0%     33.3%      0.0%      0.0%      0.0%     66.7%      0.0%      0.0%      0.0%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 265             0         0         0         0         1         4         0         1         0         0         1         7
##               0.0%      0.0%      0.0%      0.0%     14.3%     57.1%      0.0%     14.3%      0.0%      0.0%     14.3%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 266             1         0         0         0         0         0         0         5         1         2         2        11
##               9.1%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%     45.5%      9.1%     18.2%     18.2%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 267             1         4         3         1         1         5        14        20        25        11         8        93
##               1.1%      4.3%      3.2%      1.1%      1.1%      5.4%     15.1%     21.5%     26.9%     11.8%      8.6%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 268             0         0         0         0         0         0         0         0         0         2         0         2
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%    100.0%      0.0%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 269             0         0         0         0         0         0         0         0         0         2         4         6
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%     33.3%     66.7%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 270             0         1         0         9         2         0         6         3         0         0         0        21
##               0.0%      4.8%      0.0%     42.9%      9.5%      0.0%     28.6%     14.3%      0.0%      0.0%      0.0%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 271             1         4         5         2         0         3        12        12        14         5         7        65
##               1.5%      6.2%      7.7%      3.1%      0.0%      4.6%     18.5%     18.5%     21.5%      7.7%     10.8%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 273             0         1         0         3         2         0         2         0         3         7         2        20
##               0.0%      5.0%      0.0%     15.0%     10.0%      0.0%     10.0%      0.0%     15.0%     35.0%     10.0%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 274             0         0         0         0         0         2         0         0         0         0         1         3
##               0.0%      0.0%      0.0%      0.0%      0.0%     66.7%      0.0%      0.0%      0.0%      0.0%     33.3%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 275             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 276             0         0         0         0         0         0         0         0         0         0         2         2
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%    100.0%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 277             0         0         0         0         0         0         0         0         0         2         0         2
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%    100.0%      0.0%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 278             0         0         0         0         0         0         2         2         0         0         2         6
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%     33.3%     33.3%      0.0%      0.0%     33.3%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 280             3        12        12        19        15        33        35        30        39        66        18       282
##               1.1%      4.3%      4.3%      6.7%      5.3%     11.7%     12.4%     10.6%     13.8%     23.4%      6.4%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 281             4         5         3         2         3         2         6         4         4         7         0        40
##              10.0%     12.5%      7.5%      5.0%      7.5%      5.0%     15.0%     10.0%     10.0%     17.5%      0.0%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 282             7         2         0        12         5        17        11        24        22        21        40       161
##               4.3%      1.2%      0.0%      7.5%      3.1%     10.6%      6.8%     14.9%     13.7%     13.0%     24.8%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 283             0         6        16         3         4        19        12         9        12        18         7       106
##               0.0%      5.7%     15.1%      2.8%      3.8%     17.9%     11.3%      8.5%     11.3%     17.0%      6.6%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 284            38        43        27        66        52        43        40        34        40        31        14       428
##               8.9%     10.0%      6.3%     15.4%     12.1%     10.0%      9.3%      7.9%      9.3%      7.2%      3.3%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 285            30        86        50       122        94        99       130       110       141       136        75      1073
##               2.8%      8.0%      4.7%     11.4%      8.8%      9.2%     12.1%     10.3%     13.1%     12.7%      7.0%      0.1%
##               0.0%      0.1%      0.0%      0.1%      0.1%      0.1%      0.1%      0.0%      0.1%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 286            38        40        40        47        46        44        23        41        81        60        37       497
##               7.6%      8.0%      8.0%      9.5%      9.3%      8.9%      4.6%      8.2%     16.3%     12.1%      7.4%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 287            23        22        28        38        38        18        48        54        68        50        34       421
##               5.5%      5.2%      6.7%      9.0%      9.0%      4.3%     11.4%     12.8%     16.2%     11.9%      8.1%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 288           303       319       244       548       525       451       451       539       614       607       278      4879
##               6.2%      6.5%      5.0%     11.2%     10.8%      9.2%      9.2%     11.0%     12.6%     12.4%      5.7%      0.3%
##               0.2%      0.2%      0.2%      0.3%      0.3%      0.3%      0.2%      0.2%      0.3%      0.3%      0.3%
## --------------------------------------------------------------------------------------------------------------------------------
## 290           247       243       119       259       255       249       442       599       578       490       298      3779
##               6.5%      6.4%      3.1%      6.9%      6.7%      6.6%     11.7%     15.9%     15.3%     13.0%      7.9%      0.2%
##               0.2%      0.2%      0.1%      0.1%      0.2%      0.1%      0.2%      0.3%      0.3%      0.2%      0.3%
## --------------------------------------------------------------------------------------------------------------------------------
## 292             0         0         0         0         1         4         3         9        14         5         4        40
##               0.0%      0.0%      0.0%      0.0%      2.5%     10.0%      7.5%     22.5%     35.0%     12.5%     10.0%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 299             4         2         8        14        11        11        25        24        36        44        35       214
##               1.9%      0.9%      3.7%      6.5%      5.1%      5.1%     11.7%     11.2%     16.8%     20.6%     16.4%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 30              0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 300             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 301             0         0         0         1         3         3         6        41        32        26        22       134
##               0.0%      0.0%      0.0%      0.7%      2.2%      2.2%      4.5%     30.6%     23.9%     19.4%     16.4%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 302             0         0         0         0         9         5        23        33        37        39         5       151
##               0.0%      0.0%      0.0%      0.0%      6.0%      3.3%     15.2%     21.9%     24.5%     25.8%      3.3%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 304             0         0         1         9        22        11         9        13        14        19         4       102
##               0.0%      0.0%      1.0%      8.8%     21.6%     10.8%      8.8%     12.7%     13.7%     18.6%      3.9%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 306            14        28        12        20        32        28        38        59        70        73        62       436
##               3.2%      6.4%      2.8%      4.6%      7.3%      6.4%      8.7%     13.5%     16.1%     16.7%     14.2%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 307            11        13         0         9        11         8         5         8         5        11         7        88
##              12.5%     14.8%      0.0%     10.2%     12.5%      9.1%      5.7%      9.1%      5.7%     12.5%      8.0%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 308             0         0         0         0         0        11        20        27        58        46        50       212
##               0.0%      0.0%      0.0%      0.0%      0.0%      5.2%      9.4%     12.7%     27.4%     21.7%     23.6%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 309             0         0         0         2        18        19        21        19        10        10         5       104
##               0.0%      0.0%      0.0%      1.9%     17.3%     18.3%     20.2%     18.3%      9.6%      9.6%      4.8%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 31              0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 310             3         3         0        23        25        36        27        33        42        23        11       226
##               1.3%      1.3%      0.0%     10.2%     11.1%     15.9%     11.9%     14.6%     18.6%     10.2%      4.9%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 311             1         5         3         0         0         0         0         7         1         3         3        23
##               4.3%     21.7%     13.0%      0.0%      0.0%      0.0%      0.0%     30.4%      4.3%     13.0%     13.0%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 313             0         0         0         0         0         0         0         0         4         6         0        10
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%     40.0%     60.0%      0.0%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 32              0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 320             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 321             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 330             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 332             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 350             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 36              0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 370             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 38              0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 380             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 381             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 390             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 391             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 395             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 40              0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 400             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 401             0         0         0         0         0         0         1         2         0         0         0         3
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%     33.3%     66.7%      0.0%      0.0%      0.0%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 402             0       472       131       401       379       314       331       326       360       336       230      3280
##               0.0%     14.4%      4.0%     12.2%     11.6%      9.6%     10.1%      9.9%     11.0%     10.2%      7.0%      0.2%
##               0.0%      0.3%      0.1%      0.2%      0.2%      0.2%      0.2%      0.1%      0.2%      0.2%      0.2%
## --------------------------------------------------------------------------------------------------------------------------------
## 403           102       159       112       229       203       206       231       204       230       211        91      1978
##               5.2%      8.0%      5.7%     11.6%     10.3%     10.4%     11.7%     10.3%     11.6%     10.7%      4.6%      0.1%
##               0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## 404             0         0         0         0         0         1         3         4         2         3         2        15
##               0.0%      0.0%      0.0%      0.0%      0.0%      6.7%     20.0%     26.7%     13.3%     20.0%     13.3%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 405             0         0         2         0         0         0         0        12        40        44        30       128
##               0.0%      0.0%      1.6%      0.0%      0.0%      0.0%      0.0%      9.4%     31.2%     34.4%     23.4%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 408             1         8         1        14        14        21        13        22        27        15        17       153
##               0.7%      5.2%      0.7%      9.2%      9.2%     13.7%      8.5%     14.4%     17.6%      9.8%     11.1%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 409            19        21        15        35        40        37        27        51        75        46        47       413
##               4.6%      5.1%      3.6%      8.5%      9.7%      9.0%      6.5%     12.3%     18.2%     11.1%     11.4%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 41              0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 410             0         0         0         0         4         9        11        14        23         9        10        80
##               0.0%      0.0%      0.0%      0.0%      5.0%     11.2%     13.8%     17.5%     28.7%     11.2%     12.5%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 411             0         0         0         0         0         0         2        21        28        13         4        68
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      2.9%     30.9%     41.2%     19.1%      5.9%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 412             0         4         2         3         7         9         5         2         9        11         0        52
##               0.0%      7.7%      3.8%      5.8%     13.5%     17.3%      9.6%      3.8%     17.3%     21.2%      0.0%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 413             0         0         1         0         1        12         7         6         4         9         4        44
##               0.0%      0.0%      2.3%      0.0%      2.3%     27.3%     15.9%     13.6%      9.1%     20.5%      9.1%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 415            10         2         7         1         9         8        14        69        61        48        40       269
##               3.7%      0.7%      2.6%      0.4%      3.3%      3.0%      5.2%     25.7%     22.7%     17.8%     14.9%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 416            54        57        24        64        79        56        88       125        91        86        53       777
##               6.9%      7.3%      3.1%      8.2%     10.2%      7.2%     11.3%     16.1%     11.7%     11.1%      6.8%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.1%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 418             0         2         0         1         2         8         9         4        14        13        12        65
##               0.0%      3.1%      0.0%      1.5%      3.1%     12.3%     13.8%      6.2%     21.5%     20.0%     18.5%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 420            21        64         3        15        14        14        26        10        16        15         5       203
##              10.3%     31.5%      1.5%      7.4%      6.9%      6.9%     12.8%      4.9%      7.9%      7.4%      2.5%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 421             0         0         0         0         0         4         7        14         5         7         9        46
##               0.0%      0.0%      0.0%      0.0%      0.0%      8.7%     15.2%     30.4%     10.9%     15.2%     19.6%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 422             4        14        13        13        17        35        39        66        68        54        32       355
##               1.1%      3.9%      3.7%      3.7%      4.8%      9.9%     11.0%     18.6%     19.2%     15.2%      9.0%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 424            11        17        13        15        17        15        24        29        21        19        17       198
##               5.6%      8.6%      6.6%      7.6%      8.6%      7.6%     12.1%     14.6%     10.6%      9.6%      8.6%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 427             0         0         0         1         0         0         0         0         0         0         5         6
##               0.0%      0.0%      0.0%     16.7%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%     83.3%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 428             0         0         0         1         2         0         0         0         0         0         0         3
##               0.0%      0.0%      0.0%     33.3%     66.7%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 43              0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 430             0         0         0         5        12         0         0         0         0         0         0        17
##               0.0%      0.0%      0.0%     29.4%     70.6%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 440             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 45              0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 450             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 460             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 461             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 480             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 50              0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 500             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 501             0         0         0         0         0         0         0         0         0         0         1         1
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%    100.0%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 506             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 510             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 520             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 530             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 540             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 542             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 554             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 564           117         0         0         0         0         0         0         0         0         0         0       117
##             100.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
##               0.1%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 58              0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 580             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 584             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 590             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 60              0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 600           540       579       509       940       876       920      1108      1472      1515      1613      1053     11125
##               4.9%      5.2%      4.6%      8.4%      7.9%      8.3%     10.0%     13.2%     13.6%     14.5%      9.5%      0.6%
##               0.4%      0.4%      0.4%      0.5%      0.5%      0.5%      0.6%      0.6%      0.7%      0.7%      1.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 602             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 61              0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 610             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 62              0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 620             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 623             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 630             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 631             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 632             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 652             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 660             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 67              0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 670             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 680             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 690             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 70              0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 700             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 71              0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 710             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 720             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 730             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 740             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 741             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 750             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 754             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 760             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 770             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 780             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 790             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 791             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 80              0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 800             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 821             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 830             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 840             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 850             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 851             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 870             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 873           182         6         0         0         0         0         2         0         0         3         0       193
##              94.3%      3.1%      0.0%      0.0%      0.0%      0.0%      1.0%      0.0%      0.0%      1.6%      0.0%      0.0%
##               0.1%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 880             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 888             0         0         0         0         0         0         0         0         2         3         0         5
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%     40.0%     60.0%      0.0%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 890             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 90              0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 900          1323      1440       887      1454      1827      1146      1161       992       465         0         0     10695
##              12.4%     13.5%      8.3%     13.6%     17.1%     10.7%     10.9%      9.3%      4.3%      0.0%      0.0%      0.6%
##               1.0%      1.0%      0.8%      0.7%      1.1%      0.7%      0.6%      0.4%      0.2%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 901             2         0         0         0         0         0         0         1         0         0         0         3
##              66.7%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%     33.3%      0.0%      0.0%      0.0%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 902            41         0         0         0         0         0         0         0         0         0         0        41
##             100.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 904             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 905             0         1        29        85        56        55        57        40        47        55        47       472
##               0.0%      0.2%      6.1%     18.0%     11.9%     11.7%     12.1%      8.5%     10.0%     11.7%     10.0%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 917             0         0         0         0         0         0         0         0       350       673       357      1380
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%     25.4%     48.8%     25.9%      0.1%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.2%      0.3%      0.3%
## --------------------------------------------------------------------------------------------------------------------------------
## 920             1         2         0         0         0         0         0         0         0         0         0         3
##              33.3%     66.7%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 921             1         0         0         0         0         0         0         0         0         0         0         1
##             100.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 922            78         0         0         0         1         0         0         2         0         0         0        81
##              96.3%      0.0%      0.0%      0.0%      1.2%      0.0%      0.0%      2.5%      0.0%      0.0%      0.0%      0.0%
##               0.1%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 924             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 940             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 941             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 946             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 948             4        13         0         0         0         0         0         2         0         0         0        19
##              21.1%     68.4%      0.0%      0.0%      0.0%      0.0%      0.0%     10.5%      0.0%      0.0%      0.0%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 959             0         2         0         0         0         0         2         0         0         0         0         4
##               0.0%     50.0%      0.0%      0.0%      0.0%      0.0%     50.0%      0.0%      0.0%      0.0%      0.0%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 960             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 980             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 990             0         0         0         0         0         0         0         0         0         0         0         0
##               NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      NaN%      0.0%
##               0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%      0.0%
## --------------------------------------------------------------------------------------------------------------------------------
## 999            28        41        39        92        91       115       128       158       247       187        93      1219
##               2.3%      3.4%      3.2%      7.5%      7.5%      9.4%     10.5%     13.0%     20.3%     15.3%      7.6%      0.1%
##               0.0%      0.0%      0.0%      0.0%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%      0.1%
## --------------------------------------------------------------------------------------------------------------------------------
## Total      128919    147947    115644    197429    168166    169699    187815    236429    229669    222660    106095   1910472
##               6.7%      7.7%      6.1%     10.3%      8.8%      8.9%      9.8%     12.4%     12.0%     11.7%      5.6%
## ================================================================================================================================

Geocoding with Google Maps

connect to database

setwd("~/sdal/PRJ_201505_CENSUS/analysis/aschroed")
source(file="pg_connect.R")

library("ggmap")

simple example

gc <- geocode("1220 S Villge Way, Blackburg, VA", output = c("more"), source = c("google"))
print(paste(gc$address, "lat =", gc$lat, "lon =", gc$lon))
View(gc)

example pulling addresses from MLS database

geosleep <- function(){
  geocode(output = 'more', source = 'google')
  Sys.sleep(0.3)
}

build and run query to get listing address info

listingAddresses <- dbGetQuery(con, "SELECT list_number, st_number, st_direction,
                  street_name, street_suffix, city, state, zip_code
                  FROM housing.w_mls_location_data limit 5")

get geocoding of each address and add column to end of listingAddresses

listingAddresses$geo <- lapply(paste(listingAddresses$st_number, listingAddresses$st_direction,
         listingAddresses$street_name, listingAddresses$street_suffix,
         listingAddresses$city, listingAddresses$state, listingAddresses$zip_code,
         sep=" "), geocode, output = 'more', source = 'google')


geocode_sleep <- function(location, output = c("more"), source = c("google"), sleep_time = 0.3) {
  # regular geocode function call
  ret <- geocode(location, output, source)
 # sleep!
  Sys.sleep(sleep_time)
  ret
}

listingAddresses$geo <- lapply(paste(listingAddresses$st_number, listingAddresses$st_direction,
                                 listingAddresses$street_name, listingAddresses$street_suffix,
                                 listingAddresses$city, listingAddresses$state, listingAddresses$zip_code,
                                 sep=" "), geocode_sleep)

print(listingAddresses$geo)[aschroed@snowmane aschroed]$

Summary of basic usage

1. dbDriver(drv, ...) instantiates the driver object. Eg.

drv <- dbDriver("PostgreSQL")

2.dbConnect(drv,...) creates and opens a connection to the database implemented by the driver drv. Connection string should be specified with parameters like user, password, dbname, host, port, tty and options. For more details refer to the documentation. Eg.

con <- dbConnect(drv, dbname="tempdb")

3.dbListConnection(drv, ...) provides List of connections handled by the driver Eg.

dbListConnections(drv)

4.dbGetInfo(dbObject, ...) and summary(dbObject) returns information about the dbObject (driver, connection or resultSet). Eg.

dbGetInfo(drv)
summary(con)

5.dbSendQuery(con, statement, ...) submits one statement to the database. Eg.

dbSendQuery(con,"select * from TableName")

6.fetch(rs,n, ...) fetches the next n elements from the result set. Eg.

fetch(rs,n=-1) ## return all elements
fetch(rs,n=2) ##returns last 2 elements in record set.

7. dbGetQuery(con,statement, ...) submits, execute, and extract output in one operation. Eg.

result <- dbGetQuery(con,"select * from TableName")

8. dbGetException(con, ...) returns the status of the last DBMS statement sent over the connection. Eg.

dbGetException(con)

9. dbListResults(con, ...) returns the resultsets active on the given connection. Please note that the current RPostgreSQL package can handle only one resultset per connection (which may change in the future). Eg.

dbListResults(con)

10. dbListTables(con, ...) returns the list of tables available on the connection. Eg.

dbListTables(con)

11. dbExistsTable(con, TableName, ...) checks whether a particular table exists on the given connection. Returns a logical. Eg.

dbExistsTable(con,"TableName")

12. dbRemoveTable(con, TableName, ...) removes the specified table on the connection. Returns a logical indicating operation succeeded or not. Eg.

dbRemoveTable(con,"TableName")

13. dbListFields(con, TableName, ...) returns the list of column names (fields) in the table. ***DOES NOT SEEM TO WORK*** Eg.

dbListFields(con,"TableName")

14. dbColumnInfo(res, ...) produces a query that describes the output of the query. Eg.

dbColumnInfo(rs)

15. dbReadTable(conn, name, ...) imports the data stored remotely in the table name on connection conn. Use the field row.names as the row.names attribute of the output data.frame. Returns a data.frame. Eg.

dframe <-dbReadTable(con,"TableName"). 

16. dbWriteTable(conn, name, value, ...) writes the contents of the dataframe value into the table name specified. Returns a logical indicating whether operation succeeded or not. Eg.

dbWriteTable(con,"newTable",dframe)

17. dbGetStatement(res, ...) returns the DBMS statement associated with the result. Eg.

dbGetStatement(rs)

18. dbGetRowsAffected(res, ...) returns the rows affected the executed statement. If no rows are affected, "-1" is returned. Eg.

dbGetRowsAffected(rs)

19. dbHasCompleted(res, ...) returns a logical to indicate whether an operation is completed or not. Eg.

dbHasCompleted(rs)

20.dbGetRowCount(res, ...) returns number of rows fetched so far. Eg.

dbGetRowCount(rs)

21.dbBeginTransaction begins the PostgreSQL transaction. dbCommit commits the transaction while dbRollback rolls back the transaction. Returns a logical indicating whether the operation succeeded or not. Eg.

dbBeginTransaction(con)
dbRemoveTable(con,"newTable")
dbExistsTable(con,"newTable")
dbRollback(con)
dbExistsTable(con,"newTable")
dbBeginTransaction(con)
dbRemoveTable(con,"newTable")
dbExistsTable(con,"newTable")
dbCommit(con)
dbExistsTable(con,"newTable")

22. dbClearResult(rs, ...) flushes any pending data and frees the resources used by resultset. Eg.

dbClearResult(rs)

23. dbDisconnect(con, ...) closes the connection. Eg.

dbDisconnect(con)

24. dbUnloadDriver(drv,...) frees all the resources used by the driver. Eg.

dbUnloadDriver(drv)

Example

loads the PostgreSQL driver

library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")

Open a connection

con <- dbConnect(drv, dbname="R_Project")

Submits a statement

rs <- dbSendQuery(con, "select * from R_Users")

fetch all elements from the result set

fetch(rs,n=-1)

Submit and execute the query

dbGetQuery(con, "select * from R_packages")

Closes the connection

dbDisconnect(con)

Frees all the resources on the driver

dbUnloadDriver(drv)

Remember: using the RPostgreSQL library, commands will generally look like:

• dbSendQuery(connection, "your sql statement") -- for commands that DON'T bring back data
• dbGetQuery(connection, "your sql statement") -- for commands that DO bring back data

The examples here go in to the "your sql statement" part

Create a table named "customers" with three columns

create table customers (customer_no integer, first_name text, last_name text)

Delete a table named "customers"

drop table customers

Insert into a table named "customers"

insert into customers (customer_no, first_name, last_name) values (1, 'MC', 'Hammer')

Select all rows from a table named "customers"

select *
from customers

Select filtered rows from a table named "customers" where "first_name" equals a specific value

select *
from customers
where last_name = 'Hammer'

Update values in a table named "customers" where "customer_no" equals a specific value

update customers
set first_name = 'Ralph'
where customer_no = 1

Update values in a table named "customers" for ALL rows (be careful with this!)

update customers
set first_name = 'Ralph'

Select distinct (unique) values of a column (here "order_no") from the table "orders"

select distinct(order_no)
from orders

Count distinct (unique) values of a column (here "order_no") from the table "orders"

select count(distinct(order_no))
from orders

Create a new calculated column "total" in a select statement from the table "orders"

select quantity, price, quantity*price total
from orders

A Join (aka "Inner Join") between two tables "orders" and "products" -- An Inner Join will only return records that have a match

select *
from orders
join products on orders.product_no = products.product_no

A Join (aka "Inner Join") between three tables "customers", "orders" and "products"

select *
from customers
join orders on customers.customer_no = orders.customer_no
join products on orders.product_no = products.product_no

A Join (aka "Inner Join") between three tables "customers", "orders" and "products" filtered by customer "last_name"

select *
from customers
join orders on customers.customer_no = orders.customer_no
join products on orders.product_no = products.product_no
where customers.last_name = 'Hammer'

A Join (aka "Inner Join") between three tables "customers", "orders" and "products" filtered by customer "last_name", using aliases

select *
from customers c
join orders o on c.customer_no = o.customer_no
join products p on o.product_no = p.product_no
where c.last_name = 'Hammer'

A Left Join between two tables "customers" and "orders" -- a Left Join will give you all records from the table on the left whether they have a match in the table on the right or not

select *
from customers c
left join orders o on c.customer_no = o.customer_no

Writing the result of a query on "orders" to a new dynamically created table "my_new_table")

select *
into my_new_table
from orders

Appending the result of a query on "orders" to an already existing table "order_archive"

insert into order_archive (order_no, customer_no, product_no) (
select *
from orders
)

Parallel Processing with foreach

In R, "foreach" is used for parallel processing (as opposed to most programming languages where it is just shorthand for a "for" loop).

1. Create a cluster of 20 processors to be used in parallel

myCluster<-makeCluster(20)

2. Register the cluster

registerDoParallel(myCluster)

3a. Create a foreach loop

It is the %dopar% piece that tells R to use the parallel cluster. If you want to just use a single processor, substitute with %do%.

ls <- foreach(i=1:num_rows) %dopar% {

  <your code>

  }
}

3b. Append results of loop to output variable using "to."

ls <- foreach(i=1:num_rows) %dopar% {

  if(<some_condition>) > 1){
    to.ls <- <some_result>

  }
}