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?
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.
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
Source: Stackoverflow.com