Argthtjtr

I have a dataframe with 3 columns.

UserId | ItemId | Rating

(where Rating is the rating a User gave to an Item. It's a np.float16. The 2 Id's are np.int32)

How do you best compute correlations between items using python pandas?

My take is to first pivot the table (wide format) and then apply pd.corr

df = df.pivot(index='UserId', columns='ItemId', values='Rating')

df.corr()

It's working on small datasets, but not on big ones.

That first step creates a big matrix dataset mostly full of missing values. It's quite ram intensive and I can't run it with bigger dataframes.

Isn't there a simpler way to compute the correlations directly on the long dataset, without pivoting?

(I looked into pd.groupBy, but that seems to only split the dataframe, not what I'm looking for.)

EDIT: oversimplified data and working pivot code

import pandas as pd

import numpy as np

d = {'UserId': [1,2,3, 1,2,3, 1,2,3], 

     'ItemId': [1,1,1, 2,2,2, 3,3,3], 

     'Rating': [1.1,4.5,7.1, 5.5,3.1,5.5, 1.1,np.nan,2.2]}

df = pd.DataFrame(data=d)

df = df.astype(dtype={'UserId': np.int32, 'ItemId': np.int32, 'Rating': np.float32})

print(df.info())

pivot = df.pivot(index='UserId', columns='ItemId', values='Rating')

print('')

print(pivot)

corr = pivot.corr()

print('')

print(corr)

EDIT2: Large random data generator

def randDf(size = 100):

  ## MAKE RANDOM DATAFRAME, df =======================

  import numpy as np

  import pandas as pd

  import random

  import math

  dict_for_df = {}

  for i in ('UserId','ItemId','Rating'):   

    dict_for_df[i] = {}

    for j in range(size):

      if i=='Rating': val = round( random.random()*5, 1) 

      else: val = round( random.random() * math.sqrt(size/2) )

      dict_for_df[i][j] = val     # store in a dict

  # print(dict_for_df)

  df = pd.DataFrame(dict_for_df) # after the loop convert the dict to a dataframe

  # print(df.head())

  df = df.astype(dtype={'UserId': np.int32, 'ItemId': np.int32, 'Rating': np.float32})

  # df = df.astype(dtype={'UserId': np.int64, 'ItemId': np.int64, 'Rating': np.float64})

  ## remove doubles -----

  df.drop_duplicates(subset=['UserId','ItemId'], keep='first', inplace=True)

  ## show -----

  print(df.info())

  print(df.head())

  return df

# =======================



df = randDf()

I had another go, and have something that gets exactly the same correlation numbers as your method without using pivot, but is much slower. I can't say whether it uses less or more memory:

from scipy.stats.stats import pearsonr   

import itertools

import pandas as pd 

import numpy as np



d =     

itemids = list(set(df['ItemId']))

pairsofitems = list(itertools.combinations(itemids,2))



for itempair in pairsofitems:

    a = df[df['ItemId'] == itempair[0]][['Rating', 'UserId']]

    b = df[df['ItemId'] == itempair[1]][['Rating', 'UserId']]



    z = np.ones(len(set(df.UserId)), dtype=int)

    z = z * np.nan

    z[a.UserId.values] = a.Rating.values



    w = np.ones(len(set(df.UserId)), dtype=int)

    w = w * np.nan

    w[b.UserId.values] = b.Rating.values 



    bad = ~np.logical_or(np.isnan(w), np.isnan(z))

    z = np.compress(bad, z)

    w = np.compress(bad, w)

    d.append({'firstitem': itempair[0], 

              'seconditem': itempair[1], 

              'correlation': pearsonr(z,w)[0]})



df_out = pd.DataFrame(d, columns=['firstitem', 'seconditem', 'correlation'])

This was of help working out to handle the nan's before taking the correlation.

The slicing in the two lines after the for loop take time. I think though, it may have potential if the bottlenecks could be fixed.

Yes, there is some repetition in there with the z and w variables, could put that in a function.

Some explanation of what it does:

• find all combinations of pairs within your items


• organise and "x" and "y" set of points for UserId / Rating where any point pair where one of the two is missing (nan) is dropped. I think of a scatter plot and the correlation being how well a straight line fits through it.


• run pearson correlation on this x-y pair


• put the ItemId each pair and correlation into a dataframe