Is there a way to make the window in df.rolling dynamic depending on which row it is calculating for?
Is there a way to make the window in df.rolling dynamic depending on which row it is calculating for?
Loading saved threads...
PyNewb · External communityPost link
External question — Data Science Stack Exchange
Author: PyNewb
Original post: https://datascience.stackexchange.com/questions/91072
License: CC BY-SA 4.0 — https://creativecommons.org/licenses/by-sa/4.0/
Adaptation: HTML converted to plain text; contact email addresses removed.
I have a dataset of stock prices, and I want to add a column of 52 week lows for each day, however for the rows which dont have 365 days above them I just want the column to have the rolling min using whatever amount of rows exist above.
I was trying code like this, but this obviously doesnt work because its creating the column twice.
for row in data.iterrows():
if row[0] < (data.index[0] + timedelta(days = 365)):
data['52wkLow'] = data['Low']['GME'].rolling((row[0]-data.index[0]).days).min()
elif row[0] > (data.index[0] + timedelta(days = 365)):
data['52wkLow'] = data['Low']['GME'].rolling(365).min()
Here is an example of the data im wanting to get the rolling min for:
Date
2021-01-04 17.250000
2021-01-05 17.370001
2021-01-06 18.360001
2021-01-07 18.080000
2021-01-08 17.690001
2021-01-11 19.940001
2021-01-12 19.950001
2021-01-13 31.400000
2021-01-14 39.910000
2021-01-15 35.500000
2021-01-19 39.360001
2021-01-20 39.119999
2021-01-21 43.029999
2021-01-22 65.010002
2021-01-25 76.790001
2021-01-26 147.979996
2021-01-27 347.510010
2021-01-28 193.600006
2021-01-29 325.000000
2021-02-01 225.000000
2021-02-02 90.000000
2021-02-03 92.410004
2021-02-04 53.500000
2021-02-05 63.770000
2021-02-08 60.000000
2021-02-09 50.310001
2021-02-10 51.200001
2021-02-11 51.099998
2021-02-12 52.400002
2021-02-16 49.509998
2021-02-17 45.939999
2021-02-18 40.689999
2021-02-19 40.590000
2021-02-22 46.000000
2021-02-23 44.970001
2021-02-24 91.709999
2021-02-25 108.730003
2021-02-26 101.739998
2021-03-01 120.400002
2021-03-02 118.180000
2021-03-03 124.180000
2021-03-04 132.350006
2021-03-05 137.740005
2021-03-08 194.500000
2021-03-09 246.899994
2021-03-10 265.000000
2021-03-11 260.000000
2021-03-12 264.500000
2021-03-15 220.139999
2021-03-16 208.169998
2021-03-17 209.809998
Name: GME, dtype: float64
Quote
Report
Deven Ramani · External communityPost link
External answer — Data Science Stack Exchange
Author: Deven Ramani
Original post: https://datascience.stackexchange.com/a/91076
License: CC BY-SA 4.0 — https://creativecommons.org/licenses/by-sa/4.0/
Adaptation: HTML converted to plain text; contact email addresses removed.
simply use
.rolling(365, min_periods=1)
no need of for loop
data['52wkLow'] = data['Low']['GME'].rolling(365,min_periods=1).min()
Quote
Report
Mario · External communityPost link
External answer — Data Science Stack Exchange
Author: Mario
Original post: https://datascience.stackexchange.com/a/131429
License: CC BY-SA 4.0 — https://creativecommons.org/licenses/by-sa/4.0/
Adaptation: HTML converted to plain text; contact email addresses removed.
IIRC, based on
documentation
you can try following:
import pandas as pd
import numpy as np
# Sample data
dates = pd.to_datetime([
'2021-01-04', '2021-01-05', '2021-01-06', '2021-01-07', '2021-01-08',
'2021-01-11', '2021-01-12', '2021-01-13', '2021-01-14', '2021-01-15',
'2021-01-19', '2021-01-20', '2021-01-21', '2021-01-22', '2021-01-25',
'2021-01-26', '2021-01-27', '2021-01-28', '2021-01-29', '2021-02-01',
'2021-02-02', '2021-02-03', '2021-02-04', '2021-02-05', '2021-02-08',
'2021-02-09', '2021-02-10', '2021-02-11', '2021-02-12', '2021-02-16',
'2021-02-17', '2021-02-18', '2021-02-19', '2021-02-22', '2021-02-23',
'2021-02-24', '2021-02-25', '2021-02-26', '2021-03-01', '2021-03-02',
'2021-03-03', '2021-03-04', '2021-03-05', '2021-03-08', '2021-03-09',
'2021-03-10', '2021-03-11', '2021-03-12', '2021-03-15', '2021-03-16',
'2021-03-17'
])
# Ensure the number of rows in Low matches the number of dates
low_prices = np.random.uniform(10, 50, len(dates)) # Random Low prices with the correct length
# Create DataFrame
data = pd.DataFrame({
'Date': dates,
'Low': low_prices
})
data.set_index('Date', inplace=True)
# Add a new column for the rolling 52 week low
def dynamic_rolling_min(i, series):
# Calculate the number of days between the current row and the first row
# days_since_start = (i - series.index[0]).days # This line is no longer needed
# Use .loc with date slicing:
window_start = i - pd.DateOffset(years=1) # 1 year (approximately 52 weeks)
# Get the minimum within the rolling window
rolling_min = series.loc[window_start:i].min()
return rolling_min
# Apply the rolling calculation to each row
#data['52wkLow'] = data.index.to_series().apply(lambda x: dynamic_rolling_min(x, data['Low']))
#data['52wkLow'] = data['Low']['GME'].rolling(365,min_periods=1).min()
data['52wkLow'] = data['Low'].rolling(365,min_periods=1).min()
print(data[['Low', '52wkLow']])
Quote
Report
Post Reply
Quoted from Forex.com.bd-Editorial External answer — Data Science Stack Exchange Author: Mario Source score (net votes, not local likes): 1 Original post: https://datascience.stackexchange.com/a/131429 License: CC BY-SA 4.0 — https://creativecommons.org/licenses/by-sa/4.0/ Adaptation: HTML converted to plain text; contact email addresses removed. IIRC, based on documentation you can try following: import pandas as pd import numpy as np # Sample data dates = pd.to_datetime([ '2021-01-04', '2021-01-05', '2021-01-06', '2021-01-07', '2021-01-08', '2021-01-11', '2021-01-12', '2021-01-13', '2021-01-14', '2021-01-15', '2021-01-19', '2021-01-20', '2021-01-21', '2021-01-22', '2021-01-25', '2021-01-26', '2021-01-27', '2021-01-28', '2021-01-29', '2021-02-01', '2021-02-02', '2021-02-03', '2021-02-04', '2021-02-05', '2021-02-08', '2021-02-09', '2021-02-10', '2021-02-11', '2021-02-12', '2021-02-16', '2021-02-17', '2021-02-18', '2021-02-19', '2021-02-22', '2021-02-23', '2021-02-24', '2021-02-25', '2021-02-26', '2021-03-01', '2021-03-02', '2021-03-03', '2021-03-04', '2021-03-05', '2021-03-08', '2021-03-09', '2021-03-10', '2021-03-11', '2021-03-12', '2021-03-15', '2021-03-16', '2021-03-17' ]) # Ensure the number of rows in Low matches the number of dates low_prices = np.random.uniform(10, 50, len(dates)) # Random Low prices with the correct length # Create DataFrame data = pd.DataFrame({ 'Date': dates, 'Low': low_prices }) data.set_index('Date', inplace=True) # Add a new column for the rolling 52 week low def dynamic_rolling_min(i, series): # Calculate the number of days between the current row and the first row # days_since_start = (i - series.index[0]).days # This line is no longer needed # Use .loc with date slicing: window_start = i - pd.DateOffset(years=1) # 1 year (approximately 52 weeks) # Get the minimum within the rolling window rolling_min = series.loc[window_start:i].min() return rolling_min # Apply the rolling calculation to each row #data['52wkLow'] = data.index.to_series().apply(lambda x: dynamic_rolling_min(x, data['Low'])) #data['52wkLow'] = data['Low']['GME'].rolling(365,min_periods=1).min() data['52wkLow'] = data['Low'].rolling(365,min_periods=1).min() print(data[['Low', '52wkLow']])
Checking account access…