computing montly averages when time series observations are irregural

 Dear all,
I have the following problem

Let

A ={

'02/11/08'    [0.2150]    [429.5674]    [    100]    [2.2678e+04]

   '30/11/08'    [0.2117]    [463.1360]    [    100]    [2.4383e+04]

    '28/12/08'    [0.2209]    [436.6316]    [    100]    [2.3148e+04]

    '25/01/09'    [0.2212]    [441.5430]    [    100]    [2.2987e+04]

    '22/02/09'    [0.2201]    [453.7015]    [    100]    [2.3675e+04]
    '22/03/09'    [0.2185]    [461.3925]    [    100]    [2.3680e+04]
    '19/04/09'    [0.2104]    [486.4095]    [    100]    [2.5367e+04]
    '17/05/09'    [0.2162]    [451.4833]    [    100]    [2.3986e+04]
    '14/06/09'    [0.2158]    [475.8620]    [    100]    [2.4245e+04]
    '12/07/09'    [0.2211]    [449.4574]    [    100]    [2.2766e+04]
    '09/08/09'    [0.2221]    [456.2507]    [    100]    [2.2523e+04]
    '06/09/09'    [0.2175]    [472.2659]    [    100]    [2.3593e+04]
    '04/10/09'    [0.2182]    [479.3408]    [    100]    [2.4359e+04]
    '01/11/09'    [0.2286]    [442.6719]    [    100]    [2.1490e+04]
    '29/11/09'    [0.2211]    [481.4548]    [    100]    [2.4054e+04]
    '27/12/09'    [0.2259]    [468.2757]    [    100]    [2.3037e+04]
    '31/01/10'    [0.2300]    [461.5581]    [    100]    [2.2050e+04]
    '28/02/10'    [0.2259]    [487.6257]    [    100]    [2.3293e+04]
    '28/03/10'    [0.2200]    [529.8777]    [    100]    [2.5493e+04]
    '25/04/10'    [0.2315]    [433.3039]    [    100]    [2.0387e+04]
    '23/05/10'    [0.2274]    [500.8603]    [    100]    [2.4019e+04]
}

As you can see from the first column I have irregular time series observations. Each observation can represent a 4-week or a 5-week or a 6-week average. For example the observation [0.2117] which is the second value of the second column is the average of the values that have been recorded between ‘03/11/08' and '30/11/08' In order to be able to analyze econometrically these data and run my regressions I need to obtain regular time series observations. So I need to convert these values to monthly averages and have something like

Let A ={

'11/08'     [mon. average]     [mon. average]    [mon. average] 
  '12/08' [mon. average]     [mon. average]    [mon. average] 
  '01/09'  [mon. average]     [mon. average]    [mon. average]  
  '02/09'    [mon. average]     [mon. average]    [mon. average] 
  '03/09'   [mon. average]     [mon. average]    [mon. average] 
  '04/09'   [mon. average]     [mon. average]    [mon. average]    
  '05/09'  [mon. average]     [mon. average]    [mon. average]  
  '06/09'  [mon. average]     [mon. average]    [mon. average]      
  '07/09'  [mon. average]     [mon. average]    [mon. average]     
  '08/09'  [mon. average]     [mon. average]    [mon. average]     
  '09/09'  [mon. average]     [mon. average]    [mon. average]      
  '10/09' [mon. average]     [mon. average]    [mon. average]      
  '11/09'  [mon. average]     [mon. average]    [mon. average]    
  '12/09'  [mon. average]     [mon. average]     [mon. average]

}

Could interpolation be a solution to this problem? I would be grateful if you could provide some code.

cheers

[Image Analyst]

Do any of these overlap each other? If they don't, what if the observation time is in the middle of the month and went back 6 weeks into the prior month? So one month could have two mean values that can be applied to it, or even three if the observation period was only 28 days and it went from the 2nd to the 29th of a month with 30 or 31 days. Do you split that into two or three periods? Or would you expand each period so that you have one day with the mean value, then split into months and get the mean over the month? I think that's the way to go, and that's why you asked about interpolation. But you'd have to consider it's better to fill in all the days with interpolated values, or with the mean value for the time period they're in. If these are observations of random events (like stock prices or rainfall), then I don't think interpolation is valid. For example, I don't think you can guess the rainfall on July 28 by interpolating the average for the month of August and the month of June. I'd think just replicating the mean might be the best way (but I'm not a professional statistician).

[antonet]

Hi imager! yes as you can see there is overlapping across the dates as each value is the average of the values that have been observed 4 weeks (or 5 weeks or 6 weeks) back. Advice me what is the best approach to calculate the monthly averages. PLease ask me if you require further information

[antonet]

Imager HELP!

thanks

Answer 1

[per isakson]

Hint:

• vec = datevec( A{:,1}, 'dd/mm/yy' )
• out = accumarray( vec(:,2), A(something), [], @mean )

Better

    vec = datevec( char(A{:,1}), 'dd/mm/yy' );
    out = accumarray( vec(:,2:3), transpose([A{:,2}]), [], @mean );

This returns the means of the values of each month respectively. There is a value for each month. Is that what you want?

[antonet]

I will check the outcome and I will tell you. Thank you so much so the code!