I can not figure out how to multiply a dataframe with the values selected as the row from another dataframe.
I have a number of factors that I observe for variables in a universe:
df_observations = pd.DataFrame([
['variable_1', 1, 1.1, 1.2],
['variable_2', 2, 2.1, 2.2],
['variable_3', 3, 3.1, 3.2]
],
columns=['observation',
'factor_1',
'factor_2',
'factor_3'])
df_observations.set_index(['observation'], inplace=True)
df_observations
For each of the factors, I want to multiply by a coefficient, conditional on a state of the system
df_factor_state_coeff = pd.DataFrame([
['state_1', 10, 11, 12],
['state_2', 20, 21, 22],
['state_3', 30, 31, 32],
['state_4', 40, 41, 42],
],
columns=['sys_state',
'factor_1',
'factor_2',
'factor_3'])
df_factor_state_coeff.set_index(['sys_state'], inplace=True)
df_factor_state_coeff
I can only make a very "procedural" approach work:
for c in df_factor_state_coeff.loc['state_2'].index:
df_observations[c] = df_observations[c] * df_factor_state_coeff.loc['state_2'][c]
df_observations
I feel like I am missing something here and should be able to do this as a multiplication of df_observations.multiply(df_factor_state_coeff.loc['state_2'])
without having to loop but i cant figure that out. Would really appreciate a smarter way to do this.
thanks