CODEWITHSUNDEEP
rdataframe
Andriy T.
Andriy T.

Reputation: 2030

Table week to date

I have a data frame like:

> df

   week month year    x
1   1-7   sep 2013  566
2  8-14   sep 2013   65
3 15-21   sep 2013  144
4 22-28   sep 2013  455
5 29-30   sep 2013 1212

And need to convert it to:

> df_out
         date      x
1  01/09/2013  80.86
2  02/09/2013  80.86
3  03/09/2013  80.86
4  04/09/2013  80.86
5  05/09/2013  80.86
6  06/09/2013  80.86
7  07/09/2013  80.86
8  08/09/2013   9.29
9  09/09/2013   9.29
10 10/09/2013   9.29
11 11/09/2013   9.29
12 12/09/2013   9.29
13 13/09/2013   9.29
14 14/09/2013   9.29

Explanation: Week 1-7 of September 2013 has 566 units of x. I want to construct a time series that gives me units for every day of the week (from 2013-09-01 to 2013-09-07) that equal 566/7.

Note that could be a row (like row 5 of my df) where we have only 2 days. So the value x for 2013-09-29 would be 1212/2 = 606

I have tried to do it in Excel, constructing start and the end date for each row of df. That's splitting week column by "-" and constructing date columns. I can do it in R but then I am stuck.

Data:

df <- structure(list(week = c("1-7", "8-14", "15-21", "22-28", "29-30"
), month = c("sep", "sep", "sep", "sep", "sep"), year = c(2013L, 
2013L, 2013L, 2013L, 2013L), x = c(566L, 65L, 144L, 455L, 1212L
)), .Names = c("week", "month", "year", "x"), class = "data.frame", row.names = c(NA, 
-5L))

Upvotes: 1

Views: 57

Answers (1)

Robert
Robert

Reputation: 5152

Try this:

dfl=split(df,1:nrow(df))
do.call(rbind,lapply(dfl,function(wd){
  d=as.numeric(unlist(strsplit(wd$week, "-", fixed = TRUE)))
  days=d[1]:d[2]
  date=as.Date(paste(wd$year,wd$month,days,sep="/"),"%Y/%b/%d")
  x=round(rep(wd$x/length(days),length(days)),2)
  data.frame(date,x)
}))

          date      x
1.1 2013-09-01  80.86
1.2 2013-09-02  80.86
1.3 2013-09-03  80.86
1.4 2013-09-04  80.86
1.5 2013-09-05  80.86
1.6 2013-09-06  80.86
1.7 2013-09-07  80.86
2.1 2013-09-08   9.29
2.2 2013-09-09   9.29
2.3 2013-09-10   9.29
2.4 2013-09-11   9.29
2.5 2013-09-12   9.29
2.6 2013-09-13   9.29
2.7 2013-09-14   9.29
3.1 2013-09-15  20.57
3.2 2013-09-16  20.57
3.3 2013-09-17  20.57
3.4 2013-09-18  20.57
3.5 2013-09-19  20.57
3.6 2013-09-20  20.57
3.7 2013-09-21  20.57
4.1 2013-09-22  65.00
4.2 2013-09-23  65.00
4.3 2013-09-24  65.00
4.4 2013-09-25  65.00
4.5 2013-09-26  65.00
4.6 2013-09-27  65.00
4.7 2013-09-28  65.00
5.1 2013-09-29 606.00
5.2 2013-09-30 606.00

Upvotes: 2

Related Questions