Merging dataframes on index with pandas
PythonPandasMergeDataframePython Problem Overview
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 7 N/A
2/1/2012 16 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?
Python Solutions
Solution 1 - Python
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.
Solution 2 - Python
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