CODEWITHSUNDEEP
pythonarrayspandasdatetimetime
pandas_quest
pandas_quest

Reputation: 3

Pandas, sort by month and create new column for each year

I have a dataframe that looks like this:

        date         data 
        2013-09-03     10
        2013-09-04     9
        2013-10-03     14
        2014-09-02     13
        2015-08-07     12
        2016-09-02     17

I then apply the code below to select only month 9

import pandas as pd
import datetime as dt

df= df[df['Date'].dt.month == 9]  # select only the 9th month

This gets me the following:

        date         data 
        2013-09-03     10
        2013-09-04     9
        2014-09-02     13
        2016-09-02     17

But what I am trying to create is a column for each time the 9th month is selected so it can become a separate column:

        date         data  2013  2014 2016
        2013-09-03     10   10
        2013-09-04     9     9
        2014-09-07     13         13
        2016-09-08     17               17

I think I have to use the dt.year function in a for loop to create a column for each year, but I think there may be a simpler solution in pandas?

Upvotes: 0

Views: 44

Answers (1)

BENY
BENY

Reputation: 323326

You can try crosstab

s = pd.crosstab(index=df.index,columns=df.date.dt.year,values=df.data,aggfunc='sum').fillna('')
df = df.join(s)
df
Out[45]: 
        date  data 2013 2014 2016
0 2013-09-03    10   10          
1 2013-09-04     9    9          
2 2014-09-02    13        13     
3 2016-09-02    17             17

Upvotes: 1

Related Questions