CODEWITHSUNDEEP
pythonpandas
elmoslide
elmoslide

Reputation: 23

How to get the union of two MultiIndex DataFrames?

How do I merge two MultiIndexed DataFrames?

For example, let's say I have:

index1 = pd.MultiIndex.from_tuples([('2010-01-01', 'Jim'),
                                    ('2010-01-01', 'Mike'), 
                                    ('2010-01-02', 'Sam')])
index2 = pd.MultiIndex.from_tuples([('2010-01-02', 'Jim'), 
                                    ('2010-01-02', 'Sam'), 
                                    ('2010-01-03', 'Joe')])
df1 = pd.DataFrame([[7,0,7],[4,3,2],[6,2,6]], 
                   index=index1, columns=['a', 'b', 'c'])
df2 = pd.DataFrame([[4,2,0],[8,8,4],[5,5,3]], 
                   index=index2, columns=['a', 'b', 'c'])

This results in:

>> df1
                  a  b  c
2010-01-01  Jim   7  0  7
            Mike  4  3  2
2010-01-02  Sam   6  2  6

>> df2
                  a  b  c
2010-01-02  Jim   4  2  0
            Sam   8  8  4
2010-01-03  Joe   5  5  3

I want to merge df1 and df2 to produce:

>> df3
                    a1  b1  c1  a2  b2  c2
2010-01-01  Jim     7   0   7   NaN NaN NaN
            Mike    4   3   2   NaN NaN NaN
2010-01-02  Jim     NaN NaN NaN 4   2   0
            Sam     6   2   6   8   8   4
2010-01-03  Joe     NaN NaN NaN 5   5   3

I'm struggling to find a good way to do this. Any suggestions?

Upvotes: 2

Views: 390

Answers (1)

Kartik
Kartik

Reputation: 8703

Try:

df3 = df1.join(df2, how='outer', lsuffix='1', rsuffix='2')

Or

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

Upvotes: 1

Related Questions