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!

Answer 1

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

Answer 2

you can use join

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