Suppose I have following DataFrame:
key id1 id2 id3 id4 id5
1 8 1 1 9 7
2 5 2 2 2 2
3 5 4 7 9 1
I would like to count the number of times the values of id1-5 match with my key value and safe it as new column.
This would be my desired result:
key id1 id2 id3 id4 id5 matches
1 8 1 1 1 7 3
2 5 2 2 2 2 4
3 5 4 7 9 1 0
One way using pandas.DataFrame.filter with eq:
df["matches"] = df.filter(like="id").eq(df["key"], axis=0).sum(1)
print(df)
Output:
key id1 id2 id3 id4 id5 matches
0 1 8 1 1 1 7 3
1 2 5 2 2 2 2 4
2 3 5 4 7 9 1 0
Let us do
df['new'] = df.eq(df.key,axis=0).sum(axis=1)-1
df
Out[108]:
key id1 id2 id3 id4 id5 new
0 1 8 1 1 9 7 2
1 2 5 2 2 2 2 4
2 3 5 4 7 9 3 1