Group By

The group_by function allows you to group one or more columns and apply a function to the result. Specifically, the group_by function performs the following actions on an H2O Frame:

  • splits the data into groups based on some criteria
  • applies a function to each group independently
  • combines the results into an H2OFrame

The result is a new H2OFrame with columns equivalent to the number of groups created. The returned groups are sorted by the natural group-by column sort.

The group_by function accepts the following parameters:

Python and R

  • H2O Frame: This specifies the H2OFrame that you want the group by operation to be performed on.
  • by: The by option can take a list of columns if you want to group by more than one column to compute the summary.

Python Only

  • na, which controls treatment of NA values during the calculation. It can be one of:
    • all (default): any NAs are used in the calculation as-is; which usually results in the final result being NA too.
    • ignore: NA entries are not included in calculations, but the total number of entries is taken as the total number of rows. For example, mean([1, 2, 3, nan], na="ignore") will produce 1.5.
    • rm: entries are skipped during the calculations, reducing the total effective count of entries. For example, mean([1, 2, 3, nan], na="rm") will produce 2.

R Only

  • gb.control: In R, the gb.control option specifies how to handle NA values in the dataset as well as how to name output columns. Note that to specify a list of column names in the gb.control list, you must add the col.names argument.
  • nrow: Specify the name of the generated column.
  • na.methods, which controls treatment of NA values during the calculation. It can be one of:
  • all (default): any NAs are used in the calculation as-is; which usually results in the final result being NA too.
  • ignore: NA entries are not included in calculations, but the total number of entries is taken as the total number of rows. For example, mean([1, 2, 3, nan], na="ignore") will produce 1.5.
  • rm: entries are skipped during the calculations, reducing the total effective count of entries. For example, mean([1, 2, 3, nan], na="rm") will produce 2.

Note: If a list smaller than the number of columns groups is supplied, then the list will be padded by ignore.

In addition to the above parameters, any number of the following aggregations can be chained together in the group_by function:

  • count: Count the number of rows in each group of a GroupBy object.
  • max: Calculate the maximum of each column specified in col for each group of a GroupBy object.
  • mean: Calculate the mean of each column specified in col for each group of a GroupBy object.
  • min: Calculate the minimum of each column specified in col for each group of a GroupBy object.
  • mode: Calculate the mode of each column specified in col for each group of a GroupBy object.
  • sd: Calculate the standard deviation of each column specified in col for each group of a GroupBy object.
  • ss: Calculate the sum of squares of each column specified in col for each group of a GroupBy object.
  • sum: Calculate the sum of each column specified in col for each group of a GroupBy object.
  • var: Calculate the variance of each column specified in col for each group of a GroupBy object.
If no arguments are given to the aggregation (e.g., max() in grouped.sum(col="X1", na="all").mean(col="X5", na="all").max()), then it is assumed that the aggregation should apply to all columns except the GroupBy columns.

Note that once the aggregation operations are complete, calling the GroupBy object with a new set of aggregations will yield no effect. You must generate a new GroupBy object in order to apply a new aggregation on it. In addition, certain aggregations are only defined for numerical or categorical columns. An error will be thrown for calling aggregation on the wrong data types.

> library(h2o)
> h2o.init()

# Import the airlines data set and display a summary.
> airlinesURL <- "https://s3.amazonaws.com/h2o-airlines-unpacked/allyears2k.csv"
> airlines.hex <- h2o.importFile(path = airlinesURL, destination_frame = "airlines.hex")
> summary(airlines.hex)

# Find number of flights by airport
> originFlights <- h2o.group_by(data = airlines.hex, by = "Origin", nrow("Origin"), gb.control=list(na.methods="rm"))
> originFlights.R <- as.data.frame(originFlights)
> originFlights.R
    Origin nrow_Origin
1      ABE          59
2      ABQ         876
3      ACY          31
...

# Find number of flights per month
> flightsByMonth <- h2o.group_by(data = airlines.hex, by = "Month", nrow("Month"), gb.control=list(na.methods="rm"))
> flightsByMonth.R <- as.data.frame(flightsByMonth)
> flightsByMonth.R
  Month nrow_Month
1     1      41979
2    10       1999

# Find the number of flights in a given month based on the origin
> cols <- c("Origin","Month")
> flightsByOriginMonth <- h2o.group_by(data=airlines.hex, by=cols, nrow("NumberOfFlights"), gb.control=list(na.methods="rm")
> flightsByOriginMonth.R <- as.data.frame(flightsByOriginMonth)
> flightsByOriginMonth.R
    Origin Month nrow_NumberOfFlights
1      ABE     1                   59
2      ABQ     1                  846
3      ABQ    10                   30
4      ACY     1                   31
5      ALB     1                   75
...

# Find months with the highest cancellation ratio
> which(colnames(airlines.hex)=="Cancelled")
[1] 22
> cancellationsByMonth <- h2o.group_by(data = airlines.hex, by = "Month", sum("Cancelled"), gb.control=list(na.methods="rm"))
> cancellation_rate <- cancellationsByMonth$sum_Cancelled/flightsByMonth$nrow_Month
> rates_table <- h2o.cbind(flightsByMonth$Month,cancellation_rate)
> rates_table.R <- as.data.frame(rates_table)
> rates_table.R
  Month sum_Cancelled
1     1   0.025417471
2    10   0.009504752

# Use group_by with multiple columns. Summarize the destination, arrival delays, and departure delays for an origin
> cols <- c("Dest", "IsArrDelayed", "IsDepDelayed")
> originFlights <- h2o.group_by(data = airlines.hex[c("Origin",cols)], by = "Origin", sum(cols),gb.control = list(na.methods = "ignore", col.names = NULL))
# Note a warning because col.names null
> res <- h2o.cbind(lapply(cols, function(x){h2o.group_by(airlines.hex,by="Origin",sum(x))}))[,c(1,2,4,6)]
> res
  Origin sum_Dest sum_IsArrDelayed sum_IsDepDelayed
1    ABE     5884               40               30
2    ABQ    84505              545              370
3    ACY     3131                9                7
4    ALB     3646               49               50
5    AMA      317                4                6
6    ANC      100                0                1
>>> import h2o
>>> h2o.init()

# Upload the airlines dataset
>>> air = h2o.import_file("https://s3.amazonaws.com/h2o-airlines-unpacked/allyears2k.csv")
>>> air.dim
[43978, 31]

# Find number of flights by airport
>>> originFlights = air.group_by("Origin")
>>> originFlights.count()
>>> originFlights.get_frame()
Origin      nrow
--------  ------
ABE           59
ABQ          876
ACY           31
...

# Find number of flights per month based on the origin
>>> cols = ["Origin","Month"]
>>> flights_by_origin_month = air.group_by(by=cols).count(na ="all")
>>> flights_by_origin_month.get_frame()
Origin      Month    nrow
--------  -------  ------
ABE             1      59
ABQ             1     846
ABQ            10      30
...

# Find months with the highest cancellation ratio
>>> cancellation_by_month = air.group_by(by='Month').sum('Cancelled', na ="all")
>>> flights_by_month = air.group_by('Month').count(na ="all")
>>> canceled = cancellation_by_month.get_frame()['sum_Cancelled']
>>> flights = flights_by_month.get_frame()['nrow']
>>> month_count = flights_by_month.get_frame()['Month']
>>> ratio = canceled/flights
>>> month_count.cbind(ratio)
  Month    sum_Cancelled
  -------  ---------------
        1       0.0254175
       10       0.00950475

[2 rows x 2 columns]

# Use group_by with multiple columns. Summarize the destination, arrival delays, and departure delays for an origin
>>> cols_1 = ['Origin', 'Dest', 'IsArrDelayed', 'IsDepDelayed']
>>> cols_2 = ["Dest", "IsArrDelayed", "IsDepDelayed"]
>>> air[cols_1].group_by(by='Origin').sum(cols_2, na ="ignore").get_frame()
Origin      sum_Dest    sum_IsDepDelayed    sum_IsArrDelayed
--------  ----------  ------------------  ------------------
ABE             5884                  30                  40
ABQ            84505                 370                 545
ACY             3131                   7                   9
ALB             3646                  50                  49
AMA              317                   6                   4
ANC              100                   1                   0
...