How convert column in a date object, based on a index

Question

I have this df(A)

   col1  col2 index
    1    2     1
    2   76     1
    3   0      1
    4   0      1
    5   0      1
    6   0      1
    7   0      2
    8   0      2
    9   0      2
    10  0      2
    11  NA     2
    12  NA     2

I wolud like to convert the col1 in a sequence of time, with year and month, with 3 month interval,based on column index, like this:

          col1      col2     index
        gen-1975    2         1
        feb-1975    76        1
        mar-1975    0         1
        gen-1976    0         1
        feb-1976    0         1 
        mar-1976    0         1
        gen-1975    0         2
        feb-1975    0         2
        mar-1975    0         2
        gen-1976    0         2
        feb-1976    NA        2
        mar-1976    NA        2

I tried with ts and as.Date, but i haven't the expected results
Thank you in advance.

score 1 · Accepted Answer · answered Feb 18 '16 at 15:37

I'll assume that the three month intervals are always Jan, Feb, Mar. You can use ave to split df by index values. For each unique index value, you first generate a vector of years and then, for each year, generate the three months for that year. Code would look like

# set start date for all sets of data 
  start_date <- as.Date("1975-01-01") 
# number of months in each year
  num_months <- 3
# generate col1 as R Date types
  df$col1 <-  as.Date(ave(df$col1, df$index, FUN=function(x) { yrs = seq.Date(start_date, length.out=length(x)/num_months, by="year")
                                                 sapply(yrs, function(y) seq.Date(y, length.out=num_months, by="month")) } ),
                 origin=as.Date("1970-01-01"))

which gives for df

         col1 col2 index
1  1975-01-01    2     1
2  1975-02-01   76     1
3  1975-03-01    0     1
4  1976-01-01    0     1
5  1976-02-01    0     1
6  1976-03-01    0     1
7  1975-01-01    0     2
8  1975-02-01    0     2
9  1975-03-01    0     2
10 1976-01-01    0     2
11 1976-02-01   NA     2
12 1976-03-01   NA     2

If you want to use df$col1 later on in your code, you probably want to leave it as an R Date type as above. However, if you want df$col1 as a character string in month-year format, then do

 # convert col1 to character string using the month-year format 
       df <- cbind(col1=format(df$col1, "%b-%Y"), df[,-1])

which gives

        col1 col2 index
1  Jan-1975    2     1
2  Feb-1975   76     1
3  Mar-1975    0     1
4  Jan-1976    0     1
5  Feb-1976    0     1
....

score 0 · Answer 2 · answered Feb 18 '16 at 17:22

0

We could try

library(data.table)
setDT(df)[, col1:= as.character(col1)
   ][,col1:= paste(c('gen', 'feb', 'mar'),rep(c(1975, 1976), 
                 each=.N/2), sep='-'), index]

answered Feb 18 '16 at 17:22

akrun

874,273
37
540
662

How convert column in a date object, based on a index

2 Answers2