CODEWITHSUNDEEP
pythonpython-3.xpandasdatetime
Mateusz Konopelski
Mateusz Konopelski

Reputation: 1052

Pandas DatetimeIndex and to_datetime discrepancies when calculate (format) the same date

I've got a simple task of creating consectuive days and do some calculations on it.

I did it using:

date = pd.DatetimeIndex(start='2019-01-01', end='2019-01-10',freq='D')
df = pd.DataFrame([date, date.week, date.dayofweek], index=['Date','Week', 'DOW']).T
df

and now I want to calculate back the date from week and day of week using:

df['Date2'] = pd.to_datetime('2019' + df['Week'].map(str) + df['DOW'].map(str), format='%Y%W%w')

The result I get is:

enter image description here

As I understand it DatetimeIndex has a different method of calculating Week Number as 1stJan2019 should be Week=0 and dow=2 and it is when I try run code: pd.to_datetime('201902', format='%Y%W%w') : Timestamp('2019-01-01 00:00:00')

Simmilar questions where asked here and here but both for both of them the discrepency came from different time zones and here I don't use them.

Thanks for help!

Upvotes: 1

Views: 72

Answers (1)

Charles R
Charles R

Reputation: 1661

According to the documentation https://github.com/d3/d3-time-format#api-reference, it appears %W is Monday-based week whereas %w is Sunday-based weekday.

I ran the code bellow to get back the expected result :

date = pd.DatetimeIndex(start='2019-01-01', end='2019-01-10',freq='D')
df = pd.DataFrame([date, date.week, date.weekday_name, date.dayofweek], index=['Date','Week', 'Weekday', 'DOW']).T
df['Week'] = df['Week'] - 1
df['Date2'] = pd.to_datetime('2019' + df['Week'].map(str) + df['Weekday'].map(str), format='%Y%W%A', box=True)

Notice that 2018-12-31 is in the first week of year 2019

                   Date Week    Weekday DOW      Date2
0   2018-12-31 00:00:00    0     Monday   0 2018-12-31

Upvotes: 2

Related Questions