How to join column values in pandas MultiIndex DataFrame?

Question

How can I join values in columns with the same name in MultiIndex pandas DataFrame?

data = [['1','1','2','3','4'],['2','5','6','7','8']]
df = pd.DataFrame(data, columns=['id','A','B','A','B'])
df = df.set_index('id')
df.columns = pd.MultiIndex.from_tuples([('result','A'),('result','B'),('student','A'),('student','B')])

df
   result    student   
        A  B       A  B
id                     
1       1  2       3  4
2       5  6       7  8

Desired results:

        A       B
id                     
1       "1 3"   "2 4"
2       "5 7"   "6 8"

you can try swaplevel

BENY
– BENY

2017-10-09 14:47:34 +00:00
Commented Oct 9, 2017 at 14:47 — BENY
– BENY, Commented Oct 9, 2017 at 14:47

Ted Petrou · Accepted Answer · 2017-10-09 15:13:42Z

2

I am not completely sure what you are asking. If you have two separate dataframes then you should be able to just use pd.concat.

pd.concat([df1, df2], axis=1)

If you have one dataframe then just drop the top level of the index.

df.columns = df.columns.droplevel(0)

answered Oct 9, 2017 at 15:13

Ted Petrou

62.4k19 gold badges139 silver badges139 bronze badges

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

1 Comment

Vítor Mangaravite Over a year ago

pd.concat([df['result'],df['student']], axis=1) or df.columns = df.columns.droplevel(0) result ` A B A B` ` id ` ` 1 1 2 3 4` ` 2 5 6 7 8`

jezrael · Accepted Answer · 2017-10-09 17:13:32Z

1

New answer:

For join values by second level of MultiIndex in columns use groupby with agg:

#select columns define in list
df = df[['result','student']]
df1 = df.astype(str).groupby(level=1, axis=1).agg(' '.join)
print (df1)
      A    B
id          
1   1 3  2 4
2   5 7  6 8

Old answer:

You can use sort_index for sorting columns and then droplevel for remove first level of MultiIndex.

But get duplicate columns names.

print (df)
   result    student    col   
        A  B       A  B   A  B
id                            
1       1  2       3  4   6  7
2       5  6       7  8   2  1

#select columns define in list
df = df[['result','student']]
print (df)
   result    student   
        A  B       A  B
id                     
1       1  2       3  4
2       5  6       7  8

df = df.sort_index(axis=1, level=1)
df.columns = df.columns.droplevel(0)
print (df)
    A  A  B  B
id            
1   1  3  2  4
2   5  7  6  8

So better, unique columns names can be created by map with join:

df = df.sort_index(axis=1, level=1)
df.columns = df.columns.map('_'.join)
print (df)
    result_A  student_A  result_B  student_B
id                                          
1          1          3         2          4
2          5          7         6          8

df = pd.concat([df['result'],df['student']], axis=1).sort_index(axis=1)
print (df)
    A  A  B  B
id            
1   1  3  2  4
2   5  7  6  8

edited Oct 9, 2017 at 17:13

answered Oct 9, 2017 at 15:19

jezrael

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

2 Comments

Vítor Mangaravite Over a year ago

I want to join the values based on column name

jezrael Over a year ago

Please check last edit. Is not problem get duplicate columns names?

Collectives™ on Stack Overflow

How to join column values in pandas MultiIndex DataFrame?

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related