python - 多索引中的计算列

Question

我想在数据框中插入 2 列。

原始数据框

card    auth       month   order_number
Amex     A        2017-11       1234
Visa     A        2017-12       2345
Amex     D        2017-12       3456

我想按月分割 auth_status。我使用了以下代码:

bin_month_df = monthly_df.pivot_table(index='card', columns=['month', 'auth'],values='order_number', aggfunc='count')

按月划分的数据框

   month         2017-11      2017-12
    auth         A    D       A    D
    card
    mastercard  10    11     11    10
    amex        19    20     10    11
    visa        50    30     50    1

目标结果

我想为 subtotal 和 auth_rate 添加列

       month                   2017-11                       2017-12
        auth         A    D   total    pct           A    D    total  pct
        card
        mastercard  10    11     21    .47           11    10   21    .52
        amex        19    20     39    .49           10    11   21    .47
        visa        50    30     80    .63           50    1    51    .98

我在创建这些列时遇到问题。 This 链接按行显示小计，但它不会为我翻译成列或计算列。

感谢任何帮助!

Answer 1

使用:

#create sum by first level of MultiIndex
df1 = df.sum(axis=1, level=0)
df1.columns = [df1.columns, ['total'] * len(df1.columns)]
print (df1)
month      2017-11 2017-12
             total   total
card                      
mastercard      21      21
amex            39      21
visa            80      51

#select by second level and divide
df2 = df.xs('A', axis=1, level=1).div(df1.xs('total', axis=1, level=1)).round(2)
df2.columns = [df2.columns, ['pct'] * len(df2.columns)]
print (df2)
month      2017-11 2017-12
               pct     pct
card                      
mastercard    0.48    0.52
amex          0.49    0.48
visa          0.62    0.98

#join all together, sort MultiIndex
df3 = pd.concat([df, df1, df2], axis=1).sort_index(axis=1)
print (df3)
month      2017-11                 2017-12                
auth             A   D   pct total       A   D   pct total
card                                                      
mastercard      10  11  0.48    21      11  10  0.52    21
amex            19  20  0.49    39      10  11  0.48    21
visa            50  30  0.62    80      50   1  0.98    51

---

#for custom order reindex by custom MultiIndex
c = df.columns.levels[1].tolist() + ['total', 'pct']
mux = pd.MultiIndex.from_product([df.columns.levels[0], c], names=df.columns.names)
df4 = df3.reindex(columns=mux)
print(df4)
month      2017-11                 2017-12                
auth             A   D total   pct       A   D total   pct
card                                                      
mastercard      10  11    21  0.48      11  10    21  0.52
amex            19  20    39  0.49      10  11    21  0.48
visa            50  30    80  0.62      50   1    51  0.98