3

I am a total newbie in Python and I'm just starting to explore the work handed over to me.

My goal is to determine the week number of the date given. The data is coming from excel and the output will also be shown in the same excel file. My only PROBLEM is that, week 1 of 2021 started on Friday but when I run Python, week 1 starts on January 4, 2021 to January 10, 2021 instead of January 1, 2021 to January 2, 2021. I am currently using this code RPD_week = (pd.to_datetime(dffinal['RPD'], errors='coerce')).dt.strftime("%V")

Please help because I'm literally crying already...

Pam Joson
  • 31
  • 1
  • Well no. January 1st was a Friday but that doesn't mean that calendar systems start counting like that. Weeks are quirky because some there aren't always 52 weeks in a year. If you want to define weeks based on the day of the year, then sounds like you want something like `(pd.to_datetime(dffinal['RPD'], errors='coerce')).dt.dayofyear-1)//7 + 1` – ALollz Jan 22 '21 at 19:50
  • I've tried doing this but I get an error saying TypeError: can only concatenate str (not "float") to str – Pam Joson Jan 24 '21 at 02:26

2 Answers2

1

There are two common systems of numbering weeks in English-speaking countries.

One is the ISO system. Monday is the first day of the week and week 1 is the week that contains 4 January. All days in a new year preceding the first Monday are considered to be in the last week of the previous year. This is the number given by int(mydate.strftime("%V")) in Python and ISOWEEKNUM() in Excel.

The other is the North American system which has at least 2 flavours.

Excel flavour. Sunday is the first day of the week. Week 1 is the week that contains 1 January. Any days in that week prior to 1 January are considered to be in the last week of the previous year. This is the number given by WEEKNUM() in Excel.

Python flavour. Sunday is the first day of the week. All days in a new year preceding the first Sunday are considered to be in week 0. This is the number given by int(mydate.strftime("%U")) in Python.

It follows that Python and Excel frequently disagree. Excel says Thursday 2 January 2020 is in week 1 because it comes after 1 January, which marks week 1. Python says it is in week 0 because week 1 begins on the first Sunday, which was 5 January.

BoarGules
  • 16,440
  • 2
  • 27
  • 44
  • Minor quibble: The week before week 1 in the ISO8601/`%V` system is not week 0, it is week 52 or 53 of the previous year. Try, for example, `time.strftime('%V', time.strptime('2021-01-01', '%Y-%m-%d'))`. – Ture Pålsson Jul 09 '21 at 11:38
  • Quite correct, thank you. Edited to reflect your comment. – BoarGules Jul 09 '21 at 12:37
0

Does this help? The lambda function should return the the number of weeks (7 day periods) since some starting date you provide.

schustan235
  • 36
  • 3