CODEWITHSUNDEEP
pythonpandas
Holt
Holt

Reputation: 37616

Convert columns with two parts into MultiIndex

I have the following DataFrame (read from a CSV file I cannot change):

df = pd.DataFrame([['low', 0.5, 123, 0.8, 123], 
                   ['high', 0.7, 253, 0.9, 147]], 
                  columns=['type', 'g1 v0', 'g1 v1', 'g2 v0',  'g2 v1'])

I would like to "split" the columns g1 v0, g1 v1, etc. in a multiindex, e.g. like:

                   g1            g2
            v0     v1     v0     v1
   type     
0   low    0.5    123    0.8    123
1  high    0.7    253    0.9    147

Basically, I want the gx / vy part of the column names to be put into two separate levels.

Upvotes: 2

Views: 489

Answers (1)

jezrael
jezrael

Reputation: 862711

Use set_index + split:

df = df.set_index('type')
df.columns = df.columns.str.split(expand=True)
print (df)
       g1        g2     
       v0   v1   v0   v1
type                    
low   0.5  123  0.8  123
high  0.7  253  0.9  147

Another solution with drop, split and concat:

df1 = df.drop('type', axis=1)
df1.columns = df1.columns.str.split(expand=True)
print (df1)
    g1        g2     
    v0   v1   v0   v1
0  0.5  123  0.8  123
1  0.7  253  0.9  147

df = pd.concat([df['type'].rename(('','type')), df1], axis=1)
print (df)
         g1        g2     
   type   v0   v1   v0   v1
0   low  0.5  123  0.8  123
1  high  0.7  253  0.9  147

Upvotes: 4

Related Questions