Create a column where values are max of range of another column in python

Question

My Problem

I am trying to create a column in python where each value is equal to the max value of the last 64 rows of another column i.e. to find the rolling 64 day high of a stock.

I am currently using the following code, but it is really slow because of the loops. I want to try and re-do it without using loops. The dataset is simply the last closing price of a stock.

Current Working Code

import numpy as np
import pandas as pd

csv1 = pd.read_csv('vod_price.csv', delimiter = ',')
df = pd.DataFrame(csv1)

for x in range(1,65):
    df["3m high"].iloc[x]= df["PX_LAST"].iloc[:(x+1)].max()

for x in range(65,len(df.index)):
    df["3m high"].iloc[x]= df["PX_LAST"].iloc[(x-64):(x+1)].max()

df

Attempt at Solution

I have tried the following, but it just gives me the max of the whole column.

maxrange = df['PX_LAST'].between(df['PX_LAST'].shift(64),df['PX_LAST'])

df['3m high'] = df['PX_LAST'].loc[maxrange].max()

Does anyone know how I might be able to do it?

Cheers

jezrael · Accepted Answer · 2019-09-09 10:35:12Z

3

Use Series.rolling:

df["3m high"] = df["PX_LAST"].rolling(64).max()

answered Sep 9, 2019 at 10:35

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

2 Comments

pythonlearner13 Over a year ago

Thanks for such a quick reply! I just tried that and it gives me NaN for the first 64 values. Is there a way to correct this? I was thinking something like this... df['3m high'] = df['PX_LAST'].rolling(min(64,row(index))).max()

jezrael Over a year ago

@pythonlearner13 - Use df["3m high"] = df["PX_LAST"].rolling(64, min_periods=1).max()

Collectives™ on Stack Overflow

Create a column where values are max of range of another column in python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related