将一排总计添加到数据框架中

Question

我有一个数据框架，我正在尝试弄清楚如何在每个客户端添加一个行，以汇总每个客户的小时时间。这是我的数据框架的示例：

                              hours
client           month
A               January       203.50
                February      227.75
                March         159.75
                April         203.25
                May           199.90
B               January        203.50
                February       227.75
                March         159.75
                April          203.25
                May            199.90
C               January       203.50
                February      227.75
                March          159.75
                April         203.25
                May           199.90

我想为每个端口的客户添加一个新的行。看起来像这样：

                           hours
client           month
A               January    203.50
                February   227.75
                March      159.75
                April      203.25
                May        199.90
                Total     1000.34
B               January    203.50
                February   227.75
                March      159.75
                April      203.25
                May       199.90
                Total     1000.34
C               January   203.50
                February   227.75
                March      159.75
                April      203.25
                May       199.90
                Total     1000.34

我疲倦地写了一个循环的循环，遍历每个客户端，总结小时，然后将新行附加到每个客户端。我正在尝试的循环看起来像这样

在DF中持续数小时： df.append(pd.Series(vp.sum((，name ='total'

但是，这行不通。任何帮助将不胜感激！

Answer 1

iiuc，您可以使用 concat

pd.concat([df,df.sum(level=0).assign(month='Total').set_index('month',append=True)]).sort_index()
Out[1754]: 
                  hours
client month           
A      April     203.25
       February  227.75
       January   203.50
       March     159.75
       May       199.90
       Total     994.15
B      April     203.25
       February  227.75
       January   203.50
       March     159.75
       May       199.90
       Total     994.15
C      April     203.25
       February  227.75
       January   203.50
       March     159.75
       May       199.90
       Total     994.15

Answer 2

您可以 unstack，然后添加列。

df.unstack().hours.assign(Total=lambda d: d.sum(1)).stack().to_frame('hours')
                  hours
client month           
A      April     203.25
       February  227.75
       January   203.50
       March     159.75
       May       199.90
       Total     994.15
B      April     203.25
       February  227.75
       January   203.50
       March     159.75
       May       199.90
       Total     994.15
C      April     203.25
       February  227.75
       January   203.50
       March     159.75
       May       199.90
       Total     994.15

---

保留原始行顺序的变体：

# get old order of months
months = pd.unique(df.index.get_level_values(1)).tolist()
# create total dataframe with to levels of index
totals = pd.DataFrame.from_dict({
    (client, 'Total'): {'hours': v}
    for client, v in df.hours.sum(level=0).items()
}, 'index')
# append and reindex
df.append(totals).sort_index().reindex(months + ['Total'], level=1)
                  hours
client month           
A      January   203.50
       February  227.75
       March     159.75
       April     203.25
       May       199.90
       Total     994.15
B      January   203.50
       February  227.75
       March     159.75
       April     203.25
       May       199.90
       Total     994.15
C      January   203.50
       February  227.75
       March     159.75
       April     203.25
       May       199.90
       Total     994.15