0

I am trying to add a new column to a dataframe. First I want to check if the id of each row exists in another dataframe. If it does then I want to populate my row entry with the value from the other dataframe. As an example my dataframe that I want to look up looks something like this:

    id  replays
0   2   1
1   5   1
2   6   2
3   8   3
4   12  1

The dataframe for which I want to create a new column initially looks like this, with just id's:

    id
0   2
1   5
2   6

Ultimately this dataframe should be populated with a new column like this, where the replay entries are those that match the id from the first dataframe:

    id  replays
0   2   1
1   5   1
2   6   2

I am not sure how to achieve this. I have tried using lambda apply, but cannot manage to get the desired result.

ojp
  • 973
  • 1
  • 11
  • 26
  • Does this answer your question? [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) – ifly6 Dec 17 '20 at 18:35
  • This is asking how to do a left merge. `left.merge(right, on='id', how='left')` – ifly6 Dec 17 '20 at 18:36
  • Thanks for the comment. Not exactly. The first dataset that I want to look up actually had many columns which I don't want to merge. Merging all these columns to my second dataset and then dropping them seems inefficient. – ojp Dec 17 '20 at 18:44
  • 1
    Then just subset your columns. Note also that the accepted answer uses an inner merge (pandas default merge) which drops entries on the right data frame if there are no matching keys. – ifly6 Dec 18 '20 at 02:12

1 Answers1

1

You want a merge :

import pandas as pd
df_vals = pd.DataFrame({'id':[2, 5, 6, 8, 12], 
                        'replays':[1, 1, 2, 3, 1]})

df = pd.DataFrame({'id':[2, 5, 6]})

cols = ["id", "replays"]

df.merge(df_vals[cols], on ="id")

outputs :

   id  replays
0   2        1
1   5        1
2   6        2

You can select the columns beforehand if you don't need all of them. If the columns to join on have different names, you'll have to specify that as well with left_on and right_on.

Nathan Furnal
  • 2,236
  • 3
  • 12
  • 25