Converting multi column header df from wide to long format in python

Question

I would like to convert an excel file from wide format into long format.

I'm reading an excel file which has not only two rows of headers, it also includes merged cells in the header. Input Example:

| Task | Name | May,2022  | Jun,2022  | Jul,2022  |
|      |      |status|type|status|type|status|type|
| ---- | ---- |------|----|------|----|------|----|
| 1    | Tom  |1     |AB  |1     |DT  |0     |AB  | 
| 2    | John |0     |DT  |1     |CC  |0     |GH  |

Desired Output:

Task	Name	Date	status	type
1	Tom	May,2022	1	AB
1	Tom	Jun,2022	1	DT
1	Tom	Jul,2022	0	AB
2	John	May,2022	0	DT
2	John	Jun,2022	1	CC
2	John	Jul,2022	0	GH

What i have:

import pandas as pd
df = pd.read_excel(path)
df = df.melt(id_vars=["Task","Name"],
     var_name="Date",
     value_name="Value")

I don't know how to deal with the multi header. I tried combining the two headers.

import pandas as pd
df = pd.read_excel(path, header[1,2])
df.columns = df.columns.to_series().ffill().values
df.columns.value[0]='Task'
df.columns.value[1]='Name'
df = df.melt(id_vars=["Task","Name"],
     var_name="Date",
     value_name="Value")

This results in having something like: (I'm fine with the date transformation)

Task	Name	(2022-05-01 00:00:00, status)	(2022-05-01 00:00:00, type)
1	Tom	1	AB
2	John	0	DT

The melt function returns a KeyError 'Task'. Even if this would work i would somehow need to split the date from the status and type into seperate columns. Someone knows how to deal with such data?

What exactly is your input? Can you provide a DataFrame constructor? Do you just want to [`stack`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.stack.html)? — mozway, Apr 25 '23 at 11:40

jezrael · Answer 1 · 2023-04-25T11:49:51.723

Add index_col and header for MulitIndex in index and columns first:

df = pd.read_excel(path, index_col=[0,1], header=[0,1])

print (df.index)
MultiIndex([(1,  'Tom'),
            (2, 'John')],
           names=['Task', 'Name'])

print (df.columns)
MultiIndex([('May,2022', 'status'),
            ('May,2022',   'type'),
            ('Jun,2022', 'status'),
            ('Jun,2022',   'type'),
            ('Jul,2022', 'status'),
            ('Jul,2022',   'type')],
           names=['Task', None])

So possible use DataFrame.stack with DataFrame.rename_axis and DataFrame.reset_index:

out = df.stack(0).rename_axis(('Task','Name','Date')).reset_index()
print (out)
   Task  Name      Date  status type
0     1   Tom  Jul,2022       0   AB
1     1   Tom  Jun,2022       1   DT
2     1   Tom  May,2022       1   AB
3     2  John  Jul,2022       0   GH
4     2  John  Jun,2022       1   CC
5     2  John  May,2022       0   DT

Corralien · Accepted Answer · 2023-04-25T11:51:19.047

You can use set_index to preserve Task and Name columns then stack Date column and finally reset_index to get expected output:

>>> (df.set_index(['Task', 'Name']).rename_axis(columns=['Date', None])
       .stack(level=0).reset_index())

   Task  Name      Date  status type
0     1   Tom  Jul,2022       0   AB
1     1   Tom  Jun,2022       1   DT
2     1   Tom  May,2022       1   AB
3     2  John  Jul,2022       0   GH
4     2  John  Jun,2022       1   CC
5     2  John  May,2022       0   DT

Minimal Reproducible Example:

data = {'index': [0, 1],
        'columns': [('Task', ''),
         ('Name', ''),
         ('May,2022', 'status'),
         ('May,2022', 'type'),
         ('Jun,2022', 'status'),
         ('Jun,2022', 'type'),
         ('Jul,2022', 'status'),
         ('Jul,2022', 'type')],
        'data': [[1, 'Tom', 1, 'AB', 1, 'DT', 0, 'AB'],
                 [2, 'John', 0, 'DT', 1, 'CC', 0, 'GH']],
        'index_names': [None],
        'column_names': [None, None]}
df = pd.DataFrame.from_dict(data, orient='tight')

Converting multi column header df from wide to long format in python

2 Answers2