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

Question

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

The value1 values don't match. Is that a typo, or do you just want to ignore the value1 column of df1? — tdy
– tdy, Commented Oct 1, 2022 at 3:50

Nick · Accepted Answer · 2022-10-01 05:27:36Z

2

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.

edited Oct 1, 2022 at 5:27

answered Oct 1, 2022 at 3:41

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related