Pandas specify resulting column names when using agg without group by

Viewed 605

I am trying to write a function that would summarize my pandas dataframe. This function should be able to do summarization by group as well as without grouping (depending on whether i specify group by parameter).

In order to be able to perform arbitrary aggregation functions, i pass a dictionary as an argument to pandas agg. The dictionary specifies what aggregations should be performed - and ideally it would specify what would be the names of the columns that come out as a result of my aggregation.

Unfortunately, while i can easily name all columns while using df.groupby([[).agg({}), i can't specify the column names in the same way while aggregating the whole dataframe (i.e. df.agg({}))

Example

Let's have a dataframe like this:

df = pd.DataFrame(
    {
        'my_grouping_col':['A', 'A', 'B', 'B', 'C', 'C'], 
        'my_value_col_1': [1,2,3,4,5,6],
        'my_value_col_2': [7,8,9,10,11,12]
    }
)

If i want to aggregate it by group while using some custom functions i could do:

df.groupby(['my_grouping_col']).agg(
    {'my_value_col_1': ['min', 'max', ('Q1', lambda x: x.quantile(0.25))], 
     'my_value_col_2':['min', 'max', ('Q3', lambda x: x.quantile(0.75))]
    }
)

Therefore i can use a tuple to specify the aggregation function as well as it's resulting column name.

Now i would like to be able to use the same syntax even without doing groupby:

df.agg(
    {'my_value_col_1': ['min', 'max', ('Q1', lambda x: x.quantile(0.25))], 
         'my_value_col_2':['min', 'max', ('Q3', lambda x: x.quantile(0.75))]
    }
)

But this gives me: AttributeError: 'Q1' is not a valid function for 'Series' object.

I can imagine two workarounds that i would rather not use:

Option 1: Add a column with same value in all rows and then groupby. Afterwards reset index with drop=True to remove this column.

df['my_temporary_grouping_column'] = 1
df.groupby(['my_temporary_grouping_column']).agg(
    {'my_value_col_1': ['min', 'max', ('Q1', lambda x: x.quantile(0.25))], 
     'my_value_col_2':['min', 'max', ('Q3', lambda x: x.quantile(0.75))]
    }
).reset_index(drop = True)

Which gives the desired results:

    my_value_col_1  my_value_col_2
    min max Q1      min max Q3
my_grouping_col                     
A   1   2   1.25    7   8   7.75
B   3   4   3.25    9   10  9.75
C   5   6   5.25    11  12  11.75

Option 2

aggregate without groupby as:

df.agg(
    {'my_value_col_1': ['min', 'max', lambda x: x.quantile(0.25)], 
     'my_value_col_2':['min', 'max', lambda x: x.quantile(0.75)]
    }
)

And rename ... But i think the second option is really impractical.

Can i somehow change the dictionary specifying the details of the aggregation (including specifying column names) so that it works in both cases - with groupby and without? If not what would be a good way to specify the column names in agg without groupby? Note that i do not wish to use anything like: agg(Q3 = lambda x: x.quantile(0.75)). I would like to use the code within a function which takes as a parameter the details of what aggregations should be performed, such as:

def summarise(data, columns, group_cols, functions):
    tdict = {}
    [tdict.update({i.functions}) for i in columns]
    if group_cols is not None:
        out = data.groupby(group_cols).agg(tdict)
    else:
        data['temporary_group_col'] = 1
        out = data.groupby(['temporary_group_col']).agg(tdict).reset_index(drop = True)
    return out
0 Answers
Related