How to dynamically select columns in pandas based on data in another column-CodePudding

Let's say I have the following dataframes

df1

date_time | value1 | column_value
12-Mar-22   17345      17200CE
13-Mar-22   17400      17200PE
....

df2

date_time | value1 | 17200CE | 17200PE | 17300CE | .......
12-Mar-22   17345    23.3      21.2      24.5
13-Mar-22   17345    24.3      22.2      22.5

Now I want add a column in df1 which fetches the value from the column of df2 which corresponds to value in column_value column in df1 and on the same date_time.

So finally it would look like

date_time | value1 | column_value | mapped_value_result
12-Mar-22   17345    17200CE           23.3
13-Mar-22   17345    17200PE           22.2

CodePudding user response：

One way to achieve this result is to merge df1 and df2 on date_time and then use df.values:

md = df2.merge(df1, on='date_time')
df1['mapped_value_result'] = md.values[md.index, md.columns.get_indexer(md.column_values)]

Output (for your sample data):

   date_time  value1 column_values  mapped_value_result
0  12-Mar-22   17345       17200CE                 23.3
1  13-Mar-22   17400       17200PE                 22.2

Another alternative (also using a merge), is to use apply to select a value from a column_values column in that merged dataframe:

md = df2.merge(df1, on='date_time')
df1['mapped_value_result'] = md.apply(lambda x:x[x['column_values']], axis=1)

The output is the same.