CODEWITHSUNDEEP
python-3.xpandasmulti-index
Dance Party
Dance Party

Reputation: 3723

Pandas Set Top Row as MultiIndex Level 1

Given the following data frame:

d2=pd.DataFrame({'Item':['items','y','z','x'],
                'other':['others','bb','cc','dd']})
d2
    Item    other
0   items   others
1     y     bb
2     z     cc
3     x     dd

I'd like to create a multiindexed set of headers such that the current headers become level 0 and the current top row becomes level 1.

Thanks in advance!

Upvotes: 3

Views: 1421

Answers (2)

jezrael
jezrael

Reputation: 863531

Another solution is create MultiIndex.from_tuples:

cols = list(zip(d2.columns, d2.iloc[0,:]))
c1 = pd.MultiIndex.from_tuples(cols, names=[None, 0])

print (pd.DataFrame(data=d2[1:].values, columns=c1, index=d2.index[1:]))

   Item  other
0 items others
1     y     bb
2     z     cc
3     x     dd

Or if column names are not important:

cols = list(zip(d2.columns, d2.iloc[0,:]))
d2.columns = pd.MultiIndex.from_tuples(cols)

print (d2[1:])
   Item  other
  items others
1     y     bb
2     z     cc
3     x     dd

Timings:

len(df)=400k:

In [63]: %timeit jez(d22)
100 loops, best of 3: 6.22 ms per loop

In [64]: %timeit piR(d2)
10 loops, best of 3: 84.9 ms per loop

len(df)=40:

In [70]: %timeit jez(d22)
The slowest run took 4.61 times longer than the fastest. This could mean that an intermediate result is being cached.
1000 loops, best of 3: 941 µs per loop

In [71]: %timeit piR(d2)
The slowest run took 4.44 times longer than the fastest. This could mean that an intermediate result is being cached.
1000 loops, best of 3: 1.36 ms per loop

Code:

import pandas as pd

d2=pd.DataFrame({'Item':['items','y','z','x'],
                'other':['others','bb','cc','dd']})

print (d2) 
d2 = pd.concat([d2]*100000).reset_index(drop=True) 
#d2 = pd.concat([d2]*10).reset_index(drop=True)   
d22 = d2.copy()

def piR(d2):
    return (d2.T.set_index(0, append=1).T) 


def jez(d2):
    cols = list(zip(d2.columns, d2.iloc[0,:]))
    c1 = pd.MultiIndex.from_tuples(cols, names=[None, 0])

    return pd.DataFrame(data=d2[1:].values, columns=c1, index=d2.index[1:])  

print (piR(d2))
print (jez(d22))

print ((piR(d2) == jez(d22)).all())
Item   items     True
other  others    True
dtype: bool

Upvotes: 3

piRSquared
piRSquared

Reputation: 294516

Transpose the DataFrame, set_index with the first column with parameter append = True, then Transpose back.

d2.T.set_index(0, append=1).T

Upvotes: 2

Related Questions