String Date conversion in python

Question

I have a data frame with date in the format "Mar-97" and I want to convert it into "03-1997". The format of data is

     Month  SilverPrice GoldPrice
0   Mar-97  186.48  12619.24
1   Apr-97  170.65  12338.59
2   May-97  170.44  12314.94
3   Jun-97  169.96  12202.78
4   Jul-97  155.80  11582.07

I have written this code but it is converting it into "1997-03-01"

from datetime import datetime
df["Month"]=list(map(lambda x:datetime.strptime(x,'%b-%y'),df["Month"]))

and the output is something like this

      Month SilverPrice GoldPrice
0   1997-03-01  186.48  12619.24
1   1997-04-01  170.65  12338.59
2   1997-05-01  170.44  12314.94
3   1997-06-01  169.96  12202.78
4   1997-07-01  155.80  11582.07

I can do it by stripping the day value but is there any direct way to convert it into the "MM-YYYY" format .

jpp · Accepted Answer · 2018-11-30 16:25:14Z

2

`pd.Series.dt.strftime`

You can specify your datetime format via Python's strftime directives:

df['Month'] = pd.to_datetime(df['Month']).dt.strftime('%m-%Y')

print(df)

     Month  SilverPrice  GoldPrice
0  03-1997       186.48   12619.24
1  04-1997       170.65   12338.59
2  05-1997       170.44   12314.94
3  06-1997       169.96   12202.78
4  07-1997       155.80   11582.07

answered Nov 30, 2018 at 16:25

jpp

166k37 gold badges301 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Manish Kumar Singh Over a year ago

But it is giving this error ValueError: day is out of range for month

Saravana Kumar Over a year ago

@ManishKumarSingh : You are getting error because the Date column format is mixed up. "Mar-98" & "2-Sep" these two formats are present in that column. You can see this if you open excel. The solution for this is, df['Month'] = pd.to_datetime(df["Month"].apply(lambda x: datetime.strptime(x,'%b-%y'))).dt.strftime('%m-%Y')

Dani Mesejo · Accepted Answer · 2018-11-30 16:20:02Z

0

You could do:

from datetime import datetime

import pandas as pd

data = ['Mar-97',
        'Apr-97',
        'May-97',
        'Jun-97',
        'Jul-97']

df = pd.DataFrame(data=data, columns=['Month'])
df["Month"] = list(map(lambda x: datetime.strptime(x, '%b-%y').strftime('%m-%Y'), df["Month"]))
print(df)

Output

answered Nov 30, 2018 at 16:20

Dani Mesejo

62.2k6 gold badges56 silver badges86 bronze badges

Comments

Saravana Kumar · Accepted Answer · 2019-11-17 17:34:41Z

0

Date column format is mixed up in this data set. The two visible difference are "Mar-98" & "2-Sep". If you open in excel these two formats are visible.

The solution for this is, 

df['Month'] = pd.to_datetime(df["Month"].apply(lambda x: datetime.strptime(x,'%b-%y'))).dt.strftime('%m-%Y')

answered Nov 17, 2019 at 17:34

Saravana Kumar

851 silver badge4 bronze badges

Collectives™ on Stack Overflow

String Date conversion in python

3 Answers 3

`pd.Series.dt.strftime`

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related