Pandas - Add many new columns based on many aggregate functions

user_1267363 0
Viewed 49

Pandas 1.0.5

import pandas as pd
d = pd.DataFrame({
    "card_id": [1, 1, 2, 2, 1, 1, 2, 2],
    "day": [1, 1, 1, 1, 2, 2, 2, 2],
    "amount": [1, 2, 10, 20, 3, 4, 30, 40]
  })

#add columns
d['count'] = d.groupby(['card_id', 'day'])["amount"].transform('count')
d['min'] = d.groupby(['card_id', 'day'])["amount"].transform('min')
d['max'] = d.groupby(['card_id', 'day'])["amount"].transform('max')

I would like to change the three transform lines to one line. I tried this:

d['count', 'min', 'max'] = d.groupby(['card_id', 'day'])["amount"].transform('count', 'min', 'max')

Error: "TypeError: count() takes 1 positional argument but 3 were given"

I also tried this:

d[('count', 'min', 'max')] = d.groupby(['card_id', 'day']).agg(
    count = pd.NamedAgg('amount', 'count')
    ,min = pd.NamedAgg('amount', 'min')
    ,max = pd.NamedAgg('amount', 'max')
)

Error: "TypeError: incompatible index of inserted column with frame index"

pandas-groupby pandas
2 Answers

Use merge,

d = pd.DataFrame({
    "card_id": [1, 1, 2, 2, 1, 1, 2, 2],
    "day": [1, 1, 1, 1, 2, 2, 2, 2],
    "amount": [1, 2, 10, 20, 3, 4, 30, 40]
  })

df_out = d.groupby(['card_id', 'day']).agg(
    count = pd.NamedAgg('amount', 'count')
    ,min = pd.NamedAgg('amount', 'min')
    ,max = pd.NamedAgg('amount', 'max')
)

d.merge(df_out, left_on=['card_id', 'day'], right_index=True)

Output:

   card_id  day  amount  count  min  max
0        1    1       1      2    1    2
1        1    1       2      2    1    2
2        2    1      10      2   10   20
3        2    1      20      2   10   20
4        1    2       3      2    3    4
5        1    2       4      2    3    4
6        2    2      30      2   30   40
7        2    2      40      2   30   40

The output of you groupyby is creating a multilevel index and the index of this ouput doesn't match the index of d, hence the error. However, we can join the columns in d to the index in output of the groupby using merge with column names and right_index=True.

user_63615310

You could use the assign function to get the results in one go:

grouping = df.groupby(["card_id", "day"])
df.assign(
    count=grouping.transform("count"),
    min=grouping.transform("min"),
    max=grouping.transform("max"),
)


 card_id    day amount  count   min max
0   1       1   1       2       1   2
1   1       1   2       2       1   2
2   2       1   10      2       10  20
3   2       1   20      2       10  20
4   1       2   3       2       3   4
5   1       2   4       2       3   4
6   2       2   30      2       30  40
7   2       2   40      2       30  40
user_71757130
Related