CODEWITHSUNDEEP
pythonexcelpandas
PeterL
PeterL

Reputation: 515

Pandas read excel with mixed date formats

I am trying to read Excel that has date values in one column. They are however in 2 different formats:

03.07.2017
03.07.2017
30/06/2017
30/06/2017
30/06/2017
03.07.2017
03.07.2017

07 and 06 are month numbers.

I import excel with:

denik=pd.read_excel('obchodnidenik.xlsx',converters={'Vstup - datum':str})

However the dates are converted differently.

I get two date formats that have switched dates/months:

'30/06/2017'
'2017-03-07 00:00:00'

What is the best way to convert all values into correct datetime?

Upvotes: 1

Views: 3091

Answers (1)

Bharath M Shetty
Bharath M Shetty

Reputation: 30605

You can use Series.replace after reading the excel and then convert it into proper datetime and set dayfirst = True for proper months example:

n = ['03.07.2017','03.07.2017','30/06/2017','30/06/2017','30/06/2017','03.07.2017','03.07.2017']
df = pd.DataFrame(n)
df[0]=df[0].replace('[/\/.]','-',regex=True)
df[0] = pd.to_datetime(df[0],dayfirst=True)

Output:

0   2017-07-03
1   2017-07-03
2   2017-06-30
3   2017-06-30
4   2017-06-30
5   2017-07-03
6   2017-07-03

Upvotes: 1

Related Questions