Search code examples
pythonpandasdatetimeiso

Inconsistency in Pandas to_datetime() with week number of the year

I found an inconsistency with Pandas to_datetime() function and I don't know what to do.

I have a dataframe with a columnn "Year" and a column "week number of the year". I tried to use to_datetime() function to retrieve the date of the monday of the corresponding week but found an inconsistency. I turn to you to find out if this is really a mistake by Pandas or if the mistake is mine.

Ex: a row with year 2019 and week number of the year: 31

pd.to_datetime("2019311", format="%Y%W%w")
Out[166]: Timestamp('2019-08-05 00:00:00')

But this is the monday of week 32 of 2019.

And if I do :

pd.to_datetime("20190729", format="%Y%m%d").isocalendar()
Out[167]: (2019, 31, 1)

This is the actual date I wanted to retrieve.

Thank you for any help !

Solution

  • Ok I found my answer !

    New in version 3.6: %G, %u and %V were added. These parameters all correspond to ISO 8601 date values.

    %G ISO 8601 year with century representing the year that contains the greater part of the ISO week (%V).

    %u ISO 8601 weekday as a decimal number where 1 is Monday.

    %V ISO 8601 week as a decimal number with Monday as the first day of the week. Week 01 is the week containing Jan 4.

    https://docs.python.org/3/library/datetime.html#strftime-and-strptime-behavior

    pd.to_datetime("2019311", format="%G%V%u")
Out[216]: Timestamp('2019-07-29 00:00:00')

    Thank you !