Dynamic row updates in dataframe

user_11659230 0
Viewed 40

I created a classification model that predicts whether an asset is likely to have positive return for the next N days, or negative return. The model's value is +1 for positive turn, and -1 for negative return. Here is a sample output,

df=pd.DataFrame([[1,0.6],[1,0.7],[1,0.8],[-1,0.3],[-1,0.4],[-1,0.9]],columns=['prediction','confidence'])
df['date']= pd.date_range('2000-1-1', periods=6, freq='D')
df = df.set_index(['date'])

prediction  confidence
date        
2000-01-01  1   0.6
2000-01-02  1   0.7
2000-01-03  1   0.8
2000-01-04  -1  0.3
2000-01-05  -1  0.4
2000-01-06  -1  0.9

The model also specifies the confidence ranging between 0 to 1. I want to do a test where I can ignore the prediction if the model's confidence is <0.5, and instead setting the prediction same as previous day's. The change should have a follow-on impact on the rest of the dataframe, and not just for 1 day.

What I am stuck at is how to modify my dataframe (or create a new one) so I get the desired. output. Using the above example, the desired output is

prediction  confidence
date        
2000-01-01  1   0.6
2000-01-02  1   0.7
2000-01-03  1   0.8
2000-01-04  1   0.3
2000-01-05  1   0.4
2000-01-06  -1  0.9

Thanks

Edit:

Just as a further clarification, when the prediction changed in the "desired_df" on the '2000-01-04', the value on the '2000-01-05' is also affected, because now the "previous row" value is now +1, not -1. This pattern continues until model produces a prediction with sign -1 that has confidence level > 0.5.

Edit#2:

This requirement is common I think in trading/investing, where you want to keep your existing position (+1 or -1), until you can be sure something has changed (in my case confidence > 0.5), to avoid excessively buying and selling due to noise in the model.

pandas dataframe python
2 Answers

Let's try with Series.mask to replace low confidence values with NaN then Series.ffill to bring values forward:

df['prediction'] = (
    df['prediction'].mask(df['confidence'].lt(0.5)).ffill(downcast='infer')
)

df:

            prediction  confidence
date                              
2000-01-01           1         0.6
2000-01-02           1         0.7
2000-01-03           1         0.8
2000-01-04           1         0.3
2000-01-05           1         0.4
2000-01-06          -1         0.9

Mask out low confidence values:

df['prediction'].mask(df['confidence'].lt(0.5))

date
2000-01-01    1.0
2000-01-02    1.0
2000-01-03    1.0
2000-01-04    NaN
2000-01-05    NaN
2000-01-06   -1.0
Name: prediction, dtype: float64

Forward Fill values to fill in NaN values. downcast to get back to int type (if necessary)

df['prediction'].mask(df['confidence'].lt(0.5)).ffill(downcast='infer')

date
2000-01-01    1
2000-01-02    1
2000-01-03    1
2000-01-04    1
2000-01-05    1
2000-01-06   -1
Name: prediction, dtype: int64
user_154978880

Try adding this line of code:

df['prediction'] = [x if y >= 0.5 else 1 for x, y in zip(df['prediction'], df['confidence'])]
user_87083640
Related