Ndtyjky

I have a pandas dataframe as below

id  name  Base   field1    field2           field3

1   AA     Y      Yes      Consumer         Not Applicable 

1   BB     N      Yes      Consumer         Not Applicable 

2   CC     Y      Yes      Consumer         Not Applicable 

2   DD     N      Yes      Not Applicable   Not Applicable 

2   EE     N      No       Not Applicable   Modified

3   FF     Y      Yes      Not Applicable   Applicable 

3   GG     N      Yes      Not Applicable   Not Applicable 

3   HH     N      Yes      Not Applicable   Not Applicable 

The expected result is to group this dataframe based on the ID column and check if the data on all the other columns are the same data in each group, and finally write the results.

I tried this to validate the data on each group but it always says TRUE

Code:

result_list=

for col in df.columns:

        result = df.groupby(level=0)[col].apply(lambda x: len(set(x))==1)

        result_list.append(result)



final = pd.concat(result_list,1)

The expected result is

id  name     field1   field2           field3           Error

1   AA       Yes      Consumer         Not Applicable   Pass 

1   BB       Yes      Consumer         Not Applicable   Pass

2   CC       Yes      Consumer         Not Applicable   field1, field2, field3 mismatch for ID: 2

2   DD       Yes      Not Applicable   Not Applicable   field1, field2, field3 mismatch for ID: 2

2   EE       No       Not Applicable   Modified         field1, field2, field3 mismatch for ID: 2

3   FF       Yes      Not Applicable   Applicable       field3 mismatch for ID: 3

3   GG       Yes      Not Applicable   Not Applicable   field3 mismatch for ID: 3

3   HH       Yes      Not Applicable   Not Applicable   field3 mismatch for ID: 3

Any help on this?

You may get what you want with the code (assuming that df has index named id):

def handler(df):

    for col in ['field1', 'field2', 'field3']:

        if df.loc[:, col].nunique() > 1:

            return 'error in {} for id {}'.format(col, df.index[0])

    else:

        return 'pass'



result = df.groupby(level=0).apply(handler)

result = df.reset_index().merge(result.to_frame().reset_index(), on='id')

result is:

   id name field1          field2          field3                         0

0   1   AA    Yes        Consumer  Not Applicable                      pass

1   1   BB    Yes        Consumer  Not Applicable                      pass

2   2   CC    Yes        Consumer  Not Applicable  error in field1 for id 2

3   2   DD    Yes  Not Applicable  Not Applicable  error in field1 for id 2

4   2   EE     No  Not Applicable        Modified  error in field1 for id 2

5   3   FF    Yes  Not Applicable      Applicable  error in field3 for id 3

6   3   GG    Yes  Not Applicable  Not Applicable  error in field3 for id 3

7   3   HH    Yes  Not Applicable  Not Applicable  error in field3 for id 3

EDIT - minor editions in handler

def handler(df):

    cols = list()

    for col in ['field1', 'field2', 'field3']:

        if df.loc[:, col].nunique() > 1:

            cols.append(col)

    if cols:

        return 'error in {} for id {}'.format(', '.join(cols), df.index[0])

    else:

        return 'pass'

You could groupby id and then agg each column calculating the number of unique values per group and then you know there is a mistake where that number is greater than 1:

df[df.columns.drop('name')].groupby('id').agg(lambda x: len(x.unique()))>1

With this output, based on which you could construct your string.

    field1  field2  field3

id          

1   False   False   False

2   True    True    True

3   False   False   True