2

I am trying to convert a dataframe column with a date and timestamp to a year-weeknumber format, i.e., 01-05-2017 03:44 = 2017-1. This is pretty easy, however, I am stuck at dates that are in a new year, yet their weeknumber is still the last week of the previous year. The same thing that happens here.

I did the following: 

df['WEEK_NUMBER'] = df.date.dt.year.astype(str).str.cat(df.date.dt.week.astype(str), sep='-')

Where df['date'] is a very large column with date and times, ranging over multiple years.

A date which gives a problem is for example:

Timestamp('2017-01-01 02:11:27')

The output for my code will be 2017-52, while it should be 2016-52. Since the data covers multiple years, and weeknumbers and their corresponding dates change every year, I cannot simply subtract a few days.

Does anybody have an idea of how to fix this? Thanks!

Community
  • 1
  • 1
Han
  • 23
  • 3

1 Answers1

1

Replace df.date.dt.year by this:

(df.date.dt.year- ((df.date.dt.week>50) & (df.date.dt.month==1)))

Basically, it means that you will substract 1 to the year value if the week number is greater than 50 and the month is January.

Zeugma
  • 31,231
  • 9
  • 69
  • 81