Turning a two-way matrix into a summarized table

After running the MB Affinity tool, I have a 6000x6000 matrix of products and their cosine similarity to each other (similar to a correlation matrix).

I'm trying to transform it into a less unwieldy form, that summarizes the top 10 most similar products and their scores for each product. The fields might look like:

[Product] - [#1 Most Similar Product Name] - [#1 Most Similar Product Score] - [#2 Most Similar Product Name] - ... - [#10 Most Similar Product Score]

...

N = 6,000

Has anybody dealt with something like this before? I'm stumped for solutions. Keeping in mind, I'm constrained to working with a 340MB CSV file.

Thank you,

Michael

Workflow

Topic of Interest

Preparation

Predictive Analysis

Iterative Macro

Accepted answers

Sample tool

Group by row name
First 10 records

Done

All comments

Here's a module that might do the trick....

Cheers,

Mark

Turning a two-way matrix into a summarized table.yxmd

Thank you Mark! I think I was unclear in my outline of the problem.

The data looks like this:

market basket matrix.PNG

Michael

Quick Links

Tags

This months top contributors

atcodedog05 19458

Qiu 15866

MarqueeCrew 13708

apathetichell 13703