March 18, 2015
Set up
Welcome to the Hadleyverse
For today's session we will be using a number of R packages that have been developed by Hadley Wickham.
Most of these packages are on CRAN but we'll make sure we're using the most current available versions by grabbing them from github instead.
To do this we need to install Hadley's devtools package from CRAN so that we can easily grab the others from github.
install.packages("devtools")
library(devtools)
Installing from Github
The three packages we will be talking about today are:
readrtidyrdplyr
which we can install as follows
install_github("hadley/readr")
install_github("hadley/tidyr")
install_github("hadley/dplyr")
install_github("hadley/lubridate")
Datasets, presentation, source code
- Please download the following datasets that will be used in this presentation:
billboard.csv: http://tinyurl.com/df15-billboardnyparking.csv: http://tinyurl.com/df15-nyparkingweather.csv: http://tinyurl.com/df15-weather
The slides for this presentation can be found at
Slides and all source files can be found at
readr
readr
This is a new package that is designed to be a fast and friendly way of reading tabular data into R.
Core features:
- Faster than base R
- No characters as factors
- No column name mangling
- Consistent argument/fucntion naming scheme
- Plays nice with dplyr (
tbl_df) - Progress bars
Example (csv)
library(readr) library(dplyr)
weather = read_csv("weather.csv")
weather
## Source: local data frame [22 x 35] ## ## id year month element d 1 d 2 d 3 d 4 d 5 d 6 d 7 d 8 d 9 d 10 d 11 d 12 d 13 ## 1 MX17004 2010 1 tmax NA NA NA NA NA NA NA NA NA NA NA NA NA ## 2 MX17004 2010 1 tmin NA NA NA NA NA NA NA NA NA NA NA NA NA ## 3 MX17004 2010 2 tmax NA 27.3 24.1 NA NA NA NA NA NA NA 29.7 NA NA ## 4 MX17004 2010 2 tmin NA 14.4 14.4 NA NA NA NA NA NA NA 13.4 NA NA ## 5 MX17004 2010 3 tmax NA NA NA NA 32.1 NA NA NA NA 34.5 NA NA NA ## 6 MX17004 2010 3 tmin NA NA NA NA 14.2 NA NA NA NA 16.8 NA NA NA ## 7 MX17004 2010 4 tmax NA NA NA NA NA NA NA NA NA NA NA NA NA ## 8 MX17004 2010 4 tmin NA NA NA NA NA NA NA NA NA NA NA NA NA ## 9 MX17004 2010 5 tmax NA NA NA NA NA NA NA NA NA NA NA NA NA ## 10 MX17004 2010 5 tmin NA NA NA NA NA NA NA NA NA NA NA NA NA ## .. ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ## Variables not shown: d 14 (dbl), d 15 (dbl), d 16 (dbl), d 17 (dbl), d 18 (lgl), d 19 ## (lgl), d 20 (lgl), d 21 (lgl), d 22 (lgl), d 23 (dbl), d 24 (lgl), d 25 (dbl), d 26 ## (dbl), d 27 (dbl), d 28 (dbl), d 29 (dbl), d 30 (dbl), d 31 (dbl)
str(weather)
## Classes 'tbl_df', 'tbl' and 'data.frame': 22 obs. of 35 variables: ## $ id : chr "MX17004" "MX17004" "MX17004" "MX17004" ... ## $ year : int 2010 2010 2010 2010 2010 2010 2010 2010 2010 2010 ... ## $ month : int 1 1 2 2 3 3 4 4 5 5 ... ## $ element: chr "tmax" "tmin" "tmax" "tmin" ... ## $ d 1 : num NA NA NA NA NA NA NA NA NA NA ... ## $ d 2 : num NA NA 27.3 14.4 NA NA NA NA NA NA ... ## $ d 3 : num NA NA 24.1 14.4 NA NA NA NA NA NA ... ## $ d 4 : num NA NA NA NA NA NA NA NA NA NA ... ## $ d 5 : num NA NA NA NA 32.1 14.2 NA NA NA NA ... ## $ d 6 : num NA NA NA NA NA NA NA NA NA NA ... ## $ d 7 : num NA NA NA NA NA NA NA NA NA NA ... ## $ d 8 : num NA NA NA NA NA NA NA NA NA NA ... ## $ d 9 : logi NA NA NA NA NA NA ... ## $ d 10 : num NA NA NA NA 34.5 16.8 NA NA NA NA ... ## $ d 11 : num NA NA 29.7 13.4 NA NA NA NA NA NA ... ## $ d 12 : logi NA NA NA NA NA NA ... ## $ d 13 : num NA NA NA NA NA NA NA NA NA NA ... ## $ d 14 : num NA NA NA NA NA NA NA NA NA NA ... ## $ d 15 : num NA NA NA NA NA NA NA NA NA NA ... ## $ d 16 : num NA NA NA NA 31.1 17.6 NA NA NA NA ... ## $ d 17 : num NA NA NA NA NA NA NA NA NA NA ... ## $ d 18 : logi NA NA NA NA NA NA ... ## $ d 19 : logi NA NA NA NA NA NA ... ## $ d 20 : logi NA NA NA NA NA NA ... ## $ d 21 : logi NA NA NA NA NA NA ... ## $ d 22 : logi NA NA NA NA NA NA ... ## $ d 23 : num NA NA 29.9 10.7 NA NA NA NA NA NA ... ## $ d 24 : logi NA NA NA NA NA NA ... ## $ d 25 : num NA NA NA NA NA NA NA NA NA NA ... ## $ d 26 : num NA NA NA NA NA NA NA NA NA NA ... ## $ d 27 : num NA NA NA NA NA NA 36.3 16.7 33.2 18.2 ... ## $ d 28 : num NA NA NA NA NA NA NA NA NA NA ... ## $ d 29 : num NA NA NA NA NA NA NA NA NA NA ... ## $ d 30 : num 27.8 14.5 NA NA NA NA NA NA NA NA ... ## $ d 31 : num NA NA NA NA NA NA NA NA NA NA ...
dplyr
A Grammar of Data Manipulation
dplyr is based on the concepts of functions as verbs that manipulate data frames.
Single table functions / verbs:
filter(): pick rows matching criteriaslice(): pick rows using index(es)select(): pick columns by namerename(): rename specific columnsarrange(): reorder rowsmutate(): add new variablestransmute(): create new data frame with variablessample_n()/sample_frac(): randomly sample rowssummarise(): reduce variables to values
dplyr function rules
First argument is a data frame
Subsequent arguments say what to do with data frame
Always return a data frame
Don't modify in place
Pipes
Nested:
f( g( h(x), z=1), y=1 )
Piped:
h(x) %>% g(z=1) %>% f(y=1)
Pipes
For the less mathematically inclined you can think about the following sequence of actions - find key, unlock car, start car, drive to school, park.
Expressed as a set of nested fucntions in R pseudocode this would look like:
park(drive(start_car(find("keys")), to="campus"))
Writing it out using pipes give it a more natural (and easier to read) structure:
find("keys") %>%
start_car() %>%
drive(to="campus") %>%
park()
Example Data
We will demonstrate dplyr's functionality using data on parking violations from New York city. This is a small sample of a much larger data set available from NYC Open Data.
library(dplyr)
library(readr)
park = read_csv("nyparking.csv")
park
## Source: local data frame [91,003 x 20] ## ## Plate ID Registration State Plate Type Issue Date Violation Code Vehicle Body Type ## 1 FXX9781 NY PAS 2/20/14 20 SUBN ## 2 FLZ6021 NY PAS 8/12/13 37 4DSD ## 3 53902MB NY COM 10/24/13 14 VAN ## 4 FYM2426 NY PAS 9/16/13 21 SDN ## 5 GPV3714 NY PAS 6/10/14 71 SUBN ## 6 XBAV11 NJ PAS 12/27/13 78 VAN ## 7 59305JY NY COM 9/18/13 38 DELV ## 8 2146518 IN PAS 11/22/13 41 VAN ## 9 49138M2 MD PAS 12/18/13 38 SUBN ## 10 GHD5283 NY PAS 9/24/13 46 4DSD ## .. ... ... ... ... ... ... ## Variables not shown: Vehicle Make (chr), Vehicle Expiration Date (int), Violation ## Precinct (int), Issuer Precinct (int), Issuer Code (int), Violation Time (chr), ## Violation County (chr), Violation In Front Of Or Opposite (chr), House Number (chr), ## Street Name (chr), Intersecting Street (chr), Days Parking In Effect (chr), Vehicle ## Color (chr), Vehicle Year (int)
Fixing Dates
library(lubridate)
## Loading required package: methods
park$`Issue Date` = mdy(park$`Issue Date`) park
## Source: local data frame [91,003 x 20] ## ## Plate ID Registration State Plate Type Issue Date Violation Code Vehicle Body Type ## 1 FXX9781 NY PAS 2014-02-20 20 SUBN ## 2 FLZ6021 NY PAS 2013-08-12 37 4DSD ## 3 53902MB NY COM 2013-10-24 14 VAN ## 4 FYM2426 NY PAS 2013-09-16 21 SDN ## 5 GPV3714 NY PAS 2014-06-10 71 SUBN ## 6 XBAV11 NJ PAS 2013-12-27 78 VAN ## 7 59305JY NY COM 2013-09-18 38 DELV ## 8 2146518 IN PAS 2013-11-22 41 VAN ## 9 49138M2 MD PAS 2013-12-18 38 SUBN ## 10 GHD5283 NY PAS 2013-09-24 46 4DSD ## .. ... ... ... ... ... ... ## Variables not shown: Vehicle Make (chr), Vehicle Expiration Date (int), Violation ## Precinct (int), Issuer Precinct (int), Issuer Code (int), Violation Time (chr), ## Violation County (chr), Violation In Front Of Or Opposite (chr), House Number (chr), ## Street Name (chr), Intersecting Street (chr), Days Parking In Effect (chr), Vehicle ## Color (chr), Vehicle Year (int)
filter()
park %>% filter(`Issue Date` > "2013/09/01", `Issue Date` < "2014/6/30")
## Source: local data frame [80,761 x 20] ## ## Plate ID Registration State Plate Type Issue Date Violation Code Vehicle Body Type ## 1 FXX9781 NY PAS 2014-02-20 20 SUBN ## 2 53902MB NY COM 2013-10-24 14 VAN ## 3 FYM2426 NY PAS 2013-09-16 21 SDN ## 4 GPV3714 NY PAS 2014-06-10 71 SUBN ## 5 XBAV11 NJ PAS 2013-12-27 78 VAN ## 6 59305JY NY COM 2013-09-18 38 DELV ## 7 2146518 IN PAS 2013-11-22 41 VAN ## 8 49138M2 MD PAS 2013-12-18 38 SUBN ## 9 GHD5283 NY PAS 2013-09-24 46 4DSD ## 10 GBR2885 NY PAS 2013-11-15 20 SUBN ## .. ... ... ... ... ... ... ## Variables not shown: Vehicle Make (chr), Vehicle Expiration Date (int), Violation ## Precinct (int), Issuer Precinct (int), Issuer Code (int), Violation Time (chr), ## Violation County (chr), Violation In Front Of Or Opposite (chr), House Number (chr), ## Street Name (chr), Intersecting Street (chr), Days Parking In Effect (chr), Vehicle ## Color (chr), Vehicle Year (int)
park %>% filter(`Registration State` == "CA" | `Registration State` == "AZ")
## Source: local data frame [443 x 20] ## ## Plate ID Registration State Plate Type Issue Date Violation Code Vehicle Body Type ## 1 4GXP803 CA PAS 2014-06-13 21 4DSD ## 2 6RXT702 CA PAS 2013-12-03 20 4DSD ## 3 AE32447 AZ PAS 2013-09-04 78 DELV ## 4 572WWB CA PAS 2013-08-01 21 4DSD ## 5 AD56343 AZ PAS 2014-02-21 78 DELV ## 6 AD55598 AZ PAS 2013-12-20 14 VAN ## 7 6UXK262 CA PAS 2013-09-14 46 SUBN ## 8 5UIT291 CA PAS 2014-04-24 14 4DSD ## 9 AE85624 AZ PAS 2014-06-06 38 VAN ## 10 AE97488 AZ PAS 2013-11-20 14 VAN ## .. ... ... ... ... ... ... ## Variables not shown: Vehicle Make (chr), Vehicle Expiration Date (int), Violation ## Precinct (int), Issuer Precinct (int), Issuer Code (int), Violation Time (chr), ## Violation County (chr), Violation In Front Of Or Opposite (chr), House Number (chr), ## Street Name (chr), Intersecting Street (chr), Days Parking In Effect (chr), Vehicle ## Color (chr), Vehicle Year (int)
slice()
park %>% slice(3:8)
## Source: local data frame [6 x 20] ## ## Plate ID Registration State Plate Type Issue Date Violation Code Vehicle Body Type ## 1 53902MB NY COM 2013-10-24 14 VAN ## 2 FYM2426 NY PAS 2013-09-16 21 SDN ## 3 GPV3714 NY PAS 2014-06-10 71 SUBN ## 4 XBAV11 NJ PAS 2013-12-27 78 VAN ## 5 59305JY NY COM 2013-09-18 38 DELV ## 6 2146518 IN PAS 2013-11-22 41 VAN ## Variables not shown: Vehicle Make (chr), Vehicle Expiration Date (int), Violation ## Precinct (int), Issuer Precinct (int), Issuer Code (int), Violation Time (chr), ## Violation County (chr), Violation In Front Of Or Opposite (chr), House Number (chr), ## Street Name (chr), Intersecting Street (chr), Days Parking In Effect (chr), Vehicle ## Color (chr), Vehicle Year (int)
park %>% slice((n()-5):n())
## Source: local data frame [6 x 20] ## ## Plate ID Registration State Plate Type Issue Date Violation Code Vehicle Body Type ## 1 69752MD NY COM 2013-10-18 21 VAN ## 2 EUL4171 NY PAS 2013-10-18 21 4DSD ## 3 FYW4417 NY PAS 2014-05-20 46 SUBN ## 4 57224MA NY COM 2013-09-17 82 VAN ## 5 PRU5820 GA PAS 2014-02-26 31 4DSD ## 6 93951JX NY COM 2013-08-30 69 VAN ## Variables not shown: Vehicle Make (chr), Vehicle Expiration Date (int), Violation ## Precinct (int), Issuer Precinct (int), Issuer Code (int), Violation Time (chr), ## Violation County (chr), Violation In Front Of Or Opposite (chr), House Number (chr), ## Street Name (chr), Intersecting Street (chr), Days Parking In Effect (chr), Vehicle ## Color (chr), Vehicle Year (int)
select()
park %>% select(contains("street"))
## Source: local data frame [91,003 x 2] ## ## Street Name Intersecting Street ## 1 ROCKAWAY BLVD ## 2 Austin St ## 3 8 AVE ## 4 GREENPORT AVE ## 5 E 40 ST ## 6 MADISON ST ## 7 62nd St ## 8 37 AVE ## 9 Manhattan Ave ## 10 E 21st St ## .. ... ...
park %>% select(`Plate ID`:`Plate Type`)
## Source: local data frame [91,003 x 3] ## ## Plate ID Registration State Plate Type ## 1 FXX9781 NY PAS ## 2 FLZ6021 NY PAS ## 3 53902MB NY COM ## 4 FYM2426 NY PAS ## 5 GPV3714 NY PAS ## 6 XBAV11 NJ PAS ## 7 59305JY NY COM ## 8 2146518 IN PAS ## 9 49138M2 MD PAS ## 10 GHD5283 NY PAS ## .. ... ... ...
park %>% select(-(`Plate ID`:`Plate Type`))
## Source: local data frame [91,003 x 17] ## ## Issue Date Violation Code Vehicle Body Type Vehicle Make Vehicle Expiration Date ## 1 2014-02-20 20 SUBN HONDA 20140722 ## 2 2013-08-12 37 4DSD ME/BE 20150731 ## 3 2013-10-24 14 VAN FORD 20140809 ## 4 2013-09-16 21 SDN TOYOT 20140530 ## 5 2014-06-10 71 SUBN FORD 20151122 ## 6 2013-12-27 78 VAN FORD 0 ## 7 2013-09-18 38 DELV FRUEH 20130930 ## 8 2013-11-22 41 VAN FRUEH 0 ## 9 2013-12-18 38 SUBN FORD 20140188 ## 10 2013-09-24 46 4DSD NISSA 20150605 ## .. ... ... ... ... ... ## Variables not shown: Violation Precinct (int), Issuer Precinct (int), Issuer Code (int), ## Violation Time (chr), Violation County (chr), Violation In Front Of Or Opposite (chr), ## House Number (chr), Street Name (chr), Intersecting Street (chr), Days Parking In ## Effect (chr), Vehicle Color (chr), Vehicle Year (int)
rename()
park %>% rename(`Days Parking In Effect` = `Days Parking In Effect `)
## Source: local data frame [91,003 x 20] ## ## Plate ID Registration State Plate Type Issue Date Violation Code Vehicle Body Type ## 1 FXX9781 NY PAS 2014-02-20 20 SUBN ## 2 FLZ6021 NY PAS 2013-08-12 37 4DSD ## 3 53902MB NY COM 2013-10-24 14 VAN ## 4 FYM2426 NY PAS 2013-09-16 21 SDN ## 5 GPV3714 NY PAS 2014-06-10 71 SUBN ## 6 XBAV11 NJ PAS 2013-12-27 78 VAN ## 7 59305JY NY COM 2013-09-18 38 DELV ## 8 2146518 IN PAS 2013-11-22 41 VAN ## 9 49138M2 MD PAS 2013-12-18 38 SUBN ## 10 GHD5283 NY PAS 2013-09-24 46 4DSD ## .. ... ... ... ... ... ... ## Variables not shown: Vehicle Make (chr), Vehicle Expiration Date (int), Violation ## Precinct (int), Issuer Precinct (int), Issuer Code (int), Violation Time (chr), ## Violation County (chr), Violation In Front Of Or Opposite (chr), House Number (chr), ## Street Name (chr), Intersecting Street (chr), Days Parking In Effect (chr), Vehicle ## Color (chr), Vehicle Year (int)
arrange()
park %>% select(1:6) %>% arrange(`Registration State`, `Issue Date`)
## Source: local data frame [91,003 x 6] ## ## Plate ID Registration State Plate Type Issue Date Violation Code Vehicle Body Type ## 1 BLANKPLATE 99 999 2013-01-14 66 TRLR ## 2 67764JR 99 COM 2013-07-17 14 DELV ## 3 3511235 99 PAS 2013-07-18 18 VAN ## 4 1510956 99 PAS 2013-07-18 19 VAN ## 5 35253MB 99 COM 2013-07-18 46 DELV ## 6 27544PC 99 999 2013-07-19 46 VAN ## 7 64869PA 99 COM 2013-07-19 18 DELV ## 8 41591JM 99 COM 2013-07-20 46 VAN ## 9 BLANKPLATE 99 999 2013-07-20 74 ## 10 30729JB 99 COM 2013-07-22 19 DELV ## .. ... ... ... ... ... ...
arrange() & desc()
park %>% select(1:6) %>% arrange(`Registration State`, desc(`Issue Date`))
## Source: local data frame [91,003 x 6] ## ## Plate ID Registration State Plate Type Issue Date Violation Code Vehicle Body Type ## 1 TJE86H 99 COM 2014-06-16 66 TRAI ## 2 BLANKPLATE 99 999 2014-06-14 78 ## 3 BLANKPLATE 99 999 2014-06-12 20 ## 4 BLANKPLATE 99 999 2014-06-12 51 MCY ## 5 BLANKPLATE 99 999 2014-06-12 20 ## 6 B44845Y 99 COM 2014-06-08 78 VAN ## 7 FWP5931 99 PAS 2014-06-07 71 SDN ## 8 BLANKPLATE 99 999 2014-06-06 74 MCY ## 9 20331TC 99 TRC 2014-06-04 46 DELV ## 10 BLANKPLATE 99 999 2014-06-04 38 ## .. ... ... ... ... ... ...
mutate()
park %>% select(2:5) %>%
mutate(month = month(`Issue Date`),
day = day(`Issue Date`),
year = year(`Issue Date`),
wday = wday(`Issue Date`, label=TRUE))
## Source: local data frame [91,003 x 8] ## ## Registration State Plate Type Issue Date Violation Code month day year wday ## 1 NY PAS 2014-02-20 20 2 20 2014 Thurs ## 2 NY PAS 2013-08-12 37 8 12 2013 Mon ## 3 NY COM 2013-10-24 14 10 24 2013 Thurs ## 4 NY PAS 2013-09-16 21 9 16 2013 Mon ## 5 NY PAS 2014-06-10 71 6 10 2014 Tues ## 6 NJ PAS 2013-12-27 78 12 27 2013 Fri ## 7 NY COM 2013-09-18 38 9 18 2013 Wed ## 8 IN PAS 2013-11-22 41 11 22 2013 Fri ## 9 MD PAS 2013-12-18 38 12 18 2013 Wed ## 10 NY PAS 2013-09-24 46 9 24 2013 Tues ## .. ... ... ... ... ... ... ... ...
transmute()
park %>%
transmute(month = month(`Issue Date`),
day = day(`Issue Date`),
year = year(`Issue Date`),
wday = wday(`Issue Date`, label=TRUE))
## Source: local data frame [91,003 x 4] ## ## month day year wday ## 1 2 20 2014 Thurs ## 2 8 12 2013 Mon ## 3 10 24 2013 Thurs ## 4 9 16 2013 Mon ## 5 6 10 2014 Tues ## 6 12 27 2013 Fri ## 7 9 18 2013 Wed ## 8 11 22 2013 Fri ## 9 12 18 2013 Wed ## 10 9 24 2013 Tues ## .. ... ... ... ...
distinct()
park %>% distinct()
## Source: local data frame [91,003 x 20] ## ## Plate ID Registration State Plate Type Issue Date Violation Code Vehicle Body Type ## 1 FXX9781 NY PAS 2014-02-20 20 SUBN ## 2 FLZ6021 NY PAS 2013-08-12 37 4DSD ## 3 53902MB NY COM 2013-10-24 14 VAN ## 4 FYM2426 NY PAS 2013-09-16 21 SDN ## 5 GPV3714 NY PAS 2014-06-10 71 SUBN ## 6 XBAV11 NJ PAS 2013-12-27 78 VAN ## 7 59305JY NY COM 2013-09-18 38 DELV ## 8 2146518 IN PAS 2013-11-22 41 VAN ## 9 49138M2 MD PAS 2013-12-18 38 SUBN ## 10 GHD5283 NY PAS 2013-09-24 46 4DSD ## .. ... ... ... ... ... ... ## Variables not shown: Vehicle Make (chr), Vehicle Expiration Date (int), Violation ## Precinct (int), Issuer Precinct (int), Issuer Code (int), Violation Time (chr), ## Violation County (chr), Violation In Front Of Or Opposite (chr), House Number (chr), ## Street Name (chr), Intersecting Street (chr), Days Parking In Effect (chr), Vehicle ## Color (chr), Vehicle Year (int)
sample_n()
park %>% select(1:6) %>% sample_n(10)
## Source: local data frame [10 x 6] ## ## Plate ID Registration State Plate Type Issue Date Violation Code Vehicle Body Type ## 1 FRR3918 NY PAS 2013-11-08 71 4DSD ## 2 P88DUN NJ PAS 2014-05-07 20 SDN ## 3 DUB4773 NY OMS 2014-04-10 38 4DSD ## 4 FMS8984 NY PAS 2013-11-01 70 4DSD ## 5 DZC8237 NY PAS 2013-08-20 21 4DSD ## 6 FTB9860 NC PAS 2013-09-17 14 4DSD ## 7 GBU4681 NY PAS 2014-05-02 7 SUBN ## 8 GKJ3356 NY PAS 2014-06-10 21 4DSD ## 9 FYB1342 NY PAS 2013-09-04 21 SUBN ## 10 GLC3371 NY PAS 2013-11-09 7 SW
sample_frac()
park %>% select(1:6) %>% sample_frac(0.0001)
## Source: local data frame [9 x 6] ## ## Plate ID Registration State Plate Type Issue Date Violation Code Vehicle Body Type ## 1 ESW9728 NY PAS 2014-01-01 19 SUBN ## 2 GJY8845 NY PAS 2013-11-26 46 SUBN ## 3 FHZ2434 NY PAS 2014-01-17 38 VAN ## 4 GJP7718 NY PAS 2013-12-23 21 4DSD ## 5 26247JV NY COM 2013-11-23 37 VAN ## 6 27219ME NY COM 2013-11-02 85 VAN ## 7 ZEF74W NJ PAS 2013-11-16 38 4DSD ## 8 XN659S NJ PAS 2013-12-26 14 VAN ## 9 GEX2096 NY PAS 2015-04-06 71 SDN
summarise()
park %>% summarize(n(), min(`Issue Date`), max(`Issue Date`))
## Source: local data frame [1 x 3] ## ## n() min(`Issue Date`) max(`Issue Date`) ## 1 91003 2000-02-20 2031-07-13
group_by()
park %>% select(1:6) %>% group_by(`Registration State`)
## Source: local data frame [91,003 x 6] ## Groups: Registration State ## ## Plate ID Registration State Plate Type Issue Date Violation Code Vehicle Body Type ## 1 FXX9781 NY PAS 2014-02-20 20 SUBN ## 2 FLZ6021 NY PAS 2013-08-12 37 4DSD ## 3 53902MB NY COM 2013-10-24 14 VAN ## 4 FYM2426 NY PAS 2013-09-16 21 SDN ## 5 GPV3714 NY PAS 2014-06-10 71 SUBN ## 6 XBAV11 NJ PAS 2013-12-27 78 VAN ## 7 59305JY NY COM 2013-09-18 38 DELV ## 8 2146518 IN PAS 2013-11-22 41 VAN ## 9 49138M2 MD PAS 2013-12-18 38 SUBN ## 10 GHD5283 NY PAS 2013-09-24 46 4DSD ## .. ... ... ... ... ... ...
summarise() & group_by()
park %>% select(1:6) %>% group_by(`Registration State`) %>% summarize(n(), min(`Issue Date`), max(`Issue Date`))
## Source: local data frame [62 x 4] ## ## Registration State n() min(`Issue Date`) max(`Issue Date`) ## 1 99 348 2013-01-14 2014-06-16 ## 2 AB 3 2013-09-13 2014-06-19 ## 3 AK 13 2013-08-08 2014-06-09 ## 4 AL 52 2013-07-29 2014-06-24 ## 5 AR 22 2013-08-02 2014-06-02 ## 6 AZ 250 2013-07-22 2014-06-24 ## 7 BC 5 2013-12-18 2014-06-23 ## 8 CA 193 2013-07-24 2014-06-25 ## 9 CO 52 2013-07-30 2014-06-21 ## 10 CT 1388 2013-07-19 2014-06-25 ## .. ... ... ... ...
park %>% select(1:6) %>% filter(`Plate Type` != 999) %>% group_by(`Plate Type`, `Violation Code`) %>% summarize(n = n(), n_states = n_distinct(`Registration State`))
## Source: local data frame [690 x 4] ## Groups: Plate Type ## ## Plate Type Violation Code n n_states ## 1 AGC 19 1 1 ## 2 AGR 17 1 1 ## 3 AGR 71 1 1 ## 4 AGR 82 1 1 ## 5 APP 14 4 2 ## 6 APP 19 2 2 ## 7 APP 31 1 1 ## 8 APP 38 2 1 ## 9 APP 40 1 1 ## 10 APP 46 4 2 ## .. ... ... . ...
Putting it all together
Lets see if we can find out something about the distribution tickets through time.
days = park %>% select(`Issue Date`) %>% group_by(`Issue Date`) %>% summarize(n = n()) days
## Source: local data frame [471 x 2] ## ## Issue Date n ## 1 2000-02-20 1 ## 2 2000-04-05 1 ## 3 2000-11-06 1 ## 4 2001-12-23 1 ## 5 2003-12-24 1 ## 6 2010-03-12 1 ## 7 2010-09-11 1 ## 8 2010-10-10 1 ## 9 2010-10-13 1 ## 10 2011-11-02 1 ## .. ... .
plot(days,type='l')
days = days %>% filter(`Issue Date` > "2013/09/01", `Issue Date` < "2014/6/30") plot(days,type='l')
plot(days %>% filter(`Issue Date` > "2013/12/22", `Issue Date` < "2014/1/5"),type='l')
Exercise
Using the Parking Violation dataset,
- Construct a data frame containing Registration State and a issue date and time combined into a single DateTime column (try
pasteandymd_hmfrom lubridate). - Subset these data to include only datetimes between 09/01/2013 and 6/30/2014.
- Use these data to answer the following questions for both instate (NY registered cars) and out of state (non-NY registered cars):
- What day of the week are you most likely to get a ticket?
- What hour of the day are you most likely to get a ticket?
- What hour and day of the week are you most likely to get a ticket?
A Grammar of Data Manipulation (cont.)
Two table functions / verbs:
left_join- Join matching rows frombtoa.right_join- Join matching rows fromatob.inner_join- Join data. Retain only rows in both sets.full_join- Join data. Retain all values, all rows.semi_join- All rows inathat have a match inb.anti_join- All rows inathat do not have a match inb.
Joining Data
addr = data.frame(name = c("Alice","Bob",
"Carol","dave",
"Eve"),
email= c("alice@company.com",
"bob@company.com",
"carol@company.com",
"dave@company.com",
"eve@company.com"),
stringsAsFactors = FALSE)
phone = data.frame(name = c("Bob","Carol",
"Eve","Eve",
"Frank"),
phone= c("919 555-1111",
"919 555-2222",
"919 555-3333",
"310 555-3333",
"919 555-4444"),
stringsAsFactors = FALSE)addr
## name email ## 1 Alice alice@company.com ## 2 Bob bob@company.com ## 3 Carol carol@company.com ## 4 dave dave@company.com ## 5 Eve eve@company.com
phone
## name phone ## 1 Bob 919 555-1111 ## 2 Carol 919 555-2222 ## 3 Eve 919 555-3333 ## 4 Eve 310 555-3333 ## 5 Frank 919 555-4444
Outer Join
dplyr:
full_join(addr, phone)
## Joining by: "name"
## name email phone ## 1 Alice alice@company.com <NA> ## 2 Bob bob@company.com 919 555-1111 ## 3 Carol carol@company.com 919 555-2222 ## 4 dave dave@company.com <NA> ## 5 Eve eve@company.com 919 555-3333 ## 6 Eve eve@company.com 310 555-3333 ## 7 Frank <NA> 919 555-4444
Base R:
merge(addr, phone, all=TRUE)
## name email phone ## 1 Alice alice@company.com <NA> ## 2 Bob bob@company.com 919 555-1111 ## 3 Carol carol@company.com 919 555-2222 ## 4 dave dave@company.com <NA> ## 5 Eve eve@company.com 919 555-3333 ## 6 Eve eve@company.com 310 555-3333 ## 7 Frank <NA> 919 555-4444
Inner Join
dplyr:
inner_join(addr,phone)
## Joining by: "name"
## name email phone ## 1 Bob bob@company.com 919 555-1111 ## 2 Carol carol@company.com 919 555-2222 ## 3 Eve eve@company.com 919 555-3333 ## 4 Eve eve@company.com 310 555-3333
Base R:
merge(addr, phone, all=FALSE)
## name email phone ## 1 Bob bob@company.com 919 555-1111 ## 2 Carol carol@company.com 919 555-2222 ## 3 Eve eve@company.com 919 555-3333 ## 4 Eve eve@company.com 310 555-3333
Left Join
dplyr:
left_join(addr,phone)
## Joining by: "name"
## name email phone ## 1 Alice alice@company.com <NA> ## 2 Bob bob@company.com 919 555-1111 ## 3 Carol carol@company.com 919 555-2222 ## 4 dave dave@company.com <NA> ## 5 Eve eve@company.com 919 555-3333 ## 6 Eve eve@company.com 310 555-3333
Base R:
merge(addr, phone, all.x=TRUE)
## name email phone ## 1 Alice alice@company.com <NA> ## 2 Bob bob@company.com 919 555-1111 ## 3 Carol carol@company.com 919 555-2222 ## 4 dave dave@company.com <NA> ## 5 Eve eve@company.com 919 555-3333 ## 6 Eve eve@company.com 310 555-3333
Right Join
dplyr:
right_join(addr, phone)
## Joining by: "name"
## name email phone ## 1 Bob bob@company.com 919 555-1111 ## 2 Carol carol@company.com 919 555-2222 ## 3 Eve eve@company.com 919 555-3333 ## 4 Eve eve@company.com 310 555-3333 ## 5 Frank <NA> 919 555-4444
Base R:
merge(addr, phone, all.y=TRUE)
## name email phone ## 1 Bob bob@company.com 919 555-1111 ## 2 Carol carol@company.com 919 555-2222 ## 3 Eve eve@company.com 919 555-3333 ## 4 Eve eve@company.com 310 555-3333 ## 5 Frank <NA> 919 555-4444
Semi and Anti Joins
semi_join(addr, phone)
## Joining by: "name"
## name email ## 1 Bob bob@company.com ## 2 Carol carol@company.com ## 3 Eve eve@company.com
anti_join(addr, phone)
## Joining by: "name"
## name email ## 1 dave dave@company.com ## 2 Alice alice@company.com
tidyr
Tidy Data
Hadley's Definition:
A dataset is messy or tidy depending on how rows, columns and tables are matched up with observations, variables and types.
In tidy data:
- Each variable forms a column.
- Each observation forms a row.
- Each type of observational unit forms a table.
Messy data is any other other arrangement of the data.
Weather Data
w = read_csv("weather.csv")
w
## Source: local data frame [22 x 35] ## ## id year month element d 1 d 2 d 3 d 4 d 5 d 6 d 7 d 8 d 9 d 10 d 11 d 12 d 13 ## 1 MX17004 2010 1 tmax NA NA NA NA NA NA NA NA NA NA NA NA NA ## 2 MX17004 2010 1 tmin NA NA NA NA NA NA NA NA NA NA NA NA NA ## 3 MX17004 2010 2 tmax NA 27.3 24.1 NA NA NA NA NA NA NA 29.7 NA NA ## 4 MX17004 2010 2 tmin NA 14.4 14.4 NA NA NA NA NA NA NA 13.4 NA NA ## 5 MX17004 2010 3 tmax NA NA NA NA 32.1 NA NA NA NA 34.5 NA NA NA ## 6 MX17004 2010 3 tmin NA NA NA NA 14.2 NA NA NA NA 16.8 NA NA NA ## 7 MX17004 2010 4 tmax NA NA NA NA NA NA NA NA NA NA NA NA NA ## 8 MX17004 2010 4 tmin NA NA NA NA NA NA NA NA NA NA NA NA NA ## 9 MX17004 2010 5 tmax NA NA NA NA NA NA NA NA NA NA NA NA NA ## 10 MX17004 2010 5 tmin NA NA NA NA NA NA NA NA NA NA NA NA NA ## .. ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ## Variables not shown: d 14 (dbl), d 15 (dbl), d 16 (dbl), d 17 (dbl), d 18 (lgl), d 19 ## (lgl), d 20 (lgl), d 21 (lgl), d 22 (lgl), d 23 (dbl), d 24 (lgl), d 25 (dbl), d 26 ## (dbl), d 27 (dbl), d 28 (dbl), d 29 (dbl), d 30 (dbl), d 31 (dbl)
From Wide to Long
w %>% gather(day, value, `d 1`:`d 31`, na.rm = TRUE)
## Source: local data frame [66 x 6] ## ## id year month element day value ## 1 MX17004 2010 12 tmax d 1 29.9 ## 2 MX17004 2010 12 tmin d 1 13.8 ## 3 MX17004 2010 2 tmax d 2 27.3 ## 4 MX17004 2010 2 tmin d 2 14.4 ## 5 MX17004 2010 11 tmax d 2 31.3 ## 6 MX17004 2010 11 tmin d 2 16.3 ## 7 MX17004 2010 2 tmax d 3 24.1 ## 8 MX17004 2010 2 tmin d 3 14.4 ## 9 MX17004 2010 7 tmax d 3 28.6 ## 10 MX17004 2010 7 tmin d 3 17.5 ## .. ... ... ... ... ... ...
w %>% gather(day, value, -(id:element), na.rm = TRUE)
## Source: local data frame [66 x 6] ## ## id year month element day value ## 1 MX17004 2010 12 tmax d 1 29.9 ## 2 MX17004 2010 12 tmin d 1 13.8 ## 3 MX17004 2010 2 tmax d 2 27.3 ## 4 MX17004 2010 2 tmin d 2 14.4 ## 5 MX17004 2010 11 tmax d 2 31.3 ## 6 MX17004 2010 11 tmin d 2 16.3 ## 7 MX17004 2010 2 tmax d 3 24.1 ## 8 MX17004 2010 2 tmin d 3 14.4 ## 9 MX17004 2010 7 tmax d 3 28.6 ## 10 MX17004 2010 7 tmin d 3 17.5 ## .. ... ... ... ... ... ...
Data cleaning
w %>% gather(day, value, -(id:element), na.rm = TRUE) %>% mutate(date = as.Date(paste(year, month, extract_numeric(day), sep="-"))) %>% select(id, date, element, value) %>% arrange(id, date, desc(element))
## Source: local data frame [66 x 4] ## ## id date element value ## 1 MX17004 2010-01-30 tmin 14.5 ## 2 MX17004 2010-01-30 tmax 27.8 ## 3 MX17004 2010-02-02 tmin 14.4 ## 4 MX17004 2010-02-02 tmax 27.3 ## 5 MX17004 2010-02-03 tmin 14.4 ## 6 MX17004 2010-02-03 tmax 24.1 ## 7 MX17004 2010-02-11 tmin 13.4 ## 8 MX17004 2010-02-11 tmax 29.7 ## 9 MX17004 2010-02-23 tmin 10.7 ## 10 MX17004 2010-02-23 tmax 29.9 ## .. ... ... ... ...
Finishing up
w %>% gather(day, value, -(id:element), na.rm = TRUE) %>% mutate(date = as.Date(paste(year, month, extract_numeric(day), sep="-"))) %>% select(id, date, element, value) %>% arrange(id, date, desc(element)) %>% spread(element, value)
## Source: local data frame [33 x 4] ## ## id date tmax tmin ## 1 MX17004 2010-01-30 27.8 14.5 ## 2 MX17004 2010-02-02 27.3 14.4 ## 3 MX17004 2010-02-03 24.1 14.4 ## 4 MX17004 2010-02-11 29.7 13.4 ## 5 MX17004 2010-02-23 29.9 10.7 ## 6 MX17004 2010-03-05 32.1 14.2 ## 7 MX17004 2010-03-10 34.5 16.8 ## 8 MX17004 2010-03-16 31.1 17.6 ## 9 MX17004 2010-04-27 36.3 16.7 ## 10 MX17004 2010-05-27 33.2 18.2 ## .. ... ... ... ...
Exercise
We've also provided a data set from the billboard music charts (billboard.csv), using what we've covered so far try to tidy this data into a more useful and usable structure.
bb = read_csv("billboard.csv")
Acknowledgments
Acknowledgments
Above materials are derived in part from the following sources: