-
Notifications
You must be signed in to change notification settings - Fork 9
/
data-table-intro.R
271 lines (194 loc) · 8.91 KB
/
data-table-intro.R
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
# My default packages
#######################
library(stringr)
library(purrr)
library(magrittr)
library(data.table)
library(lubridate)
library(ggplot2)
library(openxlsx)
library(zoo)
library(xts)
#######################
library(microbenchmark)
# read in the flights data
# https://www.kaggle.com/usdot/flight-delays
# data.table::fread() reads text data fast
flights <- fread("data/flights.csv")
# What is a data.table ?
class(flights)
# It is an extension of the data.frame class
# Subsetting a data.table has a simple syntax
# DT[i, j, by]
#
# i filters rows (analagous to a WHERE clause in SQL)
# j is how you select/compute on columns
# by is where you can group computations
# i ============================================================================
# first index is ALWAYS rows, regardless of commas
identical(flights[2], flights[2,])
# compare to data.frame
as.data.frame(flights)[2] %>% dim
as.data.frame(flights)[2,] %>% dim
# use i to select rows
flights[1:2] # first and second row
flights[2:1] # second and first row
# it can take logical expressions
flights[MONTH == 1]
flights[MONTH == 1 & DISTANCE > 1000]
# and there's also some convenience functions baked in
identical(flights[-1:-100000], flights[!1:100000])
# order() is optimized within a data.table
# makes sorting a cinch
# sort by one column and then another
# (minus means descending)
flights[order(TAIL_NUMBER,-ARRIVAL_DELAY)]
# sort by a function on our data
flights[order(substr(ORIGIN_AIRPORT,1,1))]
# you can chain queries together
flights[!is.na(ARRIVAL_DELAY)][order(TAIL_NUMBER,-ARRIVAL_DELAY)]
# sometimes you'll see chains piped together like this
flights %>%
.[!is.na(ARRIVAL_DELAY)] %>%
.[order(TAIL_NUMBER,-ARRIVAL_DELAY)]
# j ============================================================================
# computations on columns
# select columns by name in the second index parameter
flights[ , DESTINATION_AIRPORT] # as a vector
flights[ , list(DESTINATION_AIRPORT)] # as a data.table
# use list to select multiple columns
flights[ , list(DESTINATION_AIRPORT, ORIGIN_AIRPORT)]
# data.table has lots of convenience functions
# .() is the same as list()
flights[ , .(DESTINATION_AIRPORT, ORIGIN_AIRPORT)]
# you can compute on columns as well
flights[ , unique(ORIGIN_AIRPORT)]
flights[ , sum(AIR_TIME, na.rm = TRUE) / (1e6 * 60)]
flights[ , summary(as.factor(CANCELLATION_REASON))]
flights[ , .(AIR_TIME, AIR_TIME/60)] # return multiple columns
# and since we're pulling things out using a list, you can name the items
flights[ , .(AIR_TIME, AIR_TIME_HOURS = AIR_TIME/60)]
# anytime the j expression returns a list, the whole expression will return
# a data.table
# combining i and j ============================================================
# now we're cooking
# How many flights were delayed touching down?
flights[ARRIVAL_DELAY>0, .N]
# .N is a convenience symbol to get the number of rows in a data.table
# What are some summary statistics of the delayed flights (in hours)?
flights[ARRIVAL_DELAY>0, summary(ARRIVAL_DELAY/60)]
# How many flights were on each day of the week?
flights[ARRIVAL_DELAY>0, plot(table(DAY_OF_WEEK))]
# again, we don't have to return a data.table
# an expression that returns a list in j would,
# but here we want to plot, and data.table
# evaluates the expression correctly
# by ===========================================================================
# grouping
# How many outgoing flights did each aiport have?
flights[ , .N, by = ORIGIN_AIRPORT]
# Let's order it to see the biggest airports
flights[ , .N, by = ORIGIN_AIRPORT][order(-N)]
# Which airports had the most cancelled flights?
flights[CANCELLED == 1, .N, by = ORIGIN_AIRPORT][order(-N)]
# Same thing, but using pipes
# Here we use extract, magrittr's alias for [ rather than the .[] notation above
flights %>%
extract(CANCELLED == 1, .N, by = ORIGIN_AIRPORT) %>%
extract(order(-N))
# What's the min and max departure delay for each airport, sorted by biggest
# difference between the two?
flights[ , .(MinDelay = min(DEPARTURE_DELAY, na.rm = TRUE),
MaxDelay = max(DEPARTURE_DELAY, na.rm = TRUE)),
by = ORIGIN_AIRPORT][order(MinDelay-MaxDelay)]
# Again, order can take functions of the data
# Very versatile!
# .SD ==========================================================================
# .SD is 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
mtcarsDT[ , print(.SD), by = cyl] # so we have three data.tables that get printed
# it is itself a data.table
# meaning it can be operated on!
# https://www.youtube.com/watch?v=L6aT_oEhIKo
# first two rows of flights from each day
flights[ , .SD[1:2], by = .(YEAR,MONTH,DAY)]
# 100 random obs from every day
flights[ , .SD[sample(1:.N, 100)], by = .(YEAR, MONTH,DAY)]
# Pct of flights cancelled by airport
flights %>%
extract( , .(PctCancelled = .SD[CANCELLED == 1,.N] / .N), by = ORIGIN_AIRPORT) %>%
extract(order(-PctCancelled))
# This one can be tricky. Since .SD is a data.table the first .N, being
# inside .SD[], refers to the number of rows of the subgroup
# But we've filtered it to be only the cancelled flights, so the .SD
# expression returns the number of flights cancelled
# we then divide it by the total number of flights in the group (the
# second .N) to find the percent of flights cancelled
# the last example is meant to be instructive and not recommended practice
# since we could always compute the figure quicker and more simply like this:
flights[ , .(PctCancelled = mean(CANCELLED)), by = ORIGIN_AIRPORT][order(-PctCancelled)]
# but it's important because you can do lot of little tricks using .SD
# like filtering out groups
# Remove all airports from the dataset that don't have 10k flights
flights[ , .SD[.N >= 10000], by = ORIGIN_AIRPORT]
# Here we're saying to group by the airport of origin
# So .SD in each case is the complete set of flights for an airport
# We then filter that subset. We put in a single TRUE or FALSE, is the
# number of rows greater than 10000? If it is, it returns TRUE and is
# recycled so that every row in the subset is returned. If not, then
# the FALSE is returned, recycled for all rows, and no rows are returned
# Data.table will build up the final dataset based on these little data.tables
# Appending zero rows to a data.table means nothing, and the airports
# that don't have enough rows are dropped from the final output
# Which airports don't have 10000 flights?
flights[ , .SD[.N < 10000], by = ORIGIN_AIRPORT][ , unique(ORIGIN_AIRPORT)]
# Assignment ===================================================================
# there is an assignment operator that *modifies in place* (by reference)
# jargon translation: it will alter the specific object you use it on
# this is useful for converting column types
# Create a date column that's in date format
flights[ , Date := ymd(YEAR*10000+MONTH*100+DAY)]
# Reformat the dataset into boolean format
flights[ , CANCELLED := ifelse(CANCELLED == 1, TRUE, FALSE)]
# and useful or creating new columns
flights[ , AvgSpeed := DISTANCE / (AIR_TIME/60)]
# can be done to selectively update your data.table
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')
# I like making functions for this
cancelReasonUpdate <- function(inStr){
switch(inStr,
"A" = "Airline",
"B" = "Weather",
"C" = "National Air System",
"D" = "Security",
NA)
}
flights[ , CANCELLATION_REASON2 := map_chr(CANCELLATION_REASON,cancelReasonUpdate)]
# Can also be used to delete columns
flights[ , CANCELLATION_REASON2 := NULL]
# can also be used when 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
# joins ========================================================================
# read in the list of airports
airports <- fread("data/airports.csv")
# inner join
merge(x = flights,
y = airports,
by.x = 'ORIGIN_AIRPORT',
by.y = 'local_code')
# left outer join
flights[airports, on = c(ORIGIN_AIRPORT = 'IATA_CODE')]
flights[airports, on = "ORIGIN_AIRPORT == IATA_CODE"]
# inner join
flights[airports, on = c(ORIGIN_AIRPORT = 'IATA_CODE'), nomatch = 0]
# not join (aka anti-join)
airports[!flights, on = "IATA_CODE == ORIGIN_AIRPORT"]