sdaza sdaza - 2 months ago 38
Python Question

parsing dates with pandas from Excel file

I am trying to parse dates from an Excel file following a given format

%d/%m/%y
. I am not able to set the format, I am always getting
%m/%d/%Y
. Any ideas on how to do it?

import pandas as pd

url = 'https://www.dropbox.com/s/8gqmq3jx27unsta/example_dates.xlsx?dl=1'

file = pd.ExcelFile(url, parse_date=True,
date_parser = (lambda x: pd.to_datetime(x, format ='%d/%m/%y')))

df = file.parse(0)

df
date variable
0 2016-01-06 1
1 2016-06-07 2
2 2016-12-10 3
3 2016-12-29 4

Answer Source

You can use converters:

>>> df = file.parse(0, converters={'date': lambda x: pd.to_datetime(x).strftime("%d/%m/%Y")})

>>> df
    date    variable
0   06/01/2016  1
1   07/06/2016  2
2   10/12/2016  3
3   29/12/2016  4