PHPFixing
  • Privacy Policy
  • TOS
  • Ask Question
  • Contact Us
  • Home
  • PHP
  • Programming
  • SQL Injection
  • Web3.0

Tuesday, November 1, 2022

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

 November 01, 2022     dataframe, pandas, pivot, python     No comments   

Issue

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.


Solution

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


Answered By - Shubham Sharma
Answer Checked By - Timothy Miller (PHPFixing Admin)
  • Share This:  
  •  Facebook
  •  Twitter
  •  Stumble
  •  Digg
Newer Post Older Post Home

0 Comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Total Pageviews

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...

Subscribe To

Posts
Atom
Posts
Comments
Atom
Comments

Copyright © PHPFixing