Btukfyl

I'm trying to run collaborative filtering on a large data set of med codes where each patient has 2 or more diagnoses. There are ~291K patients, and there are ~8K unique codes. In order to run CF on this data, I need to create a binary frequency matrix where each unique code is a column and there is a 0 or 1 in each patient's row and column if the disease is present or not.

The problem is this data set has ~2.3 billion cells and my laptop with 16gb of RAM can't process it. I tried it in R using the reshape package and it crashes. I wrote code in Python (below) .If I subset the data to 500 patients, it takes around 24 hours to process. Does anyone have a better way to do this? I'm wondering if the loop within a loop structure is too inefficient? Or should I apply sparseMatrix in R somehow to this data?

list samples:

subset_patients =

[['1510395', 'R31', 'N359', 'I639', 'C440', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA'], ['1275226', 'T810', 'N813', 'N393', 'M8417', 'M679', 'M1997', 'L600', 'K529', 'R634', 'R15', 'N811', 'K573', 'K571', 'K222', 'D120', 'A099', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA', 'NA'], ...... ]



sorted_codes = ['A009', 'A010', 'A011', 'A014', 'A020', 'A021', 'A022', 'A028', 'A029', ... ]

my code:

bin_freq_matrix = pd.DataFrame(0, index = np.arange(len(subset_patients)), columns = sorted_codes)



count = -1

for row in subset_patients:  #subset_patients is a small list of the patients

    for col in row:

        if col in sorted_codes:  #sorted_codes is the unique codes list

            count = count+1

            bin_freq_matrix.at[count, col]=1



print(bin_freq_matrix.head())

NEWEST VERSION:

subset_patients = patients[0:1]



def marking(row):

    # here the traverse is in the natural order of columns

    hots = {col for col in row if col in sorted_codes_set}

    # here as well there are no jumps around the memory

    return [1 if col in hots else 0 for col in sorted_codes]



bin_freq_matrix = pd.DataFrame(subset_patients).apply(marking)



print(bin_freq_matrix)

for x in bin_freq_matrix[1]:

    if x==1:

        print("yes")

Welcome to SO! Indeed you could use a slightly more optimal solution here. There are at least few things you can optimize. Let's look at them step by step moving towards a more comprehensive use of pandas funtionality.

1. 
   

   Optimize the body of the loop
   Interestingly, you do not need to change the actual code constructing the matrix much. It is enough to change the definition of the data structure to make your code so much more efficient! The following line of code:

   
   
   

   if col in sorted_codes: #sorted_codes is the unique codes list

   
   

takes a significant performance toll on the operation due to linear (big-O notation) characteristics of presence test for lists compared to sets (logarithmic characteristics) which you can easily use by changing the definition of sorted_codes copy you use to check if value exists to:

sorted_codes_set = set(sorted_codes)

Sorting the list does not help unless you use binary search. It has the same characteristics as sets but you would have to implement the search yourself. The choice is easy: sets.

2. Removal of unnecessary operations from the loop.
   The code in the loop is going to be repeated billions of times (in your case) so it should be maximally optimized.

The following line changes the dataframe in the random order which is a bad idea because pandas is optimized for sequential access and can be orders of magnitude slower otherwise:

bin_freq_matrix.at[count, col]=1

3. Use apply and a function instead of the for loop. This is likely to bring the largest gain.

The final piece of code:

def marking(row):

    # here the traverse is in the natural order of columns

    hots = {col for col in row if col in sorted_codes_set}

    # here as well there are no jumps around the memory

    return [1  if col in hots else 0 for col in sorted_codes]



bin_freq_matrix = pd.DataFrame(subset_patients).apply(marking)