Python Pandas concatenate two multi index dataframe into one with another level of multi index

Question

I am dealing with Multi Index again. This time, I am concating two dataframes with same types Multi-indexed index and columns. However, the values are different, and there will be come index.level(0) values are different. I want to concate two dataframe into one. Please take a look at below example.

In [13]: df_ver1
Out[13]: 
key  nm         0         1         2         3
bar one -0.424972  0.567020  0.276232 -1.087401
    two -0.673690  0.113648 -1.478427  0.524988
baz one  0.404705  0.577046 -1.715002 -1.039268
    two -0.370647 -1.157892 -1.344312  0.844885
foo one  1.075770 -0.109050  1.643563 -1.469388
    two  0.357021 -0.674600 -1.776904 -0.968914
qux one -1.294524  0.413738  0.276662 -0.472035
    two -0.013960 -0.362543 -0.006154 -0.923061


In [14]: df_ver2
Out[14]: 
key  nm         0         1         2         3
bar one  0.895717  0.410835 -1.413681 -1.236269
    two  0.805244  0.813850  1.607920  0.896171
baz one -1.206412  0.132003  1.024180 -0.487602
    two  2.565646 -0.827317  0.569605 -0.082240
oof one  1.431256 -0.076467  0.875906 -2.182937
    two  1.340309 -1.187678 -2.211372  0.380396
qux one -1.170299  1.130127  0.974466  0.084844
    two -0.226169 -1.436737 -2.006747  0.432390


In [15]: df_total
out[15]:
key  nm  ver            0         1         2         3
bar one ver1    -0.424972  0.567020  0.276232 -1.087401
        ver2     0.895717  0.410835 -1.413681 -1.236269
    two ver1    -0.673690  0.113648 -1.478427  0.524988
        ver2     0.805244  0.813850  1.607920  0.896171
baz one ver1     0.404705  0.577046 -1.715002 -1.039268
        ver2    -1.206412  0.132003  1.024180 -0.487602
    two ver1    -0.370647 -1.157892 -1.344312  0.844885
        ver2     2.565646 -0.827317  0.569605 -0.082240
qux one ver1    -1.294524  0.413738  0.276662 -0.472035
        ver2    -1.170299  1.130127  0.974466  0.084844
    two ver1    -0.013960 -0.362543 -0.006154 -0.923061
        ver2    -0.226169 -1.436737 -2.006747  0.432390

As you can already see, two levels of Multi-index and the third level would indicate the dataframe version as ver1 or ver2. Each values are layed in respect to columns. One thing to keep in mind is that in df_ver1, there are foo index and in df_ver2, there are oof index. These indexes does not have matching index to other dataframe, so it is concatenated in inner frames. I hope I explained well enough, and let me know if you have any questions. Thanks for the help!

Bharath M Shetty · Accepted Answer · 2018-01-16 05:48:46Z

1

Take the intersection two dataframe's index, concat and sort_index i.e

idx = d1.index.intersection(d2.index)
one = pd.concat([d1.loc[idx],d2.loc[idx]],keys=['ver1','ver2'])

one.reset_index().set_index(['key','nm','level_0']).sort_index(level=['key','nm'])

                      0         1         2         3
key nm  level_0                                        
bar one ver1    -0.424972  0.567020  0.276232 -1.087401
        ver2     0.895717  0.410835 -1.413681 -1.236269
    two ver1    -0.673690  0.113648 -1.478427  0.524988
        ver2     0.805244  0.813850  1.607920  0.896171
baz one ver1     0.404705  0.577046 -1.715002 -1.039268
        ver2    -1.206412  0.132003  1.024180 -0.487602
    two ver1    -0.370647 -1.157892 -1.344312  0.844885
        ver2     2.565646 -0.827317  0.569605 -0.082240
qux one ver1    -1.294524  0.413738  0.276662 -0.472035
        ver2    -1.170299  1.130127  0.974466  0.084844
    two ver1    -0.013960 -0.362543 -0.006154 -0.923061
        ver2    -0.226169 -1.436737 -2.006747  0.432390

answered Jan 16, 2018 at 5:48

Bharath M Shetty

30.6k6 gold badges65 silver badges111 bronze badges

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

4 Comments

EJ Kang Over a year ago

I have an error and message only says level_0. Any ideas?

Bharath M Shetty Over a year ago

@Kang I didn't get you. Only level_0 nothing else

EJ Kang Over a year ago

Yeah. I just want to understand what you wrote first. So it seems idx is responsible for finding all key values that is in both df, and one stands for concatenating two df within idx index. So when I print one, I get two level=0 index as ver1 and ver2. the last reseting index is the part I dont get. Is there another way to write the last line where you rearrange dataframe with indexes? Thanks

EJ Kang Over a year ago

df_total = one.reorder_levels(['key','nm','level_0']) This way, it shows the level type I want, but the first level values which is 'key' does not combine. It just switching the orders. thats it.

Collectives™ on Stack Overflow

Python Pandas concatenate two multi index dataframe into one with another level of multi index

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related