Merge by looking up multiple column values

I have the following 2 tables in the form of a dataframe in pandas;

this is a list of all possible combinations:

Table A:     0    1    2   +---+----+----+  0| A |None|None|   +---+----+----+  1| B |None|None|   +---+----+----+  2|...|    |    |   +---+----+----+  3| A | C  | D  |   +---+----+----+  4| B | C  | D  |   +---+----+----+  

These are the associated values of the variables:

Table B:    0   1   +---+---+  0| A | 5 |   +---+---+  1| B | 2 |   +---+---+  2| C | 7 |   +---+---+  3| D | 4 |   +---+---+  

What I need is something like this:

   0    1    2   3   +---+----+----+---+  0| A |None|None| 5 |   +---+----+----+---+  1| B |None|None| 2 |   +---+----+----+---+  2|...|    |    |   |   +---+----+----+---+  3| A | C  | D  | 7 |   +---+----+----+---+  4| B | C  | D  | 7 |   +---+----+----+---+  

Where column 3 is found by looking up each associated value from Table A, columns 0,1 and 2 in Table B, column 0 and returning the largest of those values.

for example: row 3 shows a combination of A,C and D. Therefore column 3 looks up A in Table B, a value of 5, then it looks up Cin Table B, a value of 7. Lastly it looks up D in Table B, a value of 4. Of those 3 numbers 7 is the largest so it returns that.

So far I have tried pandas.merge with no luck

https://stackoverflow.com/questions/66809518/merge-by-looking-up-multiple-column-values March 26, 2021 at 08:42AM