Efficiently computing difference matrix based on Pandas series

user_14119752 0
Viewed 71

I have a dataframe, and I am trying to find the numeric difference between different rows within one column (series), resulting in a square matrix with both dimensions equal to the length of the dataframe.

import pandas as pd
import numpy as np

df = pd.DataFrame([[200, 2],[100,2], [1000,10], [600,5], [50,1]],
                  columns=['Sales','Total prods'])
print(df['Sales'])

0     200
1     100
2    1000
3     600
4      50
Name: Sales, dtype: int64

I have written this function:

def numerical_scoring(col_name, result_ints):
    matrix_df = np.zeros(shape=(len(result_ints), len(result_ints)))
    for index_x, row_x in result_ints[[col_name]].iterrows(): 
        for index_y, row_y in result_ints[[col_name]].iterrows():
            if index_x == index_y:
                matrix_df[index_x, index_y] = 1
            else: 
                matrix_df[index_x, index_y] = abs(row_x[0] - row_y[0])
                
    return matrix_df  

print(numerical_scoring('Sales', df))

[[  1. 100. 800. 400. 150.]
 [100.   1. 900. 500.  50.]
 [800. 900.   1. 400. 950.]
 [400. 500. 400.   1. 550.]
 [150.  50. 950. 550.   1.]]

This code works for small dataframes, but when the dataframe grows to millions of records, this is taking a long time to complete. Is there a more efficient way of transforming the data?

pandas numpy python performance
2 Answers

EDIT: Shubham Sharma's answer is more elegant and efficient, and I recommend it. However, I'm leaving this here because you're bound to encounter other situations where you do have to implement your own algorithm, and it's good to understand how to do so more efficiently.

There are several things that can be improved here.

  • Your function constructs a single-column dataframe and then iterates over its rows with result_ints[[col_name]].iterrows(). It would be simpler to work with the column/series itself and iterate over its items, with result_ints[col_name].iteritems().
  • But your function only needs to operate on a single column, which is a Pandas series. So we can just pass that as the argument, and not even worry about the dataframe.
  • Your data is all integers, so there's no need for your result matrix to use float values.
  • And here's the big one: You're duplicating a lot of work. Your result matrix is symmetrical along the diagonal, so you're currently doing identical calculations twice.

Here's a version that skips some unnecessary work:

import pandas as pd
import numpy as np

def numerical_scoring(series):
    length = len(series)
    matrix = np.zeros(shape=(length, length), dtype=int)
    
    # Set the diagonal cells to 1
    for i in range(length):
        matrix[i, i] = 1
    
    # Iterate through the items...
    for i, i_val in series.iteritems():
        # For each item, iterate only through the items after it.
        for j, j_val in series[i + 1:].iteritems():
            # Set both identical cells simulataneously.
            matrix[i, j] = matrix[j, i] = abs(i_val - j_val)
    
    return matrix

df = pd.DataFrame([[200, 2],[100,2], [1000,10], [600,5], [50,1]],
                  columns=['Sales','Total prods'])
print(numerical_scoring(df['Sales']))

[[  1 100 800 400 150]
 [100   1 900 500  50]
 [800 900   1 400 950]
 [400 500 400   1 550]
 [150  50 950 550   1]]
user_129751400

You can use pdist to calculate the pairwise distances then using squareform convert the pairwise distance vector to square matrix, finally update the diagonal values of square matrix to 1:

from scipy.spatial.distance import pdist, squareform

arr = squareform(pdist(df[['Sales']]))
arr[np.diag_indices(len(arr))] = 1

Result:

array([[  1., 100., 800., 400., 150.],
       [100.,   1., 900., 500.,  50.],
       [800., 900.,   1., 400., 950.],
       [400., 500., 400.,   1., 550.],
       [150.,  50., 950., 550.,   1.]])
user_128331660
Related