0

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

Ricky
  • 2,662
  • 5
  • 25
  • 57

3 Answers3

4

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
Rich Scriven
  • 97,041
  • 11
  • 181
  • 245
2

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))
Hunaidkhan
  • 1,411
  • 2
  • 11
  • 21
2

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
r2evans
  • 141,215
  • 6
  • 77
  • 149