基于“值”d在数据透视“列”之间添加计算字段

Question

我有一份报告，我正在努力显示两个季度之间的差异。我有一个SQL查询，我正在将它读入pandas数据帧，然后旋转。在

这是我的代码：

    df = pd.read_sql_query(mtd_query, cnxn, params=[report_start, end_mtd, report_start, end_mtd, whse])
    ##(m-1)//3 + 1  Determine which Quarter each month is
    ## Create the "Period" column by combining the Quater and the Month
    df['QUARTER'] = (df['INV_MONTH'].astype(int) - 1)//3 + 1
    df['PERIOD'] = df['INV_YEAR'].astype(str) + 'Q' + df['QUARTER'].astype(int).astype(str)
    df['MARGIN'] = (df['PROFIT'].astype(float) / df['SALES'].astype(float))

    df = df.drop('INV_MONTH', axis=1)
    df = df.drop('INV_YEAR', axis=1)
    df = pd.pivot_table(df, index=['REP', 'REP_NAME', 'CUST_NO', 'CUST_NAME', 'TOTALSALES'], columns=['PERIOD'], values=['SALES', 'PROFIT', 'MARGIN'], fill_value=0)
    df = df.reorder_levels([1, 0], axis=1).sort_index(axis=1, ascending=False)
    df = df.sortlevel(level=0, ascending=True)

我正在尝试确定“PERIOD”和“MARGIN”列之间的差异。我一直找不到任何方法来完成这件事。如有任何建议，我们将不胜感激。在

电流输出显示：

^{pr2}$

期望输出如下：

PERIOD                                                                                            2017Q4                                 2017Q3                                 2017Q2                                 2017Q1                                 2016Q4                        
                                                                                                   SALES        PROFIT    MARGIN   VARIANCE          SALES        PROFIT    MARGIN    VARIANCE          SALES        PROFIT    MARGIN    VARIANCE          SALES        PROFIT    MARGIN    VARIANCE          SALES        PROFIT    MARGIN
REP    REP_NAME                       CUST_NO  CUST_NAME                      TOTALSALES                                                                                                                                                                                                    
1.0    Greensboro - House             245.0    TE CONNECTIVITY CORPORATION    103361.05         0.000000      0.000000  0.000000    -.16         434.500000     69.520000  0.160000    0           20391.666667   3262.666667  0.160000    .16           0.000000      0.000000  0.000000      0            0.000000      0.000000  0.000000
                                      1789.0   GOOD HOUSEKEEPER               50108.47        678.508182     80.170909  0.145883    .023968     585.301429     64.180476  0.121915    -0.008537     718.685000     92.033125  0.130453    -.004368     720.729333     97.955333  0.134821     .035372       1237.308333     88.210000  0.099450

在数据到字典（'r'）如下：

[{('2016Q4', 'SALES'): 0.0, ('2017Q3', 'PROFIT'): 69.520000000000067, ('2017Q1', 'PROFIT'): 0.0, ('2017Q2', 'SALES'): 20391.666666666668, ('2017Q3', 'MARGIN'): 0.16, ('2016Q4', 'PROFIT'): 0.0, ('2017Q3', 'SALES'): 434.5, ('2017Q1', 'SALES'): 0.0, ('2017Q4', 'SALES'): 0.0, ('2016Q4', 'MARGIN'): 0.0, ('2017Q4', 'PROFIT'): 0.0, ('2017Q1', 'MARGIN'): 0.0, ('2017Q4', 'MARGIN'): 0.0, ('2017Q2', 'MARGIN'): 0.16, ('2017Q2', 'PROFIT'): 3262.6666666666665}, {('2016Q4', 'SALES'): 1237.3083333333332, ('2017Q3', 'PROFIT'): 64.180476190476185, ('2017Q1', 'PROFIT'): 97.9553333333333, ('2017Q2', 'SALES'): 718.68500000000006, ('2017Q3', 'MARGIN'): 0.1219152103415191, ('2016Q4', 'PROFIT'): 88.209999999999994}]

Answer 1

我无法使用上述解决方案。
所以我这样做了：

df['MARGIN'] = (df['PROFIT'].astype(float) / df['SALES'].astype(float))
df['MARGIN'] = df['MARGIN'].astype(float)
df['PREV_MARGIN'] = df['MARGIN'].shift(-1)
df['VARIANCE'] = df['MARGIN'] - df['PREV_MARGIN']
df = df.drop('PREV_MARGIN', axis=1)

这给了我完成工作所需的数据。在

Answer 2

IIUC：

来源数据：

In [60]: df
Out[60]:
  2016Q4                     2017Q1                  2017Q2               \
  MARGIN PROFIT        SALES MARGIN     PROFIT SALES MARGIN       PROFIT
0    0.0   0.00     0.000000    0.0   0.000000   0.0   0.16  3262.666667
1    NaN  88.21  1237.308333    NaN  97.955333   NaN    NaN          NaN

                   2017Q3                   2017Q4
          SALES    MARGIN     PROFIT  SALES MARGIN PROFIT SALES
0  20391.666667  0.160000  69.520000  434.5    0.0    0.0   0.0
1    718.685000  0.121915  64.180476    NaN    NaN    NaN   NaN

解决方案：

^{pr2}$