Multiply by a different number of columns with iloc in Pandas-CodePudding

I have the following dataframe:

df = pd.DataFrame({'date' : ['2020-6','2020-07','2020-8'], 'd3_real':[1.2,1.3,0.8], 'd7_real' : [1.5,1.8,1.2], 'd14_real':[1.9,2.1,1.5],'d30_real' : [2.1, 2.2, 1.8],
                   'd7_mul':[1.12,1.1,1.15],'d14_mul':[1.08, 1.1, 1.14],'d30_mul':[1.23,1.25,1.12]})

The dX_real refers to the actual values on day 3, day 7, and day 14... and the second one is each multiplier for that specific day.

I want to calculate those predictions in the following way. First, I take the target column (d3_real, d7_real...) and then I multiply it for each multiplier depending on the case. for example, to calculate the prediction from d3_real to d30, I would need to multiply it by the multipliers of D7, D14 and D30.

df['d30_from_d3'] = df.iloc[:,1] * df.iloc[:,5] * df.iloc[:,6] * df.iloc[:,7]
df['d30_from_d7'] = df.iloc[:,2] * df.iloc[:,6] * df.iloc[:,7]
df['d30_from_d14'] = df.iloc[:,3] * df.iloc[:,7]

Is there any way to automate this with a loop? I do not know how to multiply each dX_real column without using conditional for each case as the number of multiplications changes.

This is what I have tried that it is not working as expected, as it is only multiplying the first multiplier:

pos_reals = [1,2,3]
pos_mul = [5,6,7]
clases = ['d3', 'd7','d14']

for target in pos_reals:
  for clase in pos_mul:
    df[f'f{clases}_hm_p_d30']  = df.iloc[:,target]

However, from here, I do not know how to specific which values it needs to multiply based on d3, d7 and d14.

Thanks!

CodePudding user response：

bbb = [[1, 5, 6, 7], [2, 6, 7], [3, 7]]
ddd = ['d30_from_d3', 'd30_from_d7', 'd30_from_d14']
for i in range(0, len(ddd)):
    df[ddd[i]] = df.iloc[:, bbb[i][0]]
    for x in range(1, len(bbb[i])):
        df[ddd[i]] = df[ddd[i]] * df.iloc[:, bbb[i][x]]

Output

      date  d3_real  d7_real  d14_real  d30_real  d7_mul  d14_mul  d30_mul  \
0   2020-6      1.2      1.5       1.9       2.1    1.12     1.08     1.23   
1  2020-07      1.3      1.8       2.1       2.2    1.10     1.10     1.25   
2   2020-8      0.8      1.2       1.5       1.8    1.15     1.14     1.12   

   d30_from_d3  d30_from_d7  d30_from_d14  
0     1.785370      1.99260         2.337  
1     1.966250      2.47500         2.625  
2     1.174656      1.53216         1.680

Here the first loop selects the names of the new columns from the 'ddd' list and sets the first value in the new column. In the nested loop, the numbers of the desired columns are taken from the list 'bbb' and the values are multiplied. Check with your data or show the expected result with your example. You need to check for a match.