I have an aggregation statement below:
data = data.groupby(['type', 'status', 'name']).agg({
'one' : np.mean,
'two' : lambda value: 100* ((value>32).sum() / reading.mean()),
'test2': lambda value: 100* ((value > 45).sum() / value.mean())
})
I get KeyErrors. I have been able to make it work for one lambda function but not two.
You need to specify the column in data whose values are to be aggregated.
For example,
data = data.groupby(['type', 'status', 'name'])['value'].agg(...)
instead of
data = data.groupby(['type', 'status', 'name']).agg(...)
If you don't mention the column (e.g. 'value'), then the keys in dict passed to agg are taken to be the column names. The KeyErrors are Pandas' way of telling you that it can't find columns named one, two or test2 in the DataFrame data.
Note: Passing a dict to groupby/agg has been deprecated. Instead, going forward you should pass a list-of-tuples instead. Each tuple is expected to be of the form ('new_column_name', callable).
Here is runnable example:
import numpy as np
import pandas as pd
N = 100
data = pd.DataFrame({
'type': np.random.randint(10, size=N),
'status': np.random.randint(10, size=N),
'name': np.random.randint(10, size=N),
'value': np.random.randint(10, size=N),
})
reading = np.random.random(10,)
data = data.groupby(['type', 'status', 'name'])['value'].agg(
[('one', np.mean),
('two', lambda value: 100* ((value>32).sum() / reading.mean())),
('test2', lambda value: 100* ((value > 45).sum() / value.mean()))])
print(data)
# one two test2
# type status name
# 0 1 3 3.0 0 0.0
# 7 4.0 0 0.0
# 9 8.0 0 0.0
# 3 1 5.0 0 0.0
# 6 3.0 0 0.0
# ...
If this does not match your situation, then please provide runnable code that does.
groupby/agg? If so, pass a list-of-tuples instead. I've updated the code above to show what I mean. If this is not your situation, then please open a new question with all the details (a runnable example with desired output helps a lot.)
I prefer a syntax close to R's tidyverse, which provides a lot of flexibility together with readability. There you would apply custom functions in the following way:
# Some input data
df = pd.DataFrame({
'col1': [0, 1, 0, 1, 0],
'col2': [10, 20, 30, 40, 50],
'col3': [100, 200, 300, 400, 500],
})
# Tidyverse-like aggregations
(
df
.groupby('col1')
.agg(
percent_col2_above_30=('col2', lambda x: sum(x>30)/len(x)),
col3_max_divided_by_min=('col3', lambda x: max(x)/min(x))
)
)
Sometimes you also want to do calculations across columns, so here is an example of this:
# Example input data
df = pd.DataFrame({
'col1': ["A", "A", "A", "B", "B", "B"],
'col2': ["group_1", "group_2", "group_3", "group_1", "group_2", "group_3"],
'col3': [10, 20, 80, 40, 30, 30],
})
# Perform grouped aggregations with multi-column calculations
df_result = (
df
.groupby("col1")
.apply(
lambda x: pd.Series({
"group_1_prop": x.loc[x["col2"] == "group_1", "col3"].sum() / x["col3"].sum(),
"group_2_prop": x.loc[x["col2"] == "group_2", "col3"].sum() / x["col3"].sum(),
"group_3_prop": x.loc[x["col2"] == "group_3", "col3"].sum() / x["col3"].sum(),
})
)
.reset_index()
)
set_axis to rename columns afterwardsAs @unutbu mentioned, the issue is not with the number of lambda functions but rather with the keys in the dict passed to agg() not being in data as columns. OP seems to have tried using named aggregation, which assign custom column headers to aggregated columns. A simple way to do it is calling set_axis() after aggregation. For example, the following produces the same output as the named aggregation suggested by @unutbu.
data = (
data.groupby(['type', 'status', 'name'])['value']
.agg([
'mean', # <--- 'one'
lambda value: 100* ((value>32).sum() / reading.mean()), # <--- 'two'
lambda value: 100* ((value > 45).sum() / value.mean()) # <--- 'test2'
])
.set_axis(['one', 'two', 'test2'], axis=1) # <---- rename columns here
)
This is especially useful if you want to assign custom names to aggregations involving multiple columns because the groupby.agg call can be done normally.
N = 100
data = pd.DataFrame({
'type': np.random.randint(2, size=N),
'value1': np.random.randint(50, size=N),
'value2': np.random.randint(50, size=N)
})
reading = np.random.random(10)
x = (
data.groupby('type')
.agg({
'value1': ['mean', lambda value: 100* ((value>32).sum() / reading.mean())],
'value2': lambda value: 100* ((value > 45).sum() / value.mean())
})
.set_axis(['one', 'two', 'test2'], axis=1)
)
one two test2
type
0 24.00000 2892.413355 18.627451
1 21.44186 1190.993734 4.589114
To do the same using named aggregation involves constructing an elaborate dictionary that you pass as kwargs to groupby.agg().
y = (
data.groupby('type')
.agg(**{
'one': ('value1', 'mean'),
'two': ('value1', lambda value: 100* ((value>32).sum() / reading.mean())),
'test2': ('value2', lambda value: 100* ((value > 45).sum() / value.mean()))
})
)
x.equals(y) # True
