Comparing 2 columns in Pandas DataFrame and populating a 3rd column

Question

I have a DF that has 2 int columns inside, 'CNT' and 'STG_TABLE_CNT'. I want to add a new column 'IS_MATCH' that returns 'Y' if 'CNT' and 'STG_TABLE_CNT' have the same value, or 'N' if they do not.

I tried this:

if result['CNT'] == result['STG_TABLE_CNT']:
    result['IS_MATCH'] = 'Y'
else:
    result['IS_MATCH'] = 'N'

but that is throwing the following error:

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

I realize it is searching for equality (True/False) I am just not sure how to get around that to return 'Y' or 'N' instead

Chris Adams · Accepted Answer · 2019-04-09 18:04:23Z

1

Use numpy.where, it's faster than apply or itterating:

import numpy as np

df['IS_MATCH'] = np.where(result['CNT'].eq(result['STG_TABLE_CNT']), 'Y', 'N')

answered Apr 9, 2019 at 18:04

Chris Adams

18.7k4 gold badges26 silver badges44 bronze badges

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

Comments

Quang Hoang · Accepted Answer · 2019-04-09 18:15:11Z

1

result['CNT'] == result['STG_TABLE_CNT'] gives you a whole series, and pandas is complaining that it does not know how to convert that series into True or False. What you really want is something like

result['IS_MATCH'] = (result['CNT'] == result['STG_TABLE_CNT']).\
                         apply(lambda x: 'Y' if x else 'N')

or

result['IS_MATCH'] = (result['CNT'] == result['STG_TABLE_CNT']).\
                         map({True: 'Y', False: 'N'})

edited Apr 9, 2019 at 18:15

answered Apr 9, 2019 at 18:01

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

1 Comment

JD2775 Over a year ago

thank you, your explaining as to why the error was happening was useful as well

Collectives™ on Stack Overflow

Comparing 2 columns in Pandas DataFrame and populating a 3rd column

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related