CODEWITHSUNDEEP
pythonarraysdataframemappingseries
Noki
Noki

Reputation: 1

How to copy values between 2 dataframes based on similar/matching items in multiple columns?

I have 2 dataframes (df1 and df2) and they look like this:

df1
index   gameID  Team        A      B      C
0       0001    Lakers      10     100    90
1       0001    Clippers    20     105    91
2       0002    Celtics     30     110    92
3       0002    Warriors    40     115    93
4       0003    Suns        10     100    94
5       0003    Jazz        20     105    95
6       0004    Heat        30     110    96
7       0004    Magic       40     115    97

df2
index   gameID  Team        Player      D
0       0001    Lakers      Lebron      30.5
1       0001    Clippers    Harden      29.9
2       0002    Celtics     Tatum       31.2 
3       0002    Warriors    Curry       29.8    
4       0003    Suns        Durant      40.6    
5       0003    Jazz        Clarkson    21.5    
6       0004    Heat        Butler      25.5    
7       0004    Magic       Banchero    27.8
8       0005    Mavs        Doncic      39.9
9       0005    Raptors     Quickley    19.6

I want to be able to copy columns 'A' 'B' and 'C' to df2 such that it would only copy these columns if they have matching gameID AND Team columns so the result would be:


df2
index   gameID  Team        Player      D        A     B     C
0       0001    Lakers      Lebron      30.5     10    100   90
1       0001    Clippers    Harden      29.9     20    105   91
2       0002    Celtics     Tatum       31.2     30    110   92
3       0002    Warriors    Curry       29.8     40    115   93
4       0003    Suns        Durant      40.6     10    100   94
5       0003    Jazz        Clarkson    21.5     20    105   95
6       0004    Heat        Butler      25.5     30    110   96
7       0004    Magic       Banchero    27.8     40    115   97
8       0005    Mavs        Doncic      39.9     NaN   NaN   NaN
9       0005    Raptors     Quickley    19.6     NaN   NaN   NaN

I've already tried using dict with map but dict makes use of key-value pairs and the condition I want to work with uses more than 1 column

Upvotes: 0

Views: 48

Answers (2)

sainandan naik
sainandan naik

Reputation: 21

You can use the pandas.merge() function merges two DataFrames based on common columns. Here's how you can do it:

Identify the common columns that exist in both DataFrames. In this case, it's gameID and Team.

`

Merging the DataFrames based on 'gameID' and 'Team'

df_merged = pd.merge(df2, df, on=['gameID', 'Team'], how='left')

Displaying the result

print(df_merged)`

Upvotes: 0

phantom
phantom

Reputation: 396

  • pd.merge() is used to merge df2 with df1 on the common columns gameID and Team.
  • We use how='left' to ensure that all rows from df2 are preserved, and the columns A, B, and C are added only when there is a match. If there is no match, the values will be NaN.
  • [['gameID', 'Team', 'A', 'B', 'C']] is used to select only the relevant columns from df1.
import pandas as pd

df1 = pd.DataFrame({
    'gameID': ['0001', '0001', '0002', '0002', '0003', '0003', '0004', '0004'],
    'Team': ['Lakers', 'Clippers', 'Celtics', 'Warriors', 'Suns', 'Jazz', 'Heat', 'Magic'],
    'A': [10, 20, 30, 40, 10, 20, 30, 40],
    'B': [100, 105, 110, 115, 100, 105, 110, 115],
    'C': [90, 91, 92, 93, 94, 95, 96, 97]
})

df2 = pd.DataFrame({
    'gameID': ['0001', '0001', '0002', '0002', '0003', '0003', '0004', '0004', '0005', '0005'],
    'Team': ['Lakers', 'Clippers', 'Celtics', 'Warriors', 'Suns', 'Jazz', 'Heat', 'Magic', 'Mavs', 'Raptors'],
    'Player': ['Lebron', 'Harden', 'Tatum', 'Curry', 'Durant', 'Clarkson', 'Butler', 'Banchero', 'Doncic', 'Quickley'],
    'D': [30.5, 29.9, 31.2, 29.8, 40.6, 21.5, 25.5, 27.8, 39.9, 19.6]
})

df_merged = pd.merge(df2, df1[['gameID', 'Team', 'A', 'B', 'C']], on=['gameID', 'Team'], how='left')

print(df_merged)

Upvotes: 0

Related Questions