Home / Questions / 90186
Accepted
Jorge Ponti
Asked: 2020-07-25 11:03:27 +0800 CST 2020-07-25 11:03:27 +0800 CST

Pandas Wrongly load dates in Python 3

  • 772

I am programming a task in Python but when loading the dates from a CSV using Pandas it loads them in an erroneous way, sometimes it changes the day for the month. For example, for these cases they should all be from the month 06, but sometimes it is 06 and other times it exchanges 06 for the day, changing the month, for example row 19 to 23, 27 and 28:

   ......
   19   2017-03-06 01:10:00
   20   2017-03-06 01:10:00
   21   2017-03-06 17:44:00
   22   2017-03-06 17:44:00
   23   2017-04-06 04:12:00
   24   2017-06-06 04:21:00
   25   2017-06-06 04:21:00
   26   2017-06-06 15:37:00
   27   2017-09-06 18:43:00
   28   2017-09-06 18:43:00
   29   2017-09-06 21:59:00
   ....

I load the file like this and create a list of dates:

   df = pd.read_csv('PORFA.csv', header=0, sep=';')
   Date2=pd.to_datetime(df["FDE Date"]) #Crear lista de Fechas

But when printing "Date2" it throws it as above.

I attach the BBDD that I am reading:

https://drive.google.com/file/d/0B11sJdX_AaJBRllVVjdkenE3RzA/view?usp=sharing

python

2 Answers

  1. Best Answer

    The problem is that by default it tries to parse the date interpreting that the month comes before the day, when it finds an incongruous date it tries to parse the first data as the day. This causes you to not get exceptions.

    Your date has the format dd-mm-aaaaso you must tell it to try to use the first data as day and not as month. For this use the attribute dayfirst:

    import pandas as pd

df = pd.read_csv('PORFA.csv', header=0, sep=';')
Date2=pd.to_datetime(df["FDE Date"], dayfirst = True)

    Departure:

    >>> Date2
...
19   2017-06-03 01:10:00
20   2017-06-03 01:10:00
21   2017-06-03 17:44:00
22   2017-06-03 17:44:00
23   2017-06-04 04:12:00
24   2017-06-06 04:21:00
25   2017-06-06 04:21:00
26   2017-06-06 15:37:00
27   2017-06-09 18:43:00
28   2017-06-09 18:43:00
29   2017-06-09 21:59:00
...

    Warning: dayfirst = True it is not strict, this means that it will always try to parse the date using the first data as day, but if it comes across a date that is not parsable in this way, it will parse it using another format that is plausible. Let me explain, if it encounters 05-17-2017it will not throw an exception but it will parse it as 2017-05-17.

    This shouldn't be a problem as long as you're sure your dates are valid and that they're all in the dd-mm-aaaa. This is the verbatim notice from the documentation:

    Warning: dayfirst=True is not strict, but will prefer to parse with day first (this is a known bug, based on dateutil behavior).

    Edition:

    You can properly parse the columns by loading the csv directly.

    import pandas as pd

df = pd.read_csv('PORFA.csv', header=0, sep=';',
                 parse_dates = ['FDE Date'], dayfirst = True )

    In parse_datesthis case, you must pass the names of the columns that must be parsed as a date. If they were non-standard dates, you must use the argument by date_parserpassing a function that matches the string to a valid date.

    You can later sort the DataFrame by date using one of these columns:

    df.sort_values(by="FDE Date", inplace = True)
    • 4

  2. The problem I had was due to the fact that the dates were in dd/mm/yy format and in the program where I perform the data visualization, by default it takes the first value as month, the second as day, and the third as year. when using the instruction "inplace=True" at the time of uploading the CSV file, I organize it as explained in this post

    • 0