Populate whole column with result of formula - pandas

Question

I am struggling to repopulate a pandas df column with the output of a simple Boolean formula.

Here is a sample of my data:

      Close  RSI_14  MACD  upper_BBAND  lower_BBAND  middle_BBAND
33  1.26470     5.0   1.0     1.26601     1.26456           0.0
34  1.26495     6.0   1.0     1.26566     1.26468           0.0
35  1.26555     6.0   1.0     1.26573     1.26456           1.0
36  1.26680     7.0   1.0     1.26682     1.26386           1.0
37  1.26735     7.0   1.0     1.26781     1.26366           1.0
38  1.26725     7.0   1.0     1.26812     1.26428           1.0
39  1.26730     7.0   1.0     1.26798     1.26526           1.0
40  1.26725     7.0   1.0     1.26736     1.26656           1.0
41  1.26755     7.0   1.0     1.26745     1.26701           1.0
42  1.26745     7.0   1.0     1.26766     1.26718           1.0
43  1.26705     6.0   1.0     1.26784     1.26716           0.0
44  1.26685     6.0   1.0     1.26797     1.26695           0.0
45  1.26680     6.0   1.0     1.26797     1.26674           0.0
46  1.26765     7.0   1.0     1.26799     1.26665           1.0
47  1.26750     6.0   1.0     1.26803     1.26665           1.0
48  1.26685     6.0   1.0     1.26804     1.26658           0.0
49  1.26650     5.0   1.0     1.26807     1.26631           0.0

I would like extrapolate a binary signal from a formula. The signal should be stored in the upper_BBAND and lower_BBAND columns and based on the pseudo-code below:

pseudo code to populate upper_BBAND column:

For i in len(Close):
    if middle_BBAND[i] == 1 and Close[i] < upper_BBAND[i]:
        upper_BBAND[i] = 1
    elif middleBBAND[i] == 1 and Close[i] > upper_BBAND[i]:
           upper_BBAND[i] = 0

pseudo code to populate lower_BBAND column:

For i in len(Close):
    if middle_BBAND[i] == 0 and Close[i] > lower_BBAND[i]:
         lower_BBAND[i] == 0
    elif middle_BBAND[i] == 0 and Close[i] < lower_BBAND[i]:
         lower_BBAND[i] == 1

The desired output is something like that:

      Close  RSI_14  MACD  upper_BBAND  lower_BBAND  middle_BBAND
39  1.26730     7.0   1.0         1.0         0.0           1.0
40  1.26725     7.0   1.0         1.0         0.0           1.0
41  1.26755     7.0   1.0         0.0         0.0           1.0
42  1.26745     7.0   1.0         0.0         0.0           1.0
43  1.26705     6.0   1.0         0.0         0.0           0.0
44  1.26685     6.0   1.0         0.0         0.0           0.0
45  1.26680     6.0   1.0         0.0         1.0           0.0

I am struggling to make pandas recognise this pseudo for loop.. Any suggestions?

what do you mean recognize? What is going wrong and what is your real code? — WiseDev, Aug 13 '19 at 14:47
@anky_91 I had included an unnecessary number of lines in the sample data so I shortened it in the output, yet forgot to delete them in the sample data. I will edit it now. — Antonio Marchi, Aug 13 '19 at 15:04
@BlueRineS I mean that I get a syntax error and I don't understand why and how I can iterate correctly through the length of the column. — Antonio Marchi, Aug 13 '19 at 15:04
thats fine, however in the `upper_BBAND` column, index 42 should also have 1 in that case..no? — anky, Aug 13 '19 at 15:35
@anky_91 you are right, at index 42 there should be a 1 in upper_BBAND — Antonio Marchi, Aug 13 '19 at 16:13

score 1 · Answer 1 · answered Aug 13 '19 at 15:38

1

Another option would be to use DataFrame.where:

df['upper_BBAND'] = df['upper_BBAND'].where(
    ~((df['middle_BBAND'] == 1.0) & (df['Close'] < df['upper_BBAND'])), 1.0
).where(
    ~((df['middle_BBAND'] == 1.0) & (df['Close'] > df['upper_BBAND'])), 0.0
)

A bit harder to read but should be faster on big dataframes.

answered Aug 13 '19 at 15:38

Pierre V.

1,625
1
11
14

Thank you @Pierre! This works very well. Would you expand on why this may be faster on larger data frames over a function? – Antonio Marchi Aug 13 '19 at 16:32
The reason is that it is a vectorized operation, and numpy / pandas can optimize it. It is usually faster than a looping over each row (which is what is happening with the `df.apply` solution). Some additional info: https://maxpowerwastaken.github.io/blog/pandas-dont-apply-_-vectorize/ – Pierre V. Aug 13 '19 at 16:45

score 0 · Accepted Answer · answered Aug 13 '19 at 15:13

If you've just defined the columns within the dataframe, and haven't defined them as themselves, then Close doesn't exist as a variable; you have to call df['Close'] or df.Close. But you should use apply rather than doing a for-loop:

def set_upper(row):
    if row['middle_BBAND'] ==1:       
        if row['Close'] < row['upper_BBAND']:
            return 1
        if row['Close'] > row['upper_BBAND']:
            return 0
    return row['upper_BBAND']

df['upper_BBAND'] = df.apply(set_upper,axis=1)

This works like a charm! Thank you especially for the explanation, I did not know that in this case I need to use apply - I am very new to python programming. — Antonio Marchi, Aug 13 '19 at 16:31

Populate whole column with result of formula - pandas

2 Answers2