My df, call it table A:
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
My goal is to get to here, call this table B:
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
The function I'm using is (got some great help here):
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]
elif not(x is None) and col1[index] == 10:
y = col2[index]
l.append(((float(y)/x)-1)*100)
x = None
return l
The logic behind this function is: I want to iterate through each row in every column with a _Q suffix, starting with A_Q and do the following:
if row value = '1', grab the corresponding value in col 'A'
assign that value to a variable, call it x
keep looping down the col A_Q
if row value is either 1,2,3,4,5,6,7,8 or 9, ignore
if the value is 10, then get the corresponding value in col 'A' and assign that to variable y
calculate % change, call it chg, between y and x: (y/x)-1)*100
keep going down the column with steps 1-7 above until the end
Edit: this is the part that differentiates this question from my previous question:
Now for the final steps, which is to run the function across all columns and append the results of the func above to my df in a new col, I thought of doing something like this:
q = [col for col in df.columns if '_Q' in col]
for col in q:
df["{}_S".format(col)] = df.apply(func(df[col], df[col[:len(col)-2]]))
but the error message I get is: "ValueError: no results"
How to troubleshoot/fix this? Maybe an issue is that the function returns a list which then cannot get passed to the new column? Thanks for your help!