CODEWITHSUNDEEP
pythonpandasoptimization
TobSta
TobSta

Reputation: 786

Pandas multiple transform-groupby-aggregate at once - speedup

I have to do multiple transform-groupby-aggregate operations and am currently doing those one after the other, but it's very slow:

from pandas.tseries.offsets import MonthEnd
import pandas as pd
fsc = ['E', 'P']
mtx = pd.DataFrame({'EQ': {'2': 'P', '9970': 'P', '9971': 'P'},
'HOURS': {'2': 7.2000000000000002, '9970': 18.0, '9971': 10.0},
'LOC': {'2': 'A', '9970': 'B', '9971': 'B'},
'ORG': {'2': 23, '9970': 52, '9971': 52},
'START': {'2': pd.Timestamp('2014-07-31 17:21:59'),
 '9970': pd.Timestamp('2011-12-15 17:59:59'),
 '9971': pd.Timestamp('2011-08-07 04:59:59')}})

monthly = pd.DataFrame(pd.date_range(start='1970-01-01', end="2017-04-01 23:59:59", freq="MS"))[0].transform(lambda m : (( mtx.loc[(mtx["EQ"].isin(fsc)) & (mtx["START"] >= pd.to_datetime(m)) & (mtx["START"] <= pd.to_datetime(m) + MonthEnd(1))]).groupby(["ORG","LOC"])["HOURS"].mean()))
monthly = monthly.stack().stack().reset_index()
monthly_tmp = pd.DataFrame(pd.date_range(start='1970-01-01', end="2017-04-01 23:59:59", freq="MS"))[0].transform(lambda m : (( mtx.loc[(mtx["EQ"].isin(fsc)) & (mtx["START"] >= pd.to_datetime(m)) & (mtx["START"] <= pd.to_datetime(m) + MonthEnd(1))]).groupby(["ORG","LOC"])["HOURS"].sum()))
monthly = pd.merge(monthly,monthly_tmp.stack().stack().reset_index(),on=["level_0","LOC","ORG"],how="left")

gives:

pd.DataFrame({'0_x': {0: 10.0, 1: 18.0},
'0_y': {0: 10.0, 1: 18.0},
'LOC': {0: 'B', 1: 'B'},
'ORG': {0: 52, 1: 52},
'level_0': {0: 499, 1: 503}}

How can I do all of those in one run? I tried:

f = {'HOURS': 'mean','HOURS': 'sum'}
pd.DataFrame(pd.date_range(start='1970-01-01', end="2017-04-01 23:59:59", freq="MS"))[0].transform(lambda m : (( mtx.loc[(mtx["EQ"].isin(fsc)) & (mtx["START"] >= pd.to_datetime(m)) & (mtx["START"] <= pd.to_datetime(m) + MonthEnd(1))]).groupby(["ORG","LOC"]).agg(f)))

But it returns the DataFrame in a different weird way.

Upvotes: 0

Views: 183

Answers (1)

It_is_Chris
It_is_Chris

Reputation: 14093

You could use .agg() with transpose. It wont give you your exact output but you can manipulate that in pandas:

.groupby(["ORG","LOC"])['HOURS'].agg(['mean', 'sum']).T.unstack())).stack().stack().stack().reset_index()

so just using your example 

new_df = pd.DataFrame(pd.date_range(start='1970-01-01', end="2017-04-01 23:59:59", freq="MS"))[0].transform(lambda m : ((mtx.loc[(mtx["EQ"].isin(fsc)) & (mtx["START"] >= pd.to_datetime(m)) & (mtx["START"] <= pd.to_datetime(m) + MonthEnd(1))]).groupby(["ORG","LOC"])['HOURS'].agg(['mean', 'sum']).T.unstack())).stack().stack().stack().reset_index()

and you will get 

   level_0  level_1 LOC  ORG     0
0   499     mean    B    52     10.0
1   499     sum     B    52     10.0
2   503     mean    B    52     18.0
3   503     sum     B    52     18.0

Not sure if that output is what you are looking for but you can just do something like:

new_df['mean'] = new_df[new_df['level_1'] == 'mean'][0]
new_df['sum'] = new_df[new_df['level_1'] == 'sum'][0]
new_df['sum'] = new_df['sum'].shift(-1)
new_df[~new_df['mean'].isna()].drop(columns=['level_1',0])

    level_0 LOC  ORG    mean    sum
0    499     B   52     10.0    10.0
2    503     B   52     18.0    18.0

Upvotes: 1

Related Questions