Pandas compare single value to list

Bartosz 0
Viewed 39

first I show you my code

df = pd.read_pickle('domain_list.pkl')
cc_base= pd.read_pickle('cc_base.pkl')

Here is how single record from each database looks like:

print(cc_base.iloc[100])

Domain                  kinresto.com
Phone                    12395550108
Alternative phone 2    13195550115.0
Alternative phone 3    12525550126.0
Alternative phone 4    13075550133.0
Alternative phone 5              NaN

print(cc_base.iloc[102])

Domain                       msg.com
Phone                  13075550133.0
Alternative phone 2    13195550115.0
Alternative phone 3    12395550108.0
Alternative phone 4              NaN
Alternative phone 5              NaN

and row from the second database

print(df.iloc[44556])

Phone                            12395550108
counts                                     2
Domain_list    ["['msg.com'", " 'kinresto.com']"]

I would like to check for which one domain from domain_list the phone df['Phone'] is a main number in cc_base['Phone']

Row from result dataframe should looks like this

Phone                                             12395550108
counts                                                      2
Domain_list                ["['msg.com'", " 'kinresto.com']"]
Main_phone_for_domain                        ["kinresto.com"]
Alternative_for                                   ["msg.com"]

I know how ugly looks Domain list

.replace("'", "").replace(']', '').replace('[', '').replace('"', '').replace(' ', '')

The longest domain_list has 3000 items

list dataframe pandas python
1 Answers

Firstly, to ensure Domain_list values are comparable, lets remove extraneous characters first:

import re
def clean_list (domains):
    return [re.sub("['\[\] ]",'',dom) for dom in domains]

For the given example row, here is the effect of cleaning (before and after contents are shown):

>>> df
         Phone  counts                     Domain_list
0  12395550108       2  [['msg.com',  'kinresto.com']]
>>> df.Domain_list = df.Domain_list.apply(clean_list)
>>> df
         Phone  counts              Domain_list
0  12395550108       2  [msg.com, kinresto.com]

To find the main domain based on the phone number, I used the following function and use in apply:

def find_main_domain(phone):
    main_domain = cc_base[cc_base.Phone == phone]['Domain']
    return main_domain

df['Main_phone_for_domain'] = df.Phone.apply(find_main_domain)

At this stage, here is df's content:

>> df
         Phone  counts              Domain_list Main_phone_for_domain
0  12395550108       2  [msg.com, kinresto.com]          kinresto.com

For the final column, we simply include all the rest of the domains as below:

df['Alternative_for'] = df.apply(lambda row: [x for x in row.Domain_list if x != row.Main_phone_for_domain], axis=1)

Here is the final content:

>>> df
         Phone  counts              Domain_list Main_phone_for_domain Alternative_for
0  12395550108       2  [msg.com, kinresto.com]          kinresto.com       [msg.com]
Heelara0
Related