turn single header in pandas dataframe to multiple headers

user_1387720 0
Viewed 427

Hi I have the following dataframe with headers and data

   T  |  A - x | A - y | B - x | B - y |
1  m  |    a   |   b   |    c  |   d   |
2  n  |    a   |   b   |    c  |   d   |

where 1 and 2 are just index

How can I convert it to the two following formats?

   T  |  A     |   A   |    B  |   B   |
      |  x     |   y   |    x  |   y   |
1  m  |    a   |   b   |    c  |   d   |
2  n  |    a   |   b   |    c  |   d   |

   T  |  A     |       |    B  |       |
      |  x     |   y   |    x  |   y   |
1  m  |    a   |   b   |    c  |   d   |
2  n  |    a   |   b   |    c  |   d   |

thanks in advance

pandas dataframe python
1 Answers

You can create MultiIndex for columns with separator, first convert all non separator column(s) to index by DataFrame.set_index and then use split:

df = df.set_index('T')
df.columns = df.columns.str.split(' - ', expand=True)
print (df)
   A     B   
   x  y  x  y
T            
m  a  b  c  d
n  a  b  c  d

If use only:

df.columns = df.columns.str.split(' - ', expand=True)
print (df)
    T  A     B   
  NaN  x  y  x  y
0   m  a  b  c  d
1   n  a  b  c  d

get NaN for first level for T.

If need it is possible replace it by empty string:

df.columns = df.columns.str.split(' - ', expand=True)

f = lambda x: '' if pd.isna(x) else x
df = df.rename(columns=f)
print (df)
   T  A     B   
      x  y  x  y
0  m  a  b  c  d
1  n  a  b  c  d

If need display all levels in MultiIndex check documentation:

display.multi_sparse
True
“Sparsify” MultiIndex display (don’t display repeated elements in outer levels within groups)

#if need temporary use option
with pd.option_context('display.multi_sparse', False):
    print (df)
   A  A  B  B
   x  y  x  y
T            
m  a  b  c  d
n  a  b  c  d
user_29010020
Related