CODEWITHSUNDEEP
pythonpandaspivot-table
Ricardo Fernandes
Ricardo Fernandes

Reputation: 128

Create a month for every date between a period and make them columns

I want to separate every month inside the period between the 'start' and 'end' column than I know I can use a pivot_table to make them columns:

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

Desired Output:

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

Upvotes: 0

Views: 40

Answers (2)

rafaelc
rafaelc

Reputation: 59274

Using simple pd.Series.cumsum

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

First set begin as values, and end as -values.

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

Then cumsum through axis=1 df2 = df2.cumsum(1)

Set the final to values

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

Final output:

        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

Upvotes: 1

BENY
BENY

Reputation: 323306

A method from sklearn MultiLabelBinarizer

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]

Upvotes: 0

Related Questions