CODEWITHSUNDEEP
pythonpandas
democracii
democracii

Reputation: 89

Using concat function in Pandas

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

Upvotes: 1

Views: 54

Answers (1)

jezrael
jezrael

Reputation: 862521

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])

Upvotes: 1

Related Questions