CODEWITHSUNDEEP
pythonpandas
Jason
Jason

Reputation: 467

Loop through date and assign value as week number

I have a Dataframe with a date column. I would like to create a new column and assign value for every 7 days as week 1, week 2... I have learned that df.iloc[::7, :] will do the loop but how to assign values to the new column. Thank you.

Date        Week
1/1/2020    1
2/1/2020    1
3/1/2020    1
...
8/1/2020    2
9/1/2020    2

Upvotes: 0

Views: 1055

Answers (5)

ajsp
ajsp

Reputation: 2670

One liner, just because:

df = df[['Date']]
df['Week_no'] = [pd.Period(df['Date'].iloc[k]).week for k in df.index]

Upvotes: 0

jsmart
jsmart

Reputation: 3001

There is built-in functionality to convert from date to week of year: pd.DatetimeIndex.weekofyear. For example:

import pandas as pd

n = 5
df = pd.DataFrame({'data': [*range(n)]},
             index = pd.date_range(start = '2020-01-01', periods = n, freq='4D')
            )
df['week-number'] = df.index.weekofyear
print(df)

            data  week-number
2020-01-01     0            1
2020-01-05     1            1
2020-01-09     2            2
2020-01-13     3            3
2020-01-17     4            3

weekofyear is deprecated in pandas version 1.1.0. More info here: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DatetimeIndex.weekofyear.html

Upvotes: 2

Basil
Basil

Reputation: 704

If the Date column is a datetime.date type, then isocalendar() will return a 3-tuple containing ISO year, week number, and weekday:

In [10]: d = datetime.date(2020,1,1)

In [11]: d.isocalendar()
Out[11]: (2020, 1, 3)

In [12]: d = datetime.date(2020,1,8)

In [13]: d.isocalendar()
Out[13]: (2020, 2, 3)

Upvotes: 0

jezrael
jezrael

Reputation: 862691

For general solution subtract minimal date, convert timedeltas to days and use integer division by 7 with adding 1:

df['Date'] = pd.to_datetime(df['Date'], format='%d/%m/%Y')

df['Week'] = df['Date'].sub(df['Date'].min()).dt.days // 7 + 1
print (df)
         Date  Week
0 2020-01-01     1
1 2020-01-02     1
2 2020-01-03     1
3 2020-01-08     2
4 2020-01-09     2

If there are consecutive datetimes with no duplicates divide helper array by length of rows with 7 and add 1 (but it failed if miss only 1 date):

df['Week'] = np.arange(len(df)) // 7 + 1

Upvotes: 2

Prune
Prune

Reputation: 77847

Subtract the initial date (row 1) from the current date. Convert to days (integer). divide // by 7 and add 1; that's your week number.

Can you take the coding from there?

week_num = int(current_date - Date[0]) // 7 + 1

Upvotes: 0

Related Questions