data frequency conversion -cell matrix

 Dear all,

I have the following cell matrix

 A = {

 1       ' '                        [     NaN]    [    NaN]     
 1      ' '                     [     NaN]    [    NaN]     
 1       ' '                       [     NaN]    [    NaN]     
 1      'MA 2009'              [          0]    [          0]    
 1       'MJ 2009'             [     0.2680]    [     3.0394]     
 1      'JA 2009'               [     0.0504]    [     0.6475]    
 1       'SO 2009'            [    14.0985]    [   148.2583]     
 1      'ND 2009'                    [     0.1128]    [     1.1506]     
 1       'JF 2010'                [    NaN]    [   148.2583]         
 1       'MA 2010'                   [     2.5852]    [    34.0146]    
 1       'MJ 2010'                [     0.3220]    [     3.2846]    
 1      'JA 2010'                    [    14.0985]    [   148.2583]     
 1      'SO 2010'                     [     2.5852]    [    NaN]     
 1      'ND 2010'                  [     0.2938]    [     2.8540]     
 1       'JF 2011'                   [     0.1128]    [     1.1506]     
 1       'MA 2011'                 [    14.0985]    [   148.2583]    
 1       'MJ 2011'                     [     2.1091]    [    15.0233]     
 1      'JA 2011'                   [          0]    [          0]     
 2       ' '                      [     NaN]    [    NaN]     
 2      ' '                        [     NaN]    [    NaN]     
 2      ' '                        [     NaN]    [    NaN]     
 2       'MA 2009'                    [    14.0985]    [   148.2583]     
 2       'MJ 2009'                   [     2.7827]    [    18.9879]     
 2      'JA 2009'                    [    11.8755]    [   126.4359]    
 2       'SO 2009'                  [     0.0589]    [     0.6685]     
 2       'ND 2009'                  [    11.8755]    [   126.4359]     
 2      'JF 2010'                   [     0.0504]    [     0.6475]     
 2       'MA 2010'                  [    11.8755]    [   126.4359]     
 2       'MJ 2010'                   [     0.0504]    [     0.6475]     
 2      'JA 2010'                     [          0]    [          0]     
 2       'SO 2010'                   [     0.0248]    [     0.2823]     
 2      'ND 2010'                   [          0]    [          0]     
 2       'JF 2011'                    [     2.5852]    [    34.0146]     
 2      'MA 2011'                    [     0.0207]    [     0.2282]     
 2      'MJ 2011'                   [    11.8755]    [   126.4359]     
 2       'JA 2011'                 [    14.0985]    [   148.2583]     
 3       ' '                          [     NaN]    [    NaN]     
 3       ' '                        [     NaN]    [    NaN]     
 3      ' '                             [     NaN]    [    NaN]     
 3       'MA 2009'                      [     2.1091]    [    15.0233]     
 3      'MJ 2009'                   [          0]    [          0]     
 3      'JA 2009'                    [     0.1128]    [     1.1506]    
 3       'SO 2009'                   [     0.0207]    [     0.2282]     
 3       'ND 2009'                   [          0]    [          0]     
 3       'JF 2010'                   [    NaN]    [     1.1506]     
 3       'MA 2010'                    [          0]    [          0]     
 3      'MJ 2010'                     [     2.1091]    [    15.0233]     
 3       'JA 2010'                    [          0]    [          0]     
 3      'SO 2010'                   [     2.7827]    [    NaN]     
 3      'ND 2010'                   [          0]    [          0]     
 3       'JF 2011'                     [     0.0207]    [     0.2282]     
 3      'MA 2011'                      [     2.5852]    [    34.0146]    
 3      'MJ 2011'                   [          0]    [          0]     
 3       'JA 2011'                    [    11.8755]    [   126.4359]     
  }

I want to convert these data from Bimontly to monthly for each inividual i (first column).

I searched for relevant functions and I found for example: tomonthly() but I do not know how to exactly apply it to the above setting. Note that I have 30000 invividuals and 20 numerical columns instead of the last 2 that I display above

Any help is greately appreicated thanks in advance

[salva]

If something is unclear please inform me

thanks

[per isakson]

You write:

    "I want to convert these data from Bimontly to monthly 
    for each inividual i (first column)."

but you don't write by what rule that should be done. Divide the bimonthly value by two?

[salva]

thank you per.

I was thinking somethink like

A = {

 1       ' '                        
 1      ' '                     
 1       ' '                           
 1      '3/2009'                
 1       '4/2009'               
 1      '5/2009'                   
 1       '6/2009'             
}

and so forth where as you can see the MA 2009 date is split up in 3/2009 and 4/2009. NOw for each month I have to change accordingly the last two columns. I think that one way to do that is to divide the bimontly values by two. The other way would be to interpolate each bimontly value to monthly values. Could I also use the tomonthly function?

thanks you

[per isakson]

Documentation of tomonthly says:

    newfts = tomonthly(oldfts)

where

    oldfts is Financial time series object

I don't have the Financial toolbox. However, the documentation indicates that tomonthly calculates monthly data from data with higher resolution, e.g. daily. I don't think it can take bimonthly data.

I cannot make sense of your example. Am I right that

     1, 'MJ 2009', 0.2680, 3.0394

is data for May and June 2009, i.e. 5/2009 and 6/2009? In your example you assign these numbers to 4/2009.

[salva]

Yes, you are right. the last two colums are wrong. I just wanted to show the split up of the dates and I left the last two columns as they are.

So you propose to divide each bimontly value with 2?

But then for 3/2009 and 4/2009 I will have the same values? Am I right?Does it make sense to use interpolation?

thanks

[salva]

Do you need more information? Please let me know

thanks

[Andrei Bobrov]

What would you like to have instead of ?

 A = {...
 1      'MA 2009'              [          0]    [          0]    
 1      'MJ 2009'              [     0.2680]    [     3.0394]     
 1      'JA 2009'              [     0.0504]    [     0.6475]}

in

 Aout = {...
 1       '3/2009'              [          ?]    [          ?]  
 1       '4/2009'              [          ?]    [          ?] 
 1       '5/2009'              [          ?]    [          ?]  
 1       '6/2009'              [          ?]    [          ?]
 1       '7/2009'              [          ?]    [          ?]
 1       '8/2009'              [          ?]    [          ?]}

[salva]

yes exactly I want to start with A and obtain Aout. Please helppppp!

thanks

[Oleg Komarov]

@Andrei: he wants to divide it by 2.

Answer 1

[Andrei Bobrov]

n = cellfun(@(x)x/2,A(:,3:end),'un',0);
Out = [];
for ii = 1:size(A,1)
    if strcmp(A(ii,2),' ')
        Out = [Out; A(ii,:)];
    else
        [~,k] = ismember(A{ii,2}(1:2),{'JF','MA','MJ','JA','SO','ND'});
        y = A{ii,2}(4:end);
        Out = [Out;
               [A(ii,1),{[sprintf('%d',k*2-1),'/',y]},n(ii,:);
                A(ii,1),{[sprintf('%d',k*2),'/',y]},n(ii,:)]];
    end
end

[salva]

perfect!