Skip to main content

Group by

The group_by function lets you 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:

  1. Splits the data into groups based on some criteria.
  2. Applies a function to each group independently.
  3. 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.

Group by parameters​

The group_by function accepts the following parameters:

Python and R​

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

Python only​

  • na: This option controls the treatment of NA values during the calculation. It can be one of:
    • all (default): Any NA values 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: NA 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.
  • na.methods: This option controls the treatment of NA values during the calculation. It can be one of:
    • all (default): Any NA values 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: NA 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.
  • nrow: Specify the name of the generated column.
note

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

Aggregations​

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.
note

If no arguments are given to the aggregation (for example, 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.

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.

Examples​

The following examples in Python and R show how to find the months with the highest cancellation using group_by.

import h2o
h2o.init()

# Upload the airlines dataset
air = h2o.import_file("https://s3.amazonaws.com/h2o-public-test-data/smalldata/airlines/allyears2k.zip")
air.dim
[43978, 31]

# Find number of flights by airport
origin_flights = air.group_by("Origin")
origin_flights.count()
origin_flights.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")
cancelled = cancellation_by_month.get_frame()['sum_Cancelled']
flights = flights_by_month.get_frame()['nrow']
month_count = flights_by_month.get_frame()['Month']
ratio = cancelled/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
...

The following R code shows the options by-variable with gb.control.

library(h2o)
h2o.init()

# Import the airlines dataset:
airlines.hex <- h2o.importFile("https://s3.amazonaws.com/h2o-public-test-data/smalldata/airlines/allyears2k.zip")

# Find the number of flights by airport using gb.control to handle NAs:
originFlights <- h2o.group_by(data = airlines.hex, by = "Origin", nrow("Origin"), gb.control = list(na.methods = "rm"))
originFlights.R <- as.data.frame(originFlights)

# Find the 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)

# Find months with the highest cancellation ratio:
cancellationsByMonth <- h2o.group_by(data = airlines.hex, by = "Month", sum("Cancelled"), gb.control = list(na.methods = "rm"))
cancellation_rate <- cancellationsByMonth$sum_Cancelled/flightsByMonth$nrow
rates_table <- h2o.cbind(flightsByMonth$Month, cancellation_rate)
rates_table.R <- as.data.frame(rates_table)
Feedback