I have a large dataframe (5 rows x 92,579 columns) in the following format:
1 2 3 4 5 6 7 8 9 10 11 ... 92569 92570 92571 92572 92573 92574 92575 92576 92577 92578 92579
0 10 9 8 5 5 10 1 1 6 2 3 ... 9 1 8 3 2 5 5 5 2 2 8
1 3 1 7 4 4 3 8 8 3 6 7 ... 1 8 7 5 6 4 4 4 2 6 7
2 6 4 2 9 7 6 5 5 6 7 2 ... 4 5 2 6 6 9 5 9 3 10 2
3 3 8 4 4 7 3 1 1 3 7 6 ... 8 1 5 7 2 4 1 4 6 10 2
4 4 6 5 5 5 4 1 1 4 8 10 ... 6 1 7 3 6 5 5 5 8 2 9
Each of the entries ranges from 1 to 10 (representing an assignment to one of 10 clusters).
I want to create a 92579 x 92579 matrix that represents how many times (ie. in how many rows) the variables in columns i and j have the same value. For example, variables 4 and 5 have the same value in 3 rows, so entries i_{4,5} and i_{5,4} of the co-occurrence matrix should be 3. I only need the upper triangular portion of the desired matrix (since it will be symmetric).
I've looked at similar questions here, but they don't address both of these issues:
- How to do this efficiently for a very large matrix
- How to do this for non-binary entries