I have a dataframe like this
L1 L2 L3 L4 L5
A 1 2 3 4 5
B 1 2 4 3 5
C 1 3 3 2 1
I want to calculate the number of differences between rows, for example the number of differences between A and B is 2, A and C is 3, B and C is 4.
What I really want is a difference matrix, such as
A B C
A 0 2 3
B 2 0 4
C 3 4 0
First loop solution is iterate by each row, compare by DataFrame and sum:
df = df.apply(lambda x: df.ne(x).sum(axis=1), axis=1)
print (df)
A B C
A 0 2 3
B 2 0 4
C 3 4 0
Or for improve performance are compared values in numpy with broadcasting for 3d array, sum and last is used DataFrame constructor:
a = df.to_numpy()
out = pd.DataFrame((a != a[:, None]).sum(2), index=df.index, columns=df.index)
print (out)
A B C
A 0 2 3
B 2 0 4
C 3 4 0
np.random.seed(123)
df = pd.DataFrame( np.random.randint(20, size=(100, 500)))
print (df)
In [119]: %%timeit
...: df.apply(lambda x: df.ne(x).sum(axis=1), axis=1)
...:
...:
12.8 s ± 1.02 s per loop (mean ± std. dev. of 7 runs, 1 loop each)
In [120]: %%timeit
...: a = df.to_numpy()
...: pd.DataFrame((a != a[:, None]).sum(2), index=df.index, columns=df.index)
...:
...:
14.6 ms ± 325 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)