0

I have two dataframes with one matching column [ID].

DF 1

ID     VAR
1      442
1      429
1      58
2      928
2      8493
3      093
3      809
3      913
4      133
4      490

DF2

ID       CODE
1        10foo
2        20bar
3        30foo
4        40bar

I'm trying to merge these dataframes so that I get something like this:

DF 3

ID     VAR      CODE
1      442      10foo
1      429      10foo
1      58       10foo
2      928      20bar
2      8493     20bar
3      093      30foo
3      809      30foo
3      913      30foo
4      133      40bar
4      490      40bar

I've tried this with DF3 = DF1.merge(DF2, on='ID', how='inner', right_index=True) This does actually work but ends up duplicating loads of values, more than doubling the amount of rows for some reason. Really not sure why that is happening. I need the number of rows in DF3 to be the same as DF1

Thanks for the help.

dinn_
  • 93
  • 1
  • 10
  • First, pass `validate='m:1'` and tell us the result – ifly6 Sep 30 '21 at 13:49
  • Your sample is inconsistent with your code. Why `right_index=True` and not `left_index=True`. Your 2 dataframes seems to have the same format? And this case, you can't use `on='ID'` because `on` and `x_index` are mutually exclusive. – Corralien Sep 30 '21 at 13:55
  • This is a typical use-case for `pd.Series.map`. You can try `df1['CODE'] = df1['ID'].map(df2.set_index('ID')['CODE'])` – Ch3steR Sep 30 '21 at 13:55

2 Answers2

2

To keep the same number of rows as DF1, you're going to want a left merge:

df1.merge(df2, on='ID', how='left')
vtasca
  • 1,660
  • 11
  • 17
1

We can try this one :

>>> df = pd.merge(df1,
...               df2,
...               how='left',
...               left_on=['ID'],
...               right_on=['ID'])              
>>> df
   ID   VAR     CODE
0   1   442     10foo
1   1   429     10foo
2   1   58      10foo
3   2   928     20bar
4   2   8493    20bar
5   3   93      30foo
6   3   809     30foo
7   3   913     30foo
8   4   133     40bar
9   4   490     40bar
tlentali
  • 3,407
  • 2
  • 14
  • 21