Mapping column from one pandas DataFrame to another

user_3451339 0
Viewed 70

I have a dataframe like this:

df_1 = pd.DataFrame({'players.name': ['John', 'Will' ,'John', 'Jim', 'Tim', 'John', 'Will', 'Tim'],
                     'players.diff': [0, 0, 0, 0, 0, 0, 0, 0],
                            'count': [3, 2, 3, 1, 2, 3, 2, 2]})

'count' values are constant.

And I have a different shape dataframe with players ordered differently, like so:

df_2 = pd.DataFrame({'players.name': ['Will', 'John' ,'Jim'],
                     'players.diff': [0, 0, 0]})

How do I map from df_1 values and populate a 'count' value on df_2, ending up with:

  players.name  players.diff  counts
0         Will             0       2
1         John             0       3
2          Jim             0       1
pandas python
3 Answers

Since you're just trying to create a column of counts, it'd be more meaningful to map your player names to counts:

df_2['counts'] = df_2['players.name'].map(
    df_1.groupby('players.name')['count'].first())

df_2 

  players.name  players.diff  counts
0         Will             0       2
1         John             0       3
2          Jim             0       1
user_49090870

Your sample df_1 has duplicated players.name with same count, so you need left-merge and drop_duplicates

new_df_2 = df_2.merge(df_1[['players.name','count']], on='players.name', how='left').drop_duplicates()

Out[89]:
  players.name  players.diff  count
0         Will             0      2
2         John             0      3
5          Jim             0      1
user_101892140

This could work:

pd.merge(df_1, df_2, on=["players.name", "players.diff"]).drop_duplicates()
user_81886660
Related