concat strings from different columns and get unique values in pandas df

MTALY 0
Viewed 33

I have an issue while trying to concat and use set on multiple columns.

This is an example df:

df = pd.DataFrame({'customer id':[1,2,3,4,5],
                   'email1':['ex11@email.com',np.nan,'ex31@email.com',np.nan, np.nan],
                   'email2':['ex11@email.com'   ,np.nan,'Ex3@email.com','ex4@email.com', np.nan],
                   'email3':['ex12@email.com',np.nan,'ex3@email.com','ex4@email.com', 'ex5@email.com']})

df:

   customer id          email1          email2          email3
0            1  ex11@email.com  ex11@email.com  ex12@email.com
1            2             NaN             NaN             NaN
2            3  ex31@email.com   Ex3@email.com   ex3@email.com
3            4             NaN   ex4@email.com   ex4@email.com
4            5             NaN             NaN   ex5@email.com

I would like to create a new column with unique values from all columns (email1, email2 & email3) so the created columns will have a set of unique emails per customer, some emails have different cases (upper, lower .. etc)

This is what I did so far:

df['ALL_EMAILS'] = df[['email1','email2','email3']].apply(lambda x: ', '.join(x[x.notnull()]), axis = 1)

This took about 3 minutes on a df of > 500K customers!

then I created a function to handle the output and get the unique values if the cell is not null:

def checkemail(x):
    if x:
        #to_lower
        lower_x = x.lower()
        y= lower_x.split(',')
        return set(y)

then applies it to the column:

df['ALL_EMAILS'] = df['ALL_EMAILS'].apply(checkemail)

but I got wrong output under ALL_EMAILS column!

   ALL_EMAILS
0  { ex11@email.com, ex11@email.com,  ex12@email.com}  
1                                               None  
2                   { ex3@email.com, ex31@email.com}  
3                    { ex4@email.com, ex4@email.com}  
4                                    {ex5@email.com}
concatenation pandas python-3.x python
2 Answers

Try work on the values directly instead of joining them then split again:

df['ALL_EMAILS'] = df.filter(like='email').apply(lambda x: set(x.dropna().str.lower()) or None, axis=1)

Output:

   customer id          email1          email2          email3                        ALL_EMAILS
0            1  ex11@email.com  ex11@email.com  ex12@email.com  {ex12@email.com, ex11@email.com}
1            2             NaN             NaN             NaN                              None
2            3  ex31@email.com   Ex3@email.com   ex3@email.com   {ex31@email.com, ex3@email.com}
3            4             NaN   ex4@email.com   ex4@email.com                   {ex4@email.com}
4            5             NaN             NaN   ex5@email.com                   {ex5@email.com}
Quang Hoang0

Lets filter the email like columns then stack to convert to series then transform into lowercase and aggregate with set on level=0

email = df.filter(like='email')
df['all_emails'] = email.stack().str.lower().groupby(level=0).agg(set)

   customer id          email1          email2          email3                        all_emails
0            1  ex11@email.com  ex11@email.com  ex12@email.com  {ex11@email.com, ex12@email.com}
1            2             NaN             NaN             NaN                               NaN
2            3  ex31@email.com   Ex3@email.com   ex3@email.com   {ex3@email.com, ex31@email.com}
3            4             NaN   ex4@email.com   ex4@email.com                   {ex4@email.com}
4            5             NaN             NaN   ex5@email.com                   {ex5@email.com}
userawQR0
Related