Meetup 13: Big Data and data.table

George I. Hagstrom

Pre-Thanksgiving Update

Entering Course Home Stretch
Assignments Remaining:
- tidyverse extend due this Sunday
- Final Lab due December 7th
- Final Real Meetup December 1st (do you want an extra lecture on something?)
- Final Project: Record Video Presentation and upload to Brightspace

nyhackr meetup this Thursday!

nyhackr.org

Computer Architecture Basics

Hierarchical Memory
Cache: Small and Fast
RAM: Medium Size and Speed
Disk: Huge and Slow

Memory Limited Tasks

Data processing and manipulation tasks are inherently memory/memory speed limited
Reading and writing files are also memory limited
Some data analysis tasks are (but not the topic here)
Start caring when your dataset ~1GB (or if things run slowly)

`dplyr` tradeoffs

“We optimize dplyr for expressiveness on medium data; feel free to use data.table for raw speed on bigger data”

https://h2oai.github.io/db-benchmark/

Fast R packages

`data.table` summary

data.table enhances the base R data.frame
Designed for high speed with large datasets
Style is polar opposite of the tidyverse:
- Few functions
- Very concise code
- Very stable syntax
dtplyr and tidytable are two “tidy style” backends to data.table

`data.table` top features

data.table queries executed together
data.table := operator supports passing by reference
data.table supports reindexing which allows for extremely fast searches and subsetting

Passing by reference and indexing are subtler and more advanced computing topics, won’t get to them here and will instead introudce them in a coding vignette
Also considering making a vignette on basic code benchmarking

Creating a Data Table:

using data.table():

library(data.table)

DT = data.table(
  ID = c("b","b","b","a","a","c"),
  a = 1:6,
  b = 7:12,
  c = 13:18
)
DT

       ID     a     b     c
   <char> <int> <int> <int>
1:      b     1     7    13
2:      b     2     8    14
3:      b     3     9    15
4:      a     4    10    16
5:      a     5    11    17
6:      c     6    12    18

Creating a Data Table:

using data.table():

DT = data.table(
  ID = c("b","b","b","a","a","c"),
  a = 1:6,
  b = 7:12,
  c = 13:18
)
DT

       ID     a     b     c
   <char> <int> <int> <int>
1:      b     1     7    13
2:      b     2     8    14
3:      b     3     9    15
4:      a     4    10    16
5:      a     5    11    17
6:      c     6    12    18

Creating a Data Table

Using fread

flights = fread("/home/georgehagstrom/work/Teaching/DATA607/website/meetups/Meetup13/flights14.csv")

flights

         year month   day dep_delay arr_delay carrier origin   dest air_time
        <int> <int> <int>     <int>     <int>  <char> <char> <char>    <int>
     1:  2014     1     1        14        13      AA    JFK    LAX      359
     2:  2014     1     1        -3        13      AA    JFK    LAX      363
     3:  2014     1     1         2         9      AA    JFK    LAX      351
     4:  2014     1     1        -8       -26      AA    LGA    PBI      157
     5:  2014     1     1         2         1      AA    JFK    LAX      350
    ---                                                                     
253312:  2014    10    31         1       -30      UA    LGA    IAH      201
253313:  2014    10    31        -5       -14      UA    EWR    IAH      189
253314:  2014    10    31        -8        16      MQ    LGA    RDU       83
253315:  2014    10    31        -4        15      MQ    LGA    DTW       75
253316:  2014    10    31        -5         1      MQ    LGA    SDF      110
        distance  hour
           <int> <int>
     1:     2475     9
     2:     2475    11
     3:     2475    19
     4:     1035     7
     5:     2475    13
    ---               
253312:     1416    14
253313:     1400     8
253314:      431    11
253315:      502    11
253316:      659     8

Creating a Data Table

Coercing a data frame, tibble, list, matrix, database etc:

data_cars = mtcars |> data.table()
data_cars

      mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
    <num> <num> <num> <num> <num> <num> <num> <num> <num> <num> <num>
 1:  21.0     6 160.0   110  3.90 2.620 16.46     0     1     4     4
 2:  21.0     6 160.0   110  3.90 2.875 17.02     0     1     4     4
 3:  22.8     4 108.0    93  3.85 2.320 18.61     1     1     4     1
 4:  21.4     6 258.0   110  3.08 3.215 19.44     1     0     3     1
 5:  18.7     8 360.0   175  3.15 3.440 17.02     0     0     3     2
 6:  18.1     6 225.0   105  2.76 3.460 20.22     1     0     3     1
 7:  14.3     8 360.0   245  3.21 3.570 15.84     0     0     3     4
 8:  24.4     4 146.7    62  3.69 3.190 20.00     1     0     4     2
 9:  22.8     4 140.8    95  3.92 3.150 22.90     1     0     4     2
10:  19.2     6 167.6   123  3.92 3.440 18.30     1     0     4     4
11:  17.8     6 167.6   123  3.92 3.440 18.90     1     0     4     4
12:  16.4     8 275.8   180  3.07 4.070 17.40     0     0     3     3
13:  17.3     8 275.8   180  3.07 3.730 17.60     0     0     3     3
14:  15.2     8 275.8   180  3.07 3.780 18.00     0     0     3     3
15:  10.4     8 472.0   205  2.93 5.250 17.98     0     0     3     4
16:  10.4     8 460.0   215  3.00 5.424 17.82     0     0     3     4
17:  14.7     8 440.0   230  3.23 5.345 17.42     0     0     3     4
18:  32.4     4  78.7    66  4.08 2.200 19.47     1     1     4     1
19:  30.4     4  75.7    52  4.93 1.615 18.52     1     1     4     2
20:  33.9     4  71.1    65  4.22 1.835 19.90     1     1     4     1
21:  21.5     4 120.1    97  3.70 2.465 20.01     1     0     3     1
22:  15.5     8 318.0   150  2.76 3.520 16.87     0     0     3     2
23:  15.2     8 304.0   150  3.15 3.435 17.30     0     0     3     2
24:  13.3     8 350.0   245  3.73 3.840 15.41     0     0     3     4
25:  19.2     8 400.0   175  3.08 3.845 17.05     0     0     3     2
26:  27.3     4  79.0    66  4.08 1.935 18.90     1     1     4     1
27:  26.0     4 120.3    91  4.43 2.140 16.70     0     1     5     2
28:  30.4     4  95.1   113  3.77 1.513 16.90     1     1     5     2
29:  15.8     8 351.0   264  4.22 3.170 14.50     0     1     5     4
30:  19.7     6 145.0   175  3.62 2.770 15.50     0     1     5     6
31:  15.0     8 301.0   335  3.54 3.570 14.60     0     1     5     8
32:  21.4     4 121.0   109  4.11 2.780 18.60     1     1     4     2
      mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb

Basic Syntax

Query data tables using square brackets:

DT[ i   ,  j   ,   by   ]

Code replaces each of i, j, and by
i: code for logical subsetting
j: code for transformation and aggregation
by: code for grouping

Subset Example

i: subset by index/key or logical conditions

 flights[1:20,]

     year month   day dep_delay arr_delay carrier origin   dest air_time
    <int> <int> <int>     <int>     <int>  <char> <char> <char>    <int>
 1:  2014     1     1        14        13      AA    JFK    LAX      359
 2:  2014     1     1        -3        13      AA    JFK    LAX      363
 3:  2014     1     1         2         9      AA    JFK    LAX      351
 4:  2014     1     1        -8       -26      AA    LGA    PBI      157
 5:  2014     1     1         2         1      AA    JFK    LAX      350
 6:  2014     1     1         4         0      AA    EWR    LAX      339
 7:  2014     1     1        -2       -18      AA    JFK    LAX      338
 8:  2014     1     1        -3       -14      AA    JFK    LAX      356
 9:  2014     1     1        -1       -17      AA    JFK    MIA      161
10:  2014     1     1        -2       -14      AA    JFK    SEA      349
11:  2014     1     1        -5       -17      AA    EWR    MIA      161
12:  2014     1     1         7        -5      AA    JFK    SFO      365
13:  2014     1     1         3         1      AA    JFK    BOS       39
14:  2014     1     1       142       133      AA    JFK    LAX      345
15:  2014     1     1        -5       -26      AA    JFK    BOS       35
16:  2014     1     1        18        69      AA    JFK    ORD      155
17:  2014     1     1        25        36      AA    JFK    IAH      234
18:  2014     1     1        -1         1      AA    JFK    AUS      232
19:  2014     1     1       191       185      AA    EWR    DFW      214
20:  2014     1     1        -7        -6      AA    LGA    ORD      142
     year month   day dep_delay arr_delay carrier origin   dest air_time
    distance  hour
       <int> <int>
 1:     2475     9
 2:     2475    11
 3:     2475    19
 4:     1035     7
 5:     2475    13
 6:     2454    18
 7:     2475    21
 8:     2475    15
 9:     1089    15
10:     2422    18
11:     1085    16
12:     2586    17
13:      187    12
14:     2475    19
15:      187    17
16:      740    17
17:     1417    16
18:     1521    17
19:     1372    16
20:      733     5
    distance  hour

Subset Example

i: subset by index/key or logical conditions

 flights[origin == "JFK" & dest =="ORD" & arr_delay >60, ][1:20]

     year month   day dep_delay arr_delay carrier origin   dest air_time
    <int> <int> <int>     <int>     <int>  <char> <char> <char>    <int>
 1:  2014     1     1        18        69      AA    JFK    ORD      155
 2:  2014     1     2        82       113      AA    JFK    ORD      120
 3:  2014     1     2        41       107      B6    JFK    ORD      136
 4:  2014     1     3       150       173      B6    JFK    ORD      122
 5:  2014     1     3       351       378      B6    JFK    ORD      123
 6:  2014     1     4       121       147      B6    JFK    ORD      126
 7:  2014     1     5        99        70      AA    JFK    ORD      119
 8:  2014     1     5       269       248      B6    JFK    ORD      122
 9:  2014     1     6        96        74      AA    JFK    ORD      124
10:  2014     1     7        65        63      B6    JFK    ORD      132
11:  2014     1    10       113       105      AA    JFK    ORD      139
12:  2014     1    11       354       326      AA    JFK    ORD      111
13:  2014     1    11       241       230      B6    JFK    ORD      113
14:  2014     1    15       116        88      B6    JFK    ORD      115
15:  2014     1    20        53        61      B6    JFK    ORD      132
16:  2014     1    24        89        87      AA    JFK    ORD      143
17:  2014     1    25        74        69      AA    JFK    ORD      120
18:  2014     1    26        58        62      B6    JFK    ORD      137
19:  2014     2     3       300       326      AA    JFK    ORD      146
20:  2014     2     9        -9        74      B6    JFK    ORD      130
     year month   day dep_delay arr_delay carrier origin   dest air_time
    distance  hour
       <int> <int>
 1:      740    17
 2:      740    18
 3:      740     7
 4:      740    23
 5:      740    12
 6:      740     9
 7:      740    18
 8:      740     1
 9:      740    18
10:      740    21
11:      740    19
12:      740    23
13:      740     0
14:      740    22
15:      740     7
16:      740    18
17:      740    18
18:      740    21
19:      740    22
20:      740    19
    distance  hour

Sort Example

Can also rearrange data.table using the order() function
Automatically uses data.table fast sort

flights[order(origin, -dest)] |> 
  head()

    year month   day dep_delay arr_delay carrier origin   dest air_time
   <int> <int> <int>     <int>     <int>  <char> <char> <char>    <int>
1:  2014     1     5         6        49      EV    EWR    XNA      195
2:  2014     1     6         7        13      EV    EWR    XNA      190
3:  2014     1     7        -6       -13      EV    EWR    XNA      179
4:  2014     1     8        -7       -12      EV    EWR    XNA      184
5:  2014     1     9        16         7      EV    EWR    XNA      181
6:  2014     1    13        66        66      EV    EWR    XNA      188
   distance  hour
      <int> <int>
1:     1131     8
2:     1131     8
3:     1131     8
4:     1131     8
5:     1131     8
6:     1131     9

Selecting Columns

Can choose a subset of columns in the second argument of []

flights[ , air_time ] |> 
  head()

[1] 359 363 351 157 350 339

Not “tidy” by default

Selecting Columns

Use list to select multiple columns and return data table:

flights[, list(arr_delay,air_time)] |> 
  head()

   arr_delay air_time
       <int>    <int>
1:        13      359
2:        13      363
3:         9      351
4:       -26      157
5:         1      350
6:         0      339

`list` alias `.()`

Can use . instead of list

flights[ , .(origin,dest,dep_delay,arr_delay,air_time)] |> 
  head()

   origin   dest dep_delay arr_delay air_time
   <char> <char>     <int>     <int>    <int>
1:    JFK    LAX        14        13      359
2:    JFK    LAX        -3        13      363
3:    JFK    LAX         2         9      351
4:    LGA    PBI        -8       -26      157
5:    JFK    LAX         2         1      350
6:    EWR    LAX         4         0      339

Column select alias `..()`

Can also select using a vector with .. notation:

sel = c("origin","dest","dep_delay","arr_delay","air_time")

flights[ , ..sel ] |> head()

   origin   dest dep_delay arr_delay air_time
   <char> <char>     <int>     <int>    <int>
1:    JFK    LAX        14        13      359
2:    JFK    LAX        -3        13      363
3:    JFK    LAX         2         9      351
4:    LGA    PBI        -8       -26      157
5:    JFK    LAX         2         1      350
6:    EWR    LAX         4         0      339

Logical selection

Can select range of columns:

flights[ , year:carrier  ] |> head()

    year month   day dep_delay arr_delay carrier
   <int> <int> <int>     <int>     <int>  <char>
1:  2014     1     1        14        13      AA
2:  2014     1     1        -3        13      AA
3:  2014     1     1         2         9      AA
4:  2014     1     1        -8       -26      AA
5:  2014     1     1         2         1      AA
6:  2014     1     1         4         0      AA

Logical Selection

Can use ‘!’ to exclude columns

   origin   dest air_time distance  hour
   <char> <char>    <int>    <int> <int>
1:    JFK    LAX      359     2475     9
2:    JFK    LAX      363     2475    11
3:    JFK    LAX      351     2475    19
4:    LGA    PBI      157     1035     7
5:    JFK    LAX      350     2475    13
6:    EWR    LAX      339     2454    18

Rename

Can rename columns when you select just like you name columns in a list

flights[ , list(
  arrival_delay = arr_delay, 
  departure_delay = dep_delay, 
  time_in_air = air_time
  )] |> 
  head()

   arrival_delay departure_delay time_in_air
           <int>           <int>       <int>
1:            13              14         359
2:            13              -3         363
3:             9               2         351
4:           -26              -8         157
5:             1               2         350
6:             0               4         339

Calculations

Can perform mathematical operations in the j/2nd entry
How many flights with no delay:

flights[ , sum( arr_delay + dep_delay < 0  )   ]

[1] 141814

You can combine sorting/subsetting in i with selection and computation in j

Calculations

Efficient because calculations done together, unnecessary options are avoided

flights[origin == "LGA" & month == 1 , 
        sum( arr_delay + dep_delay < 0  )   ]

[1] 3671

Do not need to subset the entire data.table, just the arr_delay and dep_delay rows

Counting

.N is an alias for counting rows

flights[origin == "LGA" & month == 1 & (arr_delay + dep_delay < 0), 
       .N  ]

[1] 3671

Note that computation can also take place in the i column

Aggregation

Third column by is for aggregation (group_by)
How many trips from each airport?

flights[ , .N, by = origin]

   origin     N
   <char> <int>
1:    JFK 81483
2:    LGA 84433
3:    EWR 87400

Aggregation

Need . alias for multiple entries:

flights[ carrier %in% c("DL","AA") , 
         .(number = .N, mean_dep_delay = mean(dep_delay)) ,
         by = .(origin,dest)    ] |> 
  head()

   origin   dest number mean_dep_delay
   <char> <char>  <int>          <num>
1:    JFK    LAX   5594       8.845370
2:    LGA    PBI   1594       7.720828
3:    EWR    LAX     62       4.870968
4:    JFK    MIA   2750      10.008364
5:    JFK    SEA   1376      11.578488
6:    EWR    MIA    848       7.503538

Sorting Groups

keyby lets you resort by groups:
Creates a key

flights[ carrier %in% c("DL","AA") , 
         .(number = .N, mean_dep_delay = mean(dep_delay)) ,
         keyby = .(origin,dest)    ] |> 
  head()

Key: <origin, dest>
   origin   dest number mean_dep_delay
   <char> <char>  <int>          <num>
1:    EWR    ATL   2966      17.108227
2:    EWR    DFW   1618      17.715698
3:    EWR    DTW    568       5.357394
4:    EWR    LAX     62       4.870968
5:    EWR    MIA    848       7.503538
6:    EWR    MSP    334      10.164671

Chaining Expressions

What if you want to perform a series of operations that don’t neatly fit in a single bracket?
Can chain together multiple brackets
Suppose we want to get number of trips for each origin dest pair but have a different order for origin and dest?

flights[carrier %in% c("DL","AA","UA"),
        .N,
        by = .(origin, dest)][order(origin,-dest)] |> 
  head(10)

    origin   dest     N
    <char> <char> <int>
 1:    EWR    TPA  1539
 2:    EWR    STT   174
 3:    EWR    SNA   657
 4:    EWR    SLC   285
 5:    EWR    SJU   608
 6:    EWR    SFO  3725
 7:    EWR    SEA   787
 8:    EWR    SDF     2
 9:    EWR    SAT   205
10:    EWR    SAN   884

Group By Expressions

Can include expressions in the by column
Group by whether the flights arrived or departed late

flights[ , .N, by = .(origin, dep_delay > 0, arr_delay > 0)][
  order(origin,dep_delay,arr_delay)]

    origin dep_delay arr_delay     N
    <char>    <lgcl>    <lgcl> <int>
 1:    EWR     FALSE     FALSE 38082
 2:    EWR     FALSE      TRUE  9298
 3:    EWR      TRUE     FALSE 11265
 4:    EWR      TRUE      TRUE 28755
 5:    JFK     FALSE     FALSE 38631
 6:    JFK     FALSE      TRUE 12749
 7:    JFK      TRUE     FALSE  8216
 8:    JFK      TRUE      TRUE 21887
 9:    LGA     FALSE     FALSE 42591
10:    LGA     FALSE      TRUE 12536
11:    LGA      TRUE     FALSE  7112
12:    LGA      TRUE      TRUE 22194

Applying functions across columns

.SD in the j column stands for the data for each group in .by
Use lapply similar to how we used across
.SDcols argument after by

flights[ , lapply(.SD, mean), by = .(origin, dest, month)
         , .SDcols = c("arr_delay","dep_delay")  ]

      origin   dest month  arr_delay dep_delay
      <char> <char> <int>      <num>     <num>
   1:    JFK    LAX     1  11.342956 20.521940
   2:    LGA    PBI     1  23.000000 21.382143
   3:    EWR    LAX     1   7.906494 18.745455
   4:    JFK    MIA     1  20.454902 22.505882
   5:    JFK    SEA     1  15.101449 27.253623
  ---                                         
1913:    EWR    SAT    10 -15.500000  9.333333
1914:    EWR    BTV    10  -2.000000 -3.800000
1915:    EWR    ORF    10   6.833333 12.000000
1916:    LGA    DAL    10 -16.000000 -6.266667
1917:    LGA    CVG    10 -11.333333 -5.666667

Applying functions across columns

.SD in the j column stands for the data for each group in .by
Use lapply similar to how we used across
`.SDcols can use logic to select columns

flights[ , lapply(.SD, mean), by = .(origin, dest, month)
         , .SDcols = is.numeric  ]

      origin   dest month  year month      day dep_delay  arr_delay  air_time
      <char> <char> <int> <num> <num>    <num>     <num>      <num>     <num>
   1:    JFK    LAX     1  2014     1 16.28984 20.521940  11.342956 339.16513
   2:    LGA    PBI     1  2014     1 16.13929 21.382143  23.000000 159.17500
   3:    EWR    LAX     1  2014     1 15.70649 18.745455   7.906494 335.64416
   4:    JFK    MIA     1  2014     1 16.18824 22.505882  20.454902 163.15294
   5:    JFK    SEA     1  2014     1 16.34783 27.253623  15.101449 336.10870
  ---                                                                        
1913:    EWR    SAT    10  2014    10 28.50000  9.333333 -15.500000 214.66667
1914:    EWR    BTV    10  2014    10 29.00000 -3.800000  -2.000000  46.20000
1915:    EWR    ORF    10  2014    10 29.33333 12.000000   6.833333  50.16667
1916:    LGA    DAL    10  2014    10 29.60000 -6.266667 -16.000000 196.60000
1917:    LGA    CVG    10  2014    10 30.00000 -5.666667 -11.333333  97.33333
      distance      hour
         <num>     <num>
   1:     2475 13.547344
   2:     1035 12.285714
   3:     2454 13.911688
   4:     1089 11.501961
   5:     2422 15.260870
  ---                   
1913:     1569 18.166667
1914:      266  8.000000
1915:      284  9.833333
1916:     1381 12.933333
1917:      585 13.000000

Applying functions across columns

.SD in the j column stands for the data for each group in .by
Use lapply similar to how we used across
`.SDcols can use logic to select columns

flights[ , lapply(.SD, mean), by = .(origin, dest, month)
         , .SDcols = patterns("delay")  ]

      origin   dest month dep_delay  arr_delay
      <char> <char> <int>     <num>      <num>
   1:    JFK    LAX     1 20.521940  11.342956
   2:    LGA    PBI     1 21.382143  23.000000
   3:    EWR    LAX     1 18.745455   7.906494
   4:    JFK    MIA     1 22.505882  20.454902
   5:    JFK    SEA     1 27.253623  15.101449
  ---                                         
1913:    EWR    SAT    10  9.333333 -15.500000
1914:    EWR    BTV    10 -3.800000  -2.000000
1915:    EWR    ORF    10 12.000000   6.833333
1916:    LGA    DAL    10 -6.266667 -16.000000
1917:    LGA    CVG    10 -5.666667 -11.333333

Joins

Two ways to do joins, put one DT in the i column of a bracket
Using merge
Define “holiday” DT (hat tip Toby Hocking)

holiday = data.table(month = c(1,7,11,12),
                     day = c(1,4,27,25),
                     holiday = c("NYE",
                                 "Independence_Day",
                                 "Thanksgiving",
                                 "Christmas"))

holiday

   month   day          holiday
   <num> <num>           <char>
1:     1     1              NYE
2:     7     4 Independence_Day
3:    11    27     Thanksgiving
4:    12    25        Christmas

Right Join

DT1[DT2, on = c(keys)] does a Right Join

flights[holiday, on = c("month","day")]

       year month   day dep_delay arr_delay carrier origin   dest air_time
      <int> <int> <int>     <int>     <int>  <char> <char> <char>    <int>
   1:  2014     1     1        14        13      AA    JFK    LAX      359
   2:  2014     1     1        -3        13      AA    JFK    LAX      363
   3:  2014     1     1         2         9      AA    JFK    LAX      351
   4:  2014     1     1        -8       -26      AA    LGA    PBI      157
   5:  2014     1     1         2         1      AA    JFK    LAX      350
  ---                                                                     
1389:  2014     7     4        -7       -18      US    LGA    DCA       43
1390:  2014     7     4        -6       -12      US    LGA    DCA       43
1391:  2014     7     4        -7       -18      US    LGA    DCA       42
1392:    NA    11    27        NA        NA    <NA>   <NA>   <NA>       NA
1393:    NA    12    25        NA        NA    <NA>   <NA>   <NA>       NA
      distance  hour          holiday
         <int> <int>           <char>
   1:     2475     9              NYE
   2:     2475    11              NYE
   3:     2475    19              NYE
   4:     1035     7              NYE
   5:     2475    13              NYE
  ---                                
1389:      214     8 Independence_Day
1390:      214    10 Independence_Day
1391:      214    12 Independence_Day
1392:       NA    NA     Thanksgiving
1393:       NA    NA        Christmas

Left Join

Reverse the arguments for left join

holiday[flights, on = c("month","day")]

        month   day holiday  year dep_delay arr_delay carrier origin   dest
        <int> <int>  <char> <int>     <int>     <int>  <char> <char> <char>
     1:     1     1     NYE  2014        14        13      AA    JFK    LAX
     2:     1     1     NYE  2014        -3        13      AA    JFK    LAX
     3:     1     1     NYE  2014         2         9      AA    JFK    LAX
     4:     1     1     NYE  2014        -8       -26      AA    LGA    PBI
     5:     1     1     NYE  2014         2         1      AA    JFK    LAX
    ---                                                                    
253312:    10    31    <NA>  2014         1       -30      UA    LGA    IAH
253313:    10    31    <NA>  2014        -5       -14      UA    EWR    IAH
253314:    10    31    <NA>  2014        -8        16      MQ    LGA    RDU
253315:    10    31    <NA>  2014        -4        15      MQ    LGA    DTW
253316:    10    31    <NA>  2014        -5         1      MQ    LGA    SDF
        air_time distance  hour
           <int>    <int> <int>
     1:      359     2475     9
     2:      363     2475    11
     3:      351     2475    19
     4:      157     1035     7
     5:      350     2475    13
    ---                        
253312:      201     1416    14
253313:      189     1400     8
253314:       83      431    11
253315:       75      502    11
253316:      110      659     8

Inner Join

Add nomatch = 0 to make it an inner join ()
Drops all the NA values

holiday[flights, on = c("month","day"), nomatch = 0]

      month   day          holiday  year dep_delay arr_delay carrier origin
      <int> <int>           <char> <int>     <int>     <int>  <char> <char>
   1:     1     1              NYE  2014        14        13      AA    JFK
   2:     1     1              NYE  2014        -3        13      AA    JFK
   3:     1     1              NYE  2014         2         9      AA    JFK
   4:     1     1              NYE  2014        -8       -26      AA    LGA
   5:     1     1              NYE  2014         2         1      AA    JFK
  ---                                                                      
1387:     7     4 Independence_Day  2014        -4         7      US    EWR
1388:     7     4 Independence_Day  2014        -3        -7      US    JFK
1389:     7     4 Independence_Day  2014        -7       -18      US    LGA
1390:     7     4 Independence_Day  2014        -6       -12      US    LGA
1391:     7     4 Independence_Day  2014        -7       -18      US    LGA
        dest air_time distance  hour
      <char>    <int>    <int> <int>
   1:    LAX      359     2475     9
   2:    LAX      363     2475    11
   3:    LAX      351     2475    19
   4:    PBI      157     1035     7
   5:    LAX      350     2475    13
  ---                               
1387:    CLT       89      529    13
1388:    CLT       86      541    11
1389:    DCA       43      214     8
1390:    DCA       43      214    10
1391:    DCA       42      214    12

Full Join

Several ways, but merge with all = TRUE works
Can use DT1[DT2] notation but clunkier

merge(flights,holiday,all = TRUE)

Key: <month, day>
        month   day  year dep_delay arr_delay carrier origin   dest air_time
        <num> <num> <int>     <int>     <int>  <char> <char> <char>    <int>
     1:     1     1  2014        14        13      AA    JFK    LAX      359
     2:     1     1  2014        -3        13      AA    JFK    LAX      363
     3:     1     1  2014         2         9      AA    JFK    LAX      351
     4:     1     1  2014        -8       -26      AA    LGA    PBI      157
     5:     1     1  2014         2         1      AA    JFK    LAX      350
    ---                                                                     
253314:    10    31  2014        -8        16      MQ    LGA    RDU       83
253315:    10    31  2014        -4        15      MQ    LGA    DTW       75
253316:    10    31  2014        -5         1      MQ    LGA    SDF      110
253317:    11    27    NA        NA        NA    <NA>   <NA>   <NA>       NA
253318:    12    25    NA        NA        NA    <NA>   <NA>   <NA>       NA
        distance  hour      holiday
           <int> <int>       <char>
     1:     2475     9          NYE
     2:     2475    11          NYE
     3:     2475    19          NYE
     4:     1035     7          NYE
     5:     2475    13          NYE
    ---                            
253314:      431    11         <NA>
253315:      502    11         <NA>
253316:      659     8         <NA>
253317:       NA    NA Thanksgiving
253318:       NA    NA    Christmas

Anti Join

Use DT[!DT2, ...] for an anti-join

flights[!holiday, on = c("month","day")]

         year month   day dep_delay arr_delay carrier origin   dest air_time
        <int> <int> <int>     <int>     <int>  <char> <char> <char>    <int>
     1:  2014     1     2        -3         1      AA    JFK    LAX      340
     2:  2014     1     2        -3         9      AA    JFK    LAX      345
     3:  2014     1     2       109       150      AA    JFK    LAX      316
     4:  2014     1     2        83       103      AA    LGA    PBI      169
     5:  2014     1     2         8        34      AA    JFK    LAX      344
    ---                                                                     
251921:  2014    10    31         1       -30      UA    LGA    IAH      201
251922:  2014    10    31        -5       -14      UA    EWR    IAH      189
251923:  2014    10    31        -8        16      MQ    LGA    RDU       83
251924:  2014    10    31        -4        15      MQ    LGA    DTW       75
251925:  2014    10    31        -5         1      MQ    LGA    SDF      110
        distance  hour
           <int> <int>
     1:     2475     8
     2:     2475    11
     3:     2475    20
     4:     1035     8
     5:     2475     7
    ---               
251921:     1416    14
251922:     1400     8
251923:      431    11
251924:      502    11
251925:      659     8

Semi Join

A little clunky, need to use na.omit and which

flights[na.omit(flights[holiday, on = c("month","day"),which = TRUE])]

       year month   day dep_delay arr_delay carrier origin   dest air_time
      <int> <int> <int>     <int>     <int>  <char> <char> <char>    <int>
   1:  2014     1     1        14        13      AA    JFK    LAX      359
   2:  2014     1     1        -3        13      AA    JFK    LAX      363
   3:  2014     1     1         2         9      AA    JFK    LAX      351
   4:  2014     1     1        -8       -26      AA    LGA    PBI      157
   5:  2014     1     1         2         1      AA    JFK    LAX      350
  ---                                                                     
1387:  2014     7     4        -4         7      US    EWR    CLT       89
1388:  2014     7     4        -3        -7      US    JFK    CLT       86
1389:  2014     7     4        -7       -18      US    LGA    DCA       43
1390:  2014     7     4        -6       -12      US    LGA    DCA       43
1391:  2014     7     4        -7       -18      US    LGA    DCA       42
      distance  hour
         <int> <int>
   1:     2475     9
   2:     2475    11
   3:     2475    19
   4:     1035     7
   5:     2475    13
  ---               
1387:      529    13
1388:      541    11
1389:      214     8
1390:      214    10
1391:      214    12

Reflection Meetup

Please fill out the following google form after the meeting or watching the video:

Click Here

Meetup 13: Big Data and data.table

Pre-Thanksgiving Update

nyhackr meetup this Thursday!

Computer Architecture Basics

Memory Limited Tasks

dplyr tradeoffs

Fast R packages

data.table summary

data.table top features

Creating a Data Table:

Creating a Data Table:

Creating a Data Table

Creating a Data Table

Basic Syntax

Subset Example

Subset Example

Sort Example

Selecting Columns

Selecting Columns

list alias .()

Column select alias ..()

Logical selection

Logical Selection

Rename

Calculations

Calculations

Counting

Aggregation

Aggregation

Sorting Groups

Chaining Expressions

Group By Expressions

Applying functions across columns

Applying functions across columns

Applying functions across columns

Joins

Right Join

Left Join

Inner Join

Full Join

Anti Join

Semi Join

Reflection Meetup

Data Science in Context Presentations

`dplyr` tradeoffs

`data.table` summary

`data.table` top features

`list` alias `.()`

Column select alias `..()`