How to merge on partial multiindex in `pandas`

user_1600108 0
Viewed 490

I have two DataFrames. The df1 has multi-index, the df2 has a standard index. How to merge them with repeating the values from df2 on every match of df2.index and df1.get.

Example

import pandas as pd
import numpy as np
idx1 = pd.MultiIndex.from_product([['bar', 'baz', 'foo'],['one','two']])
idx2 = ['bar', 'baz']
df1 = pd.DataFrame(np.random.randn(6, 2), index=idx1, columns=['A', 'B'])
df2 = pd.DataFrame(np.random.randn(2, 1), index=idx2, columns=['C'])

If df1 is

                A         B
bar one  0.690827 -0.627957
    two -0.080936 -1.330712
baz one  1.395178 -0.099748
    two -0.116789  0.723990
foo one  0.313067  0.853808
    two  0.409727 -0.529002

and df2 is

            C
bar -0.773924
baz  0.099662

How to get merge like?

                A         B         C
bar one  0.690827 -0.627957 -0.773924
    two -0.080936 -1.330712 -0.773924
baz one  1.395178 -0.099748  0.099662
    two -0.116789  0.723990  0.099662
foo one  0.313067  0.853808  NaN
    two  0.409727 -0.529002  NaN
pandas dataframe python
4 Answers

you can give names to index and use that in merge, without reindexing or resetting index like below

df1.index.set_names(["id_1", "id_2"], inplace=True)
df1.merge(df2, left_on="id_1", right_index=True, how="left")

Result

                   A          B          C
id_1 id_2                                 
bar  one    0.690827  -0.627957  -0.773924
     two   -0.080936  -1.330712  -0.773924
baz  one    1.395178  -0.099748   0.099662
     two   -0.116789   0.723990   0.099662
foo  one    0.313067   0.853808        NaN
     two    0.409727  -0.529002        NaN
user_28516850

We can just do assign 

df1['C']=df2.C.reindex(df1.index.get_level_values(0)).values
df1
                A         B         C
bar one -0.007969  0.754405 -0.137703
    two -0.690867 -0.223325 -0.137703
baz one  1.490032 -2.207812 -1.145512
    two  1.428660 -0.982451 -1.145512
foo one -1.203488 -2.190912       NaN
    two -0.791889 -0.199983       NaN
user_79645270

You could do:

df1 = df1.reset_index().set_index('level_0')
result = df1.merge(df2, left_on='level_0', right_on=df2.index, how='left').set_index(['level_0', 'level_1'])
print(result)

Output

                        A         B         C
level_0 level_1                              
bar     one      0.692937  0.119553  0.941637
        two     -0.876270 -1.148878  0.941637
baz     one      1.413018  0.170197 -0.250836
        two      1.996977  1.184525 -0.250836
foo     one     -2.504001  0.591182       NaN
        two     -0.535933 -1.259659       NaN
user_40015920

I think map on index level_0 of df1 also works

df1['C'] = df1.index.get_level_values(0).map(df2.C)

Out[71]:
                A         B         C
bar one  0.690827 -0.627957 -0.773924
    two -0.080936 -1.330712 -0.773924
baz one  1.395178 -0.099748  0.099662
    two -0.116789  0.723990  0.099662
foo one  0.313067  0.853808       NaN
    two  0.409727 -0.529002       NaN
user_101892140
Related