0

I am really struggling to get a logic for this . I have data set called Col as shown below . I am using Python and Pandas

I want to set a new column called as "STATUS" . The logic is

a. When Col==0 , i will Buy . But this Buy will happen only when Col==0 is the first value in the data set or after the Status Sell. There cannot be two Buy values without a Sell in between

b. When Col<=-8 I will Sell. But this will happen if there is a Buy preceding it in the Satus Column. There cannot be two Sells without a Buy in between them .

I have provided the example of how i want my output as. Any help is really appreciated

Here the raw data is in the column : Col and output i want is in Status

  +-------+--------+
    |  Col  | Status |
    +-------+--------+
    | 0     | Buy    |
    | -1.41 | 0      |
    | 0     | 0      |
    | -7.37 | 0      |
    | -8.78 | Sell   |
    | -11.6 | 0      |
    | 0     | Buy    |
    | -5    | 0      |
    | -6.1  | 0      |
    | -8    | Sell   |
    | -11   | 0      |
    | 0     | Buy    |
    | 0     | 0      |
    | -9    | Sell   |
    +-------+--------+
vista
  • 13
  • 3
  • 2
    kindly post the original dataframe. I assume what you currently have here is the expected output – sammywemmy Mar 19 '20 at 09:59
  • The Column Col is the Data Column i have and Status is the output –  vista Mar 19 '20 at 10:10
  • Besides of the problem you're describing here that is something I would handle inside the backtesting algorithm (assuming that is what you're trying to do). Therefore I would allow multiple buys/sells without sells/buys between them and just ignore them inside your backtesting logic. – SerAlejo Mar 19 '20 at 10:12
  • Yes, it's a backtesting algorithm . I tried using multiple Buys and Sells , but the only way to eliminate them appears like a string concatenation and using a regex. i can't think of any other way –  vista Mar 19 '20 at 10:28

1 Answers1

0

Took me some time. Relies on the following property :
the last order you can see from now, even if you chose not to send it, is always the last decision that you took. (Otherwise it would have been sent.)

df['order'] = (df['order'] == 0).astype(int) - (df['order'] <= -8).astype(int)
orders_no_filter = df.loc[df['order'] != 0, 'order']
possible = (orders_no_filter != orders_no_filter.shift(1))
df['order'] = df['order'] * possible.reindex(df.index, fill_value=0)
Mrml91
  • 456
  • 3
  • 5