How to merge two dataframes with MultiIndex?

Question

I have a frame looks like:

            2015-12-30  2015-12-31
300100  am    1             3
        pm    3             2
300200  am    5             1
        pm    4             5
300300  am    2             6
        pm    3             7

and the other frame looks like

            2016-1-1    2016-1-2    2016-1-3    2016-1-4
300100  am    1           3            5           1
        pm    3           2            4           5
300200  am    2           5            2           6
        pm    5           1            3           7
300300  am    1           6            3           2
        pm    3           7            2           3
300400  am    3           1            1           3
        pm    2           5            5           2
300500  am    1           6            6           1
        pm    5           7            7           5

Now I want to merge the two frames, and the frame after merge to be looked like this:

             2015-12-30 2015-12-31  2016-1-1    2016-1-2    2016-1-3    2016-1-4
  300100  am    1          3           1           3           5           1
          pm    3          2           3           2           4           5
  300200  am    5          1           2           5           2           6
          pm    4          5           5           1           3           7
  300300  am    2          6           1           6           3           2
          pm    3          7           3           7           2           3
  300400  am                           3           1           1           3
          pm                           2           5           5           2
  300500  am                           1           6           6           1
          pm                           5           7           7           5

I tried pd.merge(frame1,frame2,right_index=True,left_index=True), but what it returned was not the desired format. Can anyone help? Thanks!

What is frame1.index and frame2.index ? What is problem with output? — jezrael
– jezrael, Commented Dec 30, 2016 at 7:02
frame1.index is MultiIndex(levels=[[300100.0, 300200.0, 300300.0], ['am', 'pm']], labels=[[0, -1, 1, -1, 2, -1], [0, 1, 0, 1, 0, 1]]) — Steven
– Steven, Commented Dec 30, 2016 at 7:14
frame2.index is MultiIndex(levels=[[300100.0, 300200.0, 300300.0, 300400.0, 300500.0], ['am', 'pm']], labels=[[0, -1, 1, -1, 2, -1, 3, -1, 4, -1], [0, 1, 0, 1, 0, 1, 0, 1, 0, 1]]). Part of the output looks like 2016-02-01 00:00:00_x 2016-02-02 00:00:00_x \ 300100.0 am 1 3 300200.0 am 5 1 300300.0 am 2 6 — Steven
– Steven, Commented Dec 30, 2016 at 7:17
Thank you. But I think better is if you change your pictures to text (because is impossible copy your sample data) and add your problematic output to question text, because problematic formating in comments. — jezrael
– jezrael, Commented Dec 30, 2016 at 7:22
One idea - how does work pd.concat([frame1, frame2], axis=1) or pd.concat([frame1, frame2], axis=1, join='inner') ? — jezrael
– jezrael, Commented Dec 30, 2016 at 7:30

jezrael · Accepted Answer · 2016-12-30 08:12:21Z

You can use concat:

print (pd.concat([frame1, frame2], axis=1))
           2015-12-30  2015-12-31  1.1.2016  2.1.2016  3.1.2016  4.1.2016
300100 am         1.0         3.0         1         3         5         1
       pm         3.0         2.0         3         2         4         5
300200 am         5.0         1.0         2         5         2         6
       pm         4.0         5.0         5         1         3         7
300300 am         2.0         6.0         1         6         3         2
       pm         3.0         7.0         3         7         2         3
300400 am         NaN         NaN         3         1         1         3
       pm         NaN         NaN         2         5         5         2
300500 am         NaN         NaN         1         6         6         1
       pm         NaN         NaN         5         7         7         5

Values in first and second column are converted to float, because NaN values convert int to float - see docs.

One possible solution is replace NaN by some int e.g. 0 and then convert to int:

print (pd.concat([frame1, frame2], axis=1)
         .fillna(0)
         .astype(int))
           2015-12-30  2015-12-31  1.1.2016  2.1.2016  3.1.2016  4.1.2016
300100 am           1           3         1         3         5         1
       pm           3           2         3         2         4         5
300200 am           5           1         2         5         2         6
       pm           4           5         5         1         3         7
300300 am           2           6         1         6         3         2
       pm           3           7         3         7         2         3
300400 am           0           0         3         1         1         3
       pm           0           0         2         5         5         2
300500 am           0           0         1         6         6         1
       pm           0           0         5         7         7         5

piRSquared · Accepted Answer · 2016-12-30 08:17:58Z

3

you can use join

frame1.join(frame2, how='outer')

answered Dec 30, 2016 at 8:17

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Collectives™ on Stack Overflow

How to merge two dataframes with MultiIndex?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related