Home > Software engineering >  Python to increment date every week in a dataframe
Python to increment date every week in a dataframe

Time:04-26

I am trying to work on this requirement where I need to increment the date in weeks, here is the below code for the same:

import pandas as pd
import numpy as np
c=15
s={'week':[1,2,3,4,5,6,7,8],'Sales':[10,20,30,40,50,60,70,80]}
p=pd.DataFrame(data=s)
p['week'] =p['week'].apply(
    lambda x: datetime.datetime.strptime(f'2021-{x:02}-1', '%Y-%U-%u')
)

O/P-

enter image description here

How would I be able to increment from last row of week column to get next 15 weeks?

Basically, the desired output of week starts from 2022-03-01 till the next 14 weeks.

CodePudding user response:

One option is to use date_range to generate additional dates, then use set_index reindex to append them:

p = p.set_index('week').reindex(pd.date_range('2021-01-04', periods=8 14, freq='W-MON')).rename_axis(['week']).reset_index()

Output:

         week  Sales
0  2021-01-04   10.0
1  2021-01-11   20.0
2  2021-01-18   30.0
3  2021-01-25   40.0
4  2021-02-01   50.0
5  2021-02-08   60.0
6  2021-02-15   70.0
7  2021-02-22   80.0
8  2021-03-01    NaN
9  2021-03-08    NaN
10 2021-03-15    NaN
11 2021-03-22    NaN
12 2021-03-29    NaN
13 2021-04-05    NaN
14 2021-04-12    NaN
15 2021-04-19    NaN
16 2021-04-26    NaN
17 2021-05-03    NaN
18 2021-05-10    NaN
19 2021-05-17    NaN
20 2021-05-24    NaN
21 2021-05-31    NaN

CodePudding user response:

You can modify the length of of week list with range() function and your variable c, but you will also check for the length of sales, which has to have the same number of elements:

import pandas as pd
import numpy as np
import datetime

c=15
weeks = list(range(1, c 1))
sales = [10,20,30,40,50,60,70,80]

s={'week':weeks,'Sales':sales [None]*(len(weeks)-len(sales) if (len(weeks)-len(sales)) >=0 else 0)}
p=pd.DataFrame(data=s)
p['week'] =p['week'].apply(
    lambda x: datetime.datetime.strptime(f'2021-{x:02}-1', '%Y-%U-%u')
)
print(p)

CodePudding user response:

another option in DateOffset:

p = pd.concat([p, pd.DataFrame({'week': [p.iloc[-1,0] pd.DateOffset(weeks=i) for i in range(1,c)]})], ignore_index=True)

>>> p
'''
         week  Sales
0  2021-01-04   10.0
1  2021-01-11   20.0
2  2021-01-18   30.0
3  2021-01-25   40.0
4  2021-02-01   50.0
5  2021-02-08   60.0
6  2021-02-15   70.0
7  2021-02-22   80.0
8  2021-03-01    NaN
9  2021-03-08    NaN
10 2021-03-15    NaN
11 2021-03-22    NaN
12 2021-03-29    NaN
13 2021-04-05    NaN
14 2021-04-12    NaN
15 2021-04-19    NaN
16 2021-04-26    NaN
17 2021-05-03    NaN
18 2021-05-10    NaN
19 2021-05-17    NaN
20 2021-05-24    NaN
21 2021-05-31    NaN
  • Related