CODEWITHSUNDEEP
pythonpandaspivot
ombk
ombk

Reputation: 2111

Turn rows duplicates into columns/headers

    Category    Value
0   name    ggg
1   street  zzz
2   houseNumber 141
3   postalCode  4500
4   city    TRE
5   country GGG
6   lat 59.932996
7   lng 43.054331
8   name    BBB
9   street  TTT
10  houseNumber 175
11  postalCode  1352
12  city    FRT
13  country GFD
14  lat 20.741285
15  lng 47.336491

How do I turn the repetitive rows into headers of the table, and the Values into rows.

There is a huge chance this is a duplicate but nothing of the methods is working for me.

#output

  name street houseNumber postalCode ....
1  ggg ...
2  kkk ...
3  bbb ...
4
5
...

Upvotes: 0

Views: 204

Answers (3)

Stef
Stef

Reputation: 30579

If each block is exactly 8 rows long, you can simply use:

df.set_index(df.index//8).pivot(columns='Category', values='Value')

Result:

Category city country houseNumber        lat        lng name postalCode street
0         TRE     GGG         141  59.932996  43.054331  ggg       4500    zzz
1         FRT     GFD         175  20.741285  47.336491  BBB       1352    TTT

Upvotes: 1

Umar.H
Umar.H

Reputation: 23099

I think pd.pivot with a custom index based off category can work.

df1 = pd.pivot(df.assign(idx=df.groupby('Category').cumcount()),
               index='idx',
               columns='Category')

print(df1)

enter image description here

Upvotes: 2

BENY
BENY

Reputation: 323226

Let us try use ffill create the addition column for pivot

df['name'] = df.Value.where(df.Category.eq('name')).ffill()
out = df.query('Category!=name').pivot(index='name',columns='Category',values='Value')
Out[106]: 
Category city country houseNumber        lat        lng name postalCode street
name                                                                          
BBB       FRT     GFD         175  20.741285  47.336491  BBB       1352    TTT
ggg       TRE     GGG         141  59.932996  43.054331  ggg       4500    zzz

Upvotes: 3

Related Questions