Tranpose data and keep first column as a column names and store second column as rows

user_18116158 0
Viewed 33

I want to transpose my data:

      title         values
0          Ad ID:        5702319
1       Category:  Cars for Sale
2      Locations:     Phnom Penh
3         Posted:      19-Aug-21
4           View:          12517
500        Ad ID:        7023451
..             ...            ...
566     Condition:           Used
567     Body Type:         Pickup
568          Fuel:         Petrol
569  Transmission:           Auto
570         Price:             $2

so that keeps use title as a column name and values as a row. The 'title' column contains unique categories about cars. so I want data like:

 ID.   | Category|  Location|Posted ...    |Transmission|Price|
______________________________________________________________
5702319| Car-sale| PhnomPenh| 22.01.2021...|Auto       |$24000
7023451| Car-sale| PhnomPenh| 21.12.2021...|Manual.    |$7000
pandas dataframe
1 Answers

If Ad ID: value always is first per groups for created new row in ouput use Series.str.rstrip for remove last : with create groups by compare Ad ID with cumulative sum by Series.cumsum and last pivoting by DataFrame.pivot:

df1 = (df.assign(title = df['title'].str.rstrip(':'),
                g = lambda x: x['title'].eq('Ad ID').cumsum())
         .pivot('g','title','values')
         )
print (df1)
title    Ad ID Body Type       Category Condition    Fuel   Locations  \
g                                                                       
1      5702319       NaN  Cars for Sale       NaN     NaN  Phnom Penh   
2      7023451    Pickup            NaN      Used  Petrol         NaN   

title     Posted Price Transmission   View  
g                                           
1      19-Aug-21   NaN          NaN  12517  
2            NaN    $2         Auto    NaN
user_29010020
Related