How to add a data frame column to another data frame so that they match values based on indexes, when they are of uneven length?

Question

I have a dataframe that looks like the following.

      Rk              Player Pos  Age   Tm   G  ...  AST  STL  BLK  TOV   PF   PTS
0      1        Álex Abrines  SG   25  OKC  31  ...  1.2  1.0  0.4  0.9  3.2  10.1
1      2          Quincy Acy  PF   28  PHO  10  ...  2.3  0.3  1.2  1.2  7.0   5.0
2      3        Jaylen Adams  PG   22  ATL  34  ...  5.5  1.2  0.4  2.4  3.8   9.1
3      4        Steven Adams   C   25  OKC  80  ...  1.7  1.6  1.0  1.8  2.8  14.9
4      5         Bam Adebayo   C   21  MIA  82  ...  3.5  1.3  1.2  2.3  3.8  13.7
..   ...                 ...  ..  ...  ...  ..  ...  ...  ...  ...  ...  ...   ...
619  478      Brodric Thomas  SG   24  TOT  32  ...  2.7  1.4  0.9  2.0  3.6  11.3
632  485      Killian Tillie   C   22  MEM  18  ...  1.6  1.0  1.6  0.4  4.0  11.3
633  486  Xavier Tillman Sr.  PF   22  MEM  59  ...  2.5  1.5  1.1  1.5  3.8  12.9
635  488          Obi Toppin  PF   22  NYK  62  ...  1.5  0.9  0.8  1.2  2.9  13.3
637  490        Axel Toupane  SF   28  MIL   8  ...  2.4  1.2  1.8  0.0  3.5   8.3

I also have another data frame that looks like this:

                Player Average Salary
0         James Harden    $42,782,880
1            John Wall    $42,782,880
2    Russell Westbrook    $41,358,814
3         Kevin Durant    $41,063,925
4        Stephen Curry    $40,231,758
..                 ...            ...
426      Dalano Banton     $1,244,388
427         Luka Garza     $1,244,388
428       Jared Butler     $1,244,388
429        Ayo Dosunmu     $1,244,388
430      Austin Reaves     $1,244,388

I'm essentially trying to add the salaries to the main dataframe based on the name of the player. The data frames however are of uneven length which doesn't let me add a dictionary to the data frame. Any solutions?

Answer 1

You can perform the equivalent of a database join operation via pd.merge to combine relevant columns from the two tables.

Thus

pd.merge(df1, df2, how="inner", on="Player")

or similar should provide the Average Salary column from the second table in a new table that includes all columns of the first. You can then subset columns as necessary.

Additionally, you might want to use a left join instead, to retain all the data instead of subsetting the rows to only those players for whom you have salary data.

The different types of joins are summarised as follows (source):

(INNER) JOIN: Returns records that have matching values in both tables LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table