Giladbi
Reputation: 1894
Update the high of the day column based on time series data vector in python
I am trying to update the highest price of the day into a new column named high. If the lastPrice is > high than new high should be updated etc.
I have this data:
SecurityID,dateTime,ask1,ask1Volume,bid1,bid1Volume,ask2,ask2Volume,bid2,bid2Volume,ask3,ask3Volume,bid3,bid3Volume,tradePrice,tradeVolume,isTrade
2318276,2017-11-2008:00:09.052240,12869.0,1,12868.0,3,12870.0,19,12867.5,2,12872.5,2,12867.0,1,0.0,0,0
2318276,2017-11-2008:00:09.052260,12869.0,1,12868.0,3,12870.0,19,12867.5,2,12872.5,2,12867.0,1,12868.0,1,1
2318276,2017-11-2008:00:09.052260,12869.0,1,12868.0,2,12870.0,19,12867.5,2,12872.5,2,12867.0,1,12869.0,1,0
2318276,2017-11-2008:00:09.052270,12869.0,1,12868.0,2,12870.0,19,12867.5,2,12872.5,2,12867.0,1,12870.0,1,1
2318276,2017-11-2008:00:09.052270,12869.0,1,12868.0,1,12870.0,19,12867.5,2,12872.5,2,12867.0,1,12868.0,1,0
2318276,2017-11-2008:00:09.052282,12869.0,1,12868.0,1,12870.0,19,12867.5,2,12872.5,2,12867.0,1,12871.0,1,1
2318276,2017-11-2008:00:09.052282,12869.0,1,12867.5,2,12870.0,19,12867.0,1,12872.5,2,12865.5,1,12868.0,1,0
2318276,2017-11-2008:00:09.052291,12869.0,1,12867.5,2,12870.0,19,12867.0,1,12872.5,2,12865.5,1,12867.5,1,1
2318276,2017-11-2008:00:09.052291,12869.0,1,12867.5,1,12870.0,19,12867.0,1,12872.5,2,12865.5,1,12867.5,1,0
2318276,2017-11-2008:00:09.052315,12869.0,1,12867.5,1,12870.0,19,12867.0,1,12872.5,2,12865.5,1,12867.5,1,1
2318276,2017-11-2008:00:09.052315,12869.0,1,12867.5,1,12870.0,19,12867.0,1,12872.5,2,12865.5,1,12867.0,1,1
2318276,2017-11-2008:00:09.052315,12869.0,1,12867.5,1,12870.0,19,12867.0,1,12872.5,2,12865.5,1,12865.5,1,1
2318276,2017-11-2008:00:09.052315,12869.0,1,12867.5,1,12870.0,19,12867.0,1,12872.5,2,12865.5,1,13865.0,1,1
2318276,2017-11-2008:00:09.052315,12869.0,1,12867.5,1,12870.0,19,12867.0,1,12872.5,2,12865.5,1,12864.0,1,1
I am trying to update the high of the day
from matplotlib import style
import pandas as pd
import numpy as np
style.use('ggplot')
desired_width = 320
pd.set_option('display.width', desired_width)
df = pd.read_csv('C:/test.csv')
df.set_index('dateTime', inplace=True)
df.drop(['SecurityID'],1,inplace=True)
df['high'] = 0
df.high = np.where((df.tradePrice.shift(1) < df.tradePrice),df.tradePrice,df.high.shift(1))
print(df.head(10))
The result is:
tradePrice high
dateTime
2017-11-20 08:00:09.052240 0.0 NaN
2017-11-20 08:00:09.052260 12868.0 12868.0
2017-11-20 08:00:09.052260 12869.0 12869.0
2017-11-20 08:00:09.052270 12870.0 12870.0
2017-11-20 08:00:09.052270 12868.0 0.0
2017-11-20 08:00:09.052282 12871.0 12871.0
2017-11-20 08:00:09.052282 12868.0 0.0
2017-11-20 08:00:09.052291 12867.5 0.0
2017-11-20 08:00:09.052291 12867.5 0.0
2017-11-20 08:00:09.052315 12867.5 0.0
My question is, according to the syntax, if the condition is not met, I should get the prior high achieved but I am getting 0 and not the prior high. Thanks!
Upvotes: 0
Views: 37
Answers (1)
Scott Boston
Reputation: 153460
IIUC, I think you want cummax:
df['high'] = df.tradePrice.cummax()
Output:
SecurityID dateTime ask1 ask1Volume bid1 bid1Volume ask2 ask2Volume bid2 bid2Volume ask3 ask3Volume bid3 bid3Volume tradePrice tradeVolume isTrade high
0 2318276 2017-11-2008:00:09.052240 12869.0 1 12868.0 3 12870.0 19 12867.5 2 12872.5 2 12867.0 1 0.0 0 0 0.0
1 2318276 2017-11-2008:00:09.052260 12869.0 1 12868.0 3 12870.0 19 12867.5 2 12872.5 2 12867.0 1 12868.0 1 1 12868.0
2 2318276 2017-11-2008:00:09.052260 12869.0 1 12868.0 2 12870.0 19 12867.5 2 12872.5 2 12867.0 1 12869.0 1 0 12869.0
3 2318276 2017-11-2008:00:09.052270 12869.0 1 12868.0 2 12870.0 19 12867.5 2 12872.5 2 12867.0 1 12870.0 1 1 12870.0
4 2318276 2017-11-2008:00:09.052270 12869.0 1 12868.0 1 12870.0 19 12867.5 2 12872.5 2 12867.0 1 12868.0 1 0 12870.0
5 2318276 2017-11-2008:00:09.052282 12869.0 1 12868.0 1 12870.0 19 12867.5 2 12872.5 2 12867.0 1 12871.0 1 1 12871.0
6 2318276 2017-11-2008:00:09.052282 12869.0 1 12867.5 2 12870.0 19 12867.0 1 12872.5 2 12865.5 1 12868.0 1 0 12871.0
7 2318276 2017-11-2008:00:09.052291 12869.0 1 12867.5 2 12870.0 19 12867.0 1 12872.5 2 12865.5 1 12867.5 1 1 12871.0
8 2318276 2017-11-2008:00:09.052291 12869.0 1 12867.5 1 12870.0 19 12867.0 1 12872.5 2 12865.5 1 12867.5 1 0 12871.0
9 2318276 2017-11-2008:00:09.052315 12869.0 1 12867.5 1 12870.0 19 12867.0 1 12872.5 2 12865.5 1 12867.5 1 1 12871.0
10 2318276 2017-11-2008:00:09.052315 12869.0 1 12867.5 1 12870.0 19 12867.0 1 12872.5 2 12865.5 1 12867.0 1 1 12871.0
11 2318276 2017-11-2008:00:09.052315 12869.0 1 12867.5 1 12870.0 19 12867.0 1 12872.5 2 12865.5 1 12865.5 1 1 12871.0
12 2318276 2017-11-2008:00:09.052315 12869.0 1 12867.5 1 12870.0 19 12867.0 1 12872.5 2 12865.5 1 13865.0 1 1 13865.0
13 2318276 2017-11-2008:00:09.052315 12869.0 1 12867.5 1 12870.0 19 12867.0 1 12872.5 2 12865.5 1 12864.0 1 1 13865.0
Upvotes: 2
Related Questions
- Vectorization assign the newest value based on datetime
- Vectorize for loop and return x day high and low
- Pandas calculate x day high value
- How to change the value of a day column based on time column?
- How to shift values in pandas dataframe based on some columns in different day
- Python index of a day-column greater than x in the dataframe of many days
- Daily High/Low on intraday data
- Pandas Series: selecting index of highest value in a day?
- Assign first value in the day to the rest of the rows for that day using Pandas
- Python Pandas: for each day in time series function