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