102

I have two dataframes and each one has two index columns. I would like to merge them. For example, the first dataframe is the following:

 V1 A 1/1/2012 12 2/1/2012 14 B 1/1/2012 15 2/1/2012 8 C 1/1/2012 17 2/1/2012 9

The second dataframe is the following:

 V2 A 1/1/2012 15 3/1/2012 21 B 1/1/2012 24 2/1/2012 9 D 1/1/2012 7 2/1/2012 16

and as result I would like to get the following:

 V1 V2 A 1/1/2012 12 15 2/1/2012 14 N/A 3/1/2012 N/A 21 B 1/1/2012 15 24 2/1/2012 8 9 C 1/1/2012 17 N/A 2/1/2012 9 N/A D 1/1/2012 N/A 7 2/1/2012 N/A 16

I have tried a few versions using the pd.merge and .join methods, but nothing seems to work. Do you have any suggestions?

Improve this question
0

3 Answers 3

Reset to default
116

You should be able to use join, which joins on the index as default. Given your desired result, you must use outer as the join type.

>>> df1.join(df2, how='outer') V1 V2 A 1/1/2012 12 15 2/1/2012 14 NaN 3/1/2012 NaN 21 B 1/1/2012 15 24 2/1/2012 8 9 C 1/1/2012 17 NaN 2/1/2012 9 NaN D 1/1/2012 NaN 7 2/1/2012 NaN 16

Signature: _.join(other, on=None, how='left', lsuffix='', rsuffix='', sort=False) Docstring: Join columns with other DataFrame either on index or on a key column. Efficiently Join multiple DataFrame objects by index at once by passing a list.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

87

You can do this with merge:

df_merged = df1.merge(df2, how='outer', left_index=True, right_index=True)

The keyword argument how='outer' keeps all indices from both frames, filling in missing indices with NaN. The left_index and right_index keyword arguments have the merge be done on the indices. If you get all NaN in a column after doing a merge, another troubleshooting step is to verify that your indices have the same dtypes.

The merge code above produces the following output for me:

 V1 V2 A 2012-01-01 12.0 15.0 2012-02-01 14.0 NaN 2012-03-01 NaN 21.0 B 2012-01-01 15.0 24.0 2012-02-01 8.0 9.0 C 2012-01-01 17.0 NaN 2012-02-01 9.0 NaN D 2012-01-01 NaN 7.0 2012-02-01 NaN 16.0
Improve this answer

Comments

3

You can concatenate horizontally as well. Since concat matches on index and performs outer join by default, simply passing axis=1 argument to specify that the concatenation is horizontal suffices.

joined_df = pd.concat([df1, df2], axis=1)

An advantage of concat over merge and join1 is that you can pass a list of dataframes and concatenate many frames in one go with minimal fuss.

joined_df = pd.concat([df1, df2, df1, df2], axis=1)

1 It can be done with join too but if there are duplicate column names, they have to do be dealt with before the join call, whereas with concat, it doesn't matter.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.