CODEWITHSUNDEEP
pythonpandas
Bengya
Bengya

Reputation: 115

How to turn multiple rows into multiple headers headers in pandas dataframe

So I have a pandas dataFrame and I would like to turn two rows into multiple headers. so from

1    A  | A  | B   | B
2    C  | D  | C   | D
3    cat| dog|mouse|goose

to

    A       | B
    C  | D  | C   | D
1   cat| dog|mouse|goose

I found 

df.columns = df.iloc[0]

to work for 1 row but i want multiple headers from the first and second row Thanks in advance!

Upvotes: 7

Views: 10511

Answers (2)

jezrael
jezrael

Reputation: 863781

Create MultiIndex by assign first and second row, last filter out first rows by positions by DataFrame.iloc:

df.columns = [df.iloc[0].values, df.iloc[1].values]
df = df.iloc[2:].reset_index(drop=True)
print (df)
     A           B       
     C    D      C      D
0  cat  dog  mouse  goose

But better if create DataFrame from file use parameters header=[0,1] in read_csv:

df = pd.read_csv(file, header=[0,1])

Sample:

import pandas as pd

temp=u"""A,A,B,B
C,D,C,D
cat,dog,mouse,goose"""
#after testing replace 'pd.compat.StringIO(temp)' to 'filename.csv'
df = pd.read_csv(pd.compat.StringIO(temp), header=[0,1])
print (df)

     A           B       
     C    D      C      D
0  cat  dog  mouse  goose

Upvotes: 12

BENY
BENY

Reputation: 323396

Using MultiIndex create it , then assign it back 

df.columns=pd.MultiIndex.from_arrays([df.iloc[0],df.iloc[1]])
yourdf=df.iloc[2:].reset_index(drop=True)
yourdf
Out[52]: 
1    A           B       
2    C    D      C      D
0  cat  dog  mouse  goose

Upvotes: 5

Related Questions