Using a pandas MultiIndex to join two dataframes

Question

I have two pandas data frames:

df1:

        column
index1
rec-1   foo
rec-2   bar
rec-3   bar
  :      :

df2:

          test
index2
rec-1-b   baz
rec-2-b   foo
rec-3-b   quux
   :       :

along with a MultiIndex object

multiIndex1:

(rec-1,rec-1-b)
(rec-2,rec-3-b)
:

linking the two data frames together. How would I now obtain a data frame that looks like this:

joined_df:

                  column   test
index1  index2
rec-1   rec-1-b   foo      baz
rec-2   rec-3-b   bar      quux
  :        :       :         :

jezrael · Accepted Answer · 2018-02-19 14:22:07Z

2

I think you need reindex with join or concat:

mux = pd.MultiIndex.from_tuples([('rec-1','rec-1-b'),('rec-2','rec-3-b')])

df = df1.reindex(mux, level=0).join(df2.reindex(mux, level=1))

Or:

df = pd.concat([df1.reindex(mux, level=0), df2.reindex(mux, level=1)], axis=1)

print (df)
              column  test
rec-1 rec-1-b    foo   baz
rec-2 rec-3-b    bar  quux

answered Feb 19, 2018 at 14:22

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1