recipe procedure: groupby

[semio]

Reason

Sometimes we want to group a datapoint by some of its domains and calculate a new datapoint. For example we have population for age 0, 1, 2, 3, ... 10 for each country and we want to calculate population_between_0_and_10. Then we need to have a procedure to group the datapoint by country/year and sum up all data.

API

groupby groups the data in a ingredient by a key/keys and return aggregated results as new ingredient.

groupby accepts following options:

• by: the key/keys to group
• aggregate: the function to calculate the result.

[semio]

need to support more operations other than aggregate functions. like cumsum() and other custom functioins.

[semio]

close this in favour of #19. It's not very good to add accumulate functions to groupby function because it's not the nature usage for groupby.

[jheeffer]

I disagree, group_by (almost) always goes together with aggregate functions.

geo	year	population
swe	2015	10
swe	2016	11
swe	2017	11
chn	2015	10
chn	2016	21

Let's look at SQL:
Each column that is selected has to be mentioned either in group by or in an aggregate function.

GROUP BY all columns

If all columns are mentioned in group by, you'll get the same table as before (i.e. identity function).

sql: SELECT geo, year, population GROUP BY geo, year, population

geo	year	population
swe	2015	10
swe	2016	11
swe	2017	11
chn	2015	10
chn	2016	21

This is pretty useless

GROUP BY subset of columns

sql: SELECT year, population GROUP BY year, population

year	population
2015	10
2016	11
2017	11
2016	21

I don't see this as a use case for us.

GROUP BY subset of columns with aggregate function

sql: SELECT geo, avg(population) GROUP BY geo

geo	avg(population)
swe	10.66
chn	15.5

This is the normal use case that we will use

sql: SELECT geo, avg(year), population GROUP BY geo, population

geo	avg(year)	population
swe	2015	10
swe	2016.5	11
chn	2015	10
chn	2016	21

This is a weird use case which we will probably not use.

Now we have to see how we put this in a recipe.

[semio]

Yes, I agree that group by always run with aggregate functions. The API described on the top is supported in Chef. I am thinking about another type of functions here on the last 2 comments. Sorry for the misleading...

For now if we want to group by geo and get average population for each country, we can write recipe like this:

procedure: groupby
ingredients: [datapoints-population]
options:
    by:
        - geo
    aggregate: mean

But the column to do aggregate is fixed to the datapoint column, which is population in this example.

Other than aggregate functions like sum, mean, min, max etc, I was thinking about the cumulative functions like cumsum() or cumprod().

For example we have number of some event happened in one year:

Time	Occurrence
1990	100
1991	20
1992	321

Then the cumulative sum will be:

Time	Total Occurrence
1990	100
1991	120
1992	441

So actually we don't need to run group by. We just need to sort the time column in order.

But in the SG dataset, where all datapoint have geo columns, we do want to run groupby first before running cumulative function... So I am in doubt that if we should add support for cumulative function in groupby procedure or we add cumulative procedure that have a groupby option. What do you think?

[jheeffer]

Ah, cumulative functions vs aggregate functions... okay, interesting! My fault for not understanding correctly. I see you used them already too, didn't expect that!

https://github.com/open-numbers/ddf--gapminder--systema_globalis/blob/99a9d764a17d0bb2a8444a5aae588aaa297484b4/etl/recipes/recipe_cdiac.yaml#L100-L106

[jheeffer]

Nonetheless, what do you think about the below update of groupby?

Name of procedure is still groupby. Another option would be aggregate, but it is possible to groupby without aggregating while aggregating always involves some kind of grouping.

Possibility to do groupby without any aggregate functions. Behaviour would be as above.

{
  procedure: 'groupby'
  ingredient: 'gw-population'
  result: 'gw-population-aggregated'
  options: {
    aggregate: {
      year: {
        function: 'complex_function',
        foo: 'bar',
        another_parameter: 684
      }
      population: 'sum'
    },
    groupby: 'geo' || ['geo','and','more']
}

If a column in the ingredient is not mentioned in aggregate or group, it is not included in result.

[semio]

Great, it looks good. I will implement it as you described.

[jheeffer]

Be sure that the complex_function case has the same syntax as in #25

so either like above

year: {
  function: 'complex_function',
  foo: 'bar',
  another_parameter: 684
}

or with parameters object

year: {
  function: 'complex_function'
  parameters: { 
    foo: 'bar', 
    another_parameter: 684 
  }
}

Do you have a preference and why?