3

I want to merge two dataframes by the index columns. My code is:

import pandas as pd
import numpy as np
df1 = pd.DataFrame({'a': ['red', 'red', 'red']})
df2 = pd.DataFrame({'b': [1, 2, 2]})

df = df1.merge(df2, how='left', left_on=df1.index, right_on=df2.index)
print(df.head())

   key_0    a  b
0      0  red  1
1      1  red  2
2      2  red  2

The result has an unwanted column key_0. Question: How do I get rid of this column (without any drops after the merge)?

Carsten
  • 2,765
  • 1
  • 13
  • 28
  • Why merge? You need: `pd.concat([df1, df2], 1)` . – harvpan Aug 08 '19 at 19:45
  • I simplified the example. If `df2` would be longer than `df1`, I would get rows with `NaNs`. Hence, I wanted the left-merge. – Carsten Aug 08 '19 at 19:49
  • And I don't think it is a duplicate because I did not find anything about the `key_0` issue (also not mentioned in the referenced question). Hence, I thought this might be a useful reference if anybody has the same problem. – Carsten Aug 08 '19 at 19:52

1 Answers1

1

Instead of treating the index-columns as regular columns, I was able to get rid of the key_0 by using the keywords left_index and right_index:

df = df1.merge(df2, how='left', left_index=True, right_index=True)
print(df.head())

     a  b
0  red  1
1  red  2
2  red  2
Carsten
  • 2,765
  • 1
  • 13
  • 28