CODEWITHSUNDEEP
python-3.xpandasdataframedata-sciencedata-analysis
manoj kumar
manoj kumar

Reputation: 115

Split the Datetime into Year and Month column in python

How can we split the Datetime values to Year and month and need to split the columns year (2017_year, 2018_year so on...) and values under the year column should get month of respective year?

Example data:

call time            area    age
2017-12-12 19:38:00  Rural   28
2018-01-12 22:05:00  Rural   50
2018-02-12 22:33:00  Rural   76
2019-01-12 22:37:00  Urban   45
2020-02-13 00:26:00  Urban   52

Required Output:

call time            area    age   Year_2017  Year_2018
2017-12-12 19:38:00  Rural   28      jan          jan
2018-01-12 22:05:00  Rural   50      Feb          Feb 
2018-02-12 22:33:00  Rural   76      mar          mar
2019-01-12 22:37:00  Urban   45      Apr          Apr
2020-02-13 00:26:00  Urban   52      may          may

Upvotes: 1

Views: 360

Answers (1)

jezrael
jezrael

Reputation: 862581

I think you need generate years and month from call time datetimes, so output is different:

Explanation - First generate column of months by DataFrame.assign and Series.dt.strftime, then convert years to index with append=True for MultiIndex, so possible reshape by Series.unstack, last add to original:

df1 = (df.assign(m = df['call time'].dt.strftime('%b'))
         .set_index(df['call time'].dt.year, append=True)['m']
         .unstack()
         .add_prefix('Year_'))
print (df1)
call time Year_2017 Year_2018 Year_2019 Year_2020
0               Dec       NaN       NaN       NaN
1               NaN       Jan       NaN       NaN
2               NaN       Feb       NaN       NaN
3               NaN       NaN       Jan       NaN
4               NaN       NaN       NaN       Feb


df = df.join(df1)
print (df)
            call time   area  age Year_2017 Year_2018 Year_2019 Year_2020
0 2017-12-12 19:38:00  Rural   28       Dec       NaN       NaN       NaN
1 2018-01-12 22:05:00  Rural   50       NaN       Jan       NaN       NaN
2 2018-02-12 22:33:00  Rural   76       NaN       Feb       NaN       NaN
3 2019-01-12 22:37:00  Urban   45       NaN       NaN       Jan       NaN
4 2020-02-13 00:26:00  Urban   52       NaN       NaN       NaN       Feb

Upvotes: 4

Related Questions