python - 如何使用 loc 在 Pandas 中使用多个索引将总和写入底行

问题描述

我有一个带有多个分组的 Pandas 表，我正在使用如下所示的 2 个索引示例 -

      |     |Transactions|Sales|Refund|Cashback|Total
|Index|Index|----------------------------------------
 one_ | two_|DATA_        |DATA|DATA_ |DATA_   |DATA_|
 one_ | two_|DATA_        |DATA|DATA_ |DATA_   |DATA_|
 one_ | two_|DATA_        |DATA|DATA_ |DATA_   |DATA_|
 one_ | two_|DATA_        |DATA|DATA_ |DATA_   |DATA_|
 one_ | two_|DATA_        |DATA|DATA_ |DATA_   |DATA_|
 one_ | two_|DATA_        |DATA|DATA_ |DATA_   |DATA_|
   ...etc

问题是，当我使用df.loc['Total']= df.sum()is 时会产生以下结果 -

            |Transactions|Sales|Refund|Cashback|Total
(one_,two_) |DATA_       |DATA |DATA_ |DATA_   |DATA_|
(one_,two_) |DATA_       |DATA |DATA_ |DATA_   |DATA_|
(one_,two_) |DATA_       |DATA |DATA_ |DATA_   |DATA_|
(one_,two_) |DATA_       |DATA |DATA_ |DATA_   |DATA_|
(one_,two_) |DATA_       |DATA |DATA_ |DATA_   |DATA_|
(one_,two_) |DATA_       |DATA |DATA_ |DATA_   |DATA_|
   Total    |SUM__       |     |      |        |

我想要的输出如下：

      |     |Transactions|Sales|Refund|Cashback|Total
|Index|Index|----------------------------------------
 one_ | two_|DATA_        |DATA|DATA_ |DATA_   |DATA_|
 one_ | two_|DATA_        |DATA|DATA_ |DATA_   |DATA_|
 one_ | two_|DATA_        |DATA|DATA_ |DATA_   |DATA_|
 one_ | two_|DATA_        |DATA|DATA_ |DATA_   |DATA_|
 one_ | two_|DATA_        |DATA|DATA_ |DATA_   |DATA_|
 one_ | two_|DATA_        |DATA|DATA_ |DATA_   |DATA_|
      |TOTAL|SUM          |SUM |SUM   |SUM     |SUM  |

这是我当前的代码，我必须产生上述结果 -

df = pd.DataFrame(transaction_object)

            # Check if dataframe empty 
            if df.empty:
                messages.error(request, 'No Data Found')
                context['desc'] = 'Summary Reports'
                return render(request, 'console/reports.html', context=context)
            df.settlementhistoryid = df.settlementhistoryid.replace(settlement_date)

            def format(x):
                return "{:.2f}".format(x / 100)

            def card_scheme_rules(x):
                a = x['transactionsequencenumber'].nunique()
                b = x[df['transactiontype'] == 1]['transactionamount'].sum()
                c = x[df['transactiontype'] == 4]['transactionamount'].sum()
                d = x[df['transactiontype'] == 3]['transactionamount'].sum()
                e = x['transactionamount'].sum()
                return pd.Series([a, b, c, d, e], index=['transactions', 'sales', 'refund', 'cashback', 'Total'])

            df.settlementhistoryid = pd.to_datetime(df.settlementhistoryid).dt.strftime('%d-%m-%Y')
            df.transactiondate = pd.to_datetime(df.transactiondate).dt.strftime('%d-%m-%Y')
            grouped_df = df.groupby(['settlementhistoryid', 'transactiondate']).apply(card_scheme_rules)

            grouped_df['sales'] = grouped_df['sales'].apply(format)
            grouped_df['refund'] = grouped_df['refund'].apply(format)
            grouped_df['cashback'] = grouped_df['cashback'].apply(format)
            grouped_df['Total'] = grouped_df['Total'].apply(format)

            grouped_df.loc['Total'] = pd.Series(grouped_df['transactions'].sum(), index=['transactions'])
            card_scheme_html_class = str(grouped_df.to_html().replace('class="dataframe"',
                                                                      'class="display" style="width:100%;"'))

是否有其他值可以通过 loc[] 传递？

标签： pythonpandas

python - 如何使用 loc 在 Pandas 中使用多个索引将总和写入底行

问题描述

解决方案

推荐阅读