Quick reference: data.table

1) \(i\)

• first index is ALWAYS rows, regardless of commas

identical(flights[2], flights[2,])

## [1] TRUE

• Compare to data.frame

as.data.frame(flights)[2]  %>% dim

## [1] 5819079       1

as.data.frame(flights)[2,] %>% dim

## [1]  1 31

• Use \(i\) to select rows

flights[1:2] # first and second row

##    YEAR MONTH DAY DAY_OF_WEEK AIRLINE FLIGHT_NUMBER TAIL_NUMBER
## 1: 2015     1   1           4      AS            98      N407AS
## 2: 2015     1   1           4      AA          2336      N3KUAA
##    ORIGIN_AIRPORT DESTINATION_AIRPORT SCHEDULED_DEPARTURE DEPARTURE_TIME
## 1:            ANC                 SEA                   5           2354
## 2:            LAX                 PBI                  10              2
##    DEPARTURE_DELAY TAXI_OUT WHEELS_OFF SCHEDULED_TIME ELAPSED_TIME
## 1:             -11       21         15            205          194
## 2:              -8       12         14            280          279
##    AIR_TIME DISTANCE WHEELS_ON TAXI_IN SCHEDULED_ARRIVAL ARRIVAL_TIME
## 1:      169     1448       404       4               430          408
## 2:      263     2330       737       4               750          741
##    ARRIVAL_DELAY DIVERTED CANCELLED CANCELLATION_REASON AIR_SYSTEM_DELAY
## 1:           -22        0         0                                   NA
## 2:            -9        0         0                                   NA
##    SECURITY_DELAY AIRLINE_DELAY LATE_AIRCRAFT_DELAY WEATHER_DELAY
## 1:             NA            NA                  NA            NA
## 2:             NA            NA                  NA            NA

flights[2:1] # second and first row

##    YEAR MONTH DAY DAY_OF_WEEK AIRLINE FLIGHT_NUMBER TAIL_NUMBER
## 1: 2015     1   1           4      AA          2336      N3KUAA
## 2: 2015     1   1           4      AS            98      N407AS
##    ORIGIN_AIRPORT DESTINATION_AIRPORT SCHEDULED_DEPARTURE DEPARTURE_TIME
## 1:            LAX                 PBI                  10              2
## 2:            ANC                 SEA                   5           2354
##    DEPARTURE_DELAY TAXI_OUT WHEELS_OFF SCHEDULED_TIME ELAPSED_TIME
## 1:              -8       12         14            280          279
## 2:             -11       21         15            205          194
##    AIR_TIME DISTANCE WHEELS_ON TAXI_IN SCHEDULED_ARRIVAL ARRIVAL_TIME
## 1:      263     2330       737       4               750          741
## 2:      169     1448       404       4               430          408
##    ARRIVAL_DELAY DIVERTED CANCELLED CANCELLATION_REASON AIR_SYSTEM_DELAY
## 1:            -9        0         0                                   NA
## 2:           -22        0         0                                   NA
##    SECURITY_DELAY AIRLINE_DELAY LATE_AIRCRAFT_DELAY WEATHER_DELAY
## 1:             NA            NA                  NA            NA
## 2:             NA            NA                  NA            NA

• Logical expressions

flights[MONTH == 1]
flights[MONTH == 1 & DISTANCE > 1000]

• identical() function

identical(flights[-1:-100000], flights[!1:100000])

## [1] TRUE

• order(): optimized within a data.table

• makes sorting a cinch
• sort by one column and then another
• minus (‘-’) means descending order

flights[order(TAIL_NUMBER,-ARRIVAL_DELAY)] %>% head() %>% knitr::kable()

sort by a function on our data

flights[order(substr(ORIGIN_AIRPORT,1,1))] %>% head %>% knitr::kable()

• chain queries together

flights[!is.na(ARRIVAL_DELAY)][order(TAIL_NUMBER,-ARRIVAL_DELAY)] %>% head() %>% knitr::kable()

flights %>%
  .[!is.na(ARRIVAL_DELAY)] %>%
  .[order(TAIL_NUMBER,-ARRIVAL_DELAY)] %>% head() %>% knitr::kable()

2) \(j\)

• computations on columns

# select columns by name in the second index parameter
flights[ , DESTINATION_AIRPORT] %>% head() %>% knitr::kable()           # as a vector

flights[ , list(DESTINATION_AIRPORT)] %>% head() %>% knitr::kable()     # as a data.table

• use list to select multiple columns

flights[ , list(DESTINATION_AIRPORT, ORIGIN_AIRPORT)] %>% head() %>% knitr::kable()

• .() is the same as list() in data.table

flights[ , .(DESTINATION_AIRPORT, ORIGIN_AIRPORT)] %>% head() %>% knitr::kable()

• also computable on columns

flights[ , unique(ORIGIN_AIRPORT)]  %>% head() %>% knitr::kable()

flights[ , sum(AIR_TIME, na.rm = TRUE) / (1e6 * 60)]  %>% head() %>% knitr::kable()

flights[ , summary(as.factor(CANCELLATION_REASON))]  %>% head() %>% knitr::kable()

flights[ , .(AIR_TIME, AIR_TIME/60)] %>% head() %>% knitr::kable()# return multiple columns

• name the items

flights[ , .(AIR_TIME, AIR_TIME_HOURS = AIR_TIME/60)] %>% head() %>% knitr::kable()

Anytime the \(j\) expression returns a list, the whole expression will return a data.table.

3) \(i\) and \(j\)

flights[ARRIVAL_DELAY>0, .N] 

## [1] 2086896

flights[ARRIVAL_DELAY>0, summary(ARRIVAL_DELAY/60)]

##     Min.  1st Qu.   Median     Mean  3rd Qu.     Max. 
##  0.01667  0.10000  0.25000  0.55188  0.63333 32.85000

flights[ARRIVAL_DELAY>0, plot(table(DAY_OF_WEEK))] 

## [1] 1 2 3 4 5 6 7

4) by: grouping

flights[ , .N, by = ORIGIN_AIRPORT] %>% head() %>% knitr::kable()

flights[ , .N, by = ORIGIN_AIRPORT][order(-N)] %>% head() %>% knitr::kable()

flights[CANCELLED == 1, .N, by = ORIGIN_AIRPORT][order(-N)]

##      ORIGIN_AIRPORT    N
##   1:            ORD 8548
##   2:            DFW 6254
##   3:            LGA 4531
##   4:            EWR 3110
##   5:            BOS 2654
##  ---                    
## 521:          13931    1
## 522:          14730    1
## 523:          10158    1
## 524:          11097    1
## 525:          14986    1

flights %>%
  extract(CANCELLED == 1, .N, by = ORIGIN_AIRPORT) %>%
  extract(order(-N))
# Here we use extract, magrittr's alias for [ rather than the .[] notation above

flights[ , .(MinDelay = min(DEPARTURE_DELAY, na.rm = TRUE), 
             MaxDelay = max(DEPARTURE_DELAY, na.rm = TRUE)), 
         by = ORIGIN_AIRPORT][order(MinDelay-MaxDelay)] %>% head() %>% knitr::kable()

5) .SD: a special name for the subset of the data.table for each group

mtcarsDT <- as.data.table(mtcars, keep.rownames = 'Model')
mtcarsDT[ ,unique(cyl)] # we have three groups by cylinder count

## [1] 6 4 8

mtcarsDT[ , print(.SD), by = cyl] # so we have three data.tables that get printed

##             Model  mpg  disp  hp drat    wt  qsec vs am gear carb
## 1:      Mazda RX4 21.0 160.0 110 3.90 2.620 16.46  0  1    4    4
## 2:  Mazda RX4 Wag 21.0 160.0 110 3.90 2.875 17.02  0  1    4    4
## 3: Hornet 4 Drive 21.4 258.0 110 3.08 3.215 19.44  1  0    3    1
## 4:        Valiant 18.1 225.0 105 2.76 3.460 20.22  1  0    3    1
## 5:       Merc 280 19.2 167.6 123 3.92 3.440 18.30  1  0    4    4
## 6:      Merc 280C 17.8 167.6 123 3.92 3.440 18.90  1  0    4    4
## 7:   Ferrari Dino 19.7 145.0 175 3.62 2.770 15.50  0  1    5    6
##              Model  mpg  disp  hp drat    wt  qsec vs am gear carb
##  1:     Datsun 710 22.8 108.0  93 3.85 2.320 18.61  1  1    4    1
##  2:      Merc 240D 24.4 146.7  62 3.69 3.190 20.00  1  0    4    2
##  3:       Merc 230 22.8 140.8  95 3.92 3.150 22.90  1  0    4    2
##  4:       Fiat 128 32.4  78.7  66 4.08 2.200 19.47  1  1    4    1
##  5:    Honda Civic 30.4  75.7  52 4.93 1.615 18.52  1  1    4    2
##  6: Toyota Corolla 33.9  71.1  65 4.22 1.835 19.90  1  1    4    1
##  7:  Toyota Corona 21.5 120.1  97 3.70 2.465 20.01  1  0    3    1
##  8:      Fiat X1-9 27.3  79.0  66 4.08 1.935 18.90  1  1    4    1
##  9:  Porsche 914-2 26.0 120.3  91 4.43 2.140 16.70  0  1    5    2
## 10:   Lotus Europa 30.4  95.1 113 3.77 1.513 16.90  1  1    5    2
## 11:     Volvo 142E 21.4 121.0 109 4.11 2.780 18.60  1  1    4    2
##                   Model  mpg  disp  hp drat    wt  qsec vs am gear carb
##  1:   Hornet Sportabout 18.7 360.0 175 3.15 3.440 17.02  0  0    3    2
##  2:          Duster 360 14.3 360.0 245 3.21 3.570 15.84  0  0    3    4
##  3:          Merc 450SE 16.4 275.8 180 3.07 4.070 17.40  0  0    3    3
##  4:          Merc 450SL 17.3 275.8 180 3.07 3.730 17.60  0  0    3    3
##  5:         Merc 450SLC 15.2 275.8 180 3.07 3.780 18.00  0  0    3    3
##  6:  Cadillac Fleetwood 10.4 472.0 205 2.93 5.250 17.98  0  0    3    4
##  7: Lincoln Continental 10.4 460.0 215 3.00 5.424 17.82  0  0    3    4
##  8:   Chrysler Imperial 14.7 440.0 230 3.23 5.345 17.42  0  0    3    4
##  9:    Dodge Challenger 15.5 318.0 150 2.76 3.520 16.87  0  0    3    2
## 10:         AMC Javelin 15.2 304.0 150 3.15 3.435 17.30  0  0    3    2
## 11:          Camaro Z28 13.3 350.0 245 3.73 3.840 15.41  0  0    3    4
## 12:    Pontiac Firebird 19.2 400.0 175 3.08 3.845 17.05  0  0    3    2
## 13:      Ford Pantera L 15.8 351.0 264 4.22 3.170 14.50  0  1    5    4
## 14:       Maserati Bora 15.0 301.0 335 3.54 3.570 14.60  0  1    5    8

## Empty data.table (0 rows and 1 cols): cyl

Ref: https://www.youtube.com/watch?v=L6aT_oEhIKo

flights[ , .SD[1:2], by = .(YEAR,MONTH,DAY)] %>% head() %>% knitr::kable()

flights[ , .SD[sample(1:.N, 100)], by = .(YEAR, MONTH,DAY)] %>% head() %>% knitr::kable()

flights[ , .(PctCancelled = mean(CANCELLED) %>% round(., 3)), by = ORIGIN_AIRPORT][order(-PctCancelled)] %>% head() %>% knitr::kable()

flights[ , .SD[.N >= 10000], by = ORIGIN_AIRPORT] %>% head() %>% knitr::kable()

flights[ , .SD[.N < 10000], by = ORIGIN_AIRPORT][ , unique(ORIGIN_AIRPORT)] %>% head()

## [1] "FAI" "PBG" "IAG" "PSE" "BQN" "GEG"

6) Assignment

• there is an assignment operator that modifies in place (by reference)
• jargon translation: it will alter the specific object you use it on

• Create a date column that’s in date format

flights[ , Date := ymd(YEAR*10000+MONTH*100+DAY)] %>% head() %>% knitr::kable()

• Reformat the dataset into boolean format

flights[ , CANCELLED := ifelse(CANCELLED == 1, TRUE, FALSE)]

• Create new columns

flights[ , AvgSpeed := DISTANCE / (AIR_TIME/60)]

• Update the data.table selectively

flights[ CANCELLATION_REASON2 == 'A', CANCELLATION_REASON := 'Airline' ]
flights[ CANCELLATION_REASON2 == 'B', CANCELLATION_REASON := 'Weather'] 
flights[ CANCELLATION_REASON2 == 'C', CANCELLATION_REASON := 'National Air System'] 
flights[ CANCELLATION_REASON2 == 'D', CANCELLATION_REASON := 'Security'] 
flights[ CANCELLATION_REASON2 == '', CANCELLATION_REASON := NA] 

flights[!is.na(CANCELLATION_REASON2), .N, by = CANCELLATION_REASON2] %>%
    ggplot(aes(x = CANCELLATION_REASON2, y = N)) +
    geom_histogram(stat = 'identity')

[As a function]

cancelReasonUpdate <- function(inStr){
  switch(inStr,
         "A" = "Airline",
         "B" = "Weather",
         "C" = "National Air System",
         "D" = "Security",
               NA)
}

flights[ , CANCELLATION_REASON2 := map_chr(CANCELLATION_REASON,cancelReasonUpdate)]

• Delete columns

flights[ , CANCELLATION_REASON2 := NULL]

• Grouping

flights[ , MaxAvgSpeed := max(AvgSpeed, na.rm = TRUE), by = .(ORIGIN_AIRPORT, DESTINATION_AIRPORT)]

note that the group level statistic is now added to each observation

6) join

• Load the airports dataset

airports <- fread("Data/airports.csv")
airports %>% head() %>% knitr::kable()

merge(x = flights,
      y = airports,
      by.x = 'ORIGIN_AIRPORT',
      by.y = 'IATA_CODE') %>% head() %>% knitr::kable()

flights[airports, on = c(ORIGIN_AIRPORT = 'IATA_CODE')] %>% head() %>% knitr::kable()

flights[airports, on = "ORIGIN_AIRPORT == IATA_CODE"] %>% head() %>% knitr::kable()

flights[airports, on = c(ORIGIN_AIRPORT = 'IATA_CODE'), nomatch = 0] %>% head() %>% knitr::kable()

airports[!flights, on = "IATA_CODE == ORIGIN_AIRPORT"] %>% head() %>% knitr::kable()