Pfthb

I have a data table as below:

dt=data.table(
id=c(1,2,3,4,5,6),
date=c("28 Jul 2009","31 Jul 2009","31 Jul 2009","04 Aug 2009","10 Aug 2009","06 Aug 2009") 
)

I want to add three more columns day,month,year to this column.So I tried

dt[,day:=unlist(strsplit(date,"\ "))[1]]
dt[,month:=unlist(strsplit(date,"\ "))[2]]
dt[,year:=unlist(strsplit(date,"\ "))[3]]

But this just appended all columns with same split for the first row.

SoI tried

dt[,day:=lapply(date,function (x) unlist(strsplit(x,"\ "))[1]),]
dt[,month:=lapply(date,function (x) unlist(strsplit(x,"\ "))[2]),]
dt[,year:=lapply(date,function (x) unlist(strsplit(x,"\ "))[3]),]

which worked. But how do I combine all in one line instead of 3 lines and any other efficeint methods.Any help is appreciated

You want tstrsplit(), also from the data table package. From help(tstrsplit), it is "a convenient wrapper function to split a column using strsplit and assign the transposed result to individual columns."

dt[, c("day", "month", "year") := tstrsplit(date, " ")]

dt
# id date day month year
# 1: 1 28 Jul 2009 28 Jul 2009
# 2: 2 31 Jul 2009 31 Jul 2009
# 3: 3 31 Jul 2009 31 Jul 2009
# 4: 4 04 Aug 2009 04 Aug 2009
# 5: 5 10 Aug 2009 10 Aug 2009
# 6: 6 06 Aug 2009 06 Aug 2009

You can easily do it using library lubridate and dplyr

library(data.table)
dt=data.table(
 id=c(1,2,3,4,5,6),
 date=c("28 Jul 2009","31 Jul 2009","31 Jul 2009","04 Aug 2009","10 Aug 2009","06 Aug 2009") 
)
str(dt)

library(lubridate)
dt$date<- dmy(dt$date)
library(dplyr)
mutate(dt, day= day(dt$date),month = month(dt$date),year = year(dt$date))

The issue is that your [1] indexing is taking the first of 18 elements, not the first of each of a list of 6. Try this:

dt[,day := sapply(strsplit(date, "\s"), `[[`, 1),]
dt
# id date day
# 1: 1 28 Jul 2009 28
# 2: 2 31 Jul 2009 31
# 3: 3 31 Jul 2009 31
# 4: 4 04 Aug 2009 04
# 5: 5 10 Aug 2009 10
# 6: 6 06 Aug 2009 06