DT %>% melt.data.table(id.vars= c("col1"), measure.vars=c("col2"),
                       variable.name="var", value.name="val")
DT %>% dcast.data.table(formula= col1+col2 ~ co3, 
                        value.var=c("col4"))

https://github.com/rstudio/cheatsheets/blob/master/data-import.pdf

melt, dcast ==> gather, spread

#--- --- --- -----
table4a %>% as.data.table()
moltenDD <- as.data.table(table4a) %>% melt.data.table(id.vars="country", variable.name="year", value.name="case")
moltenDD %>% dcast.data.table(formula=country~year, value.var="case")

table4a
dcastDD <- table4a %>% gather(`1999`, `2000`, key="year", value="case")
dcastDD %>% spread(key=year, value=case)

#--- --- --- -----
table2 %>% as.data.table()
dcastDD <- as.data.table(table2) %>% dcast.data.table(country+year ~ type, value.var="count")
dcastDD %>% melt.data.table(id.vars=c('country','year'),\tvariable.name="cases", value.name="count") 

table2
moltenDD <- table2 %>% spread(key=type, value=count)
#table2 %>% spread(key=year, value=count)
moltenDD %>% gather(`cases`, `population`, key="type", value="count")

melt, dcast

DT0 <- data.table(x=1:20, y1=rnorm(20), y2=rnorm(20,.5))
#      x          y1          y2
# 1:   1 -0.58122437  0.58533709
# 2:   2 -1.01616426  0.73756413
# 3:   3 -0.36792958 -0.03372934
# ...
# 19: 19  0.56873638  1.41555847
# 20: 20  1.46933741 -0.63625173

moltenDT <- DT0 %>% melt.data.table(id.vars=c("x"), measure.vars=c("y1","y2"), 
\t\t\t\t\t\t\t\t  variable.name="Var", value.name="Val")
#      x      Var         Val
#  1:  1       y1 -0.58122437
#  2:  2       y1 -1.01616426
# ....
# 39: 19       y2  1.41555847
# 40: 20       y2 -0.63625173

dcastDT <- moltenDT %>% dcast.data.table(formula = x ~ Var,
\t\t\t\t\t\t\t\t\t   value.var = c('Val'))

dcast하면 key 가 생긴다.

all.equal(DT0, dcastDT)
# [1] "Datasets has different keys. 'target' has no key. 'current': x."
DT0 %>% key()         # NULL
dcastDT %>% key()     # [1] "x"

DT0 %>% setkey(x)
identical(DT0, dcastDT) # TRUE

Timeserise to data.table

passenger <- tsbox::ts_dt(AirPassengers)

melt.data.table() & dcast.data.table()

data.table에 맞게 reshape2 package의 함수를 수정한 dcast & melt

Convert DT to long form where each dob is a separate observation.

rs <- dcast.data.table(rs,"Step~variable")

data_melt <- melt.data.table(data, id.vars = c("A", "B", "C"))
data_cast <- dcast.data.table(result, id ~ Param)

DT <- data.table(
  i_1 = c(1:5, NA),
  i_2 = c(NA,6,7,8,9,10),
  f_1 = factor(sample(c(letters[1:3], NA), 6, TRUE)),
  f_2 = factor(c("z", "a", "x", "c", "x", "x"), ordered=TRUE),
  c_1 = sample(c(letters[1:3], NA), 6, TRUE),
  d_1 = as.Date(c(1:3,NA,4:5), origin="2013-09-01"),
  d_2 = as.Date(6:1, origin="2012-01-01") )
DT[, l_1 := DT[, list(c=list(rep(i_1, sample(5,1)))), by = i_1]$c]
DT[, l_2 := DT[, list(c=list(rep(c_1, sample(5,1)))), by = i_1]$c]
#    i_1 i_2  f_1 f_2  c_1        d_1        d_2            l_1       l_2
# 1:   1  NA    a   z    c 2013-09-02 2012-01-07      1,1,1,1,1   c,c,c,c
# 2:   2   6    c   a    c 2013-09-03 2012-01-06        2,2,2,2   c,c,c,c
# 3:   3   7    b   x  2013-09-04 2012-01-05        3,3,3,3  NA,NA,NA
# 4:   4   8    c    c        2012-01-04        4,4,4,4 c,c,c,c,c
# 5:   5   9    c   x  2013-09-05 2012-01-03              5        NA
# 6:  NA  10    b   x    c 2013-09-06 2012-01-02 NA,NA,NA,NA,NA       c,c

# id, measure ------------------------------------------------------------------
# as character/integer/numeric vectors
DT %>% melt.data.table(id.vars=1:2, measure.vars=c("f_1"))
DT %>% melt.data.table(id.vars=c("i_1","i_2"), measure.vars=3) # same as above
DT %>% melt.data.table(id.vars=1:2, measure.vars=3L,  value.factor=T) # same, but 'value' is factor
DT %>% melt.data.table(id.vars=1:2, measure.vars=3:4, value.factor=T) # 'value' is *ordered* factor

# preserves attribute when types are identical, ex: Date
DT %>% melt.data.table(id.vars=3:4, measure.vars=c("d_1","d_2"))
DT %>% melt.data.table(id.vars=3:4, measure.vars=c("i_1","d_1")) # attribute not preserved

# on list
DT %>% melt.data.table(id.vars=c("l_1", "l_2")) # value is a list
DT %>% melt.data.table(id.vars=c("c_1", "l_1")) # c1 coerced to list

# on character
DT %>% melt.data.table(id.vars=c("c_1", "f_1"), measure.vars=c("i_1","i_2","f_1","f_2","c_1","d_1","d_2")) # value is char
DT %>% melt.data.table(id.vars=c("c_1", "i_2")) # i2 coerced to char

# on na.rm=TRUE. NAs are removed efficiently, from within C
DT %>% melt.data.table(id.vars=c("c_1", "i_2"), na.rm=TRUE) # remove NA

# measure.vars can be also a list
# melt "f_1,f_2" and "d_1,d_2" simultaneously, retain 'factor' attribute
# convenient way using internal function patterns()
melt(DT, id=1:2, measure=patterns("^f_", "^d_"), value.factor=TRUE)
# same as above, but provide list of columns directly by column names or indices
melt(DT, id=1:2, measure=list(3:4, c("d_1", "d_2")), value.factor=TRUE)
# same as above, but provide names directly:
melt(DT, id=1:2, measure=patterns(f="^f_"), value.factor=TRUE)

# na.rm=TRUE removes rows with NAs in any 'value' columns
melt(DT, id=1:2, measure=patterns("f_", "d_"), value.factor=TRUE, na.rm=TRUE)

# return 'NA' for missing columns, 'na.rm=TRUE' ignored due to list column
melt(DT, id=1:2, measure=patterns("l_", "c_"), na.rm=TRUE)

dd
merged.df
melt.data.table(data.table(merged.df), id=1, measure=patterns("^name"), value.factor=F)
make.nam

Examples

Q1. Calculate total number of rows by month and then sort on descending order

mydata[, .N, by = month] [order(-N)]

The .N operator is used to find count.  

Q2. Find top 3 months with high mean arrival delay

mydata[, .(mean_arr_delay = mean(arr_delay, na.rm = TRUE)), by = month][order(-mean_arr_delay)][1:3]

Q3. Find origin of flights having average total delay is greater than 20 minutes

mydata[, lapply(.SD, mean, na.rm = TRUE), .SDcols = c("arr_delay", "dep_delay"), by = origin][(arr_delay + dep_delay) > 20]

Q4.  Extract average of arrival and departure delays for carrier == 'DL' by 'origin' and 'dest' variables

mydata[carrier == "DL",
        lapply(.SD, mean, na.rm = TRUE),
        by = .(origin, dest),
        .SDcols = c("arr_delay", "dep_delay")]

Q5. Pull first value of 'air_time' by 'origin' and then sum the returned values when it is greater than 300

mydata[, .SD[1], .SDcols="air_time", by=origin][air_time > 300, sum(air_time)]