%% Grouped Statistics Calculations with Tall Arrays
% This example shows how to calculate grouped statistics of a tall timetable containing power outage data. The example uses the grouptransform, groupsummary, and groupcounts functions to calculate various quantities of interest, such as the most common power outage cause in each region. Even though the raw data in this example only has about 1500 rows, you can use the techniques presented here on much larger data sets because no assumptions are made about the size of the data.
%
%% Create Datastore and Tall Timetable
% The sample file, outages.csv, contains data representing electric utility outages in the United States. The file contains six columns: Region, OutageTime, Loss, Customers, RestorationTime, and Cause.
% Create a datastore for the outages.csv file. Use the "TextScanFormats" option to specify the kind of data each column contains: categorical ("%C"), floating-point numeric ("%f"), or datetime ("%D").

data_formats = ["%C","%D","%f","%f","%D","%C"];
ds = tabularTextDatastore("outages.csv","TextscanFormats",data_formats);

%
% Create a tall table on top of the datastore, and convert the tall table into a tall timetable. The OutageTime variable is used for the row times since it is the first datetime or duration variable in the table.

T = tall(ds);
T = table2timetable(T)

%
%
%% Replace Missing Data
% Some of the rows in the RestorationTime variable have missing times, represented by NaT values. Remove these rows from the table.

T = rmmissing(T,"DataVariables","RestorationTime");

% For the numeric variables in the timetable, instead of removing rows with missing values, replace the missing values with the mean value for each region.

T = grouptransform(T,"Region","meanfill",["Loss","Customers"]);

% Use ismissing to confirm that no pieces of missing data remain in the table.

tf = any(ismissing(T),"all");
gather(tf)

%
%% Preview Data
% Now that the data does not contain missing values, bring a small number of rows into memory to get an idea of what the data contains.

gather(head(T))

%
%% Mean Power Outage Duration by Region
% Determine the mean power outage duration in each region using groupsummary. First, create a new variable OutageDuration in the table that contains the duration of each outage, found by subtracting the outage time from the restoration time. In the call to groupsummary, specify:
% "Region" as the grouping variable
% "mean" as the computation method
% "OutageDuration" as the variable to operate on.

T.OutageDuration = T.RestorationTime - T.OutageTime;
times = groupsummary(T,"Region","mean","OutageDuration")

%
% Change the display format of the duration results to be in days, and gather the results into memory. The results show the mean outage duration in each region, as well as the number of reported outages in each region.

times.mean_OutageDuration.Format = "d";
times = gather(times)

%
%% Most Common Power Outage Causes by Region
% Determine how often each power outage cause occurs in each region. Use groupcounts with the Cause and Region variables as grouping variables. Gather the results into memory.

causes = groupcounts(T,["Cause","Region"]);
causes = gather(causes)

% Each cause occurs several times in the table, so even though the table contains the correct data it is not in the proper format to see how often each cause occurs in each region. To improve the presentation of the data, unstack the GroupCount variable so that each column corresponds to a region and each row corresponds to an outage cause.

RegionCauses = gather(unstack(causes,"GroupCount","Region"))

% Not all combinations of outage causes and regions are represented in the data, so the resulting table contains some NaNs. Fill in the NaN values with zeros.

RegionCauses = fillmissing(RegionCauses,"constant",{"",0,0,0,0,0})

%
%% Worst Power Outages in Each Region
% Calculate the broadest customer impact for each power outage in each region.

WorstOutages = groupsummary(T,["Region","Cause"],"max","Customers");
WorstOutages = gather(WorstOutages)

% Combine the data in the Region and Cause variables into a single categorical variable by briefly converting them into strings. Then, create a categorical histogram of the maximum number of affected customers for each cause in each region.

WorstOutages.RegionCause = categorical(string(WorstOutages.Region)+" ("+string(WorstOutages.Cause)+")");
histogram("Categories",WorstOutages.RegionCause,"BinCounts",WorstOutages.max_Customers,...
    "DisplayOrder","descend")
ylabel("Max # Affected Customers")

%
% Copyright 2019 The MathWorks, Inc.