1

I have a dataframe called 'data':

USER   FIELD1
Jack        1
Jill        2
Kane        3

and a separate dataframe called 'ids' that serves to be used as a conversion table:

ID            ID_NEW
Jack        Jack_NEW
Jill        Jill_NEW
Tyler      TYLER_NEW

How can I iterate through 'data' to replace the USER value if it is found in the 'ids' dataframe while keeping values that are not found? To end up with something like:

USER           FIELD1
Jack_NEW            1
Jill_NEW            2
Kane                3
stackoverflow
  • 139
  • 8

1 Answers1

0

You can do this in a couple steps. First, you join the two DataFrames together on "USER = ID" using pd.merge. This will leave nulls in "ID_NEW" where the dfs don't join, so then you bring in the "USER" values for those using Series.combine_first. Finally, you reassign column names and filter down to just the columns you want to keep.

merged = pd.merge(data, ids, how="left", left_on="USER", right_on="ID")
merged["USER"] = merged["ID_NEW"].combine_first(merged["USER"])
data = merged[["USER", "FIELD1"]]

If you have a lot of columns in data, you could do data = merged[data.columns] at the end.

Steven Rouk
  • 893
  • 7
  • 9
  • is there a way to do this without merging? maybe through an iterative fashion? my actual 'data' df has dozens of columns so it would be messy on the last step – stackoverflow Aug 26 '20 at 19:26
  • Could you do `data = data[data.columns]` to easily subset? Merging is probably going to be the best practice solution here. I'm sure there are other ways to do it, but they'd be a little more convoluted. – Steven Rouk Aug 26 '20 at 19:34
  • not sure how to incorporate that. data=data[data.columns] seems like it won't do anything since you'd just be setting it equal to whatever columns are already existing – stackoverflow Aug 26 '20 at 20:35
  • 1
    rewrote your solution a bit: `merged = pd.merge(data, ids, how="left", left_on="USER", right_on="ID") merged["USER"] = merged["ID_NEW"].combine_first(merged["USER"]) merged = merged.drop(['ID', 'ID_NEW'], axis=1)` thanks again :) – stackoverflow Aug 26 '20 at 20:40