CODEWITHSUNDEEP
pythonpandasdate
daveskis
daveskis

Reputation: 153

Python weekly dataframe between two dates

I have a dataframe below:

    Code       First         End
0    A2M  31/03/2018  29/09/2020
1    AAN  31/03/2007  29/06/2007
2    AAP  31/12/1999  29/09/2000
3    ABB  30/06/2009  29/06/2009
4    ABC  31/12/2009  29/06/2019
5    ABS  31/03/2006  30/03/2008
6    ADP  31/12/1999  29/06/2003
7    AEO  31/03/2002  29/09/2002
8   AFG1  30/09/2006  30/12/2007
9    AGL  31/03/2007  29/06/2020
10  AGL1  31/12/1999  29/09/2006
11   AGO  31/03/2011  30/03/2013
12   AIO  31/12/2007  30/03/2016
13   ALD  31/12/1999  29/06/2020
14   ALL  30/09/2000  29/06/2020
15   ALQ  30/06/2011  29/06/2020
16   ALU  30/06/2019  29/06/2020
17   ALX  31/03/2016  29/06/2020
18   ALZ  30/06/2014  29/06/2014
19   AMC  31/12/1999  29/06/2020

I need to create a new dataframe that lists each of the weeks for each of the codes. For example:

ticker  date
A2M 31/03/2018
A2M 7/04/2018
A2M 14/04/2018
A2M 21/04/2018
A2M 28/04/2018
A2M 5/05/2018
A2M 12/05/2018
A2M 19/05/2018
A2M 26/05/2018
A2M 2/06/2018

I have tried to use a previous answer on here:

import datetime

d1 = datetime.date(2016,12,01)
d2 = datetime.date(2017,01,31)

weekly = d1
while weekly <= d2:
    print weekly
    weekly = weekly + datetime.timedelta(days=7)

But I can't get it to work in a dataframe for multiple Codes.

Upvotes: 1

Views: 75

Answers (1)

Lambda
Lambda

Reputation: 1392

Create a date_range, and then use explode

# pandas version: 1.1.4

df['date_rng'] = df.apply(lambda x: pd.date_range(x['First'], x['End'], freq='7D'), axis=1)
df_exploded = df.explode("date_rng")
result = df_exploded[['Code', 'date_rng']]
result.columns = ['ticker', 'date']

output:

    ticker  date
0   A2M 2018-03-31
0   A2M 2018-04-07
0   A2M 2018-04-14
0   A2M 2018-04-21
0   A2M 2018-04-28
... ... ...
19  AMC 2020-05-29
19  AMC 2020-06-05
19  AMC 2020-06-12
19  AMC 2020-06-19
19  AMC 2020-06-26
6560 rows × 2 columns

Upvotes: 2

Related Questions