I'd like to create a new dataframe from the results of groupby on another. The result should have one row per group (basically a vectorized map-reduce), and the new column names bear no relation to the existing names. This seems like a natural use for agg, but it only seems to produce existing columns.
d = pd.DataFrame({'a': [0,0,1,1], 'b': [3,4,5,6], 'c': [7,8,9,0]})
a b c
0 0 3 7
1 0 4 8
2 1 5 9
3 1 6 0
agg() will create new columns with a Series:
d.groupby('a')['b'].agg({'x': lambda g: g.sum()})
x
a
0 7
1 11
But frustratingly not with a DataFrame:
d.groupby('a').agg({'x': lambda g: g.b.sum()})
KeyError: 'x'
I can do it by returning a one-row DataFrame from apply():
d.groupby('a').apply(lambda g: pd.DataFrame([{'x': g.b.mean(), 'y': (g.b * g.c).sum()}])).reset_index(level=1, drop=True)
x y
a
0 3.5 53
1 5.5 45
but this is ugly and, as you can imagine, creating a new dict, list, and DataFrame for every row is slow for even modestly-sized inputs.
Here is a comparison of a few different ways to do it. I prefer returning a Series; reasonably succinct, clear, and efficient. Thanks to @Siraj S for the inspiration.
df = pd.DataFrame(np.random.rand(1000000, 5), columns=list('abcde'))
grp = df.groupby((df.a * 100).astype(int))
%timeit grp.apply(lambda g: pd.DataFrame([{'n': g.e.count(), 'x': (g.b * g.c).sum() / g.c.sum(), 'y': g.d.mean(), 'z': g.e.std()}])).reset_index(level=1, drop=True)
1 loop, best of 3: 328 ms per loop
%timeit grp.apply(lambda g: (g.e.count(), (g.b * g.c).sum() / g.c.sum(), g.d.mean(), g.e.std())).apply(pd.Series)
1 loop, best of 3: 266 ms per loop
%timeit grp.apply(lambda g: pd.Series({'n': g.e.count(), 'x': (g.b * g.c).sum() / g.c.sum(), 'y': g.d.mean(), 'z': g.e.std()}))
1 loop, best of 3: 265 ms per loop
%timeit grp.apply(lambda g: {'n': g.e.count(), 'x': (g.b * g.c).sum() / g.c.sum(), 'y': g.d.mean(), 'z': g.e.std()}).apply(pd.Series)
1 loop, best of 3: 273 ms per loop
%timeit pd.concat([grp.apply(lambda g: g.e.count()), grp.apply(lambda g: (g.b * g.c).sum() / g.c.sum()), grp.apply(lambda g: g.d.mean()), grp.apply(lambda g: g.e.std())], axis=1)
1 loop, best of 3: 708 ms per loop
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments