Loop logic to calculate % change

Question

My dataframe:

A   B   C    A_Q  B_Q     C_Q
27  40  41    2    1      etc
28  39  40    1    5    
30  28  29    3    6    
28  27  28    4    1    
15  10  11    5    4    
17  13  14    1    5    
16  60  17    8    10   
14  21  18    9    1    
20  34  23    10   2    
21  45  34    7    4    

I want to iterate through each row in every column with a _Q suffix, starting with A_Q and do the following:

1. if row value = '1', grab the corresponding value in col 'A'
2. assign that value to a variable, call it x
3. keep looping down the col A_Q
4. if row value is either 1,2,3,4,5,6,7,8 or 9, ignore
5. if the value is 10, then get the corresponding value in col 'A' and assign that to variable y
6. calculate % change, call it chg, between y and x: (y/x)-1)*100
7. append chg to dataframe
8. keep going down the column with steps 1-7 above until the end

Then do the same for the other columns B_Q, C_Q etc

So for example, in the above, the first "1" that appears corresponds to 28 in col A. So x = 28. Then keep iterating, ignoring values 1 through 9, until you get a 10, which corresponds to 20 in col A. Calculate % change = ((20/27)-1)*100 = -25.9% and append that to df in a newly created col A_S. Then resume from that point on with same steps until reach end of the file. And finally, do the same for the rest of the columns.

So then the df would look like:

A   B   C    A_Q  B_Q     C_Q    A_S        B_S         C_S etc
27  40  41    2    1      etc
28  39  40    1    5    
30  28  29    3    6    
28  27  28    4    1    
15  10  11    5    4    
17  13  14    1    5    
16  60  17    8    10                        50
14  21  18    9    1    
20  34  23    10   2             -25.9
21  45  34    7    4    

I thought to create a function and then do something like df ['_S'] = df.apply ( function, axis =1) but am stuck on the implementation of the above steps 1-8. Thanks!

Answer 1

Do you need to append the results as a new column? You're going to end up with nearly empty columns with just one data value. Could you just append all of the results at the bottom of the '_Q' columns? Anyway here's my stab at the function to do all you asked:

def func(col1, col2):
    l = []
    x = None
    for index in range(0, len(col1)):
        if x is None and col1[index] == 1:
            x = col2[index]
            l.append(0)
        elif not(x is None) and col1[index] == 10:
            y = col2[index]
            l.append(((float(y)/x)-1)*100)
            x = None
        else:
            l.append(0)
    return l

You'd then pass this function A_Q as col1 and A as col2 and it should return what you want. For passing functions, assuming that every A, B, C column has an associated _Q column, you could do something like:

q = [col for col in df.columns if '_Q' in col]
for col in q:
    df[col[:len(col) - 2] + '_S] = func(df[col], df[col[:len(col) - 2]