Pandas rearrange DataFrame from long-form to wide-form

Question

I was looking for a while but i didn't find a solution for my problem ...

I have a csv with the following structure:

date_time, country, temp, dc
2018-01-01 00:00:00, Germany, 12, 0
...
2018-01-01 00:00:00, Austria, 13, 3
...
2018-01-01 00:00:00, France, 4, 9
...

as you can see, date_time will repeat.

I want to use python pandas to get the following structure:

|                     | Germany  | Austria  |  France
|                     | temp, dc | temp, dc | temp, dc
________________________________________________________
| 2018-01-01 00:00:00 | 12  , 0  | 13  , 3  | 4   , 9

I want to get two headers .. first separates the countries, second the attributes temp and dc. My index should be the date_time attribute.

Thank you for help!!!

score 3 · Accepted Answer · answered May 24 '18 at 11:28

3

This will give you what you want:

df.pivot_table(index='date_time', columns='country', values=['temp', 'dc']).swaplevel(axis=1).sort_index(axis=1)
#country   Austria      France      Germany     
#               dc temp     dc temp      dc temp
#date_time                                      
#1               3   13      9    4       0   12

answered May 24 '18 at 11:28

zipa

27,316
6
40
58

awesome! thank you very much!!! – khuesmann May 24 '18 at 11:32
@zipa- Nice Answer – Mohamed Thasin ah May 24 '18 at 11:32

Mohamed Thasin ah · Answer 2 · 2018-05-24T11:54:20.090

0

try this,

df =df.groupby('date_time').apply(lambda x:x.set_index(['date_time','country']).unstack()).swaplevel(axis=1).reset_index(level=1, drop=True)

Output:

country             Austria France Germany Austria France Germany
                       temp   temp    temp      dc     dc      dc
date_time                                                        
2018-01-01 00:00:00      13      4      12       3      9       0

edited May 24 '18 at 11:54

answered May 24 '18 at 11:11

Mohamed Thasin ah

10,754
11
52
111

Thanks for reply! But this output is not looking like my desired .. attributes and countries are swapped. And why is date_time doubled? I'll try to go on playing this .apply(lambda ...) technique! THANKS!! – khuesmann May 24 '18 at 11:19
@khuesmann - answer updated – Mohamed Thasin ah May 24 '18 at 11:33

Pandas rearrange DataFrame from long-form to wide-form

2 Answers2