CODEWITHSUNDEEP
rdate
Madamespring
Madamespring

Reputation: 35

Date split in R

I have a data frame like this:

Date
20130101
20130102
20130103
20130104

how i can split Date column in different column? I have already used following function but it is not working :

library(data.table)  
setDT(DF)[, tstrsplit(DATE, "/|\\s", type.convert = TRUE)]

Upvotes: 0

Views: 1390

Answers (3)

G. Grothendieck
G. Grothendieck

Reputation: 270055

Here are some solutions that do not require any packages. They all produce a data.frame with a "Date" class column followed by numeric columns for year, month and day. (The input used in reproducible form is given in the Note at the end.)

1) POSIXlt First convert the Date column to "Date" class giving date and then to an unclassed "POSIXlt" object giving lt. Now pick off the elements of lt appropriately:

date <- as.Date(as.character(DF$Date), format = "%Y%m%d")
lt <- unclass(as.POSIXlt(date))
with(lt, data.frame(Date = date, year = year + 1900, month = mon + 1, day = mday))

giving:

        Date year month day
1 2013-01-01 2013     1   1
2 2013-01-02 2013     1   2
3 2013-01-03 2013     1   3
4 2013-01-04 2013     1   4

2) format 

data.frame(date = as.Date(as.character(DF$Date), format = "%Y%m%d"),
           year = as.numeric(format(date, "%Y")), 
           month = as.numeric(format(date, "%m")), 
           day = as.numeric(format(date, "%d")))

giving:

        date year month day
1 2013-01-01 2013     1   1
2 2013-01-02 2013     1   2
3 2013-01-03 2013     1   3
4 2013-01-04 2013     1   4

3) math

with(DF, data.frame(date = as.Date(as.character(DF$Date), format = "%Y%m%d"),
                    year = Date %/% 10000, 
                    month = Date %% 10000 %/% 100, 
                    day = Date %% 100))

giving:

        date year month day
1 2013-01-01 2013     1   1
2 2013-01-02 2013     1   2
3 2013-01-03 2013     1   3
4 2013-01-04 2013     1   4

4) read.fwf

data.frame(date = as.Date(as.character(DF$Date), format = "%Y%m%d"),
           read.fwf(textConnection(as.character(DF$Date)), c(4, 2, 2),
                    col.names = c("year", "month", "day")))

giving:

        date year month day
1 2013-01-01 2013     1   1
2 2013-01-02 2013     1   2
3 2013-01-03 2013     1   3
4 2013-01-04 2013     1   4

5) sub/read.table

date.ch <- sub("(....)(..)(..)", "\\1-\\2-\\3", DF$Date)
data.frame(date = as.Date(date.ch), 
           read.table(text = date.ch, col.names = c("year", "month", "day"), sep = "-"))

giving:

        date year month day
1 2013-01-01 2013     1   1
2 2013-01-02 2013     1   2
3 2013-01-03 2013     1   3
4 2013-01-04 2013     1   4

Note: The input used, "DF", in reproducible form is:

DF <- data.frame(Date = 20130101:20130104)

Upvotes: 2

bramtayl
bramtayl

Reputation: 4024

You can also do this with lubridate

library(dplyr)
library(lubridate)

data = 
  data_frame(Date = c(20130101, 20130102, 20130103, 20130104) ) %>%
  mutate(date = 
           Date %>%
           as.character %>% 
           ymd,
         year = year(date),
         month = month(date),
         day = day(date))

Upvotes: 0

Hanjo Odendaal
Hanjo Odendaal

Reputation: 1441

If you not set on using data.table you could use the following command that incorporates substr:

x = data.frame("20130101", "20130102", "20130103", "20130104")

y<-data.frame(Year=substr(x[,1],1,4),
              Month=substr(x[,1],5,6),
              Day=substr(x[,1],7,8))

If you are sure your data is in the same format for the whole vector.

Upvotes: 0

Related Questions