将月与月转换为月与月之前

Question

初始表

salesman    training_date   01/20   02/20   03/20   04/20   05/20   06/20   07/20   08/20   09/20   10/20   11/20   12/20
0   John    2020-11-01       100      20     200     250      0       28      80      30     150     100     300    250
1   Ruddy   2020-07-12       90       50      30     225     300     100      95      10      20      0     20      100

在Python中：

t1 = {'salesman': ['John', 'Ruddy'],
     'training_date':['2020-11-30','2020-07-12'],
     '01/20': [100, 90], '02/20':[20,50], '03/20':[200,30],'04/20':[250,225],'05/20':[0,300],'06/20':[28,100],
     '07/20': [80, 95], '08/20':[30,10], '09/20':[150,20],'10/20':[100,0],'11/20':[300,20],'12/20':[250,100],
     }
t1a = pd.DataFrame(data=t1)
t1a

预期的数据帧：

    salesman    training_date   training_month  1m_prior    2m_prior    3m_prior    4m_prior    5m_prior    6m_prior
0   John          2020-11-30         300           100        150           30         80      28       0
1   Ruddy         2020-07-12          95           100        300          225         30      50      90

在Python中：

t2 = {'salesman': ['John', 'Ruddy'],
     'training_date':['2020-11-30','2020-07-12'],
     'training_month': [300, 95], '1m_prior':[100,100], '2m_prior':[150,300],
     '3m_prior':[30,225],'4m_prior':[80,30],'5m_prior':[28,50], '6m_prior': [0, 90]}
t2a = pd.DataFrame(data=t2)
t2a

说明：
约翰在11月1日接受训练。在11月1日之前，约翰在10月份赚了100万美元。 在11月1日，9月1日之前，约翰赚了150美元

鲁迪在7月12日接受训练。在7月12日之前，即6月，Ruddy赚了100万美元。 在5月12日之前的200万美元，Ruddy赚了300美元

在理想情况下，我们开始计算一个完整的月份，总是从每个月的1号开始。 所以，如果Ruddy是在2020年7月12日被雇佣的，那么一个月前应该是6月1日至6月30日

到目前为止，我们在Excel中手动转换数据

Answer 1

首先使用to_datetime和astype（我将其标记为'anchor_date'）将training_date转换为当月的第一个。然后，我们设置索引并将列转换为datetime数据类型和堆栈，为我们在下一步中计算时间差提供了一种简单的方法

import pandas as pd
import numpy as np

# Make datetime and then turn value into first of the month
df['training_date'] = pd.to_datetime(df['training_date'])
df['date_anchor'] = df.training_date.astype('datetime64[M]')

df = df.set_index(['salesman', 'training_date', 'date_anchor'])
df.columns = pd.Index(pd.to_datetime(df.columns, format='%m/%y'), name='date')
df = df.stack().reset_index()

#   salesman training_date date_anchor       date    0
#0      John    2020-11-30  2020-11-01 2020-01-01  100
#1      John    2020-11-30  2020-11-01 2020-02-01   20
#2      John    2020-11-30  2020-11-01 2020-03-01  200
#3      John    2020-11-30  2020-11-01 2020-04-01  250
#...
#19    Ruddy    2020-07-12  2020-07-01 2020-08-01   10
#20    Ruddy    2020-07-12  2020-07-01 2020-09-01   20
#21    Ruddy    2020-07-12  2020-07-01 2020-10-01    0
#22    Ruddy    2020-07-12  2020-07-01 2020-11-01   20
#23    Ruddy    2020-07-12  2020-07-01 2020-12-01  100   

现在我们需要计算两者之间的整数月数，这可以通过一些数学来实现，并在将来使用np.select到NaN个月并设置标签。最后，以数据帧为轴心

df['months'] = ((df.date.dt.year - df.date_anchor.dt.year) * 12 
                 + (df.date.dt.month - df.date_anchor.dt.month))

df['months'] = np.select([df.months.eq(0), df.months.lt(0)],
                         ['training_month', df.months.abs().astype(str) + 'm_prior'],
                         df.months.abs().astype(str) + 'm_post')

df = (df.pivot_table(index=['salesman', 'training_date'], columns='months', values=0)
        .rename_axis(columns=None)
        .reset_index())

---

  salesman training_date  10m_prior  1m_post  1m_prior  2m_post  2m_prior  3m_post  3m_prior  4m_post  4m_prior  5m_post  5m_prior  6m_prior  7m_prior  8m_prior  9m_prior  training_month
0     John    2020-11-30      100.0    250.0     100.0      NaN     150.0      NaN      30.0      NaN      80.0      NaN      28.0       0.0     250.0     200.0      20.0           300.0
1    Ruddy    2020-07-12        NaN     10.0     100.0     20.0     300.0      0.0     225.0     20.0      30.0    100.0      50.0      90.0       NaN       NaN       NaN            95.0