CODEWITHSUNDEEP
pythonpandas
CGully
CGully

Reputation: 79

if string in pandas series contains a string from another pandas dataframe

Struggling newbie. If I have two pandas dataframes something like :

    import pandas as pd
    data = {'col1': ['black sphynx bob','brown labrador','grey labrador mervin',
            'brown siamese cat','white siamese']}
    desc_df = pd.DataFrame(data=data)

    catg = {'dog': ['labrador','rottweiler',
            'beagle'],'cat':['siamese','sphynx','ragdoll']}

    catg_df = pd.DataFrame(data=catg)

    desc_df
               col1
    0      black spyhnx bob
    1        brown labrador
    2  grey labrador mervin
    3     brown siamese cat
    4         white Siamese

   catg_df
         cat         dog
   0  siamese    labrador
   1   sphynx  rottweiler
   2  ragdoll      beagle

I'd like to end up with desc_df dataframe:

           col1             col2
0      black spyhnx bob     cat
1        brown Labrador     dog
2  grey labrador Mervin     dog
3     brown siamese cat     cat 
4         white Siamese     cat

I thought I maybe could use the apply method with a function. I'm just not 100% confident if that is the best way to approach this and how exactly it could be done. Many thanks

Upvotes: 1

Views: 48

Answers (2)

BENY
BENY

Reputation: 323226

You can using str.contains + np.where

desc_df['col2']=np.where(desc_df.col1.str.contains(catg_df.cat.str.cat(sep='|')),'cat','dog')
desc_df
Out[1538]: 
                   col1 col2
0      black spyhnx bob  dog
1        brown labrador  dog
2  grey labrador mervin  dog
3     brown siamese cat  cat
4         white siamese  cat

OK update for multiple condition 

d=catg_df.apply('|'.join).to_dict()
desc_df.col1.apply(lambda x : ''.join([z if pd.Series(x).str.contains(y).values else '' for z,y in d.items()]))
Out[1568]: 
0       
1    dog
2    dog
3    cat
4    cat
Name: col1, dtype: object

Upvotes: 1

jpp
jpp

Reputation: 164673

One way is to create a dictionary mapping animals to type.

Then use pd.Series.apply with next and a generator expression:

d = {i: k for k in catg_df for i in catg_df[k].unique()}

desc_df['col2'] = desc_df['col1'].apply(lambda x: next((d.get(i) for i in x.split() \
                                                        if i in d), None))

print(desc_df)

#                    col1 col2
# 0      black sphynx bob  cat
# 1        brown labrador  dog
# 2  grey labrador mervin  dog
# 3     brown siamese cat  cat
# 4         white siamese  cat

Upvotes: 1

Related Questions