Pandas give dates ordinal weekly value

user_12285101 0
Viewed 91

I have dataframe similar to this:

dates=['2017-12-01','2017-12-05','2017-12-17','2017-12-28','2018-01-05','2018-01-09']
val=[5,2,3,6,1,7]
df = pd.DataFrame(list(zip(dates, val)),
               columns =['date', 'val'])
df

    date    val
0   2017-12-01  5
1   2017-12-05  2
2   2017-12-17  3
3   2017-12-28  6
4   2018-01-05  1
5   2018-01-09  7

I want to crate new column that will give to each date the number of week it is between two dates. For example, assuming that I define period that is between 01/12/2017-01/07/2018,
I'll give each date value which is number of week from 01/12. Result for that would be something like this:


    date        val    week_num
0   2017-12-01  5       1
1   2017-12-05  2       1
2   2017-12-17  3       3
3   2017-12-28  6       4
4   2018-01-05  1       6
5   2018-01-09  7       6

So my question is how can I give "count weeks" value for each date between two periods?

pandas date datetime python
4 Answers

We can subtract the timestamps in date column from the start of defined period i.e. 01/12/2017 to calculate the elapsed days, then we can calculate the integer division of the elapsed days with respect to 7 to get the elapsed weeks.

df['date'] = pd.to_datetime(df['date'])

days = df['date'] - pd.to_datetime('01/12/2017', dayfirst=True)
df['week_num'] = (days.dt.days // 7) + 1

        date  val  week_num
0 2017-12-01    5         1
1 2017-12-05    2         1
2 2017-12-17    3         3
3 2017-12-28    6         4
4 2018-01-05    1         6
5 2018-01-09    7         6
user_128331660

Code

df['date'] = pd.to_datetime(df['date'])
df['week_num'] = 1+(df['date']-df.loc[0,'date']).dt.days // 7

Output

      date     val week_num
0   2017-12-01  5   1
1   2017-12-05  2   1
2   2017-12-17  3   3
3   2017-12-28  6   4
4   2018-01-05  1   6
5   2018-01-09  7   6

Explanation

We are subtracting first element of date from each date element which returns TimeDelta object.
We then fetch the days integer from TimeDelta object using dt accessor.
Then dividing the days with 7 go get the week number and finally adding 1 to week number to get desired output.

user_21069340

You first must convert the date as string to date type. Here I used a for-loop to calculate the differences in days between the first date and the following. The deltas are stored in a list and added to the Data Frame.

from datetime import datetime
d = []
df['date'] = pd.to_datetime(df['date'])
for i in df['date']:
  a = i-df['date'][0]
  d.append(int(a.days//7)+1)
df['delta']=d
df

Output:

        date        val    delta   
0       2017-12-01  5      1   
1       2017-12-05  2      1   
2       2017-12-17  3      3   
3       2017-12-28  6      4   
4       2018-01-05  1      6
5       2018-01-09  7      6
user_157538630

Good answers all around, just to add another approach using numpy timedelta:

import numpy as np
import datetime
start = datetime.datetime(year=2017, month=12, day=1)
df['week_num'] = (pd.to_datetime(df['date']) - start) // np.timedelta64(1,'W') + 1

    date    val     week_num
0   2017-12-01  5   1
1   2017-12-05  2   1
2   2017-12-17  3   3
3   2017-12-28  6   4
4   2018-01-05  1   6
5   2018-01-09  7   6
user_29649630
Related