0
$\begingroup$

May I know how to combine several rows into one single row after I used Pandas groupby function?

In below example, I would like to to group the data by Employee ID, Customer Last Name and Customer First Name. Then I want all his dependents' data listed in the same row.

Thanks a lot!

enter image description here

enter image description here

$\endgroup$
1
  • $\begingroup$ Could you provide data to reproduce your example? $\endgroup$
    – imitusov
    Oct 21, 2020 at 9:05

2 Answers 2

1
$\begingroup$

I think what you are looking for here is pandas.pivot(). It will transform your table from long to wide format. I first adapted your "Dep Relationship" column so that each child has a unique identifier within each Employee-ID.

Then called the function with the "Employee ID" as the index, the Relationship as the "column-generating variable" and the "Dep Name" and "Dep birth date" as the values filling the wide table.

Employee_ID = [1234, 1234, 1234, 1234, 5678, 5678]
First_name = ["Mohammed", "Mohammed", "Mohammed", "Mohammed", "Tony", "Tony"]
Last_name = ["Formal","Formal", "Formal", "Formal", "Smith", "Smith"]
Dep_first_name= ["Adm", "Fareeda", "Oasis", "Rain", "Anna", "Theo"]
Dep_relationship= ["Child1", "Child2", "Child3","Child4", "Child1", "Child2"]
Dep_birth_date = ["20/01/2006", "08/01/2001", "30/01/2013", "04/01/1999", "04/08/2008", "26/12/2004"]

zipped = list(zip(Employee_ID ,First_name , Last_name, Dep_first_name, Dep_relationship, Dep_birth_date ))
df = pd.DataFrame(zipped, columns=["Employee_ID ","First_name", "Last_name", "Dep_first_name", "Dep_relationship", "Dep_birth_date "])
pd.pivot(df, index="Employee_ID ", columns="Dep_relationship", values=["Dep_first_name","Dep_birth_date "])
$\endgroup$
0
$\begingroup$

You should use the function drop_duplicates :

Define the columns you take into account:

unique_columns = Seq("Employee Id", "Customer Last name", "Customer First Name")

But it seems to me that the column Employee Id is enough, as you have only one person per Employee Id so you can define :

unique_columns = Seq("Employee Id", "Customer Last name", "Customer First Name")

Then you can write:


import pandas as pd 

unique_columns = Seq("Employee Id", "Customer Last name", "Customer First Name")
df = df.drop_duplicates(subset=unique_columns)
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.