How to merge month and year columns to get single mm-yyyy column?

Question

I have a df like this:

Sr.  lwd_month lwd_year
1     3        2015
2     6        2018
3.    9        2017
4.    NaN      NaN
5.    5        2015

How can I merge this two columns to get dataframe like below?:

Sr.  lwd_month   lwd_Year   MonthYear
1     3          2015    03-2015
2     6          2018     06-2018
3.    9          2017     09-2017
4.    NaN        NaN      NaT
5.    5          2015     05-2015
6.    3          NaN      NaT

Thanks

jezrael · Accepted Answer · 2018-06-29T06:20:23.370

2

First need columns names with lowercase year and month and pandas version 0.18.1+.

Then use to_datetime for convert by multiple columns with strftime for strings:

df['MonthYear']=pd.to_datetime(df.assign(day=1)[['year','month','day']]).dt.strftime('%m-%Y')
print (df)
   Sr.  month    year MonthYear
0  1.0    3.0  2015.0   03-2015
1  2.0    6.0  2018.0   06-2018
2  3.0    9.0  2017.0   09-2017
3  4.0    NaN     NaN       NaT
4  5.0    5.0  2015.0   05-2015

print (type(df.loc[0, 'MonthYear']))
<class 'str'>

Similar for month period use to_period:

df['MonthYear'] = pd.to_datetime(df.assign(day=1)[['year','month','day']]).dt.to_period('m')
print (df)
   Sr.  month    year MonthYear
0  1.0    3.0  2015.0   2015-03
1  2.0    6.0  2018.0   2018-06
2  3.0    9.0  2017.0   2017-09
3  4.0    NaN     NaN       NaT
4  5.0    5.0  2015.0   2015-05

print (type(df.loc[0, 'MonthYear']))
<class 'pandas._libs.tslibs.period.Period'>

edited Jun 29 '18 at 06:20

answered Jun 29 '18 at 05:54

jezrael

822,522
95
1,334
1,252

1st and 2nd method giving an error `to assemble mappings requires at least that [year, month, day] be specified: [month,year] is missing` – Sociopath Jun 29 '18 at 06:08
I think 3rd not working because there might be a cases where month is present and year is NaN. – Sociopath Jun 29 '18 at 06:10
@AkshayNevrekar - OK, try `df['MonthYear'] = pd.to_datetime(df.assign(Day=1)[['Year','Month','Day']].rename(columns=str.lower)).dt.strftime('%m-%Y')` – jezrael Jun 29 '18 at 06:10
my actual column names are different from given in question. If I'm not wrong I have to replace them with `Year` and `Month` which I'm doing. – Sociopath Jun 29 '18 at 06:12
@AkshayNevrekar - Yes, if repalce them by lowercase it will working nice. – jezrael Jun 29 '18 at 06:12
still giving same error. I edited column names please check. – Sociopath Jun 29 '18 at 06:18
@AkshayNevrekar - need columns names `year`, `month`. – jezrael Jun 29 '18 at 06:19
1

Thanks. changing column name helped here. – Sociopath Jun 29 '18 at 06:24

score 2 · Answer 2 · answered Jun 29 '18 at 06:12

Why not just this:

df['MonthYear'] = pd.to_datetime(df[['Year', 'Month']].assign(Day=1)).dt.strftime('%m-%Y')
print(df)

Output:

   Sr.  Month    Year MonthYear
0  1.0    3.0  2015.0   03-2015
1  2.0    6.0  2018.0   06-2018
2  3.0    9.0  2017.0   09-2017
3  4.0    NaN     NaN       NaT
4  5.0    5.0  2015.0   05-2015

How to merge month and year columns to get single mm-yyyy column?

2 Answers2

Linked