How to efficiently get cell values from multiple DataFrames to insert into a master DataFrame

Your master_df has only 2 combinations of value for master_df.col1 and master_df.col3. Therefore, a simple .lookup and np.where will yield your desired output

df1_val = df1.lookup(master_df.col2, master_df.col4)
df2_val = df2.lookup(master_df.col2, master_df.col4)
master_df['col5'] = np.where(master_df.col1.eq('M') & master_df.col3.eq('X'), df1_val, df2_val)

Out[595]:
  col1  col2 col3  col4    col5
0  M    0     X    2021  0.6320
1  F    1     Z    2022  0.2320
2  F    2     Z    2023  0.3700
3  M    3     X    2024  0.5005

---

Note: if master_df.col1 and master_df.col3 have more than 2 combinations of values, you just need np.select instead of np.where

Here is a solution without using a for loop, I wish it'll work for you

firs we'll make two filter for which dataframe to use

df1_filter = (master_df["col1"] == 'M') & (master_df["col3"] == 'X') 
df2_filter = (master_df["col1"] == 'F') & (master_df["col3"] == 'Z') 

second, for each dataframe, we'll use the appropriate filter to get the values of interest for df1

row1_index = master_df[df1_filter]["col2"]
col1_index = master_df[df1_filter]["col4"]
df1_values_of_interest = df1.iloc[row1_index][col1_index]

for df2

row2_index = master_df[df2_filter]["col2"]
col2_index = master_df[df2_filter]["col4"]
df2_values_of_interest = df2.iloc[row2_index][col2_index]

with this approache,the values of interest are going to be in the diagonal,so we'll try to get them (each one with it's appropriate index) and concatenate them

aa = pd.Series(np.diag(df1_values_of_interest), index=df1_values_of_interest.index)
bb = pd.Series(np.diag(df2_values_of_interest), index=df2_values_of_interest.index)
res = pd.concat([aa, bb])

finally, we'll add the result to the master df

master_df['col5'] = res

I hope the solution is clear,and it'll work for you.if you need more clarification don't hesitate to ask. good luck !