I am trying to simply multiply columns in a single DF based on the column names starting with a string. If a column contains that "string" value, then the values in those columns are multiplied by a value in a different column (column = Avg_Adj_Prod). Here is an example of my data:
df:
site month Adj1 Adj2 Adj3 Adj4 Adj5 Avg_Adj_Prod
0 West1 8 45 52 36 21 19 0.95
1 West1 9 21 17 34 23 33 0.82
2 East3 8 17 16 53 21 19 1.45
3 East3 9 38 41 44 51 62 1.05
Final df:
site month Adj1 Adj2 Adj3 Adj4 Adj5
0 West1 8 42.75 49.4 34.2 19.95 18.05
1 West1 9 17.22 13.94 33.18 18.86 27.06
2 East3 8 24.65 23.2 76.85 30.45 27.55
3 East3 9 39.9 43.05 46.2 53.55 65.1
Some code that would initiate the process:
df.loc[:,df.columns.str.contains('Adj')] = ?
Use filter to get the columns starting in "Avg", then mul and update for in place modification (no output):
df.update(df.filter(regex='^Adj').mul(df['Avg_Adj_Prod'], axis=0))
modified df:
site month Adj1 Adj2 Adj3 Adj4 Adj5 Avg_Adj_Prod
0 West1 8 42.75 49.40 34.20 19.95 18.05 0.95
1 West1 9 17.22 13.94 27.88 18.86 27.06 0.82
2 East3 8 24.65 23.20 76.85 30.45 27.55 1.45
3 East3 9 39.90 43.05 46.20 53.55 65.10 1.05
If you prefer indexing:
cols = df.filter(regex='^Adj').columns
df[cols] = df[cols].mul(df['Avg_Adj_Prod'], axis=0)