Transpose rows to column python

Question

I have data like this

                         Column1  Column2    Column3
0  This Sushi is Awesome          NaN        NaN
1  NaN                            Id:        2261
2  NaN                            City:      Tokyo
3  NaN                            Food:      Positive
4  NaN                            Price:     NaN
5  This food is really expensi... NaN        NaN
6  NaN                            Id:        3`
7  NaN                            City:      Osaka
8  NaN                            Food:      Negative
9  NaN                            Price:     Negative

i wrote code like this but i got error

pivoted = data.pivot(index='Column1',columns='Column2', values='Column3')

ValueError: Index contains duplicate entries, cannot reshape

pivot_table also doesnt work

I want to have output like this

0                                 Id         City      Food      Price
1  This Sushi is Awesome          2261       Tokyo     Positive  NaN
2  This food is really expensi... 3          Osaka     Negative  Negative

Use ffill on Column1, then look at SO post on "How to pivot a dataframe". — Scott Boston
– Scott Boston, Commented Jan 22, 2019 at 13:39

jezrael · Accepted Answer · 2019-01-22 14:04:10Z

1

Use pre processing before pivot - check missing values per Column1, then forward fillling, remove : from Column2 by rstrip and last filter by boolean indexing:

m = df['Column1'].isnull()
df['Column1'] = df['Column1'].ffill()
df['Column2'] = df['Column2'].str.rstrip(':')

pivoted = df[m].pivot(index='Column1',columns='Column2', values='Column3')
print (pivoted)
Column2                         City      Food    Id     Price
Column1                                                       
This Sushi is Awesome          Tokyo  Positive  2261       NaN
This food is really expensive  Osaka  Negative    3`  Negative

edited Jan 22, 2019 at 14:04

answered Jan 22, 2019 at 14:00

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mr_and_Mrs_D Over a year ago

Damn I was trying :P

Collectives™ on Stack Overflow

Transpose rows to column python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related