Python Program for Column to Row Transpose using Pandas
Last Updated :
20 Jan, 2022
Given an Input File, having columns Dept and Name, perform an operation to convert the column values to rows. Name contains pipe separated values that belong to a particular department identified by the column Dept.
Attached Dataset: emp_data
Examples:
Input:
dept, name
10, Vivek|John
20, Ritika|Shubham|Nitin
30, Vishakha|Ankit
Output:
dept, name
10, Vivek
10, John
20, Ritika
20, Shubham
20, Nitin
30, Vishakha
30, Ankit
Method 1: Pythonic Way
Python
data = pd.read_csv(r 'GFG.txt' )
new = data[ "name" ]. str .split( "|" ,expand = True )
data[ "Name1" ] = new[ 0 ]
data[ "Name2" ] = new[ 1 ]
data[ "Name3" ] = new[ 2 ]
data.drop(columns = [ "name" ], inplace = True )
d_name1 = data[[ 'dept' , 'Name1' ]]
d_name2 = data[[ 'dept' , 'Name2' ]]
d_name3 = data[[ 'dept' , 'Name3' ]]
union_df = pd.concat([d_name1,d_name2,d_name3],ignore_index = True )
union_df.fillna('',inplace = True )
union_df[ 'name' ] = union_df[ 'Name1' ].astype( str ) + union_df[ 'Name2' ].astype( str ) + union_df[ 'Name3' ].astype( str )
union_df.drop([ 'Name1' , 'Name2' , 'Name3' ],axis = 1 ,inplace = True )
final_df = union_df[union_df[ 'name' ]! = '']
final_df.sort_values( 'dept' )
|
Output:
Note: Shortcoming of above method is when there are more than 3 names separated by |
Method 2: Exploring Pandas
Python
emp_df = pd.read_csv(r 'GFG.txt' )
emp_df1 = pd.DataFrame(emp_df.name. str .split( '|' ).to_list(),
index = emp_df.dept).stack()
emp_df1 = emp_df1.reset_index([ 0 , 'dept' ])
emp_df1.columns = [ 'Dept' , 'Name' ]
emp_df1
|
Output:
Method 3: The Pandas way: explode()
Python
df = pd.read_csv(r 'GFG.txt' )
df1 = df.assign(name = df[ 'name' ]. str .split( '|' )).explode( 'name' )
df1
|
Output:
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...