merge pandas dataframes under new index level

user_2302262 0
Viewed 65

I have 2 pandas DataFrames act and exp that I want to combine into a single dataframe df:

import pandas as pd
from numpy.random import rand
act = pd.DataFrame(rand(3,2), columns=['a', 'b'])
exp = pd.DataFrame(rand(3,2), columns=['a', 'c'])

act #have

          a         b
0  0.853910  0.405463
1  0.822641  0.255832
2  0.673718  0.313768

exp #have

          a         c
0  0.464781  0.325553
1  0.565531  0.269678
2  0.363693  0.775927

Dataframe df should contain one more column index level than act and exp, and contain each under its own level-0 identifier, like so:

df  #want

        act                 exp          
          a         b         a         c
0  0.853910  0.405463  0.464781  0.325553
1  0.822641  0.255832  0.565531  0.269678
2  0.673718  0.313768  0.363693  0.775927

Any ideas as to how to do this?

It's a bit like mergeing the two frames:

act.merge(exp, left_index=True, right_index=True, suffixes=['_act', '_exp'])

      a_act         b     a_exp         c
0  0.853910  0.405463  0.464781  0.325553
1  0.822641  0.255832  0.565531  0.269678
2  0.673718  0.313768  0.363693  0.775927

...but using an additional level, instead of a suffix, to prevent name collisions.

I tried:

#not working
pd.DataFrame({'act': act, 'exp':exp})

I could use loops to build up the df series-by-series, but that doesn't seem right.

Many thanks.

pandas dataframe python
2 Answers

May be you can try using concat:

pd.concat([act, exp], axis=1, keys=['act', 'exp'])

Result:

          act                      exp
       a           b             a           c
0   0.604027    0.933399    0.830059    0.317602
1   0.992192    0.991513    0.397223    0.904166
2   0.382579    0.981182    0.862077    0.239373
user_59167270 
import numpy as np
import pandas as pd
from numpy.random import rand
act = pd.DataFrame(rand(3,2), columns=['a', 'b'])
exp = pd.DataFrame(rand(3,2), columns=['a', 'c'])
print(act)
print(exp)
df = pd.DataFrame(rand(3,4), columns=['act_a', 'act_b', 'exp_a', 'exp_c'])
# load data to act and exp
df['act_a'] = act['a']
df['act_b'] = act['b']
df['exp_a'] = exp['a']
df['exp_c'] = exp['c']
print(df)

Output:

          a         b
0  0.520894  0.451379
1  0.560014  0.427791
2  0.900554  0.326217
          a         c
0  0.766543  0.746780
1  0.207466  0.711153
2  0.341080  0.136082
      act_a     act_b     exp_a     exp_c
0  0.520894  0.451379  0.766543  0.746780
1  0.560014  0.427791  0.207466  0.711153
2  0.900554  0.326217  0.341080  0.136082
user_21239970
Related