为期间之间的每个日期创建一个月，并将其设为列

Question

我想在“开始”列和“结束”列之间的时段内每个月进行分隔，这样我就可以使用pivot\u表使它们成为列：

subscription|values| start   | end
x           |1     |5/5/2018 |6/5/2018
y           |2     |5/5/2018 |8/5/2018
z           |1     |5/5/2018 |9/5/2018
a           |3     |5/5/2018 |10/5/2018
b           |4     |5/5/2018 |11/5/2018
c           |2     |5/5/2018 |12/5/2018

期望输出：

subscription|jan| feb | mar | abr | jun | jul | aug | sep | out | nov | dez
x           |   |     |     |     | 1   | 1   |     |     |     |     |
y           |   |     |     |     | 2   | 2   | 2   |     |     |     |
z           |   |     |     |     | 1   | 1   | 1   | 1   |     |     |
a           |   |     |     |     | 3   | 3   | 3   | 3   | 3   |     |
b           |   |     |     |     | 4   | 4   | 4   | 4   | 4   | 4   |
c           |   |     |     |     | 2   | 2   | 2   | 2   | 2   | 2   | 2

Answer 1

使用简单的^{}

import calendar
df2 = pd.DataFrame(np.zeros(shape=[len(df),13]), 
                   columns=map(lambda s: calendar.month_abbr[s], 
                                        np.arange(13)))

第一组以值开始，以-values结束

r = np.arange(len(df))
df2.values[r, df.start.dt.month] =  df['values']
df2.values[r, df.end.dt.month]   = -df['values']

然后cumsum到axis=1 df2=df2.cumsum（1）

将final设置为values

df2.values[r, df.end.dt.month]= df['values']

最终输出：

        Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
0   0   0   0   0   0   1   1   0   0   0   0   0   0
1   0   0   0   0   0   2   2   2   2   0   0   0   0
2   0   0   0   0   0   1   1   1   1   1   0   0   0
3   0   0   0   0   0   3   3   3   3   3   3   0   0
4   0   0   0   0   0   4   4   4   4   4   4   4   0
5   0   0   0   0   0   2   2   2   2   2   2   2   2

Answer 2

来自sklearnMultiLabelBinarizer的方法

from sklearn.preprocessing import MultiLabelBinarizer
df['L'] = [pd.date_range(x, y, freq='M') for x, y in zip(df.start, df.end)]
mlb = MultiLabelBinarizer()
yourdf=pd.DataFrame(mlb.fit_transform(df['L']),columns=mlb.classes_, index=df.index).mul(df['values'],0)
yourdf.columns=yourdf.columns.strftime('%Y%B')
yourdf['subscription']=df['subscription']
yourdf
Out[75]: 
   2018May  2018June      ...       2018November  subscription
0        1         0      ...                  0             x
1        2         2      ...                  0             y
2        1         1      ...                  0             z
3        3         3      ...                  0             a
4        4         4      ...                  0             b
5        2         2      ...                  2             c
[6 rows x 8 columns]