How to unpivot pandas dataframe

Question

I have a Pandas dataframe which looks as follows: Starting Table:

Kode	Country	Procedure	male	male	female	female
Kode	Country	Procedure	two-year-old	three-year-old	two-year-old	three-year-old
1a	US	proc_1	4	6	3	6
1a	UK	proc_1	2	3	5	1
1b	US	proc_2	15	3	5	2
1b	UK	proc_2	8	4	7	3

CSV:

Code;Country;Procedure;male;male;female;female Code;Country;Procedure;two-year-old;three-year-old;two-year-old;three-year-old 1a;US;proc_1;4;6;3;6 1a;UK;proc_1;2;3;5;1 1b;US;proc_2;15;3;5;2 1b;UK;proc_2;8;4;7;3

My python code:

df = pd.read_csv('test.csv', header=[0,1],delimiter= ';', engine='python', encoding= 'unicode_escape')

In fact I have two row headers How to pivot pandas dataframe to get the following result?

Resulting Table:

Code	Country	Procedure	Gender	Age	Frequency
1a	US	proc_1	male	two-year-old	4
1a	US	proc_1	male	three-year-old	6
1a	US	proc_1	female	two-year-old	3
1a	US	proc_1	female	three-year-old	6
1a	UK	proc_1	male	two-year-old	2
1a	UK	proc_1	male	three-year-old	3
1a	UK	proc_1	female	two-year-old	5
1a	UK	proc_1	female	three-year-old	1
1b	...

hey @dublin123, try this: pandas.pydata.org/docs/reference/api/pandas.pivot_table.html — Rutangaba
– Rutangaba, Commented Nov 5, 2021 at 13:38

ramslök · Accepted Answer · 2021-11-05 20:51:26Z

You'll likely want to go with melt which is sort of the opposite of pivot.

We specify the identifer variables: the first three columns
The rest of the columns become value variables and we assign them names, and a name for the new value column

import io import pandas as pd piv = pd.read_csv(io.StringIO("""Code;Country;Procedure;male;male;female;female Code;Country;Procedure;two-year-old;three-year-old;two-year-old;three-year-old 1a;US;proc_1;4;6;3;6 1a;UK;proc_1;2;3;5;1 1b;US;proc_2;15;3;5;2 1b;UK;proc_2;8;4;7;3"""), header=[0, 1], delimiter=";") unpivoted = piv.melt(id_vars=list(piv.columns[:3]), var_name=['gender', 'age'], value_name='frequency') # cleanup the tupleized columns unpivoted.columns = [(col[0] if isinstance(col, tuple) else col) for col in unpivoted.columns]

Collectives™ on Stack Overflow

How to unpivot pandas dataframe

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related