Pandas: How do I return a row value once a column reaches a certain value of another column?
From your question:
creating a new timestamp column for when
running_bid_maxgreater than or equal to the value inask_price_target_good. Then create a separate timestamp column for whenrunning_bid_minis less than or equal toask_price_target_bad
the problem seems trivial:
df['g'] = np.where(df.running_bid_max.ge(df.ask_price_target_good), df['time'], pd.NaT)
df['l'] = np.where(df.running_bid_min.le(df.ask_price_target_bad), df['time'], pd.NaT)
Or am I missing something?
Update: you might want to ffill and bfill after the above commands:
df['g'] = df['g'].bfill()
df['l'] = df['l'].ffill()
Output, for example df['g']:
0 2019-07-24 08:00:59.058198
1 2019-07-24 08:00:59.058198
2 2019-07-24 08:00:59.058198
3 2019-07-24 08:00:59.058198
4 2019-07-24 08:00:59.058198
5 2019-07-24 08:00:59.058198
6 2019-07-24 08:00:59.058198
7 2019-07-24 08:00:59.058198
8 2019-07-24 08:00:59.058198
9 2019-07-24 08:00:59.058198
10 2019-07-24 08:01:00.802679
11 2019-07-24 08:01:02.781289
12 2019-07-24 08:01:04.645144
13 2019-07-24 08:01:06.491997
14 2019-07-24 08:01:08.586688
It would be very nice if you could print the desired output. Otherwise I may miss the logic.
If you are working on large amount of data, it makes sense to apply steaming analytics*. (This will quite memory efficient and if you use cytoolz even 2-4 times faster)
So basically you would like to partition your data based on either one or the other condition:
partitions = toolz.partitionby(lambda x: (x['running_bid_max'] >= x['ask_price_target_good']) or
(x['running_bid_min'] <= x['ask_price_target_bad']), data_stream)
Whatever you will do with individual partitions is up to you (you can create addtional fields or columns etc.).
print([(part[0]['time'], part[-1]['time'],
part[0]['running_bid_max'] > part[0]['ask_price_target_good'],
part[0]['running_bid_min'] > part[0]['ask_price_target_bad'])
for part in partitions])
[('2019-07-24T07:59:46.393418', '2019-07-24T07:59:46.393418', False, False),
('2019-07-24T07:59:44.432034', '2019-07-24T07:59:44.432034', False, True),
('2019-07-24T07:59:48.425615', '2019-07-24T07:59:54.428181', False, False),
('2019-07-24T07:59:58.550378', '2019-07-24T08:00:57.338769', False, True),
('2019-07-24T08:00:59.058198', '2019-07-24T08:01:08.586688', True, True)]
Also note that it is easy to create individual DataFrames
info_cols = ['running_bid_max', 'ask_price_target_good', 'running_bid_min', 'ask_price_target_bad', 'time']
data_frames = [pandas.DataFrame(_)[info_cols] for _ in partitions]
data_frames
running_bid_max ask_price_target_good running_bid_min ask_price_target_bad time
0 291.4 291.53 291.09 291.13 2019-07-24T07:59:46.393418
running_bid_max ask_price_target_good running_bid_min ask_price_target_bad time
0 291.4 291.46 291.09 291.06 2019-07-24T07:59:44.432034
running_bid_max ask_price_target_good running_bid_min ask_price_target_bad time
0 291.4 291.53 291.09 291.13 2019-07-24T07:59:48.425615
1 291.4 291.53 291.09 291.13 2019-07-24T07:59:50.084206
2 291.4 291.53 291.09 291.13 2019-07-24T07:59:52.326455
3 291.4 291.53 291.09 291.13 2019-07-24T07:59:54.428181
running_bid_max ask_price_target_good running_bid_min ask_price_target_bad time
0 291.40 291.55 291.2 291.15 2019-07-24T07:59:58.550378
1 291.40 291.55 291.2 291.15 2019-07-24T08:00:00.837238
2 291.51 291.66 291.4 291.26 2019-07-24T08:00:57.338769
running_bid_max ask_price_target_good running_bid_min ask_price_target_bad time
0 291.96 291.66 291.40 291.26 2019-07-24T08:00:59.058198
1 291.96 291.66 291.40 291.26 2019-07-24T08:01:00.802679
2 291.96 291.66 291.45 291.26 2019-07-24T08:01:02.781289
3 291.96 291.66 291.45 291.26 2019-07-24T08:01:04.645144
4 292.07 291.66 291.45 291.26 2019-07-24T08:01:06.491997
5 292.10 291.66 291.45 291.26 2019-07-24T08:01:08.586688
Unfortunatly I couldn't find a one liner pytition_by for DataFrame. It surely is hidden somewhere. (But again, pandas usually loads all data into memory - if you want to aggregate during I/O than streaming could be a way to go.)
*Streaming Example
For example, lets us create a simple csv stream:
def data_stream():
with open('blubb.csv') as tsfile:
reader = csv.DictReader(tsfile, delimiter='\t')
number_keys = [_ for _ in reader.fieldnames if _ != 'time']
def update_values(data_item):
for k in number_keys:
data_item[k] = float(data_item[k])
return data_item
for row in reader:
yield update_values(dict(row))
that yields one processed row at a time:
next(data_stream())
{'time': '2019-07-24T07:59:46.393418',
'bid_price': 291.1,
'ask_price': 291.33,
'running_bid_max': 291.4,
'running_bid_min': 291.09,
'ask_price_target_good': 291.53,
'ask_price_target_bad': 291.13}
I am not sure I correctly understand your problem. I provide below a solution to the following problem:
- For a given row (which I will call the current row), we keep all the rows whose time is between the time of this row and the time of this row plus 5 minutes
- In the rows we have kept, we search if
running_bid_maxmight be superior to the value we have in theask_price_target_goodcolumn of the current row - If so, we keep the first occurrence of
running_bid_maxsuperior toask_price_target_goodof the current row
In your example, for row 0, we have 291.46 in ask_price_target_good. At row 8 (whose time in within the time frame of 5 minutes from the time of row0), we find 291.51 (which is superior to 291.46) and thus we would like to keep this value for row 0.
A symmetric operation must be done for running_bid_min that must be tested to be inferior to ask_price_target_bad.
To solve this problem, I wrote the following code. I am not using iterrows but the apply function of DataFrame. Nevertheless, I need, for each row, to select a bunch of rows from the whole dataframe (the 5 minutes time window) before searching the lines that might be superior to ask_price_target_good. I hope this will be fast enough if you have large dataframes.
import numpy as np
import pandas as pd
import datetime as dtm
data = pd.read_csv("data.csv", parse_dates=["time"])
TIME_WINDOW = 5*60
def over_target_good(row, dataframe):
time_window = dataframe.time <= (row.time
+ dtm.timedelta(seconds=TIME_WINDOW))
window_data = dataframe[time_window]
over_test = window_data.running_bid_max >= row.ask_price_target_good
over_data = window_data[over_test]
if len(over_data) > 0:
return over_data.running_bid_max[over_data.index[0]]
return np.NaN
def below_target_bad(row, dataframe):
time_window = dataframe.time <= (row.time
+ dtm.timedelta(seconds=TIME_WINDOW))
window_data = dataframe[time_window]
below_test = window_data.running_bid_min <= row.ask_price_target_bad
below_data = window_data[below_test]
if len(below_data) > 0:
return below_data.running_bid_min[below_data.index[0]]
return np.NaN
print("OVER\n", data.apply(over_target_good, axis=1, args=(data,)) )
print("BELOW\n", data.apply(below_target_bad, axis=1, args=(data,)) )