Compare excel sheets and highlight the differences if exceeding Certain value(Eg>=5)

Question

I have 2 data frames df1 & df2, I am able to highlight the differences if the values are not equal(data.ne)

But What I like to achieve is, to Highlight those parameters if the absolute difference between two data frames is (>=5)

df_all = pd.concat ([df1.set_index('C'),df2.set_index('C')],axis='columns',keys=['MASTER','DEVICE'])
df_final = df_all.swaplevel(axis ='columns')[df1.columns[1:]]
def highlight(data, color='Yellow'):
    attr = 'background-color: {}'.format(color)
    other = data.xs('MASTER',axis ='columns',level=-1)
    return pd.DataFrame(np.where(data.ne(other,level=0), attr, ''),index=data.index, columns=data.columns)
df_final.style.apply(highlight,axis=None)

my goal is to achieve this in using swaplevel for better viewing.

Please guide me. Many thanks in advance.

I just started coding in python and using pandas on my own and I admit I am a bit lost.

Thanks for reading all this and thanks in advance for any suggestions and feedback. ! If further information is required , please leave your comment.

Answer 1

return pd.DataFrame(np.where(abs(data.sub(other,level=0))>=5.0, attr, ''),index=data.index, columns=data.columns)

Changing the above code will generate the report if difference is greater than or equal to (>=5)