CODEWITHSUNDEEP
pythonpandaspython-datetime
jecklymeister
jecklymeister

Reputation: 65

Pandas: Multiple date formats in one column

I have two date formats in one Pandas series (column) that need to be standardized into one format (mmm dd & mm/dd/YY)

Date

Jan 3

Jan 2

Jan 1

12/31/19

12/30/19

12/29/19

Even Excel won't recognize the mmm dd format as a date format. I can change the mmm to a fully-spelled out month using str.replace:

df['Date'] = df['Date'].str.replace('Jan', 'January', regex=True)

But how do I add the current year? How do I then convert January 1, 2020 to 01/01/20?

Upvotes: 2

Views: 6969

Answers (3)

jecklymeister
jecklymeister

Reputation: 65

Found the solution (needed to use apply):

df['date'] = df['date'].apply(dateutil.parser.parse)

Upvotes: 0

InfoLearner
InfoLearner

Reputation: 15608

Have you tried the parse()

from dateutil.parser import parse
import datetime

def clean_date(text):
  datetimestr = parse(text)
  text = datetime.strptime(datetimestr, '%Y%m%d')
  return text

df['Date'] = df['Date'].apply(clean_date)
df['Date'] = pd.to_datetime(df['Date'])

Upvotes: 4

Abhay
Abhay

Reputation: 615

If it's in a data frame use this:

from dateutil.parser import parse
import pandas as pd

for i in range(len(df['Date'])):
    df['Date'][i] = parse(df['Date'][i])
df['Date'] = pd.to_datetime(df['Date']).dt.strftime("%d-%m-%Y")

Upvotes: 1

Related Questions