How can we detect inconsistency in pandas dataframe?

user_6072229 0
Viewed 1438

I have the following dataframe for which I want to detect if the results are inconsistent: 

>>> import pandas as pd
>>> import numpy as np
>>> df = pd.DataFrame(np.array([[1, 2, 3, 1], [4, 5, 6, 0], [7, 8, 9, 0], [4, 5, 6, 1], [1, 2, 3, 1]]),
...                    columns=['a', 'b', 'c', 'result'])
>>> df
   a  b  c  result
0  1  2  3       1
1  4  5  6       0
2  7  8  9       0
3  4  5  6       1
4  1  2  3       1

My goal is to drop those rows that show different results for the same values (id 1 and 3 should be dropped). I know I can detect duplicates and loop over the results

>>> df[df.duplicated(['a', 'b', 'c'], keep=False)]
   a  b  c  result
0  1  2  3       1
1  4  5  6       0
3  4  5  6       1
4  1  2  3       1

But I was wondering if there's a more pythonic way of getting (and dropping) those indexes.

pandas python
4 Answers

I would compute the number of unique values per unique set of a, b, c values:

to_keep = df[df.groupby(['a', 'b', 'c'])['result'].transform('nunique') == 1]

it gives:

   a  b  c  result
0  1  2  3       1
2  7  8  9       0
4  1  2  3       1
user_35452730

One way would be to use a bitwise operator

>>> duplicates = df[df.duplicated(['a', 'b', 'c'], keep=False) & ~df.duplicated(['a', 'b', 'c', 'result'], keep=False)]
>>> duplicates
   a  b  c  result
1  4  5  6       0
3  4  5  6       1
>>> df.drop(duplicates.index)
>>> df
   a  b  c  result
0  1  2  3       1
2  7  8  9       0
4  1  2  3       1

Not sure if this is a clear solution for dataframes with a lot of columns

user_60722290

Group and then discard results which have more than one values for result

a = df.groupby(['a','b','c'])['result'].apply(set).reset_index()
a_filter = a[a.result.apply(len)==1].copy()
a_filter.result = a.result.apply(lambda x:next(iter(x)))
a_filter

Gives output:

    a   b   c   result
0   1   2   3   1
2   7   8   9   0
user_82933090

I suggest not drop index values by mask, because it working only for unique index values, if some duplicates in remove also rows which not need.

Better is filtering by inverted mask by ~:

cols = ['a','b','c']
mask1 = df.duplicated(cols, keep=False) 
mask2 = ~df.duplicated(cols + ['result'], keep=False)

df = df[~(mask1 & mask2)]
print (df)
   a  b  c  result
0  1  2  3       1
2  7  8  9       0
4  1  2  3       1

Or use | for bitwise OR with swap ~ from mask2 to mask1:

cols = ['a','b','c']
mask1 = ~df.duplicated(cols, keep=False) 
mask2 = df.duplicated(cols + ['result'], keep=False)

df = df[(mask1 | mask2)]
print (df)
   a  b  c  result
0  1  2  3       1
2  7  8  9       0
4  1  2  3       1
user_29010020
Related