Grouping Pandas Dataframe Across Rows - 2.0
Further to this question Grouping Pandas dataframe across rows, the op is: amount clients Comp1 16.360417 Comp2 69.697501 Comp3 85.700000 Comp4 36.
Solution 1:
Improving from previous answer, we need to use stack by setting two columns as index.
cols= ['amount','tdate']
df['new'] = df['amount']/df.drop(cols,1).count(1)
#Set the index as new and tdate by droping amount column, stack and drop the nans.
x = df.drop(['amount'],1).set_index(['new','tdate']).stack().dropna()
#Create dataframe from amount,tdate and the clients
ndf = pd.DataFrame({'amount':x.index.get_level_values('new'),'tdate':x.index.get_level_values('tdate'),'clients':x.values})
#Groupby `clients` and `tdate`
ndf.groupby(['clients','tdate']).sum().reset_index()
Output :
clients tdate amount 0 Comp1 10/31/2017 7.000000 1 Comp1 12/31/2017 7.125000 2 Comp1 2/2/2017 2.568750 3 Comp2 1/1/2017 7.550000 4 Comp2 10/31/2017 33.202000 5 Comp2 12/31/2017 26.710084 6 Comp2 2/2/2017 2.568750 7 Comp3 10/31/2017 81.650000 8 Comp3 10/31/2107 4.050000 9 Comp4 1/1/2017 30.000000 10 Comp4 10/31/2017 7.000000 11 Comp5 1/1/2017 7.550000 12 Comp5 10/31/2017 26.202000 13 Comp5 12/31/2017 10.404500
Post a Comment for "Grouping Pandas Dataframe Across Rows - 2.0"