I need to break down the flow data into groups of data with months for every year.

For example: All the entries for the month of Jan for all the years (suppose 2010-2022) are stored in one group. Similarly, all the entries for the month of Feb for all the years.(suppose 2010-2022) are stored in second group and so on. My end goal is to calculate the percentile of monthly data for all the years.

Date Flow

12/1/2010 100

12/2/2010 200

12/3/2010 150

12/4/2010 176

.....

.....

12/1/2022 145

I tried to create the vlookup function to create the matrix but i wasn't able to. So, I decided to group all months together and then perform operations on them.

Answer 1

[Cris LaPierre]

Look into groupsummary, specifically the groupbin option 'monthofyear'.

[Cris LaPierre]

We are working a little blind, as you haven't shared your data, but here's an example using a data set included in MATLAB.

One thing to check is that the variable you want to group by is a datetime.

T = readtable('outages.csv')

T = 1468×6 table
       Region           OutageTime        Loss     Customers     RestorationTime            Cause       
    _____________    ________________    ______    __________    ________________    ___________________

    {'SouthWest'}    2002-02-01 12:18    458.98    1.8202e+06    2002-02-07 16:50    {'winter storm'   }
    {'SouthEast'}    2003-01-23 00:49    530.14    2.1204e+05                 NaT    {'winter storm'   }
    {'SouthEast'}    2003-02-07 21:15     289.4    1.4294e+05    2003-02-17 08:14    {'winter storm'   }
    {'West'     }    2004-04-06 05:44    434.81    3.4037e+05    2004-04-06 06:10    {'equipment fault'}
    {'MidWest'  }    2002-03-16 06:18    186.44    2.1275e+05    2002-03-18 23:23    {'severe storm'   }
    {'West'     }    2003-06-18 02:49         0             0    2003-06-18 10:54    {'attack'         }
    {'West'     }    2004-06-20 14:39    231.29           NaN    2004-06-20 19:16    {'equipment fault'}
    {'West'     }    2002-06-06 19:28    311.86           NaN    2002-06-07 00:51    {'equipment fault'}
    {'NorthEast'}    2003-07-16 16:23    239.93         49434    2003-07-17 01:12    {'fire'           }
    {'MidWest'  }    2004-09-27 11:09    286.72         66104    2004-09-27 16:37    {'equipment fault'}
    {'SouthEast'}    2004-09-05 17:48    73.387         36073    2004-09-05 20:46    {'equipment fault'}
    {'West'     }    2004-05-21 21:45    159.99           NaN    2004-05-22 04:23    {'equipment fault'}
    {'SouthEast'}    2002-09-01 18:22    95.917         36759    2002-09-01 19:12    {'severe storm'   }
    {'SouthEast'}    2003-09-27 07:32       NaN    3.5517e+05    2003-10-04 07:02    {'severe storm'   }
    {'West'     }    2003-11-12 06:12    254.09    9.2429e+05    2003-11-17 02:04    {'winter storm'   }
    {'NorthEast'}    2004-09-18 05:54         0             0                 NaT    {'equipment fault'}

Tsum = groupsummary(T,'OutageTime','monthofyear','mean','Loss')

Tsum = 12×3 table
    monthofyear_OutageTime    GroupCount    mean_Loss
    ______________________    __________    _________

              1                  103         386.13  
              2                  118         366.32  
              3                   95         284.17  
              4                   80         707.81  
              5                  106         731.59  
              6                  165         405.09  
              7                  185         764.26  
              8                  170         831.86  
              9                  148         854.78  
              10                  94         388.59  
              11                  93         232.35  
              12                 111         452.83  

This calculates the mean loss by month. You could then use the resutls to 'calculate the percentile of monthly data for all the years'.

[Harjas]

Thanks for your help. I was able to use the groupsummary to group data into months. But, can I directly use prctile to calculate the percentile of months instead of mean. Or I have to access the access the data of each group and calculate the percentile separately.

[Cris LaPierre]

You can specify a function for the method. Use the following syntax:

To use prctile with this example with a percentage of 42, I would do the following:

T = readtable('outages.csv')

T = 1468×6 table
       Region           OutageTime        Loss     Customers     RestorationTime            Cause       
    _____________    ________________    ______    __________    ________________    ___________________

    {'SouthWest'}    2002-02-01 12:18    458.98    1.8202e+06    2002-02-07 16:50    {'winter storm'   }
    {'SouthEast'}    2003-01-23 00:49    530.14    2.1204e+05                 NaT    {'winter storm'   }
    {'SouthEast'}    2003-02-07 21:15     289.4    1.4294e+05    2003-02-17 08:14    {'winter storm'   }
    {'West'     }    2004-04-06 05:44    434.81    3.4037e+05    2004-04-06 06:10    {'equipment fault'}
    {'MidWest'  }    2002-03-16 06:18    186.44    2.1275e+05    2002-03-18 23:23    {'severe storm'   }
    {'West'     }    2003-06-18 02:49         0             0    2003-06-18 10:54    {'attack'         }
    {'West'     }    2004-06-20 14:39    231.29           NaN    2004-06-20 19:16    {'equipment fault'}
    {'West'     }    2002-06-06 19:28    311.86           NaN    2002-06-07 00:51    {'equipment fault'}
    {'NorthEast'}    2003-07-16 16:23    239.93         49434    2003-07-17 01:12    {'fire'           }
    {'MidWest'  }    2004-09-27 11:09    286.72         66104    2004-09-27 16:37    {'equipment fault'}
    {'SouthEast'}    2004-09-05 17:48    73.387         36073    2004-09-05 20:46    {'equipment fault'}
    {'West'     }    2004-05-21 21:45    159.99           NaN    2004-05-22 04:23    {'equipment fault'}
    {'SouthEast'}    2002-09-01 18:22    95.917         36759    2002-09-01 19:12    {'severe storm'   }
    {'SouthEast'}    2003-09-27 07:32       NaN    3.5517e+05    2003-10-04 07:02    {'severe storm'   }
    {'West'     }    2003-11-12 06:12    254.09    9.2429e+05    2003-11-17 02:04    {'winter storm'   }
    {'NorthEast'}    2004-09-18 05:54         0             0                 NaT    {'equipment fault'}

Tsum = groupsummary(T,'OutageTime','monthofyear',@(A) prctile(A,42),'Loss')

Tsum = 12×3 table
    monthofyear_OutageTime    GroupCount    fun1_Loss
    ______________________    __________    _________

              1                  103         146.13  
              2                  118         154.68  
              3                   95         115.89  
              4                   80         130.31  
              5                  106         138.05  
              6                  165         173.06  
              7                  185         163.81  
              8                  170          82.64  
              9                  148         239.51  
              10                  94         65.001  
              11                  93         30.416  
              12                 111         98.327  

[Harjas]

Thanks a lot. It worked. My attached data is continous from 1st Jan to 31st Dec for 5 years(2001-2005). I want to compare the data (For eg: Minimun, Maximum, Percentile) from 27th Sept to 27th Oct for particular year- 2004 with all the years within that range(27-09 to 27-10). How can I modify the above syntax to be able to group the data for all years but only from 27th Sept to 27th Oct and perform the required operations (For eg: Minimun, Maximum, Percentile) ?

[Cris LaPierre]

I think the best approach here is to create a second table that is a subset of the full data set, but just contains the date ranges of interest. Then you can use groupsummary to compute the desired stats grouped by year. I don't know what you want to use for percentile, so I use 42.

T = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/1043800/Example.xlsx',...
    'ReadVariableNames',false)

T = 1826×2 table
       Var1        Var2
    ___________    ____

    01-Jan-2001     10 
    02-Jan-2001     20 
    03-Jan-2001     30 
    04-Jan-2001     40 
    05-Jan-2001     50 
    06-Jan-2001     60 
    07-Jan-2001     70 
    08-Jan-2001     80 
    09-Jan-2001     90 
    10-Jan-2001    100 
    11-Jan-2001    110 
    12-Jan-2001    120 
    13-Jan-2001    130 
    14-Jan-2001    140 
    15-Jan-2001    150 
    16-Jan-2001    160 

% Use 'day of year' to identify dates within the desired range for all years
drng = day(datetime(2004,9,27):caldays(1):datetime(2004,10,27),'dayofyear');
d = day(T.Var1,'dayofyear');

% Create subtable
idx= ismember(d,drng);
Tsub = T(idx,:)

Tsub = 155×2 table
       Var1        Var2
    ___________    ____

    28-Sep-2001    2710
    29-Sep-2001    2720
    30-Sep-2001    2730
    01-Oct-2001    2740
    02-Oct-2001    2750
    03-Oct-2001    2760
    04-Oct-2001    2770
    05-Oct-2001    2780
    06-Oct-2001    2790
    07-Oct-2001    2800
    08-Oct-2001    2810
    09-Oct-2001    2820
    10-Oct-2001    2830
    11-Oct-2001    2840
    12-Oct-2001    2850
    13-Oct-2001    2860

% compute stats for each year
Tstats = groupsummary(Tsub,'Var1','year',{@(A) prctile(A,42),'max','min'},'Var2');
Tstats.Properties.VariableNames = ["Tyr","GroupCnt","Tmin","Tmax","Tpct"]

Tstats = 5×5 table
    Tyr     GroupCnt     Tmin     Tmax     Tpct 
    ____    ________    ______    _____    _____

    2001       31       2835.2     3010     2710
    2002       31       6485.2     6660     6360
    2003       31        10135    10310    10010
    2004       31        13785    13960    13660
    2005       31        17445    17620    17320

[Cris LaPierre]

Leap years are going to cause some issues with my previous answer. Unfortunately, I couldn't find a slick way to do this, so here's a brute force approach.

T = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/1043800/Example.xlsx',...
    'ReadVariableNames',false);

% Define range of interest
startD = datetime(2004,9,27);
stopD = datetime(2004,10,27);

% find data for all years between the indicated month/days
y = unique(year(T.Var1));
idx = zeros(height(T),1);
for i = 1:length(y)
    idx = idx + isbetween(T.Var1,datetime(y(i),month(startD),day(startD)),...
        datetime(y(i),month(stopD),day(stopD)));
end
Tsub = T(logical(idx),:)

Tsub = 155×2 table
       Var1        Var2
    ___________    ____

    27-Sep-2001    2700
    28-Sep-2001    2710
    29-Sep-2001    2720
    30-Sep-2001    2730
    01-Oct-2001    2740
    02-Oct-2001    2750
    03-Oct-2001    2760
    04-Oct-2001    2770
    05-Oct-2001    2780
    06-Oct-2001    2790
    07-Oct-2001    2800
    08-Oct-2001    2810
    09-Oct-2001    2820
    10-Oct-2001    2830
    11-Oct-2001    2840
    12-Oct-2001    2850

% Compute summary stats
Tstats = groupsummary(Tsub,'Var1','year',{@(A) prctile(A,42),'max','min'},'Var2');
Tstats.Properties.VariableNames = ["Tyr","GroupCnt","Tmin","Tmax","Tpct"]

Tstats = 5×5 table
    Tyr     GroupCnt     Tmin     Tmax     Tpct 
    ____    ________    ______    _____    _____

    2001       31       2825.2     3000     2700
    2002       31       6475.2     6650     6350
    2003       31        10125    10300    10000
    2004       31        13785    13960    13660
    2005       31        17435    17610    17310