Pandas: Compare rows within groups in a dataframe and create summary rows to mark / highlight different entries in group

user_9271275 0
Viewed 299

I have a pandas dataframe with approx. 1200 rows, where some of the rows are duplicated multiple times. The df looks like this:

ID  Serial  Age Grade   Chem    Bio   Math  Phy
M001    2   52   37       1      1     1     1
M001    2   55   37 2     1      0     1
M001    3   51   36,5     1      1     1     0
M001    3   51   46,5     1      0     1     1
M041    2   52   36,1     1      1     0     0
M041    2   51   36,1     2      1     2     4
M041    2   52   36,1     1      1     0    
M041    2   52   36,1     1      1     1    
M010    5   58   37,4     0      1     1     3
M010    5   55   39,4     1      2     1     1
M010    5   58   37,4     1      1     1     1

The duplicates in the dataframe are supposed to be identified by the ID and Serial column and I was able to do that. However, I would like to compare each group of ID+Serial rows to find where they differ. This is a bit tricky as sometimes there are 2 rows in a group to compare and sometimes the rows (to compare) are more than 2 within each group.

I am interested in a solution where I can groupby the dataframe based on ID and Serial cols and then compare rows within each group. If there is a difference between two or more cells that needs to be recorded (e.g., in a new row with a X below the conflicting cells or perhaps highlighting the cells in red color). The resulting dataframe should look something like this:

ID  Serial  Age  Grade  Chem    Bio  Math   Phy
M001    2    52   37      1      1    1      1
M001    2    55   37      2      1    0      1
M001    2    X            X           X 
M001    3    51   36,5    1      1    1      0
M001    3    51   46,5    1      0    1      1
M001    3         X              X           X
M041    2    52   36,1    1      1    0      0
M041    2    51   36,1    2      1    2      4
M041    2    52   36,1    1      1    0 
M041    2    52   36,1    1      1    1 
M041    2    X            X           X      X
M010    5    58   37,4    0      1    1      3
M010    5    55   39,4    1      2    1      1
M010    5    58   37,4    1      1    1      1
M010    5    X    X       X      X           X

Can someone help with this issue?

pandas dataframe python
1 Answers

You can create a comparison marking table by grouping on ID and Serial using .groupby() and get the number of unique entries for each column by .nunique(). Mark with 'X' if number of unique entries > 1 or blank otherwise.

Finally, concat the newly created comparison marking table back to the original dataframe by pd.concat(). Sort by ID and Serial columns by .sort_values() to bring back the related entries together.

df_mark = (df.groupby(['ID', 'Serial'])
             .nunique()
             .gt(1)
             .replace({True: 'X', False: ''})
             .reset_index()
           )

(pd.concat([df, df_mark])
   .sort_values(['ID', 'Serial'])
   .reset_index(drop=True)
)

Result:

      ID  Serial Age Grade Chem Bio Math  Phy
0   M001       2  52    37    1   1    1  1.0
1   M001       2  55    37    2   1    0  1.0
2   M001       2   X          X        X     
3   M001       3  51  36,5    1   1    1  0.0
4   M001       3  51  46,5    1   0    1  1.0
5   M001       3         X        X         X
6   M010       5  58  37,4    0   1    1  3.0
7   M010       5  55  39,4    1   2    1  1.0
8   M010       5  58  37,4    1   1    1  1.0
9   M010       5   X     X    X   X         X
10  M041       2  52  36,1    1   1    0  0.0
11  M041       2  51  36,1    2   1    2  4.0
12  M041       2  52  36,1    1   1    0  NaN
13  M041       2  52  36,1    1   1    1  NaN
14  M041       2   X          X        X    X
user_150706970
Related