Extract date elements from POSIXlt and put into data frame in R

Question

My second question of the day and my last attempt to use R to clean up this data. Here's the sitrep:

I have a data frame that has a column which is a POSIXlt date type. I want to extract the day, month and year from that column and create 3 new columns called (cleverly) day, month and year.

The data frame looks like this:

order_id      dd_mmm_yy
   1          2005-07-28
   2          2007-03-04

I want to end up with this:

order_id      dd_mmm_yy    day   month   year
   1          2005-07-28    28     7     2005
   2          2007-03-04    4      3     2007

I've created a function to extract the day, month and year and return them in a list (or data frame, I've tried both).

extractdate = function (date) {
        day = format(date, format="%d")
        month = format(date, format="%m")
        year = format(date, format="%Y")

       list(day=day, month=month, year=year)
 }

Here's what I've tried based on an earlier problem and question:

cbind(orders, t(sapply(orders$dd_mmm_yy, extractdate)))

which gives me this:

Error in data.frame(..., check.names = FALSE) : 
arguments imply differing number of rows: 5, 9

The t(sapply... by itself gives me this for some crazy reason:

      day         month       year       
sec   Character,5 Character,5 Character,5
min   Character,5 Character,5 Character,5
hour  Character,5 Character,5 Character,5
mday  Character,5 Character,5 Character,5
mon   Character,5 Character,5 Character,5
year  Character,5 Character,5 Character,5
wday  Character,5 Character,5 Character,5
yday  Character,5 Character,5 Character,5
isdst Character,5 Character,5 Character,5

What on earth is going on? Am I better off using something like Python or Java to do all the data manipulation I need to do on this data before bringing it into R for analysis?

score 27 · Accepted Answer · answered Nov 14 '11 at 19:09

27

POSIXlt objects are a list of 9 components (see the Details section of ?POSIXlt for more information). Because the dd_mmm_yy column is POSIXlt, you don't need a function to extract the components. You can just extract the components by their names:

orders$day <- orders$dd_mmm_yy$mday        # day of month
orders$month <- orders$dd_mmm_yy$mon+1     # month of year (zero-indexed)
orders$year <- orders$dd_mmm_yy$year+1900  # years since 1900
orders
#   order_id  dd_mmm_yy day month year
# 1        1 2005-07-28  28     7 2005
# 2        2 2007-03-04   4     3 2007

answered Nov 14 '11 at 19:09

Joshua Ulrich

173,410
32
338
418

Thanks! That worked perfectly. I knew there had to be a simple solution. – Dave Kincaid Nov 14 '11 at 21:14
There is a problem with this solution, check below. – mmann1123 Jun 08 '12 at 18:17
@user1034797: there is no problem with this solution. You assume I change the elements of a POSIXlt object, which I do not. – Joshua Ulrich Jun 08 '12 at 21:37

score 10 · Answer 2 · answered Nov 14 '11 at 19:42

One liner using lubridate

require(plyr); require(lubridate)
mutate(mydf, date = ymd(dd_mmm_yy), day = day(date), 
  month = month(date), year = year(date))

  order_id  dd_mmm_yy       date day month year
1        1 2005-07-28 2005-07-28  28     7 2005
2        2 2007-03-04 2007-03-04   4     3 2007

score 4 · Answer 3 · answered Nov 14 '11 at 19:02

4

Try this (DF as your data.frame):

extractdate <- function(date) {
    day <- format(date, format="%d")
    month <- format(date, format="%m")
    year <- format(date, format="%Y")

    cbind(day, month, year)
}

cbind(DF, extractdate(DF$dd_mmm_yy))

answered Nov 14 '11 at 19:02

jrara

16,239
33
89
120

Feng Jiang · Answer 4 · 2021-09-17T23:05:41.303

1

If you are using the data.table package, it already has functions to extract data time components from POSIXct.

second(x)
minute(x)
hour(x)
yday(x)
wday(x)
mday(x)
week(x)
isoweek(x)
month(x)
quarter(x)
year(x)

The use is straightforward (e.g. dt[, day := day(dd_mmm_yy)]). You can see the full documentation here.

edited Sep 17 '21 at 23:05

answered Sep 03 '20 at 18:19

Feng Jiang

1,776
19
25

Extract date elements from POSIXlt and put into data frame in R

4 Answers4

Linked

Related