Validate rows from a Pandas dataframe are equal between values in columns

Question

Say I have a pandas DataFrame as below:

   C30    C25    C20    C15    C10
1  AJA    EJE    IJI    OJO    UJU
2  AJA    EJE    IJI    OJO    UJU
3  AJA    EJE    IJI    OJO    UJU
4  ABA    EBE    IBI    OBO    UBU
5  ABA    EBE    IBI    OBO    UBU
6  ABA    EBE    IBI    OBO    UNU
7  BOB    BIB    BEB    BAB    BUB

I would like to validate all the rows that have equal values in C30 have also equal values in C25, C20, C15 & C10.

I have approximately 60,000 rows with a lot of variation in the column values, if elements in column C30 are equal then the corresponding column C25 elements should also be equal and so on.

For the example, validation should shows a mismatch because C10 value in row 6 is not equal to 4 & 5 rows. What's the most efficient way to do this?

yes, right answer is yours. I think I changed by mistake but I returned it to yours. Thanks for your help. Your proposal was exactly what I wanted. — XaHUaR
– XaHUaR, Commented Apr 9, 2021 at 13:35

jezrael · Accepted Answer · 2021-04-01 07:25:26Z

1

You can use DataFrameGroupBy.nunique and compare for not equal 1 by DataFrame.ne:

m = df.groupby('C30').nunique().ne(1)
print (m)
       C25    C20    C15    C10
C30                            
ABA  False  False  False   True
AJA  False  False  False  False
BOB  False  False  False  False

And then if need values if no matched use np.where:

i, c  = np.where(m)

L = list(zip(m.index[i], m.columns[c]))

if len(L) > 0:
    print ('mismatch')
    print (L)
mismatch
[('ABA', 'C10')]

answered Apr 1, 2021 at 7:25

jezrael

868k102 gold badges1.4k silver badges1.3k bronze badges

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

Comments

Vishnudev Krishnadas · Accepted Answer · 2021-04-01 08:04:46Z

1

Use DataFrame.duplicated to find rows that are not unique.

df['is_dup'] = df.duplicated(keep=False)

Output

   C30  C25  C20  C15  C10  is_dup
1  AJA  EJE  IJI  OJO  UJU    True
2  AJA  EJE  IJI  OJO  UJU    True
3  AJA  EJE  IJI  OJO  UJU    True
4  ABA  EBE  IBI  OBO  UBU    True
5  ABA  EBE  IBI  OBO  UBU    True
6  ABA  EBE  IBI  OBO  UNU   False
7  BOB  BIB  BEB  BAB  BUB   False

edited Apr 1, 2021 at 8:04

answered Apr 1, 2021 at 7:31

Vishnudev Krishnadas

11k2 gold badges29 silver badges58 bronze badges

2 Comments

jezrael Over a year ago

I think answer is wrong, not tested only mismatch because C10 value in row 6 is not equal to 4 & 5 rows.

Vishnudev Krishnadas Over a year ago

Isn't that what OP wanted @jezrael

Collectives™ on Stack Overflow

Validate rows from a Pandas dataframe are equal between values in columns

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related