0

For example, this is the data I have. I want to convert this weekly data into monthly data by weighted averages of interest. The thing is that some of the 7 day X1 data will overlap from one month to the other.

    Week                    interest Start        End
1   2004-01-04 - 2004-01-10       62 2004-01-04 2004-01-10
2   2004-01-11 - 2004-01-17       70 2004-01-11 2004-01-17
3   2004-01-18 - 2004-01-24       69 2004-01-18 2004-01-24
4   2004-01-25 - 2004-01-31       68 2004-01-25 2004-01-31
5   2004-02-01 - 2004-02-07       63 2004-02-01 2004-02-07
6   2004-02-08 - 2004-02-14       65 2004-02-08 2004-02-14
7   2004-02-15 - 2004-02-21       66 2004-02-15 2004-02-21
8   2004-02-22 - 2004-02-28       66 2004-02-22 2004-02-28
9   2004-02-29 - 2004-03-06       70 2004-02-29 2004-03-06
10  2004-03-07 - 2004-03-13       80 2004-03-07 2004-03-13
11  2004-03-14 - 2004-03-20       82 2004-03-14 2004-03-20

I tried to convert this data into daily data and then aggregate daily data into monthly data, but I don't know how to convert it. More specifically, a data frame looks like the following: 

2004-01-04 62 
2004-01-05 62 
2004-01-06 62 
... 
2004-01-10 62 
2005-01-11 70 
.... 
...
hotzst
  • 7,238
  • 9
  • 41
  • 64
Tianning He
  • 11
  • 1
  • 2

1 Answers1

2

Read the data into a zoo object, merge it with a daily grid and fill in the empty dates using na.locf. Then aggregate by year/month:

# input data frame in reproducible form

Lines <- "interest Start        End
62 2004-01-04 2004-01-10
70 2004-01-11 2004-01-17
69 2004-01-18 2004-01-24
68 2004-01-25 2004-01-31
63 2004-02-01 2004-02-07
65 2004-02-08 2004-02-14
66 2004-02-15 2004-02-21
66 2004-02-22 2004-02-28
70 2004-02-29 2004-03-06
80 2004-03-07 2004-03-13
82 2004-03-14 2004-03-20"
DF <- read.table(text = Lines, header = TRUE)

# convert to zoo, merge with a daily grid and aggregate by month using mean

library(zoo)
z.st <- read.zoo(DF[c("Start", "interest")])
z.en <- read.zoo(DF[c("End", "interest")])
z <- c(z.st, z.en)

g <- zoo(, seq(start(z), end(z), "day"))
m <- na.locf(merge(z, g))
aggregate(m, as.yearmon, mean)

giving:

Jan 2004 Feb 2004 Mar 2004 
67.25000 65.17241 77.70000

Update: fixed

G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
  • That's exactly what I need!! Just one more thing, since what we got is a zoo series, how to convert it into a data frame? Thanks a lot! – Tianning He Mar 13 '16 at 17:59
  • If `ag` is the result of `aggregate` then `fortify.zoo(ag)` is a data frame. The `Index` column will be of class `"yearmon"` . – G. Grothendieck Mar 13 '16 at 18:06