Pandas use several rows for a column MultiIndex in a DataFrame

Question

When you load a CSV in pandas you can easily specify the number of rows to use as column indexes, as such:

import pandas
from six import StringIO
df = """a | X | X  | Y | Y  | Z  | Z
        b | C | N  | C | N  | C  | N
        c | i | i  | i | j  | j  | j
        d | 3 | 10 | 4 | 98 | 81 | 0"""
df = StringIO(df.replace(' ',''))
df = pandas.read_csv(df, sep="|", header=[0,1,2])

>>> df
   a  X      Y       Z
   b  C   N  C   N   C  N
   c  i   i  i   j   j  j
0  d  3  10  4  98  81  0

But how do you produce the same result from a Dataframe in memory? How do you simply specifying which set of rows should be used for the column index ?

Without of course going through this hack:

>>> df

   0  1   2  3   4   5  6
0  a  X   X  Y   Y   Z  Z
1  b  C   N  C   N   C  N
2  c  i   i  i   j   j  j
3  d  3  10  4  98  81  0

path = '~/test/temp.csv'
df.to_csv(path, header=None, index=None)
df = pandas.read_csv(path, header=[0,1,2])

Or even this hack:

>>> df

   0  1   2  3   4   5  6
0  a  X   X  Y   Y   Z  Z
1  b  C   N  C   N   C  N
2  c  i   i  i   j   j  j
3  d  3  10  4  98  81  0

df = df.transpose().set_index([0,1,2]).transpose()

I tried using this method, but it does not accept an axis parameter:

df.set_index(['a', 'b', 'c'], axis=1)

jezrael · Accepted Answer · 2019-06-14 11:05:13Z

2

Your alternative solution should be improved a bit:

df = df.T.set_index([0,1,2]).T

Another solution without transpose:

df.columns = pd.MultiIndex.from_tuples(df.iloc[:3].apply(tuple))
df = df.iloc[3:].reset_index(drop=True)
print (df)
   a  X      Y       Z   
   b  C   N  C   N   C  N
   c  i   i  i   j   j  j
0  d  3  10  4  98  81  0

edited Jun 14, 2019 at 11:05

answered Jun 14, 2019 at 10:59

jezrael

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

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

Collectives™ on Stack Overflow

Pandas use several rows for a column MultiIndex in a DataFrame

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related