How to retain multiindex in pandas dataframe while concatenate two separate dataframe

Question

dfs_subs dataframe contain multiindex one level is Question-0 and next level is (Score,..) and i have other dataframe which is having only one level.When i am doing table_1 = pd.concat([df_metadata, dfs_subs], axis=1, levels=0)

table_1 output is showing like below

but i want Question-0 should be one separate level instead of combined as one single column.

For example:

What is print (df_metadata.columns[:5].tolist()) and print (dfs_subs[:5].tolist()) ? — jezrael
– jezrael, Commented Apr 12, 2021 at 11:39
Thanks for your response. df_metadata.columns[:5].tolist()) = ['id', 'FriendlyName', 'CreatedDate', 'AuditorId', 'EmployeeId'] (dfs_subs[:5].tolist()) [('Question-0', ('Score', 'InitialScoreOfQuestion', 'NotApplicable', 'FeedbackCommentQuestion', 'PresetCommentQuestion', 'MechanicsFeedBack', 0)), ('Question-1', ('Score', 'InitialScoreOfQuestion', 'NotApplicable', 'FeedbackCommentQuestion', 'PresetCommentQuestion', 'MechanicsFeedBack', 1)), ...... — Saswat Ray
– Saswat Ray, Commented Apr 12, 2021 at 12:03

jezrael · Accepted Answer · 2021-04-12 12:54:25Z

1

If need MultiIndex in both is possible add empty values for second level for MultiIndex in both DataFrames:

df1.columns = pd.MultiIndex.from_product([df1.columns, ['']])
print (df1)
  Name Age
          
0  Sam  43

#check how looks MultiIndex converted to list
print (df1.columns.tolist())
[('Name', ''), ('Age', '')]

df = pd.concat([df1, df2], axis=1)
print (df)
  Name Age Address  Fav
             score Time
0  Sam  43       7   10

#check how looks MultiIndex converted to list
print (df.columns.tolist())
[('Name', ''), ('Age', ''), ('Address', 'score'), ('Fav', 'Time')]

edited Apr 12, 2021 at 12:54

answered Apr 12, 2021 at 11:43

jezrael

867k102 gold badges1.4k silver badges1.3k bronze badges

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

9 Comments

Saswat Ray Over a year ago

Okay.Can you tell me is it possible to do multiindex to a subset of dataframe.I was trying this approach but its not working.I want to apply multiindex to only few columns headers instead of all headers in dataframe.

jezrael Over a year ago

@SaswatRay - hmm, I see it now. Problem is

[('Question-0', ('Score', 'InitialScoreOfQuestion', 'NotApplicable', 'FeedbackCommentQuestion', 'PresetCommentQuestion', 'MechanicsFeedBack', 0)), ('Question-1', ('Score', 'InitialScoreOfQuestion', 'NotApplicable', 'FeedbackCommentQuestion', 'PresetCommentQuestion', 'MechanicsFeedBack', 1)),

is not MultiIndex, it is column of tuples. How was created?

jezrael Over a year ago

Btw, what is expected ouput?

jezrael Over a year ago

@SaswatRay - Hmm, I think main question is simple - need MultiIndex? If not, try remove it some way.

Saswat Ray Over a year ago

for ex. Input : df1 = Name Age Sam 43 df2= Address Fav score Time 7 10 Desired output: Name Age Address Fav score Time Sam 43 7 10 Note: Address and score are two multiindex so as Fav and Time

|

Collectives™ on Stack Overflow

How to retain multiindex in pandas dataframe while concatenate two separate dataframe

1 Answer 1

9 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Related