Pandas: group multiple columns under one header

Question

Set-up

I have a pandas dataframe df consisting out of multiple columns, with headers like,

| id | x, single room | x, double room | y, single room | y, double room |
--------------------------------------------------------------------------
   ⋮          ⋮               ⋮                 ⋮                 ⋮

Problem

I'd like to group the columns starting with x and starting with y under headers in the following way,

     |             x             |             y             |
--------------------------------------------------------------
| id | single room | double room | single room | double room |
--------------------------------------------------------------
   ⋮        ⋮             ⋮              ⋮             ⋮

How do I go about?

This can be done (more or less) with MultiIndexing.

languitar
– languitar

2017-04-20 11:17:06 +00:00
Commented Apr 20, 2017 at 11:17 — languitar
– languitar, Commented Apr 20, 2017 at 11:17

jezrael · Accepted Answer · 2017-04-20 12:15:17Z

8

You can use split, but main problem is get id to last level:

col =['id','x, single room','x, double room','y, single room','y, double room' ]
df = pd.DataFrame([[1,1,1,1,1]], columns=col)
print (df)
   id  x, single room  x, double room  y, single room  y, double room
0   1               1               1               1               1

#create tuples from MultiIndex
a = df.columns.str.split(', ', expand=True).values
print (a)
[('id', nan) ('x', 'single room') ('x', 'double room') ('y', 'single room')
 ('y', 'double room')]

#swap values in NaN and replace NAN to ''
df.columns = pd.MultiIndex.from_tuples([('', x[0]) if pd.isnull(x[1]) else x for x in a])
print (df)
               x                       y            
  id single room double room single room double room
0  1           1           1           1           1

Old solution:

a = pd.DataFrame(df.columns.str.rsplit(', ', expand=True).values.tolist())
mask = a[1].isnull()
a.loc[mask, [0,1]] = a.loc[mask, [1,0]].values
a[0] = a[0].fillna('')
df.columns = a.set_index([0,1]).index
df.columns.names = ('', '')

edited Apr 20, 2017 at 12:15

answered Apr 20, 2017 at 11:30

jezrael

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

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

3 Comments

LucSpan Over a year ago

Great! Thank you.

jezrael Over a year ago

I have nicer solution, give me a sec.

susim Over a year ago

@jezrael It's really fantastic.

Collectives™ on Stack Overflow

Pandas: group multiple columns under one header

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related