Pandas merging DF on different columns-CodePudding

Texas Hold'em exercise in Python. I have two dataframes, the first one representing hole cards that each player has in hand, and the second one representing the whole deck of cards:

       HC1 HC2
Player
1       51  46
2       48  28
3       14   2
4       41  12
5        5  38
6       52  30
7       32  35
8       18  15
9       39  10

      Seed  Value      Prob HC1 HC2 Table
CardN
1        h      1  0.019231
2        h      2  0.019231
3        h      3  0.019231
4        h      4  0.019231
5        h      5  0.019231
6        h      6  0.019231
7        h      7  0.019231
8        h      8  0.019231
9        h      9  0.019231
10       h     10  0.019231
11       h     11  0.019231
12       h     12  0.019231
13       h     13  0.019231
14       d      1  0.019231
15       d      2  0.019231
16       d      3  0.019231
17       d      4  0.019231
18       d      5  0.019231
19       d      6  0.019231
20       d      7  0.019231
21       d      8  0.019231
22       d      9  0.019231
23       d     10  0.019231
24       d     11  0.019231
25       d     12  0.019231
26       d     13  0.019231
27       c      1  0.019231
28       c      2  0.019231
29       c      3  0.019231
30       c      4  0.019231
31       c      5  0.019231
32       c      6  0.019231
33       c      7  0.019231
34       c      8  0.019231
35       c      9  0.019231
36       c     10  0.019231
37       c     11  0.019231
38       c     12  0.019231
39       c     13  0.019231
40       s      1  0.019231
41       s      2  0.019231
42       s      3  0.019231
43       s      4  0.019231
44       s      5  0.019231
45       s      6  0.019231
46       s      7  0.019231
47       s      8  0.019231
48       s      9  0.019231
49       s     10  0.019231
50       s     11  0.019231
51       s     12  0.019231
52       s     13  0.019231

I need to put in the HC1 and HC2 columns of this last df the related player ID (which is in the first dataframe). HC1 and HC2 of the "hands" dataframe need to be correlated with CardN column of the deck dataframe.

Any solution? Thank you very much!

CodePudding user response：

I believe you can join the DF's using merge like so. You'd first do the first H, then the second:

Create table for player and HC1 (triple check on how to make a df from previous ones, I'm not 100 the syntax will work creating the HC1 and HC2 tables)

HC1 = 1stDF[['Player','HC1']]
HC1 = df.rename(columns={"Player": "PlayerHC1"})
HC2 = 1stDF[['Player','HC2']]
HC2 = df.rename(columns={"Player": "PlayerHC2"})

newDF = 2cndDF.merge(HC1, on='HC1', how='left')
newDF = 2cndDF.merge(HC2, on='HC2', how='left')

Good luck, this should get you what you want!

You should be left with: CardN - Seed - Value - Prob - PlayerHC1 - PlayerHC2

(If this is right, please accept answer, trying to get to 50 points! lol)