highlight column by column difference for each row in pandas

user_1058584 0
Viewed 173

Let's say I have a pd.DataFrame that looks as such:

id  col1_a  col1_b  col2_a  col2_b
1   x       x       2       3  
2   z       d       4       5
3   y       y       9       9
4   p       p       8       1

What this dataframe represents is a 2 dataframe (df_a, df_b) column by column comparison.

I am trying to get a dataframe that highlights and finds the columns that contain those differences as such:

id  col1_a  col1_b  col2_a  col2_b   diff
1   x       x       2       3        col2
2   z       d       4       5        col1,col2
3   y       y       9       9        None
4   p       p       8       1        col2

How can I achieve something like this without having to doubly traverse through the cols and rows.

I know I can achieve this by doing something similar (not tested):

for col_ptr1 in df.columns:
   for col_ptr2 in df.columns:
      for idx, row in df.iterrows():
         if col_ptr1.strip('_a') == col_ptr2.strip('_b'):
            blah blah blah...

This is super ugly. I wonder if there is a more pandas style approach to this.

pandas dataframe python
5 Answers

Select the subset of columns containing col, then split these column names around delimiter _ and extract the first component of split using the str accessor

Now, group the dataframe on the col prefix extracted in the previous step, and agg using nunique along axis=1 to count the unqiue values. Check for the unique values if not equal to one then add the corresponding column names in diff columns using dataframe.dot

c = df.filter(regex=r'_(a|b)$')
m = c.groupby(c.columns.str.split('_').str[0], axis=1).nunique().ne(1)
df['diff'] = m.dot(m.columns + ',').str[:-1]

   id col1_a col1_b  col2_a  col2_b       diff
0   1      x      x       2       3       col2
1   2      z      d       4       5  col1,col2
2   3      y      y       9       9           
3   4      p      p       8       1       col2
user_128331660

Here is another way with groupby on axis=1 to create common groups and then compare each group with the second column and get the column name when they don't match:

u = df.set_index("id")

cols = u.columns.str.split("_").str[0]
l = (g.ne(g.iloc[:,-1],axis=0) for i,g in u.groupby(cols,axis=1))

df['diff_'] = df['id'].map(pd.concat(l,axis=1).dot(cols+',').str[:-1])

print(df)

   id col1_a col1_b  col2_a  col2_b      diff_
0   1      x      x       2       3       col2
1   2      z      d       4       5  col1,col2
2   3      y      y       9       9           
3   4      p      p       8       1       col2
user_98406370

I suppose an answer can be this:

cols = [(x, y) for x in df.columns for y in df.columns if x.strip('_a') == y.strip('_b')]

diffs = []
for idx, row in df.iterrows():
    for c in col:
        if row[c[0]] != row[c[1]]:
            # difference found!
user_10585840

You can use apply function, which also provide more efficient computation than direct iterations. This code can be easily generalized to the case on many column pairs.

def find_diff(x):
    res = ''
    if (x['col1_a'] != x['col1_b']):
        res += 'col1'
    if (x['col2_a'] != x['col2_b']):
        res += 'col2' if len(res) == 0 else ',col2'
    return None if res == '' else res

df['diff'] = df.apply(find_diff, axis=1)

More information about apply function can be found here.

user_159150190

You can use pandas.DataFrame.apply:

import pandas as pd

def get_diff(x):
    if x.col1_a == x.col1_b and x.col2_a == x.col2_b:
        return None
    if x.col1_a != x.col1_b and x.col2_a != x.col2_b:
        return 'col1,col2'
    if x.col1_a != x.col1_b:
        return 'col1'
    if x.col2_a != x.col2_b:
        return 'col2'

df['diff'] = df.apply(get_diff, axis=1)
user_123868900
Related