将总行添加到带有元组的pandas DataFrame中 [英] Adding total row to a pandas DataFrame with tuples inside

问题描述

这是我的上一个问题(已回答) .它帮助我解决了最初的问题，但现在我陷入了另一个困境.

Here is my previous question (that has been answered). It helped me for my initial problem but now I am stuck on another one.

我在pandas.DataFrame下找到了这个，我试图为每个子级别添加总计行.

I have this below pandas.DataFrame which I try to add total rows for each sub levels.

Level  Company  Item
1      X        a       (10, 20)
                b       (10, 20)
       Y        a       (10, 20)
                b       (10, 20)
                c       (10, 20)
2      X        a       (10, 20)
                b       (10, 20)
                c       (10, 20)
       Y        a       (10, 20)

我想要这个:

Level  Company  Item
1      X        a       (10, 20)
                b       (10, 20)
                total   (20, 40)
       Y        a       (10, 20)
                b       (10, 20)
                c       (10, 20)
                total   (30, 60)
       total            (50, 100)
                total   (50, 100)
2      X        a       (10, 20)
                b       (10, 20)
                c       (10, 20)
                total   (30, 60)
       Y        a       (10, 20)
                total   (10, 20)
       total            (40, 80)
                total   (40, 80)

要获取数据框:

level = list(map(int, list('111112222')))
company = list('XXYYYXXXY')
item = list('ababcabca')
value = [(10,20)]*9
col = ['Level', 'Company', 'Item', 'Value']
df = pd.DataFrame([level,company,item,value]).T
df.columns = col
df.groupby(['Level', 'Company', 'Item'])['Value'].sum()

但是我的结果是:

Level  Company  Item
1      X        a       (10, 20)
                b       (10, 20)
       Y        a       (10, 20)
                b       (10, 20)
                c       (10, 20)
       total            (50, 100)
2      X        a       (10, 20)
                b       (10, 20)
                c       (10, 20)
       Y        a       (10, 20)
       total            (40, 80)

使用以下脚本:

def f(x):
    return tuple(sum(x) for x in zip(*filter(lambda x: type(x) == tuple, x)))
m=df.unstack(level=['Company','Item'])
m=m.assign(total=m.apply(f, axis=1))
m=m.stack(level='Company')
m=m.assign(total=m.apply(f))
m=m.stack(level='Item')
m

推荐答案

使用:

#s=df.groupby(['Level', 'Company', 'Item'])['Value'].sum()

def GetTupleSum(x):
    return tuple(sum(y) for y in zip(*x.dropna()))

df= s.unstack('Item')
df['total']=df.apply(GetTupleSum,axis=1)
( df.unstack()
    .assign(total_company=df['total'].groupby(level=0).apply(GetTupleSum) )
    .stack(['Company','Item']) )

输出

Level  Company  Item         
1      X        a                 (10, 20)
                b                 (10, 20)
                total             (20, 40)
       Y        a                 (10, 20)
                b                 (10, 20)
                c                 (10, 20)
                total             (30, 60)
                total_company    (50, 100)
2      X        a                 (10, 20)
                b                 (10, 20)
                c                 (10, 20)
                total             (30, 60)
       Y        a                 (10, 20)
                total             (10, 20)
                total_company     (40, 80)
dtype: object

这篇关于将总行添加到带有元组的pandas DataFrame中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！