how split data with respect of months?

Admin 1
Viewed 53

Hi I have a time series data set. I would like to make a new column for each month.

data:

 creationDate            fre      skill
2019-02-15T20:43:29Z      14       A
2019-02-15T21:10:32Z      15       B
2019-03-22T07:14:50Z      41       A
2019-03-22T06:47:41Z      64       B
2019-04-11T09:49:46Z      25       A
2019-04-11T09:49:46Z      29       B

output:

skill   2019-02     2019-03      2019-04
 A        14           41          25
 B        15           64          29

I know I can do it manually like below and make columns (when I have date1_start and date1_end):

dfdate1=data[(data['creationDate'] >= date1_start) & (data['creationDate']<= date1_end)]

But since I have many many months, it is not feasible to that this ways for each month.

pandas time-series
1 Answers

Use DataFrame.pivot with convert datetimes to month periods by Series.dt.to_period:

df['dates'] = pd.to_datetime(df['creationDate']).dt.to_period('m')
df = df.pivot('skill','dates','fre')

Or to custom strings YYYY-MM by Series.dt.strftime:

df['dates'] = pd.to_datetime(df['creationDate']).dt.strftime('%Y-%m')
df = df.pivot('skill','dates','fre')

EDIT:

ValueError: Index contains duplicate entries, cannot reshape

It means there are duplicates, use DataFrame.pivot_table with some aggregation, e.g. sum, mean:

df = df.pivot_table(index='skill',columns='dates',values='fre', aggfunc='sum')
user_29010020
Related