Comparing values from different rows in groupby

user_10239456 0
Viewed 250

I would like to print each time inconsistency where the a start is different from the end from the previous row, grouped by the 'id' column. In the following data, the last row would be a case of inconsistency.

start,end,id
0,2,1
1,5,2
2,10,1
5,7,2
7,9,2
11,13,1

I have managed to do this using a for loop:

def check_consistency(df):
    grouped_df = df.groupby('id')
    for key, group in grouped_df:
        df = pd.DataFrame()
        df['start'] = group['start'].iloc[1:]
        df['end'] = group['end'].shift().iloc[1:]
        consistent = df['start'] == df['end']
        if not all(consistent):
            print(key)
            print(df[consistent == False])

Is there a way to achieve the same goal without using a for loop and creating an auxiliar DataFrame?

Edit: following is the expected output.

DataFrame:

df = pd.DataFrame({'start': [0,1,2,5,7,11], 'end': [2,5,10,7,9,13], 'id': [1,2,1,2,2,1]})

Expected output:

1
   start   end
5     11  10.0
pandas-groupby pandas python
1 Answers

Firstly, we sort by id. Then make a mask comparing each start with previous row end and group by id.

For each group, the first entry of mask is defaulted to True since it has no previous row and is not to be selected for our extraction.

Finally, we select those rows with mask being False (start not equal to previous row end) by using .loc with the negation of the boolean mask.

df1 = df.sort_values('id', kind='mergesort')    # Merge Sort for stable sort to maintain sequence other than sort key
mask = (df1['start']
           .eq(df1['end'].shift())
           .groupby(df1['id']).transform(lambda x: [True] + x.iloc[1:].tolist())
       )
df1.loc[~mask]

Output:

   start     end  id
5     11      13   1
user_150706970
Related