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