Aggregating with pd.NamedAgg with additional conditions

user_3888115 0
Viewed 279

I want to aggregate time-series data based on several conditions. Apart of grouping the data by a timespan and the "type"- column, I would like to count and sum only positive values in the respective groups.
Can this be done elegantly without using .filter or creating subsets beforehand, and merging the aggregate data after?

The following code has been corrected thanks to jezrael's answer below. However, you should check out his answer for a more performant solution. While I prefer "the style" of the solution that I took here, for my dataframe ~50k rows, his approach is far faster.

Sample data:

datelist = ['2021-01-01','2021-02-01','2021-03-01']
datelist = [pd.to_datetime(item) for item in datelist] 
datelist = [item for item in datelist for _ in (range(5))]
valuelist = np.random.randint(-100,100,size=(15))
typelist = np.random.randint(0,3, size=(15))
df = pd.DataFrame(
    {'values': valuelist,
     'types': typelist
    }, index = datelist)
print(df)
            values  types
2021-01-01     -91      2
2021-01-01     -32      1
2021-01-01     -88      1
2021-01-01       7      1
2021-01-01     -84      0
2021-02-01     -57      0
2021-02-01     -28      1
2021-02-01     -11      0
2021-02-01     -66      1
2021-02-01      -9      2
2021-03-01      55      2
2021-03-01     -10      0
2021-03-01     -89      1
2021-03-01      61      1
2021-03-01     -28      1

myagg = {
        'values_sum' : ('values', 'sum'),
        'values_positive_count' : ('values', lambda x: (x > 0).sum()), # counts positive values
        'values_negative_count' : ('values', lambda x: (x < 0).sum()), # counts negative values
        'values_negative_sum' : ('values', lambda x: ((x < 0)*x).sum()), # corrected the parenthesis thanks to jezraels input - works now
    }
df_agg = df.groupby([pd.Grouper(freq='D'), pd.Grouper('type')]).agg(**myagg)

Desired result:

print(dfagg)
                  sum  values_positive_count  values_negative_count  a_positive_sum
           types                                                   
2021-01-01 0      106                      3                      0               0
           1      -62                      0                      1               7
           2       -4                      0                      1               0
2021-02-01 0      -97                      0                      2               0
           1       12                      1                      1               0
           2       58                      1                      0               0
2021-03-01 0      -35                      1                      1               0
           1      111                      2                      0              55
           2       85                      1                      0              61
pandas aggregate python
2 Answers

There is a ( after lambda x and for match column types is not necessary for pd.Grouper:

myagg = {
        'values_sum' : ('values', 'sum'),
        'values_positive_count' : ('values', lambda x: (x > 0).sum()), 
        'values_negative_count' : ('values', lambda x: (x < 0).sum()),
        'values_negative_sum' : ('values', lambda x: ((x < 0)*x).sum()), 
}
df_agg = df.groupby([pd.Grouper(freq='D'), 'types']).agg(**myagg)

Or if you need better performance, create helper columns before groupby like this, so aggregate by sum only:

myagg = {
        'values_sum' : ('values', 'sum'),
        'values_positive_count' : ('p', 'sum'),
        'values_negative_count' : ('n', 'sum'),
        'values_negative_sum' : ('n_sum','sum')
}
df_agg = (df.assign(p = df['values'].gt(0),
                    n = df['values'].lt(0),
                    n_sum = df['values'].lt(0).mul(df['values']),
                   )
            .groupby([pd.Grouper(freq='D'), 'types'])
            .agg(**myagg))
user_29010020

It makes a result what you desired:

#1. Using 'NamedAgg' (https://pandas-docs.github.io/pandas-docs-travis/user_guide/groupby.html#named-aggregation)

df = pd.DataFrame(
    {
        'values': valuelist,
        'types': typelist,
        'date': datelist,  # You do not need to make it as index.
    }
)

df = df.groupby(['date', 'types']).agg(
    sum=pd.NamedAgg(column='values', aggfunc=sum),
    values_positive_count=pd.NamedAgg(column='values', aggfunc=lambda x: (x>0).sum()),
    values_negative_count=pd.NamedAgg(column='values', aggfunc=lambda x: (x<0).sum()),
    a_positive_sum=pd.NamedAgg(column='values', aggfunc=lambda x: ((x > 0)*x).sum()),
)

print(df)

#2. Using groupby and rename

df = pd.DataFrame(
    {
        'values': valuelist,
        'types': typelist,
        'date': datelist,  # You do not need to make it as index.
    }
)

df = df.groupby(['date', 'types']).agg({
    'values': [sum, lambda x: (x>0).sum(), lambda x: (x<0).sum(), lambda x: ((x > 0)*x).sum()]
})

df = df.rename(columns={
    '<lambda_0>': 'values_positive_count',
    '<lambda_1>': 'values_negative_count',
    '<lambda_2>': 'a_positive_sum',
})
df = df.droplevel(0, axis=1)

print(df)

                  sum  values_positive_count  values_negative_count  a_positive_sum
date       types                                                                   
2021-01-01 0      -69                      0                      1               0
           1       49                      1                      0              49
           2       91                      2                      1             107
2021-02-01 0       -6                      1                      1              93
           1       -1                      1                      1              63
           2       14                      1                      0              14
2021-03-01 0      -67                      0                      2               0
           1       60                      1                      1              90
           2      -93                      0                      1               0
user_17795320
Related