0
$\begingroup$

I have this data:

enter image description here

I wanted to compare A and B for matches not by row but rather search A0 if it is in column B and so on. Moreover, I wanted to ignore the .AX in column A because it would not find any matches in column B anyway.

I used this, but it matches values row by row and it returns False or True. I would like to print the matches in a new Column C:

    df3['match'] = df3.A == df3.B

Thank you.

Improve this question
$\endgroup$

1 Answer 1

Reset to default
0
$\begingroup$

To clarify, this question is about comparing two columns to check if the 3-letter combinations match.

So, I would approach this in the following manner:

# Extract the 3-letter combinations from column a
df3["a normalised"] = df3["a"].str[:3]

# Then check if what is in `a normalised` is in column b 

b_matches = list(df3[df3[“b”].isin(list(df3[“a normalised”]))][“b”].unique())
df3.loc[:, "match"] = False

b_match_idx = df3[df3["a normalised"].isin(b_matches)].index

df3.at[np.array(b_match_idx),"match"] = True

EDIT: The parentheses have now been resolved. Also the .loc warning can now be mitigated.

Improve this answer
$\endgroup$
8
  • $\begingroup$ Thanks for the reply. However, I got a KeyError for column b. I guess there is a parenthesis problem in b_matches = list but I can't make it work. What do you think? $\endgroup$
    – Steven
    Jul 24, 2020 at 10:23
  • $\begingroup$ Thanks for the comment @Steven, my parentheses weren't correct, I have edited my answer accordingly. $\endgroup$
    – shepan6
    Jul 24, 2020 at 10:34
  • $\begingroup$ That worked, thanks a lot. I got 2 issues there: 1) It matches values "row by row" but it doesn't match, for example, A0 with B1, B2 and so on, meaning all values in A column are matched with all values in B column, I don't know if I explained correctly. 2) The script works but it returns also this: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead $\endgroup$
    – Steven
    Jul 24, 2020 at 11:23
  • $\begingroup$ So what the code should do is that it set the values in the match column to be true if any of the values in the a normalised exists in the b column. It doesn't tell you which ones it matches with. Do you want me to include this in the implementation? The warning comes about from the df3["match"] line. I will properly edit this again when I hear from you. $\endgroup$
    – shepan6
    Jul 24, 2020 at 11:33
  • 1
    $\begingroup$ Got you thank you for the comment @Steven, this should now have resolved that, I have obtained the indices where the 3-letter combinations from column a normalised exist in column b and then used the .at() method to convert those indices which do 'match' to become True. $\endgroup$
    – shepan6
    Jul 24, 2020 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.