Remove duplicates and add some column using python pandas

user_12869655 0
Viewed 55

Is it possible to do the followings using Python Pandas?

I have a csv file like the table A.

TABLE A
------------------------------------------------
Name               Email
------------------------------------------------
Hinckley Joel      hjoel@mail.com
Hinckley Joel      hjoel@mail.com 
Hinckley Joel      hinkleyjoel@mail.com
Joel Hinckley      hinkjoel@mail.com
Siegel Allison     sallison@mail.com
Nielsen Tami       ntami@mail.com
Nielsen Tami       ntami@gmail.com
...

I want to remove the rows with the duplicated name, also I want to add a new column "Secondary Email".
The secondary email will be the first email of the duplicated rows.

The final table I want to make is Table B.

TABLE B
-----------------------------------------------------------
Name               Email                   Secondary Email
-----------------------------------------------------------
Hinckley Joel      hjoel@mail.com          hinkleyjoel@mail.com
Siegel Allison     sallison@mail.com
Nielsen Tami       ntami@mail.com

As you can see from Table A and B, I want to consider as a same person even if the first and last name was replaced. (ex : "Hinckley Joel" and "Joel Hinckley")
Also, I want to take the secondary email (ex : hinkleyjoel@mail.com) and add it to the new column.

Thank you in advance.

pandas python
3 Answers

This is pivoting with two columns, but you need to remove duplicates:

(df.drop_duplicates()
   .assign(col=lambda x: x.groupby("Name").cumcount())
   .pivot(index='Name', columns='col', values='Email')
   .add_prefix('Email_').reset_index()
)

Output:

col            Name            Email_0               Email_1
0     Hinckley Joel     hjoel@mail.com  hinkleyjoel@mail.com
1     Joel Hinckley  hinkjoel@mail.com                   NaN
2      Nielsen Tami     ntami@mail.com       ntami@gmail.com
3    Siegel Allison  sallison@mail.com                   NaN
user_42384080

Not as pretty and tidy as @QuangHoang but the same concept:

df_dup = df.drop_duplicates()
df_out = df_dup.set_index(['Name', df_dup.groupby('Name')['Email'].cumcount()])
df_out = df_out.unstack()
df_out.columns = [f'Secondary Email' if j != 0 else f'{i}' for i, j in df_out.columns]
df_out.reset_index()

Output:

            Name              Email       Secondary Email
0   Hinckley Joel     hjoel@mail.com  hinkleyjoel@mail.com
1   Joel Hinckley  hinkjoel@mail.com                   NaN
2    Nielsen Tami     ntami@mail.com       ntami@gmail.com
3  Siegel Allison  sallison@mail.com                   NaN
user_63615310

By use of duplicated and simple masking and merger: But first, drop the duplicate rows since two rows can have same name and emial as you have mentioned in your question..

df.drop_duplicates(inplace=True)
duplicates = df['Name'].duplicated()
df[~duplicates].merge(df[duplicates].drop_duplicates(subset=['Name']).rename(columns={'email':'second email'}), how='left')

               Name            email               second email
0     Hinckley Joel     hjoel@mail.com  hinkleyjoel@mail.com
1     Joel Hinckley  hinkjoel@mail.com                   NaN
2      Nielsen Tami     ntami@mail.com       ntami@gmail.com
user_91363480
Related