Condense repeating values in table into one cell

I am looking at monthly storage data for a watershed. The data is from each month from 2005-2015. I have a variable with the datetimes, 'MonthDates', for the storage (i.e. Jan, Feb, Mar), and I have a variable with the storage, 'S', for each month. I created a table, 'storage', that has both of these variables. I am trying to find out which month has the lowest storage on average. This would be a lot easier for me to find if I could format the table so that the storage for each month is condensed into one place, rather than each month repeating ten times throughout the column in the table. I attached a photo of the table that I created in Matlab and a photo of what I would like the table format to look like in Excel. I am new to Matlab and am not very familiar with all of the commands, so any help is useful if you think there is a better way to find the average for each month.

Answer 1

[Peter Perkins]

You may have other reasons for reshaping your data, but it's easy to compute those means without doing so:

>> monthDates = datetime(2005,1,1,'Format','MM-yyyy') + calmonths(0:(12*11 - 1))';
>> storage = rand(size(t));
>> t = timetable(monthDates,storage)
t =
  132×1 timetable
    monthDates    storage 
    __________    ________
    01-2005        0.64912
    02-2005        0.73172
    03-2005        0.64775
    04-2005        0.45092
    05-2005        0.54701
    06-2005        0.29632
    07-2005        0.74469
    [snip]

If you put one more variable in that timetable ...

>> t.monthOfYear = categorical(month(t.monthDates,'shortname'))
t =
  132×2 timetable
    monthDates    storage     monthOfYear
    __________    ________    ___________
    01-2005        0.64912        Jan    
    02-2005        0.73172        Feb    
    03-2005        0.64775        Mar    
    04-2005        0.45092        Apr    
    05-2005        0.54701        May    
    06-2005        0.29632        Jun    
    07-2005        0.74469        Jul    
    [snip]

... then computing the monthly means is one line:

>> monthlyMeans = varfun(@mean,t,'InputVariables','storage','GroupingVariables','monthOfYear','OutputFormat','table')
monthlyMeans =
  12×3 table
    monthOfYear    GroupCount    mean_storage
    ___________    __________    ____________
        Apr            11          0.55913   
        Aug            11          0.32596   
        Dec            11          0.51905   
        Feb            11          0.56055   
        Jan            11           0.5817   
        Jul            11          0.49449   
        Jun            11          0.41813   
        Mar            11           0.7096   
        May            11          0.39363   
        Nov            11          0.43788   
        Oct            11          0.51204   
        Sep            11          0.53772

Note that the retime function also supports computing "monthly means", but in a different sense: compute the mean value within each month within each year. That would be appropriate for something like aggregating daily data, but in your case you want to compute means across years, so retime is not the right way.

But to answer your original question, add one more variable to your data, convert to a table, and unstack:

>> t.year = year(t.monthDates)
t =
  132×3 timetable
    monthDates    storage     monthOfYear    year
    __________    ________    ___________    ____
    01-2005        0.64912        Jan        2005
    02-2005        0.73172        Feb        2005
    03-2005        0.64775        Mar        2005
    04-2005        0.45092        Apr        2005
    05-2005        0.54701        May        2005
    06-2005        0.29632        Jun        2005
    07-2005        0.74469        Jul        2005
    [snip]
>> t = timetable2table(t,'ConvertRowTimes',false)
t =
  132×3 table
    storage     monthOfYear    year
    ________    ___________    ____
     0.64912        Jan        2005
     0.73172        Feb        2005
     0.64775        Mar        2005
     0.45092        Apr        2005
     0.54701        May        2005
     0.29632        Jun        2005
     0.74469        Jul        2005
     [snip]

>> unstack(t,'storage','year','GroupingVariable','monthOfYear')
Warning: Table variable names were modified to make them valid MATLAB identifiers. 
ans =
  12×12 table
    monthOfYear     x2005      x2006       x2007      x2008      x2009      x2010       x2011      x2012       x2013       x2014       x2015  
    ___________    _______    ________    _______    _______    _______    ________    _______    ________    ________    ________    ________
        Jan        0.64912     0.78023    0.64432    0.47092    0.90488     0.29668    0.23728    0.037739     0.49417     0.90472     0.97868
        Feb        0.73172    0.081126    0.37861    0.23049    0.97975     0.31878    0.45885     0.88517     0.77905     0.60987     0.71269
        Mar        0.64775     0.92939    0.81158    0.84431    0.43887     0.42417    0.96309     0.91329     0.71504     0.61767     0.50047
        Apr        0.45092     0.77571    0.53283    0.19476    0.11112     0.50786    0.54681     0.79618     0.90372     0.85944     0.47109
        May        0.54701     0.48679    0.35073    0.22592    0.25806    0.085516    0.52114    0.098712     0.89092     0.80549    0.059619
        Jun        0.29632     0.43586      0.939    0.17071    0.40872     0.26248    0.23159     0.26187     0.33416     0.57672     0.68197
        Jul        0.74469     0.44678    0.87594    0.22766     0.5949     0.80101     0.4889     0.33536     0.69875     0.18292    0.042431
        Aug        0.18896     0.30635    0.55016     0.4357    0.26221     0.02922    0.62406     0.67973     0.19781     0.23993    0.071445
        Sep        0.68678     0.50851    0.62248     0.3111    0.60284     0.92885    0.67914     0.13655    0.030541     0.88651     0.52165
        Oct        0.18351     0.51077    0.58704    0.92338    0.71122     0.73033    0.39552     0.72123     0.74407    0.028674     0.09673
        Nov        0.36848     0.81763    0.20774    0.43021    0.22175     0.48861    0.36744     0.10676     0.50002      0.4899     0.81815
        Dec        0.62562     0.79483    0.30125    0.18482    0.11742     0.57853    0.98798     0.65376     0.47992     0.16793     0.81755

Or

>> unstack(t,'storage','monthOfYear','GroupingVariable','year')
ans =
  11×13 table
    year      Apr        Aug         Dec        Feb         Jan         Jul         Jun        Mar        May         Nov        Oct         Sep   
    ____    _______    ________    _______    ________    ________    ________    _______    _______    ________    _______    ________    ________
    2005    0.45092     0.18896    0.62562     0.73172     0.64912     0.74469    0.29632    0.64775     0.54701    0.36848     0.18351     0.68678
    2006    0.77571     0.30635    0.79483    0.081126     0.78023     0.44678    0.43586    0.92939     0.48679    0.81763     0.51077     0.50851
    2007    0.53283     0.55016    0.30125     0.37861     0.64432     0.87594      0.939    0.81158     0.35073    0.20774     0.58704     0.62248
    2008    0.19476      0.4357    0.18482     0.23049     0.47092     0.22766    0.17071    0.84431     0.22592    0.43021     0.92338      0.3111
    2009    0.11112     0.26221    0.11742     0.97975     0.90488      0.5949    0.40872    0.43887     0.25806    0.22175     0.71122     0.60284
    2010    0.50786     0.02922    0.57853     0.31878     0.29668     0.80101    0.26248    0.42417    0.085516    0.48861     0.73033     0.92885
    2011    0.54681     0.62406    0.98798     0.45885     0.23728      0.4889    0.23159    0.96309     0.52114    0.36744     0.39552     0.67914
    2012    0.79618     0.67973    0.65376     0.88517    0.037739     0.33536    0.26187    0.91329    0.098712    0.10676     0.72123     0.13655
    2013    0.90372     0.19781    0.47992     0.77905     0.49417     0.69875    0.33416    0.71504     0.89092    0.50002     0.74407    0.030541
    2014    0.85944     0.23993    0.16793     0.60987     0.90472     0.18292    0.57672    0.61767     0.80549     0.4899    0.028674     0.88651
    2015    0.47109    0.071445    0.81755     0.71269     0.97868    0.042431    0.68197    0.50047    0.059619    0.81815     0.09673     0.52165

[Stephanie Arnold]

Thank you!! This is super helpful!!!