CODEWITHSUNDEEP
pythonpandas
YourEconProf
YourEconProf

Reputation: 311

Concatenate Two DataFrames With Hierarchical Columns

I would like to merge two DataFrames while creating a multilevel column naming scheme denoting which dataframe the rows came from. For example:

In [98]: A=pd.DataFrame(np.arange(9.).reshape(3,3),columns=list('abc'))
In [99]: A
Out[99]: 
   a  b  c
0  0  1  2
1  3  4  5
2  6  7  8

In [100]: B=A.copy()

If I use pd.merge(), then I get

In [104]: pd.merge(A,B,left_index=True,right_index=True)
Out[104]: 
   a_x  b_x  c_x  a_y  b_y  c_y
0    0    1    2    0    1    2
1    3    4    5    3    4    5
2    6    7    8    6    7    8

Which is what I expect with that statement, what I would like (but I don't know how to get!) is:

In [104]: <<one or more statements>>
Out[104]: 
     A              B
     a    b    c    a    b    c
0    0    1    2    0    1    2
1    3    4    5    3    4    5
2    6    7    8    6    7    8

Can this be done without changing the original pd.DataFrame calls? I am reading the data in the dataframes in from .csv files and that might be my problem.

Upvotes: 9

Views: 2216

Answers (2)

Jeff
Jeff

Reputation: 128978

first case can be ordered arbitrarily among A,B (not the columns, just the order A or B) 2nd should preserve ordering

IMHO this is pandonic!

In [5]: concat(dict(A = A, B = B),axis=1)
Out[5]: 
   A        B      
   a  b  c  a  b  c
0  0  1  2  0  1  2
1  3  4  5  3  4  5
2  6  7  8  6  7  8

In [6]: concat([ A, B ], keys=['A','B'],axis=1)
Out[6]: 
   A        B      
   a  b  c  a  b  c
0  0  1  2  0  1  2
1  3  4  5  3  4  5
2  6  7  8  6  7  8

Upvotes: 8

Andy Hayden
Andy Hayden

Reputation: 375565

Here's one way, which does change A and B:

In [10]: from itertools import cycle

In [11]: A.columns = pd.MultiIndex.from_tuples(zip(cycle('A'), A.columns))

In [12]: A
Out[12]:
   A
   a  b  c
0  0  1  2
1  3  4  5
2  6  7  8

In [13]: B.columns = pd.MultiIndex.from_tuples(zip(cycle('B'), B.columns))

In [14]: A.join(B)
Out[14]:
   A        B
   a  b  c  a  b  c
0  0  1  2  0  1  2
1  3  4  5  3  4  5
2  6  7  8  6  7  8

I actually think this would be a good alternative behaviour, rather than suffixes...

Upvotes: 6

Related Questions