pandas 根据其他行的总和/差异添加新行 [英] pandas add new row based on sum/difference of other rows

问题描述

df有

id    measure   t1  t2  t3
1     savings   1    2   5
1     income    10   15  14
1     misc       5    5   5
2     savings    3   6   12
2     income     4   20  80
2     misc       1   1    1

df想要-为每个id的度​​量添加一个新行，称为支出，方法是针对每个期间t1，t2，t3的每个id减去measure = income-measure = Savings

df want- add a new row to the measure for each id, called spend, calculated by subtracting measure=income - measure=savings, for each of the periods t1,t2,t3, for each id

id    measure   t1  t2  t3
1     savings   1    2   5
1     income    10   15  14
1     misc      5     5   5
1     spend     9    13  9
2     savings    3   6   12
2     income     4   20  80
2     misc       1    1   1
2     spend      1   14  68

尝试:

df.loc[df['Measure'] == 'spend'] =          
                        df.loc[df['Measure'] == 'income']-
                        (df.loc[df['Measure'] == 'savings'])

失败，因为我没有加入groupby以获得预期的结果

Failing because I am not incorporating groupby for desired outcome

推荐答案

这是使用 groupby diff

df1=df[df.measure.isin(['savings','spend'])].copy()

s=df1.groupby('id',sort=False).diff().dropna().assign(id=df.id.unique(),measure='spend')
df=df.append(s,sort=True).sort_values('id')
df
Out[276]: 
   id  measure    t1    t2    t3
0   1  savings   1.0   2.0   5.0
1   1   income  10.0  15.0  14.0
1   1    spend   9.0  13.0   9.0
2   2  savings   3.0   6.0  12.0
3   2   income   4.0  20.0  80.0
3   2    spend   1.0  14.0  68.0

更新

df1=df.copy()
df1.loc[df.measure.ne('income'),'t1':]*=-1
s=df1.groupby('id',sort=False).sum().assign(id=df.id.unique(),measure='spend')
df=df.append(s,sort=True).sort_values('id')

这篇关于 pandas 根据其他行的总和/差异添加新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！