Pandas: check if string value in one column is part of string of another column in same row of dataframe - current script returning all Yes

Question

I have the following dataframe:

df = pd.DataFrame([['50030', '36 @ 3159 W/270, LWD[GR,RES,PWD] @ 4015', '3159'], 
                   ['50030', '36 @ 3159 W/270, LWD[GR,RES,PWD] @ 4015', '3994'],
                   ['50030', '36 @ 3159 W/270, LWD[GR,RES,PWD] @ 4015', '5401'],
                   ['50030', '26 @ 3994, LWD[GR,RES,PWD] @ 5430, 20 @ 5401', '3159'],
                   ['50030', '26 @ 3994, LWD[GR,RES,PWD] @ 5430, 20 @ 5401', '3994'],
                   ['50030', '26 @ 3994, LWD[GR,RES,PWD] @ 5430, 20 @ 5401', '5401']],
                  columns = ["WKEY", "Description", "DEPTH"])

I want to compare the value in the DEPTH column (which is a string value) to the string in the Description column only for the same row. A new column called "Compare" would have a yes or no depending on if it exists.

Based on this post: python pandas - Check if partial string in column exists in other column, I tried this code:

df['Compare'] = df['DEPTH'].apply(lambda x: 'Yes' if df['Description'].str.contains(x).any() else 'No')

However, it returns all rows with Yes:

It should be Yes, No, No, No, Yes, Yes.

The ultimate plan is to delete all rows which do not have the value in the DEPTH column also listed somewhere in the Description column in the same row.

I feel like I'm one step away from getting this to work, so any direction would be appreciated.

Thanks!

do you want to match the number at any position? or should it always be the number after the first @? — mozway
– mozway, Commented Nov 18, 2021 at 15:46

BENY · Accepted Answer · 2021-11-18 15:45:18Z

3

In your case

df['Compare'] = df.apply(lambda x: 'Yes' if x['DEPTH'] in x['Description'] else 'No',axis=1)
df
Out[133]: 
    WKEY                                   Description DEPTH Compare
0  50030       36 @ 3159 W/270, LWD[GR,RES,PWD] @ 4015  3159     Yes
1  50030       36 @ 3159 W/270, LWD[GR,RES,PWD] @ 4015  3994      No
2  50030       36 @ 3159 W/270, LWD[GR,RES,PWD] @ 4015  5401      No
3  50030  26 @ 3994, LWD[GR,RES,PWD] @ 5430, 20 @ 5401  3159      No
4  50030  26 @ 3994, LWD[GR,RES,PWD] @ 5430, 20 @ 5401  3994     Yes
5  50030  26 @ 3994, LWD[GR,RES,PWD] @ 5430, 20 @ 5401  5401     Yes

answered Nov 18, 2021 at 15:45

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pandas: check if string value in one column is part of string of another column in same row of dataframe - current script returning all Yes

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related