0

I have 2 dataframes like so:

df1

Name Animal
John Dog
John Cat
John Horse
Mary Dog
Mary Cat
Mary Horse

df2

Name Color
John Blue
Mary Red

I would like to merge them such that:

df3

Name Animal Color
John Dog Blue
John Cat Blue
John Horse Blue
Mary Dog Red
Mary Cat Red
Mary Horse Red

What would be the clearest way to go about this? I've tried multiple permutations of concat(), append(), merge(), and join() functions to no avail. I'm sure it has to be something simple, but most of the literature around this focuses on subsetting and eliminating duplicates, but not adding them.

JNevill
  • 46,980
  • 4
  • 38
  • 63
NigelThornberry7
  • 9
  • 3
  • `df3 = df1.merge(df2, on='Name')` I think would get you 95% of the way of the there (just an extra column you'll have to drop). – JNevill Sep 07 '22 at 20:23

1 Answers1

1
df['Color'] = df['Name'].map(df2.set_index(['Name'])['Color'])

    Name    Animal  Color
0   John    Dog     Blue
1   John    Cat     Blue
2   John    Horse   Blue
3   Mary    Dog     Red
4   Mary    Cat     Red
5   Mary    Horse   Red
Naveed
  • 11,495
  • 2
  • 14
  • 21