I have a dataframe like this:
where the name and the id is repeated, some information change, and I want to keep all in a single row.
Responsable Id First Name Last Name Homephone Workphone Email
1 Sally Sharrick
100 Angela Beale 9153426011 Angela.beale@gmail.com
100 Angela Beale 3055881122 3052435552 Angela.beale@gmail.com
100 Angela Beale 3055881122 9153426011 Angela.beale@gmail.com
100 Angela Beale 3055881122 michael.beale@gmail.com
1000 Ayishah Robinson 7707454800
1000 Ayishah Robinson 7707454800 ayishah.robinson@yahoo.com
10000 Jennifer Bacotti 3057267672 jenifer.bacotti@gmail.com
10001 Monica Plata 3056081805 monica.plata@gmail.com
10003 Martin Ravila
10004 Martin Rovira 7862034321
10004 Martin Rovira 7862034321 martin.rovira@gmail.com
10006 Cesar Hernandez 3059875822 cesar.hernandez@gmail.com
10008 Alvaro Garrido 7863881770 alvaro.garrido@gmail.com
10008 Alvaro Garrido 7863881770 Alvaroh.garrido@gmail.com
10008 Alvaro Garrido 7863881770 7863881770 alvaro.garrido@gmail.com
I want to join the reapeated information for the same person and create a new column for those different. something like this:
Responsable Id First Name Last Name Homephone Workphone Workphone 2 Email Email 2
1 Sally Sharrick
100 Angela Beale 3055881122 9153426011 3052435552 Angela.beale@gmail.com michael.beale@gmail.com
1000 Ayishah Robinson 7707454800 ayishah.robinson@yahoo.com
10000 Jennifer Bacotti 3057267672 jenifer.bacotti@gmail.com
10001 Monica Plata 3056081805 monica.plata@gmail.com
10004 Martin Rovira 7862034321 martin.rovira@gmail.com
10006 Cesar Hernandez 3059875822 cesar.hernandez@gmail.com
10008 Alvaro Garrido 7863881770 7863881770 alvaro.garrido@gmail.com Alvaroh.garrido@gmail.com
I have tried with:
search_data = search_data.groupby(['Responsable Id', 'First Name', 'Last Name'], as_index=False).agg(" ".join)
but this conserved the repeated information.
and
search_data = search_data.groupby(['Responsable Id', 'First Name', 'Last Name'], as_index=False).first()
but it only merge this first element it find
any help is aprecciated.