Adding additional date rows to pandas dataframe

Question

I have a dataframe that holds quarterly earnings dates for a number stocks, looking like this:

df = pd.DataFrame(  {
'ticker': ['AAPL', 'AAPL', 'AAPL','AAPL', 'MSFT', 'MSFT','MSFT', 'MSFT'], 
'datetime': ['2015-01-01', '2015-04-01', '2015-07-01', '2015-12-01', '2015-01-01', '2015-04-01', '2015-07-01', '2015-12-01'],
})
df['datetime'] = pd.to_datetime(df['datetime'])

I would like to create an additional n rows before and after each date, to make it look like this (for n=1):

df = pd.DataFrame(  {
'ticker': ['AAPL', 'AAPL', 'AAPL','AAPL','AAPL', 'AAPL', 'AAPL','AAPL','AAPL', 'AAPL', 'AAPL','AAPL', 'MSFT', 'MSFT','MSFT', 'MSFT','MSFT', 'MSFT','MSFT', 'MSFT','MSFT', 'MSFT','MSFT', 'MSFT'], 
'datetime': ['2014-12-31','2015-01-01','2015-01-02','2015-03-30','2015-04-01', '2015-04-02', '2015-06-30','2015-07-01','2015-07-02', '2015-11-30','2015-12-01', '2015-12-02',
'2014-12-31','2015-01-01','2015-01-02','2015-03-30','2015-04-01', '2015-04-02', '2015-06-30','2015-07-01','2015-07-02', '2015-11-30','2015-12-01', '2015-12-02'],
})
df['datetime'] = pd.to_datetime(df['datetime'])

Andrej Kesely · Accepted Answer · 2021-05-10 18:17:48Z

2

Substract and add to each date one day and then .explode():

t = pd.Timedelta(days=1)
df["datetime"] = df["datetime"].apply(lambda x: [x - t, x, x + t])
df = df.explode("datetime").reset_index(drop=True)
print(df)

Prints:

   ticker   datetime
0    AAPL 2014-12-31
1    AAPL 2015-01-01
2    AAPL 2015-01-02
3    AAPL 2015-03-31
4    AAPL 2015-04-01
5    AAPL 2015-04-02
6    AAPL 2015-06-30
7    AAPL 2015-07-01
8    AAPL 2015-07-02
9    AAPL 2015-11-30
10   AAPL 2015-12-01
11   AAPL 2015-12-02
12   MSFT 2014-12-31
13   MSFT 2015-01-01
14   MSFT 2015-01-02
15   MSFT 2015-03-31
16   MSFT 2015-04-01
17   MSFT 2015-04-02
18   MSFT 2015-06-30
19   MSFT 2015-07-01
20   MSFT 2015-07-02
21   MSFT 2015-11-30
22   MSFT 2015-12-01
23   MSFT 2015-12-02

EDIT: For n > 1:

n = 2
df["datetime"] = df["datetime"].apply(
    lambda x: [x + pd.Timedelta(days=d) for d in range(-n, n)]
)
df = df.explode("datetime").reset_index(drop=True)
print(df)

Prints:

   ticker   datetime
0    AAPL 2014-12-30
1    AAPL 2014-12-31
2    AAPL 2015-01-01
3    AAPL 2015-01-02
4    AAPL 2015-03-30
5    AAPL 2015-03-31

...

edited May 10, 2021 at 18:17

answered May 10, 2021 at 17:55

Andrej Kesely

196k15 gold badges60 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Niccola Tartaglia Over a year ago

Wow, I never stop being amazed by the elegance of pandas library. Explode is perfect for this. Thank you so much, this is fantastic!!!

Ben.T · Accepted Answer · 2021-05-10 18:13:04Z

2

you can use DateOffset and concat like:

n = 1 # nb of rows to add
res = (pd.concat([df.assign(datetime = df['datetime'] + pd.DateOffset(days=i)) 
                  for i in range(-n, n+1)])
         .sort_values(['ticker','datetime'])
         .reset_index(drop=True)
)
print(res.head(10))
  ticker   datetime
0   AAPL 2014-12-31
1   AAPL 2015-01-01
2   AAPL 2015-01-02
3   AAPL 2015-03-31
4   AAPL 2015-04-01
5   AAPL 2015-04-02
6   AAPL 2015-06-30
7   AAPL 2015-07-01
8   AAPL 2015-07-02
9   AAPL 2015-11-30

edited May 10, 2021 at 18:13

answered May 10, 2021 at 18:09

Ben.T

29.7k6 gold badges39 silver badges57 bronze badges

Collectives™ on Stack Overflow

Adding additional date rows to pandas dataframe

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related