Skip to content

From daily series to monthly and viceversa

11 messages · manta, Gabor Grothendieck

manta
# 
I have the following daily exchange rate series  (from january 1st 1996 to
december 31st 2008) and I want to obtain them monthly series from it. I've
read about the 'zoo' library but I'm not getting it how to do it. These are
the data (left column day-month-year, right column the index)

31/12/1993	1,12509
03/01/1994	1,12509
04/01/1994	1,12558
05/01/1994	1,1258
06/01/1994	1,12596
07/01/1994	1,12753
10/01/1994	1,1273
11/01/1994	1,12416
12/01/1994	1,1275

Also, I have monthly CPI data and I want to interpolate using the reference
CPI formula in order to obtain the daily series. The time window is the same
(January 1996, December 2008).
Thanks in advance for your help.
Gabor Grothendieck
# 
Try this:
+ 03/01/1994      1,12509
+ 04/01/1994      1,12558
+ 05/01/1994      1,1258
+ 06/01/1994      1,12596
+ 07/01/1994      1,12753
+ 10/01/1994      1,1273
+ 11/01/1994      1,12416
+ 12/01/1994      1,1275"
Warning message:
closing unused connection 3 (Lines)
Dec 1993 Jan 1994
 1.12509  1.12750

and read ?read.zoo, ?aggregate.zoo and the three zoo vignettes
vignette(package = "zoo") # lists their names
vignette("zoo") # displays first one
On Wed, Apr 15, 2009 at 2:26 PM, manta <mantino84 at libero.it> wrote:
manta
# 
Ok, thanks for the quick reply.
I was not able to use the first command, but reading the quick reference
helped me.
Here's what I did.
1996-01-01 1996-01-02 1996-01-03 1996-01-04 1996-01-05 1996-01-08 1996-01-09
1996-01-10 1996-01-11 1996-01-12 1996-01-15 
   1.33139    1.33244    1.32790    1.31406    1.32811    1.32665    1.32469   
1.32842    1.32902    1.32680    1.32279

Then, using
gen 1996 feb 1996 mar 1996 apr 1996 mag 1996 giu 1996 lug 1996 ago 1996 set
1996 ott 1996 nov 1996 dic 1996 gen 1997 
 1.28836  1.30718  1.30173  1.26778  1.27209  1.27933  1.31271  1.31042 
1.27980  1.28972  1.27267  1.27112  1.19681

But this is not what I wanted, because for example the january observation
is not the mean of the month of january, it is simply the last observation
of january. What I need is the mean of the month to be my monthly
observation. Also, the series has some missing data (exchange rates are not
traded every day) and every month could have different # of observation, is
this going to be a problem?

Thanks, Marco
Gabor Grothendieck wrote:

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      manta
    

    

      
      #
    

  


  

      
Ok, using

mcambio <- aggregate(cambio, as.yearmon, mean)

works perfectly!! Should I worry about the missing values or not anyway? And
then I go to the following question. From monthly data to daily using a
specific formula?

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      Gabor Grothendieck
    

    

      
      #
    

  


  

      
You can remove missing values with:

zm <- aggregate(cambio, as.yearmon, mean, na.rm = TRUE)

Its not clear what your second question is asking.  If you
want the series to have a Date class rather than yearmon class
with the 1st of the month then:

zd <- zm
time(zd) <- as.Date(time(zm))

or

zd <- aggregate(zm, as.Date, force)

        
On Wed, Apr 15, 2009 at 5:28 PM, manta <mantino84 at libero.it> wrote:

            

      
      
      
    

        

      
      
    

  
  


  


      

    

    

      
      

        


  

        

      manta
    

    

      
      #
    

  


  

      
Ok, I'll try to explain my issue. I have a monthly series (CPI index) and I
want to interpolate it using a specific lagged harmonized formula to get the
corresponding daily series. The formula is the following
 
CPI^=CPI(t-3)+(d-1)/D*(CPI(t-2)-CPI(t-3))
where

CPI^ is the CPI for the day we are calculating the reference CPI
CPI(t-i) is the CPI for the month i months prior
d is the day of the month for which we are calculating the reference CPI
D is the number of days in the month we are calculating

Then, the interpolation will give me 7 observations a week, but I need only
the observations from Monday to Friday. Therefore, I will have also to
discarde those estimates for Saturdays and Sundays.
Hope you can help. Thanks.

Marco

        
Gabor Grothendieck wrote:

            

      
      
      
    

        

  
    

      
      
    

  


  


      

    

    
1 day later

    

      
      

        
      

    

    

      
      

        


  

        

      Gabor Grothendieck
    

    

      
      #
    

  


  

      
Here is a partial solution:

library(zoo)
# z is CPI.  Just use 1, 2, 3, ... for example.
z <- zooreg(1:12, as.yearmon("2008-01"), freq = 12)
days.in.month <- as.numeric(as.Date(time(z), frac = 1) - as.Date(time(z)) + 1)
z2 <- cbind(z, z1 = lag(z, -1), z2 = lag(z, -2), z3 = lag(z, -3), days.in.month)
time(z2) <- as.Date(time(zz))
z3 <- na.locf(
		cbind(zz,
			zoo(, dd),
			day.of.month = as.numeric(format(time(zz), "%d"))
))
z3$day.of.month <- as.numeric(format(time(zz2), "%d"))
# now each row of z3 has all the data you need so apply(z3, 1, your.function)

        
On Fri, Apr 17, 2009 at 2:13 PM, manta <mantino84 at libero.it> wrote:

            

      
      
      
    

        

      
      
    

  
  


  


      

    

    

      
      

        


  

        

      manta
    

    

      
      #
    

  


  

      
Well Gabor, this is actually a really good help, thanks so much.
There is only one problem, I'm getting what you say, but in the code there
are a couple of errors

time(z2) <- as.Date(time(zz)) #probably z2
z3 <- na.locf(
		cbind(zz,
			zoo(, dd), #dd object not found
			day.of.month = as.numeric(format(time(zz), "%d"))
))
z3$day.of.month <- as.numeric(format(time(zz2), "%d")) #z2?

And why did you call partial this solution?
Thanks again for you great help
Take care

        
Gabor Grothendieck wrote:

            

      
      
      
    

        

      
      
    

            

      
      
      
    

        

      
      
    

        

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      Gabor Grothendieck
    

    

      
      #
    

  


  

      
Try this:

library(zoo)

# z is CPI.  Just use 1, 2, 3, ... for example.

z <- zooreg(1:12, as.yearmon("2008-01"), freq = 12)

# z2 contains z and lags of z and days.in.month
# It has a Date class time index, rather than yearmon.

days.in.month <- as.numeric(as.Date(time(z), frac = 1) - as.Date(time(z)) + 1)
z2 <- cbind(z, z1 = lag(z, -1), z2 = lag(z, -2), z3 = lag(z, -3), days.in.month)
time(z2) <- as.Date(time(z2))

# z3 is z2 expanded to include each day of each month and
# day of the month that each row represents

dd <- seq(time(z2)[1], as.Date(as.yearmon(tail(time(z2), 1)), frac = 1), "day")
z3 <- na.locf(cbind(z2, zoo(, dd)))
z3$day.of.month <- as.numeric(format(time(z3), "%d"))

# now each row of z3 has all the data you need so apply(z3, 1, your.function)

        
On Sat, Apr 18, 2009 at 2:28 PM, manta <mantino84 at libero.it> wrote: