Convert date string YYYY-MM-DD to YYYYMM in pandas

Question

Is there a way in pandas to convert my column date which has the following format '1997-01-31' to '199701', without including any information about the day?

I tried solution of the following form:

df['DATE'] = df['DATE'].apply(lambda x: datetime.strptime(x, '%Y%m'))

but I obtain this error : 'ValueError: time data '1997-01-31' does not match format '%Y%m''

Probably the reason is that I am not including the day in the format. Is there a way better to pass from YYYY-MM_DD format to YYYYMM in pandas?

Solution 1

Might not need to go through the datetime conversion if the data are sufficiently clean (no incorrect strings like 'foo' or '001231'):

df = pd.DataFrame({'date':['1997-01-31', '1997-03-31', '1997-12-18']})

df['date'] = [''.join(x.split('-')[0:2]) for x in df.date]
#     date
#0  199701
#1  199703
#2  199712

Or if you have null values:

df['date'] = df.date.str.replace('-', '').str[0:6]

Solution 2

One way is to convert the date to date time and then use strftime. Just a note that you do lose the datetime functionality of the date

df = pd.DataFrame({'date':['1997-01-31' ]})
df['date'] = pd.to_datetime(df['date'])
df['date'] = df['date'].dt.strftime('%Y%m')


    date
0   199701