How to add a column in a rolling basis pandas?-CodePudding

| Open| High| Low| Close|

|----------|-----|-----|----|------|
|2019-12-30|216  | 221 | 213|   220| 
|2019-12-31|241  | 250 | 229|   243|
|2020-01-01|239  | 242 | 217|   229|
|2020-01-02|244  | 252 | 234|   249|
------------------------------------

I have a dataframe of stocks as above with its Date, Open, High, Low, Close. I want to add a column in pandas/python which shows close on a rolling base after 3 days to predict return of 3 days for eg. 1 row will be close of further three days and it will roll as it shifts on next day.

|Date      | Open| High| Low| Close| Rolling|
|----------|-----|-----|----|------|--------|
|2019-12-30|216  | 221 | 213|   220|     249|(this value is after 3 days on 2020-01-02)
|2019-12-31|241  | 250 | 229|   243|     252|(this value is of 3rd JAN(2020-01-03)
|2020-01-01|239  | 242 | 217|   229|        |(Close of 4th Jan) 
|2020-01-02|244  | 252 | 234|   249|        |(Close of 5th Jan)
|2020-01-03|249  | 256 | 244|   252|        |(Close of 6th Jan)
------------------------------------

If possible pls provide solution if we can do it without for loop.

CodePudding user response：

Simple indexation does the job, try this;

import numpy as np
rolling_num = 3
df["rolling_close"] = list(df["Close"][rolling_num:])   [np.nan]*(rolling_num)

Added 3 nan value for last 3 rows...

Hope this Helps...

CodePudding user response：

Another possible solution, using pandas.rolling:

df['Rolling'] = df['Close'].rolling(4).agg(lambda x: x.iloc[-1]).shift(-3)

Output:

         Date  Open  High  Low  Close  Rolling
0  2019-12-30   216   221  213    220    249.0
1  2019-12-31   241   250  229    243    252.0
2  2020-01-01   239   242  217    229      NaN
3  2020-01-02   244   252  234    249      NaN
4  2020-01-03   249   256  244    252      NaN

CodePudding user response：

You can create a new column with pandas.DataFrame.assign and pandas.Timedetla to jump 3 days ahead of the original Date then use pandas.merge to get the corresponding Close value.

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

out = (df.assign(Date2 = lambda x: x['Date']   pd.Timedelta(3, 'days'))
         .merge(df[['Date','Close']], left_on='Date2', right_on='Date', how='left', suffixes=('','_'))
         .drop(columns=['Date2','Date_'])
         .rename(columns={'Close_':'Rolling'})
      )

# Output :

print(out)

        Date  Open  High  Low  Close  Rolling
0 2019-12-30   216   221  213    220    249.0
1 2019-12-31   241   250  229    243    252.0
2 2020-01-01   239   242  217    229      NaN
3 2020-01-02   244   252  234    249      NaN
4 2020-01-03   249   256  244    252      NaN