Using concat function in Pandas

Question

I want to merge df1 and df2. I used concat function with outer join and use multi index.

The result is merged index value,, I want to divide index columns..

Please advise me how to do.

   df1:
        CODE    U-01    U-02    U-03    U-04    U-05        
  INDEX host                                                                                    
   L1   A        3.0    3.0     3.0     3.0      3.0        

   L2   B        3.0    3.0     3.0     3.0      3.0

   L3   C        3.0    3.0     3.0     3.0      3.0    

   L4   D        3.0    3.0     3.0     3.0      3.0


  df2:
  CODE      U-01     U-02   U-03    U-04    U-05    
  LEVEL      H        L       H      M        L 
  STANDARD   3        3       3      3        2

so, My code is ,

 total_data = pd.concat([df1, df2], join='outer')

But, The result is

            U-01    U-02    U-03    U-04    U-05        
 LEVEL       H       L        H       M       L 
STANDARD     3       3        3       3       2                                                                     
 (L1,A)     3.0     3.0      3.0     3.0     3.0        

 (L2,B)     3.0     3.0      3.0     3.0     3.0

 (L3,C)     3.0     3.0      3.0     3.0     3.0    

 (L4,D)     3.0     3.0      3.0     3.0     3.0

I want to split the column and use multi index.

 Desired result would be :

             U-01   U-02    U-03    U-04    U-05        
 LEVEL        H       L       H       M       L 
 STANDARD     3       3       3       3       2                                                                     
 L1      A   3.0      3.0    3.0     3.0     3.0        

 L2      B   3.0      3.0    3.0     3.0     3.0

 L3      C   3.0      3.0    3.0     3.0     3.0    

 L4      D   3.0      3.0    3.0     3.0     3.0
  

index = INDEX, host

What is print (df1.index.tolist()), print (df1.columns.tolist()), print (df2.index.tolist()), print (df2.columns.tolist()) ? — jezrael
– jezrael, Commented Aug 20, 2020 at 6:29
df1.index : MultiIndex[(L1,A], (L2,B), (L3,C), (L4,D)] df1. columns : Index['U-01', 'U-02', 'U-03', 'U-04', 'U-05'] df2.index : Index['LEVEL', 'STANDARD'] df2. columns : Index['U-01', 'U-02', 'U-03', 'U-04', 'U-05'] — democracii
– democracii, Commented Aug 20, 2020 at 6:35

jezrael · Accepted Answer · 2020-08-20 06:38:56Z

1

You need MultiIndex in index in both DataFrames before concat.

So use:

df2 = df2.assign(new = '').set_index('new', append=True)

total_data = pd.concat([df1, df2])

Then second level is filled by spaces in second level (what is a bit trick, because cannot see it).

Also is possible add some value to second level (now it is possible see, there is value new):

df2 = df2.assign(new = 'new').set_index('new', append=True)

total_data = pd.concat([df1, df2])

answered Aug 20, 2020 at 6:38

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

Using concat function in Pandas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related