How to Get Week Number in Python

How to retrieve the week number of the year starting at 1 Jan?

Is there another way in which I can get the week number of year based on starting at 1st Jan so I get 1 for 01/01/2022?

You can use, for a given date dt, as you said in the question:

TO_CHAR(dt, 'fmWW')

or can calculate it using:

FLOOR((dt - TRUNC(dt, 'YY'))/7)+1

or, to match the python code, can get the ISO week using:

TO_CHAR(dt, 'fmIW')

Or, if you want to start counting the week from the 1st January and change weeks on a Monday then:

FLOOR((dt - TRUNC(TRUNC(dt, 'YY'), 'IW'))/7)+1

Then the query:

WITH sample_dates (dt) AS (
  SELECT DATE '2021-12-30' + LEVEL - 1 FROM DUAL CONNECT BY LEVEL <= 40
)
SELECT dt,
       TO_CHAR(dt, 'fmWW') AS week,
       TO_CHAR(dt, 'fmIW') AS isoweek,
       FLOOR((dt - TRUNC(dt, 'YY'))/7)+1 AS weekfromyearstart,
       FLOOR((dt - TRUNC(TRUNC(dt, 'YY'), 'IW'))/7)+1 AS montosunweekfromyearstart
FROM   sample_dates

Outputs:






































































































































































































































































































DTWEEKISOWEEKWEEKFROMYEARSTARTMONTOSUNWEEKFROMYEARSTART
2021-12-3052525253
2021-12-3153525353
2022-01-0115211
2022-01-0215211
2022-01-031112
2022-01-041112
2022-01-051112
2022-01-061112
2022-01-071112
2022-01-082122
2022-01-092122
2022-01-102223
2022-01-112223
2022-01-122223
2022-01-132223
2022-01-142223
2022-01-153233
2022-01-163233
2022-01-173334
2022-01-183334
2022-01-193334
2022-01-203334
2022-01-213334
2022-01-224344
2022-01-234344
2022-01-244445
2022-01-254445
2022-01-264445
2022-01-274445
2022-01-284445
2022-01-295455
2022-01-305455
2022-01-315556
2022-02-015556
2022-02-025556
2022-02-035556
2022-02-045556
2022-02-056566
2022-02-066566
2022-02-076667

To get the week number in a column using Python

This line isn't as nice, but it works:

p['week'] = [datetime.datetime.strptime(str(p['yr'][i])+p['Month'][i]+str(p['Days'][i]), '%Y%b%d').isocalendar()[1] for i in range(len(p))]

How to get the week number of the current quarter in Python?

Unless this is a very common way to reckon week numbering, I don't know if you are going to find a library that will do this exactly for you, but it's easy enough to accomplish using dateutil's relativedelta and a little logic. Here's a simple implementation that returns a tuple (quarter, week). Since you said that Q1 starts April 1st, I am assuming that the period from January 1st to April 1st is called Q0:

from datetime import date, datetime, timedelta
import typing

from dateutil import relativedelta

NEXT_MONDAY = relativedelta.relativedelta(weekday=relativedelta.MO)
LAST_MONDAY = relativedelta.relativedelta(weekday=relativedelta.MO(-1))
ONE_WEEK = timedelta(weeks=1)

def week_in_quarter(dt: datetime) -> typing.Tuple[int, int]:
    d: date = dt.date()
    year = d.year

    # Q0 = January 1, Q1 = April 1, Q2 = July 1, Q3 = October 1
    quarter = ((d.month - 1) // 3)
    quarter_start = date(year, (quarter * 3) + 1, 1)
    quarter_week_2_monday = quarter_start + NEXT_MONDAY

    if d < quarter_week_2_monday:
        week = 1
    else:
        cur_week_monday = d + LAST_MONDAY
        week = int((cur_week_monday - quarter_week_2_monday) / ONE_WEEK) + 2

    return quarter, week

Which returns:

$ python week_in_quarter.py 
2020-01-01: Q0-W01
2020-02-01: Q0-W05
2020-02-29: Q0-W09
2020-03-01: Q0-W09
2020-06-30: Q1-W14
2020-07-01: Q2-W01
2020-09-04: Q2-W10
2020-12-31: Q3-W14

If I've misunderstood the first quarter of the calendar year, and it's actually the case that January 1–April 1 of year X is considered Q4 of year X-1, then you can change the return quarter, week line at the end to this (and change the return type annotation):

if quarter == 0:
    year -= 1
    quarter = 4

return year, quarter, week

Which changes the return values to:

$ python week_in_quarter.py 
2020-01-01: FY2019-Q4-W01
2020-02-01: FY2019-Q4-W05
2020-02-29: FY2019-Q4-W09
2020-03-01: FY2019-Q4-W09
2020-06-30: FY2020-Q1-W14
2020-07-01: FY2020-Q2-W01
2020-09-04: FY2020-Q2-W10
2020-12-31: FY2020-Q3-W14

If this is something that is a speed bottleneck, it should probably be easy to write an optimized version of this that does not use dateutil.relativedelta, but instead calculates this based on day of week, day of year and whether or not this is a leap year (calendar calculations in Python usually go faster if you can turn it into integer operations as early in the process as possible), but I suspect that in most cases this version should be the easiest to read and understand.

If you would like to avoid the dependency on dateutil, you can replace NEXT_MONDAY and LAST_MONDAY with simple functions:

def next_monday(dt: date) -> date:
    weekday = dt.weekday()
    return dt + timedelta(days=(7 - weekday) % 7)

def last_monday(dt: date) -> date:
    weekday = dt.weekday()
    return dt - timedelta(days=weekday)

In which case you would assign the two _monday variables as quarter_week_2_monday = next_monday(quarter_start) and cur_week_monday = last_monday(dt), respectively.

As a note: if I were writing this function, I'd probably not have it return a bare tuple of integers, but instead use attrs or a dataclass to create a simple class for the purpose, like so:

import attr

@attr.s(auto_attribs=True, frozen=True, slots=True)
class QuarterInWeek:
    year: int
    quarter: int
    week: int

    def __str__(self):
        return f"FY{self.year}-Q{self.quarter}-W{self.week:02d}"

(Note that slots=True is optional, and I think not available if you use dataclasses.dataclass instead — it's just that this is a simple struct and I tend to use slots classes for simple structs).

getting week number from date python

As far as why the week number is 1 for 12/29/2014 -- see the question I linked to in the comments. For the second part of your question:

January 1, 2014 was a Wednesday. We can take the minimum date of your date column, get the day number and subtract from the difference:

Solution

# x["date"] = pd.to_datetime(x["date"])  # if not already a datetime column
min_date = x["date"].min() + 1  # + 1 because they're zero-indexed
x["weeks_from_start"] = ((x["date"].diff().dt.days.cumsum() - min_date) // 7 + 1).fillna(1).astype(int)

Output:

        date  weeks_from_start
0 2014-01-01                 1
1 2014-12-29                52
2 2015-01-01                52
3 2015-01-15                54

Step by step

The first step is to convert the date column to the datetime type, if you haven't already:

In [3]: x.dtypes
Out[3]:
date    object
dtype: object

In [4]: x["date"] = pd.to_datetime(x["date"])

In [5]: x
Out[5]:
        date
0 2014-01-01
1 2014-12-29
2 2015-01-01
3 2015-01-15

In [6]: x.dtypes
Out[6]:
date    datetime64[ns]
dtype: object

Next, we need to find the minimum of your date column and set that as the starting date day of the week number (adding 1 because the day number starts at 0):

In [7]: x["date"].min().day + 1
Out[7]: 2

Next, use the built-in .diff() function to take the differences of adjacent rows:

In [8]: x["date"].diff()
Out[8]:
0        NaT
1   362 days
2     3 days
3    14 days
Name: date, dtype: timedelta64[ns]

Note that we get NaT ("not a time") for the first entry -- that's because the first row has nothing to compare to above it.

The way to interpret these values is that row 1 is 362 days after row 0, and row 2 is 3 days after row 1, etc.

If you take the cumulative sum and subtract the starting day number, you'll get the days since the starting date, in this case 2014-01-01, as if the Wednesday was day 0 of that first week (this is because when we calculate the number of weeks since that starting date, we need to compensate for the fact that Wednesday was the middle of that week):

In [9]: x["date"].diff().dt.days.cumsum() - min_date
Out[9]:
0      NaN
1    360.0
2    363.0
3    377.0
Name: date, dtype: float64

Now when we take the floor division by 7, we'll get the correct number of weeks since the starting date:

In [10]: (x["date"].diff().dt.days.cumsum() - 2) // 7 + 1
Out[10]:
0     NaN
1    52.0
2    52.0
3    54.0
Name: date, dtype: float64

Note that we add 1 because (I assume) you're counting from 1 -- i.e., 2014-01-01 is week 1 for you, and not week 0.

Finally, the .fillna is just to take care of that NaT (which turned into a NaN when we started doing arithmetic). You use .fillna(value) to fill NaNs with value:

In [11]: ((x["date"].diff().dt.days.cumsum() - 2) // 7 + 1).fillna(1)
Out[11]:
0     1.0
1    52.0
2    52.0
3    54.0
Name: date, dtype: float64

Finally use .astype() to convert the column to integers instead of floats.

Get values with specific week number - Pandas

Just use slicing, should work. And as ijdnam_alim mentioned, use days.

week_prior_5=gdf_hpo_2['weeknumber'].max() - timedelta(days=35)

df_5weeks = gdf_hpo_2[(gdf_hpo_2.weeknumber <= gdf_hpo_2.weeknumber.max()) \
                       and (gdf_hpo_2.weeknumber > week_prior_5)]

Let me know if this doesn't work.

How do I convert an object to a week number in datetime

Add Index column to melt first for only week values in variable, then convert to floats, integers and strings, so possible match by weeks:

data = [[0,'John',1,2,3]]

df = pd.DataFrame(data, columns = ['Index','Owner','32.0','33.0','34.0'])

print(df)
   Index Owner  32.0  33.0  34.0
0      0  John     1     2     3

df = df.melt(id_vars=['Index','Owner'])

s = df['variable'].astype(float).astype(int).astype(str) + '-0-2021'
print (s)
0    32-0-2021
1    33-0-2021
2    34-0-2021
Name: variable, dtype: object

#https://stackoverflow.com/a/17087427/2901002
df['variable'] = pd.to_datetime(s, format = '%W-%w-%Y')

print (df)
   Index Owner   variable  value
0      0  John 2021-08-15      1
1      0  John 2021-08-22      2
2      0  John 2021-08-29      3

EDIT:

For get original DataFrame (integers columns for weeks) use DataFrame.pivot:

df1 = (df.pivot(index=['Index','Owner'], columns='variable', values='value')
         .rename_axis(None, axis=1))
df1.columns = df1.columns.strftime('%W')
df1 = df1.reset_index()
print (df1)
   Index Owner  32  33  34
0      0  John   1   2   3

Related Topics

Pandas: Drop Consecutive Duplicates

Why Python 3.6.1 Throws Attributeerror: Module 'Enum' Has No Attribute 'Intflag'

Is It Pythonic: Naming Lambdas

Pandas Read_Csv: Low_Memory and Dtype Options

Python List VS. Array - When to Use

How to Create a Guid/Uuid in Python

How to Convert SQLalchemy Row Object to a Python Dict

Join List of Lists in Python

How to Convert a Utc Datetime to a Local Datetime Using Only Standard Library

How to Delete Items from a Dictionary While Iterating Over It

What Is the Standard Way to Add N Seconds to Datetime.Time in Python

Unicodedecodeerror: 'Utf8' Codec Can't Decode Byte 0Xa5 in Position 0: Invalid Start Byte

How to Save a New Sheet in an Existing Excel File, Using Pandas

Python Re.Sub Group: Number After \Number

Pythonic Way to Check If a List Is Sorted or Not

Pandas Dataframe to List of Lists

What Exactly Does "Import *" Import

Access a Function Variable Outside the Function Without Using "Global"


Leave a reply



Submit