Counting matching columns which have "1"s in each row

Hello!

I have a database of items which users have selected, and I would like to create a table which summarises the relationships between each item (i.e. a table which shows how many users have selected both items (items in rows and columns).

My input table is attached and looks like the below sshot, except for 'user_id', all columns reference an individual item (e.g. 536, etc):

My desired output is a table like the below which counts the number of users who have selected both items (add a column to the right which will provide this count):

Please can someone guide me on how to build this count function? I have got lost in the "transform" section, i'm sure the answer is there.

The overall purpose of this exercise is to provide the 'edges' for the network analysis tool (but I don't need help with that, yet )

Thanks community!

Scott

matching columns input.csv

output table.csv

Transformation

Preparation

Accepted answers

Felipe_Ribeir0

Hi @scottwbarlow1

Please take a look at the attached workflow, if i understood well the problem, this is a possible way to solve it

Counting matching columns which have 1s in each row.yxzp

All comments

binu_acs

@scottwbarlow1 Looking for something like below

Count_1017.yxmd

scottwbarlow1

Not quite. I am looking to count how many users bought each pair of items. For example 1,973 users purchased both items "536" and "786" (first two columns in the first document).