Need to iterate over row to check conditions and retrieve values from different columns if the conditions are met

Question

I have a daily price data for a stock. Pasting last 31 rows of the data as an example dataset as below:

       Date         RSI         Smooth          max         min
110  2019-02-13   38.506874  224.006543         NaN         NaN
111  2019-02-14   39.567068  227.309923         NaN         NaN
112  2019-02-15   43.774479  229.830776         NaN         NaN
113  2019-02-18   43.651440  231.690179         NaN         NaN
114  2019-02-19   43.467237  232.701976         NaN         NaN
115  2019-02-20   44.370123  233.526131         NaN         NaN
116  2019-02-21   45.605073  233.834988  233.834988         NaN
117  2019-02-22   46.837518  232.335179         NaN         NaN
118  2019-02-25   42.087860  229.570711         NaN         NaN
119  2019-02-26   39.008014  226.379526         NaN         NaN
120  2019-02-27   39.542339  225.607475         NaN  225.607475
121  2019-02-28   39.051104  228.305615         NaN         NaN
122  2019-03-01   48.191687  232.544289         NaN         NaN
123  2019-03-05   51.909527  237.063534         NaN         NaN
124  2019-03-06   52.988668  240.243201         NaN         NaN
125  2019-03-07   54.205990  242.265173         NaN         NaN
126  2019-03-08   54.967076  243.912033         NaN         NaN
127  2019-03-11   58.080738  244.432163  244.432163         NaN
128  2019-03-12   55.587328  243.573710         NaN         NaN
129  2019-03-13   51.714123  241.191933         NaN         NaN
130  2019-03-14   48.948075  238.470485         NaN         NaN
131  2019-03-15   46.615111  236.144640         NaN         NaN
132  2019-03-18   48.219815  233.588265         NaN         NaN
133  2019-03-19   41.866898  230.271903         NaN  230.271903
134  2019-03-20   34.818844  239.457110         NaN         NaN
135  2019-03-22   42.167870  246.824173         NaN         NaN
136  2019-03-25   60.228588  255.294124         NaN         NaN
137  2019-03-26   66.896640  267.069173         NaN         NaN
138  2019-03-27   68.823285  278.222343         NaN         NaN
139  2019-03-28   63.654023  289.042091  289.042091         NaN

I am trying to develop a logic of code which as below: if max > 0, then search for the previous non-zero max value which and assign it to max2. Also, assign the corresponding RSI of previous non-zero max as RSI2.

Desired output: For line 139 in the data set, max2 will be 244.432163 and RSI2 will be 58.080738 For line 138 in the data set, max2 will be 0 and RSI 2 will be 0 and so on...

I tried different approached but was unsuccessful at getting any outputs so I do not have a sample code to paste.

I also tried using if loops but I am unable to make it work. I am very new at programming.

How close does: `df['max2'] = df['max'].dropna().shift(1).fillna(0)` get you? — Jon Clements, Apr 15 '19 at 16:03
Hi Jon Clements - It does get me the right max2 values but not the corresponding RSI value for RSI2. — Chirayu05, Apr 15 '19 at 16:09
The challenge with RSI column is that it has all the values irrespective of the fact whether max is zero or not. In that case, how will your approach work to compress the RSI column and then shift it by 1? — Chirayu05, Apr 15 '19 at 16:12
Ummm... Possibly `df[['max2', 'RSI2']] = df[['max', 'RSI']].dropna(subset=['max']).shift(1, fill_value=0)` probably gets you a bit closer.... — Jon Clements, Apr 15 '19 at 16:15

MyNameIsCaleb · Answer 1 · 2019-04-15T16:21:52.750

2

First you will need to iterate the dataframe.

Then you will need to store the previous values that you will need to save on the next hit. Since you are always going back to the previous max, you can reuse that as you loop through.

Something like this (did not test, just for an idea):

last_max = 0   
last_rsi = 0
for index, row in df.iterrows():
    if row['max']:
        row['max2'] = last_max
        row['rsi2'] = last_rsi
        last_max = row['max']   # store this max/rsi for next time
        last_rsi = row['rsi']

edited Apr 15 '19 at 16:21

answered Apr 15 '19 at 15:58

MyNameIsCaleb

4,409
1
13
31

Hi... What if these columns are a part of a dataframe df? How will the code change? Sorry for asking really silly questions here. I got an error as below: raceback (most recent call last): File "C:\NonWork\Personal\Stocks\Testing\Noise Filtering\Test\Test_code.py", line 100, in if row['max']: TypeError: tuple indices must be integers or slices, not str – Chirayu05 Apr 15 '19 at 16:02
Fixed, iterrows returns a tuple of index, data so changed the for loop to reflect. https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.iterrows.html – MyNameIsCaleb Apr 15 '19 at 16:22

score 0 · Accepted Answer · answered Apr 15 '19 at 16:21

0

The right answer is to add a line of code as below:

df[['max2', 'RSI2']] = df[['max', 'RSI']].dropna(subset=['max']).shift(1).fillna(0)

answered Apr 15 '19 at 16:21

Chirayu05

129
7

Need to iterate over row to check conditions and retrieve values from different columns if the conditions are met

2 Answers2