Here we use data.table v1.9.5

devtools::install_github("Rdatatable/data.table")
library(data.table)

• An enhanced version of data.frames
• Optimize most heavy works in C
• Minimize redundant copies

• Speed
• Automatically optimization
• Total solution for ETL
• Concise syntax

Benchmarks : Grouping

DT[i, j, by]

Take DT, subset rows using i, then calculate j, grouped by by.

• fread: read text file into data.table
• :=: add/remove/update a column by reference.
• rbindlist: Same as do.call("rbind", l) on data.frames, but faster.

filepaths = list.files("data/daily", full.names = TRUE)
locations = gsub("data\\/daily\\/|_....-..-..\\.csv", "", filepaths)
dates = gsub("data\\/daily\\/.*_|\\.csv", "", filepaths)
weather = list()
for (i in 1:length(filepaths)) {
    weather[[i]] = fread(filepaths[i])
    weather[[i]][, city := locations[i]]
    weather[[i]][, date := dates[i]]
}
weather = rbindlist(weather)

You can use data.table with any function that suitable for data.frame.

class(weather)

## [1] "data.table" "data.frame"

Here we use lubridate::ymd_h to generate datetime. The warning message is due to the missing data of Kaohsiung in 2015-06-24

weather[,datetime := lubridate::ymd_h(paste(date, ObsTime), tz = "CST")]

## Warning: 2 failed to parse.

weather[is.na(datetime)]

##                 ObsTime              StnPres                SeaPres
## 1:                   NA                   NA                     NA
## 2: 觀測時間(LST)ObsTime 測站氣壓(hPa)StnPres 海平面氣壓(hPa)SeaPres
##           Temperature              Tddewpoint            RH          WS
## 1:                 NA                      NA            NA          NA
## 2: 氣溫(℃)Temperature 露點溫度(℃)Td dew point 相對溼度(%)RH 風速(m/s)WS
##                             WD              WSGust
## 1:                          NA                  NA
## 2: 風向(最多風向)(360degree)WD 最大陣風(m/s)WSGust
##                           WDGust           Precp             PrecpHour
## 1:                            NA              NA                    NA
## 2: 最大陣風風向(360degree)WDGust 降水量(mm)Precp 降水時數(hr)PrecpHour
##                SunShine                    GloblRad           Visb
## 1:                   NA                          NA             NA
## 2: 日照時數(hr)SunShine 全天空日射量(MJ/㎡)GloblRad 能見度(km)Visb
##         city       date datetime
## 1: Kaohsiung 2015-06-24     <NA>
## 2: Kaohsiung 2015-06-24     <NA>

weather = weather[!is.na(datetime),]
str(weather)

## Classes 'data.table' and 'data.frame':   105096 obs. of  18 variables:
##  $ ObsTime    : chr  "1" "2" "3" "4" ...
##  $ StnPres    : chr  "1002.9 " "1001.5 " "1002.6 " "1002.3 " ...
##  $ SeaPres    : chr  "1005.1 " "1003.7 " "1004.8 " "1004.5 " ...
##  $ Temperature: chr  "28.6 " "28.3 " "28.3 " "28.1 " ...
##  $ Tddewpoint : chr  "21.1 " "22.1 " "21.6 " "21.7 " ...
##  $ RH         : chr  "64 " "69 " "67 " "68 " ...
##  $ WS         : chr  "1.3 " "3.2 " "2.5 " "1.1 " ...
##  $ WD         : chr  "340.0 " "20.0 " "330.0 " "330.0 " ...
##  $ WSGust     : chr  "2.7 " "5.8 " "4.6 " "4.2 " ...
##  $ WDGust     : chr  "310.0 " "40.0 " "20.0 " "320.0 " ...
##  $ Precp      : chr  "0.0 " "0.0 " "0.0 " "0.0 " ...
##  $ PrecpHour  : chr  "0.0 " "0.0 " "0.0 " "0.0 " ...
##  $ SunShine   : chr  " " " " " " " " ...
##  $ GloblRad   : chr  "0.00 " "0.00 " "0.00 " "0.00 " ...
##  $ Visb       : chr  " " "10.0 " " " " " ...
##  $ city       : chr  "Hualien" "Hualien" "Hualien" "Hualien" ...
##  $ date       : chr  "2012-08-15" "2012-08-15" "2012-08-15" "2012-08-15" ...
##  $ datetime   : POSIXct, format: "2012-08-15 01:00:00" "2012-08-15 02:00:00" ...
##  - attr(*, ".internal.selfref")=<externalptr>

• .SD: Subset of Data.table. It's a data table that holds the data for the current group defined using by.
• .SDcols specifies the columns that returned in .SD.

numeric_cols = colnames(weather)[!colnames(weather) %in% c("city", "date", "datetime")]
weather[, c(numeric_cols) := (lapply(.SD, function(x){gsub("[^0-9.TxV]", "", x)})), 
        .SDcols = numeric_cols]

You can update partial cells in a data.table.

# Check which numeric columns contain special symbols
weather[, lapply(.SD, function(x){sum(grepl("^[TxV]$", x), na.rm = FALSE)}), 
        .SDcol = numeric_cols]

##    ObsTime StnPres SeaPres Temperature Tddewpoint RH WS  WD WSGust WDGust
## 1:       0       0       0           0          0  0  0 209      0      0
##    Precp PrecpHour SunShine GloblRad Visb
## 1:  3884         0        0        0    0

weather[WD == "V", WD := ""]
weather[Precp == "T", Precp := "0"]

• := also can modify multiple columns by reference in a functional way

weather[, `:=`(month = substr(date, 6, 7),
               isRain = Precp > 0
               )]
weather[, c(numeric_cols) := (lapply(.SD, as.numeric)), .SDcols = numeric_cols]
weather[, date := as.Date(date)]

• . is an abbreviation of list within data.table
• Because j handle expressions, you need to set with = FALSE when slicing with character vector

# Select columns with list
ws = weather[, .(city, datetime, Temperature, Tddewpoint)]

# Select columns with character vector
attrs = c("city", "datetime", "Temperature", "Tddewpoint")
ws2 = weather[, attrs, with = FALSE]
identical(ws, ws2)

## [1] TRUE

• You can use by to summarize by each group. keyby will add key automatically.
• .N: returns the number of rows in the subset.

hottest = ws[, .(datetime, Temperature, T.Max = max(Temperature)), keyby = city]
hottest = hottest[, count := .N]
hottest = hottest[Temperature == T.Max, 
                  head(.SD[, .(T.Max, datetime)], 3), # output top 3 to check ties 
                  by = city]
hottest

##         city T.Max            datetime
## 1:   Hualien  35.1 2013-07-12 11:00:00
## 2: Kaohsiung  36.9 2014-09-15 12:00:00
## 3:  Taichung  36.5 2014-09-15 14:00:00
## 4:    Taipei  38.3 2013-08-08 14:00:00

• chaining: DT[ … ][ … ][ … ]

  Avoid intermediate assignment.

hottest2 = ws[, .(datetime, Temperature, T.Max = max(Temperature)), keyby = city
              ][, count := .N
                ][Temperature == T.Max, 
                  head(.SD[, .(T.Max, datetime)], 3), # output top 3 to check ties 
                  by = city]
key(hottest2)

## [1] "city"

identical(hottest, hottest2)

## [1] TRUE

• CJ: Cross Join. Generate a data.table from the cross product of the vectors.
• setnames: Set attributes of data.table by reference.

# cj = CJ(ws$city, ws$datetime, unique = TRUE)  # don't use POSIXct in CJ
cj = CJ(ws$city, as.character(ws$datetime), unique = TRUE)[
    , V2 := lubridate::ymd_hms(V2, tz = "CST")]
setnames(cj, names(cj), c("city", "datetime"))
ws = merge(cj, ws, all.x = TRUE, all.y = FALSE, by = c("city", "datetime"))
ws[, date := as.character(datetime)][,
    `:=`(date = as.Date(substr(date, 1, 10)),
         year = substr(date, 1, 4),
         month = substr(date, 6, 7),
         hour = substr(date, 12, 13))]

• We can subset row by a particular columns after assiggn it as a key with setkey.
• set is used to repeatedly update rows and columns by reference.

inter_mean = ws[year == "2015" & month == "06" & city == "Kaohsiung",
                .(meanT = mean(Temperature, na.rm = TRUE),
                  meanTd = mean(Tddewpoint, na.rm = TRUE)),
                by = hour]
setkey(inter_mean, hour)
Tj = which(colnames(ws) == "Temperature")
Tdj = which(colnames(ws) == "Tddewpoint")
for (i in which(is.na(ws$Temperature))) {
    set(ws, i, Tj, inter_mean[ws[i, hour], meanT])
    set(ws, i, Tdj, inter_mean[ws[i, hour], meanTd])
}

In this example, all values are concatenate into single column.

ws[, .(coef(lm(Tddewpoint ~ Temperature))), by = city]

##         city         V1
## 1:   Hualien -2.7831813
## 2:   Hualien  0.9292042
## 3: Kaohsiung -5.8939668
## 4: Kaohsiung  1.0290631
## 5:  Taichung -1.2627112
## 6:  Taichung  0.8169720
## 7:    Taipei -2.0428652
## 8:    Taipei  0.8500687

As long as j returns a list, each element of the list will become a column in the resulting data.table.

ws[, as.list(coef(lm(Tddewpoint ~ Temperature))), by = city]

##         city (Intercept) Temperature
## 1:   Hualien   -2.783181   0.9292042
## 2: Kaohsiung   -5.893967   1.0290631
## 3:  Taichung   -1.262711   0.8169720
## 4:    Taipei   -2.042865   0.8500687

Sort Temperature in ascending and Tddewpoint in descending

• order in data.table acts just like base::order, but faster.
• - means decending.

ws[order(Temperature, -Tddewpoint)]

##             city            datetime Temperature Tddewpoint       date
##      1: Taichung 2014-02-21 06:00:00         6.8        3.7 2014-02-21
##      2: Taichung 2014-02-21 05:00:00         7.2        4.5 2014-02-21
##      3: Taichung 2014-02-21 04:00:00         7.4        2.9 2014-02-21
##      4: Taichung 2014-01-23 05:00:00         7.6        5.3 2014-01-23
##      5: Taichung 2014-01-23 07:00:00         7.7        5.2 2014-01-23
##     ---                                                               
## 105116:   Taipei 2014-08-02 12:00:00        37.4       24.3 2014-08-02
## 105117:   Taipei 2014-07-09 13:00:00        37.5       25.9 2014-07-09
## 105118:   Taipei 2013-08-08 15:00:00        37.7       25.0 2013-08-08
## 105119:   Taipei 2013-08-08 13:00:00        37.8       24.4 2013-08-08
## 105120:   Taipei 2013-08-08 14:00:00        38.3       25.4 2013-08-08
##         year month hour
##      1: 2014    02   06
##      2: 2014    02   05
##      3: 2014    02   04
##      4: 2014    01   05
##      5: 2014    01   07
##     ---                
## 105116: 2014    08   12
## 105117: 2014    07   13
## 105118: 2013    08   15
## 105119: 2013    08   13
## 105120: 2013    08   14

:= and set* modify the data.table by references. You can use copy to do a deep copy to avoid side effect(modify a object within a function).

deepf = function(dt) {
    dt = copy(dt)
    dt[, city := substr(city, 1, 1)]
}
deepf(hottest2)
hottest2

##         city T.Max            datetime
## 1:   Hualien  35.1 2013-07-12 11:00:00
## 2: Kaohsiung  36.9 2014-09-15 12:00:00
## 3:  Taichung  36.5 2014-09-15 14:00:00
## 4:    Taipei  38.3 2013-08-08 14:00:00

shallowf = function(dt) {
    dt[, city := substr(city, 1, 1)]
}
shallowf(hottest2)
hottest2

##    city T.Max            datetime
## 1:    H  35.1 2013-07-12 11:00:00
## 2:    K  36.9 2014-09-15 12:00:00
## 3:    T  36.5 2014-09-15 14:00:00
## 4:    T  38.3 2013-08-08 14:00:00

data.table implement melt in reshape2. Additionally, you can use regular expression with patterns to melt multiple columns.

melted = melt.data.table(weather, 
                         id.vars = c("city", "datetime"),
                         measure = patterns(".*Pres", "Precp"),
                         value.name = c("Pres", "Precp")
                         )
melted[c(1,2,.N-1,.N),]

##       city            datetime variable   Pres Precp
## 1: Hualien 2012-08-15 01:00:00        1 1002.9     0
## 2: Hualien 2012-08-15 02:00:00        1 1001.5     0
## 3:  Taipei 2015-08-14 23:00:00        2 1008.7     0
## 4:  Taipei 2015-08-15 00:00:00        2 1008.4     0

You can also use dcast with data.table. Furthermore, you can cast multiple value.vars simultaneously.

dcasted = dcast.data.table(melted, city + datetime ~ variable,
                           value.var = c("Pres", "Precp"))
dcasted[c(1,2,.N-1,.N),]

##       city            datetime Pres_1 Pres_2 Precp_1 Precp_2
## 1: Hualien 2012-08-15 01:00:00 1002.9 1005.1       0       0
## 2: Hualien 2012-08-15 02:00:00 1001.5 1003.7       0       0
## 3:  Taipei 2015-08-14 23:00:00 1005.2 1008.7       0       0
## 4:  Taipei 2015-08-15 00:00:00 1004.9 1008.4       0       0

• data.table wiki - Getting started
• The data.table Cheat Sheet
• Data Analysis in R, the data.table Way
• Benchmarks : Grouping