CODEWITHSUNDEEP
pythonpandasdatetime
yponde
yponde

Reputation: 61

Combine Month name and Year in one column

I need to combine Month name and Year column in one column 'Month-Year' in datetime format (Day,Month,Year). Been having issues with NaN and data types. Also need to assign default day as 1 for all rows.

Current data types:

Month: (O)
Year: float64

df1=pd.DataFrame({'Month':["January"," ","December","February"," "," ","March","July"],
'Year':["2020"," ","2017","2015"," "," ","2019","2015"]})

Final Dataframe

enter image description here

Upvotes: 1

Views: 703

Answers (3)

Yash Thenuan
Yash Thenuan

Reputation: 631

Try this:

df1["date"] = pd.to_datetime(df1.Month, format='%B', errors='coerce').dt.date
df1.apply(lambda x: x.date.replace(year=int(float(x.Year))) if x.Year!= " " else "",axis=1)

Upvotes: 0

Pygirl
Pygirl

Reputation: 13349

Try:

df1 = df1.replace(r'\s+', np.nan, regex=True)
df1['Month-Year'] = pd.to_datetime(df1['Month']+ '-'+ df1['Year']).dt.strftime('%d/%m/%Y')

df1:

    Month       Year    Month-Year
0   January     2020    01/01/2020
1   NaN         NaN     NaN
2   December    2017    01/12/2017
3   February    2015    01/02/2015
4   NaN         NaN     NaN
5   NaN         NaN     NaN
6   March       2019    01/03/2019
7   July        2015    01/07/2015

Upvotes: 5

kpsk2000
kpsk2000

Reputation: 41

One thing u could do is Create a column(Month-Integer) such that January to 01, February to 02, and so-on. Then use this column and Year column to create Month-Year Column, then if you want you can delete the (Month-Integer) Column.

Upvotes: 0

Related Questions