How to pivot a table based on the values of one column

let’s say I have the below dataframe:

dataframe = pd.DataFrame({'col1': ['Name', 'Location', 'Phone','Name', 'Location'], 
'Values': ['Mark', 'New York', '656','John', 'Boston']})

which looks like this:

col1           Values
Name           Mark
Location       New York
Phone          656
Name           John
Location       Boston

As you can see I have my wanted columns as rows in col1 and not all values have a Phone number, is there a way for me to transform this dataframe to look like this:

Name    Location    Phone
Mark    New York    656
John    Boston      NaN

I have tried to transpose in Excel, do a Pivot and a Pivot_Table:

pivoted = pd.pivot_table(data = dataframe, values='Values', columns='col1')

But this comes out incorrectly. any help would be appreciated on this.

NOTES: All new section start with the Name value and end before the Name value of the next person.

Answer

Create a new index using cumsum to identify unique sections then do pivot as usual…

df['index'] = df['col1'].eq('Name').cumsum()
df.pivot('index', 'col1', 'Values')

col1   Location  Name Phone
index                      
1      New York  Mark   656
2        Boston  John   NaN

Advertisement

Answer