CODEWITHSUNDEEP
pythonpandas
user10102827
user10102827

Reputation: 319

I cannot make my ideal DataFrame

There is a csv data like

No,User,A,B,C,D
1 Tom 100 120 110 90
1 Juddy 89 90 100 110
1 Bob 99 80 90 100
2 Tom 80 100 100 70
2 Juddy 79 90 80 70
2 Bob 88 90 95 90
・
・
・

I want to transform this csv data into this DataFrame like 

    Tom_A Tom_B Tom_C Tom_D  Juddy_A Juddy_B Juddy_C Juddy_D  Bob_A Bob_B Bob_C Bob_D
No  
1   100      120    110         90  89      90      100         110
99  80  90  100
2   80       100    100          70 79      90      80      70
88  90  95  90

I run the codes,

import pandas as pd
csv = pd.read_csv("user.csv", header=0, index_col=‘No', sep='\s|,', engine='python')

but output is not my ideal one.I cannot understand how to make columns is not resignated like Tom_A・Tom_B・Juddy_A which is in csv.

How should I fix my codes?

Upvotes: 1

Views: 61

Answers (1)

user3483203
user3483203

Reputation: 51165

Setup

df = pd.DataFrame({'No': {0: 1, 1: 1, 2: 1, 3: 2, 4: 2, 5: 2}, 'User': {0: 'Tom', 1: 'Juddy', 2: 'Bob', 3: 'Tom', 4: 'Juddy', 5: 'Bob'}, 'A': {0: 100, 1: 89, 2: 99, 3: 80, 4: 79, 5: 88}, 'B': {0: 120, 1: 90, 2: 80, 3: 100, 4: 90, 5: 90}, 'C': {0: 110, 1: 100, 2: 90, 3: 100, 4: 80, 5: 95}, 'D': {0: 90, 1: 110, 2: 100, 3: 70, 4: 70, 5: 90}})

You want pivot_table:

out = df.pivot_table(index='No', columns='User')

       A              B              C               D
User Bob Juddy  Tom Bob Juddy  Tom Bob Juddy  Tom  Bob Juddy Tom
No
1     99    89  100  80    90  120  90   100  110  100   110  90
2     88    79   80  90    90  100  95    80  100   90    70  70

To get the prefix:

out.columns = out.columns.swaplevel(0,1).to_series().str.join('_')

   Bob_A  Juddy_A  Tom_A  Bob_B  Juddy_B  Tom_B  Bob_C  Juddy_C  Tom_C  Bob_D  Juddy_D  Tom_D
No
1      99       89    100     80       90    120     90      100    110    100      110     90
2      88       79     80     90       90    100     95       80    100     90       70     70

Upvotes: 3

Related Questions