Join 2 columns with list values in pandas(Avoiding duplicates and NaN)

user_15022675 0
Viewed 121

I have pandas dataframe as follows,

loc_1                               loc_2                             

[mumbai, gujarat, sri lanka]        [chennai, UP]
[Goa, telangana]                    [Kashmir, Goa, Rajkot]
NaN                                 [Bihar, Orissa]

I want to create a new column that is a combination of both the above columns, I did search other similar questions but the issue I am facing is that,

When I do,

data['locations'] = data['loc_1'] + data['loc_2']

Output
--------
loc_1                               loc_2                       locations

[mumbai, gujarat, sri lanka]        [chennai, UP]                [mumbai, gujarat, sri lanka,chennai, UP]   
[Goa, telangana]                    [Kashmir, Goa, Rajkot]       [Goa, telangana,Kashmir, Goa, Rajkot]
NaN                                 [Bihar, Orissa]              NaN

Issue

As you can see above, there are duplicate values as well as NaN values formed. How to avoid them?

Remember

The original dataset contains values in list, str and NaN format.

Dataset:


loc = pd.DataFrame({
'loc_1': [['mumbai', 'gujarat', 'sri lanka'],['Goa', 'telangana'],np.nan],
'loc_2':[['chennai','UP'],['kashmir','goa','rajkot'],['bihar','orissa']],
'loc_3':['Chennai','Bangalore','Vizag']

})
pandas dataframe list python
4 Answers

First join values with replace NaNs (floats) to empty lists:

data['locations'] = data['loc_1'].apply(lambda x: [] if isinstance(x, float) else x) + data['loc_2']

And then remove duplicates with same order like original by converting to dictionaries by dict.fromkeys:

data['locations'] = data['locations'].apply(lambda x: list(dict.fromkeys(x)))

If the order is not important you can use a set:

data['locations'] = data['locations'].apply(lambda x: list(set(x)))
user_29010020

If you use loc.fillna("", inplace=True), then additions containing empty values should not result in NaNs anymore.

To filter duplicates from a column containing lists, use:

loc['locations'] = loc['locations'].apply(lambda locs: list(set(locs)))
user_55877360

List comprehension can be quite quick. Lets try

   data['location']=[list(set([] if isinstance(x, float) else x).union(set(y))) for x,y in zip (data['loc_1'],data['loc_2'])]

 

      loc_1                     loc_2      loc_3  \
0  [mumbai, gujarat, sri lanka]  [chennai, UP, sri lanka]    Chennai   
1              [Goa, telangana]    [kashmir, goa, rajkot]  Bangalore   
2                           NaN           [bihar, orissa]      Vizag   

                                    location  
0  [chennai, UP, gujarat, mumbai, sri lanka]  
1     [telangana, rajkot, goa, kashmir, Goa]  
2                            [bihar, orissa]
user_89869750

Step 1 : replace all Nans with empty list so it takes care of NAN

    #Function to remove Nans
    def isnan(x):
       if isinstance(x, (int,  float, complex)) and math.isnan(x):
          return True

    # Apply to your data
    loc1 = loc.apply(lambda x:x.apply(lambda x:[] if isnan(x) else x))

  1. Step 2: After concatenating take the set, to remove duplicates

     loc1['location'] = (loc1['loc_1']  + loc1['loc_2']).apply(set)

If you want the final results not to be set please convert to list

user_140490870
Related