0

I have 2 pandas DataFrames:

df1:

c1 c2 x
1 1 1
2 1 4
1 2 1
2 2 4

and df2:

c1 c2 c3
1 1 1
1 2 1
2 2 2

and I want to append the x row to df2 like:

c1 c2 c3 x
1 1 1 1
1 2 1 1
2 2 2 4

I tried to use join, but because I don't have a unique index, it didn't work:

df1.set_index(['c1', 'c2'], inplace=True)
df2.set_index(['c1', 'c2'], inplace=True,drop=False)
df2=df2.join(df1)

df2:

c1 c2 c3 x
1 1 1 Nan
1 2 1 Nan
2 2 2 Nan
Peter Leimbigler
  • 10,775
  • 1
  • 23
  • 37
Avichai Edri
  • 1
  • 1

1 Answers1

0

Perhaps the simplest way to do what your question asks is this:

df2 = pd.merge(df2, df1, on=['c1', 'c2'])

We have done an 'inner' join (the default for pandas.merge()) on columns c1 and c2 of dataframes df1 and df2.

Alternatively, this is a way to get the specified result using DataFrame.join() in an approach similar to the one you tried according to your question:

df2 = df2.set_index(['c1', 'c2']).join(df1.set_index(['c1', 'c2'])).reset_index()

Result:

   c1  c2  c3  x
0   1   1   1  1
1   1   2   1  1
2   2   2   2  4

Note that inplace has been removed as its use is generally discouraged, and we now go ahead and allow c1 and c2 columns of df2 to be dropped by set_index() and use reset_index() later to restore them.

constantstranger
  • 9,176
  • 2
  • 5
  • 19