Compare how many different values per columns in 2 dataframes

user_7504470 0
Viewed 38

I have 2 dataframes and i am trying to find number of different value per column:

df 1
-----
id  rank    value   group
0   1       999      A
1   2        3       A
2   3       345      B
3   56       8       C
4   7       54       D
_____
df 2
_____
id rank    value    group
0   1      111       A
1   2       3        B
2   3      345       B
3  56       11       C
4   7       2        D
5   4       92       E

I have a function to calculate the differences:

def diff_helper(x):
    if x[0] == x[1]:
        return 'same'
    return 'diff'
merged_df = pd.merge(df1, df2, on = 'id')
merged_df.apply(lambda frame: frame.apply(diff_helper, axis=1))

We ignore the last row of df2 because it doesn't overlap with df1. My expected output is

rank : 0
value: 3
group: 1

value is 3 because 999 vs 111, 8 vs 11, 54 vs 2.

group is 1 because A vs B in second row

pandas dataframe lambda python
1 Answers

Let us try

df1 = df1.set_index('id') ; df2 = df2.set_index('id')
df1=df1[df1.index.isin(df2.index)]
df2=df2[df2.index.isin(df1.index)]
s = df1.ne(df2)
# if we only need the number after sum , we are done ~ 
s.sum()
Out[9]:
rank     0
value    3
group    1
dtype: int64

t = pd.concat([df1[s].stack(), df2[s].stack()]).astype(str).groupby(level=[0,1]).agg(' vs '.join).groupby(level=1).agg(','.join)
out = pd.concat([s.sum(), t],axis=1)
Out[20]: 
       0                                       1
rank   0                                     NaN
value  3  999.0 vs 111.0,8.0 vs 11.0,54.0 vs 2.0
group  1                                  A vs B
user_79645270
Related