Merging Dataframes On Index With Pandas

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 1

Solution 1:

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/201212152/1/201214NaN3/1/2012NaN21
B 1/1/201215242/1/201289
C 1/1/201217NaN2/1/20129NaN
D 1/1/2012NaN72/1/2012NaN16

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.

Solution 2:

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:

V1V2A2012-01-01  12.015.02012-02-01  14.0NaN2012-03-01   NaN21.0B2012-01-01  15.024.02012-02-01   8.09.0C2012-01-01  17.0NaN2012-02-01   9.0NaND2012-01-01   NaN7.02012-02-01   NaN16.0