Multiple Pandas Ranking Operations within a Loop - Better Optimization and Performance

Question

I have implemented the following code which works as intended. However, I would like to improve my code in terms of performance and efficiency

Code in Question

import pandas as pd
from scipy.stats import norm

# data frame of length 40,000 rows, containing 25 columns
for indx in df.index:
    matrix_ordered_first = df.loc[indx].rank(method='first',na_option='bottom')
    matrix_ordered_avg = df.loc[indx].rank(method='average', na_option='bottom')
    matrix_ordered_first.loc[df.loc[indx] == 0] = matrix_ordered_avg
    matrix_computed = norm.ppf(matrix_ordered_first / (len(df.columns) + 1))
    df.loc[indx] = matrix_computed.T

A peak of the dataframe

Here is a part view of my dataframe df:

s            s1     s2           s3         s4      ...     s21        s23        s24  s25
0            NaN   5.406999   5.444658   4.640154  ...  4.633389   5.517850       NaN  6.121492
1            NaN   2.147866   1.758245   1.274754  ...  1.465129   1.200157       NaN  1.789203
2       2.872652   5.492498   2.547415   3.754654  ...  3.686420   1.540947  4.405961  1.715685
3            NaN  46.316837  27.197062  72.910797  ...       NaN  46.812153       NaN       NaN
4       1.365775   1.329316   1.852473   1.208155  ...  1.489296   1.313321  1.462968  1.249645

[5 rows x 25 columns]

Explanation

The code above is the part of a long python script in which this part runs slower than the other parts of the program. So what I am trying to do in the above code is to iterate over the data frame in a row-wise fashion. Then, for each row I have to perform a chain of pandas ranking operations followed by a statistical test equivalent to the "One-tail test”.Finally, transpose the matrix which will then be fed as a row for the data frame.

How can I improve this block of code in terms of efficiency, speed, and performance?

On a separate note, I not experienced in pandas so my code may seem amateur and for that I kindly seek your guidance.

Thank you so much in advance,

Answer 1

A basic principle of performance optimization in pandas is to avoid for-loops and vectorize as much computation as possible.

Here is one version of improved code:

order_df = df.rank(axis=1, method="first", na_option="bottom")
order_df_avg = df.rank(axis=1, method="average", na_option="bottom")
order_df.where(df.astype(bool), order_df_avg, inplace=True)
matrix_computed = norm.ppf(order_df / (len(df.columns) + 1))
df[:] = matrix_computed

One potential further improvement is to compute only the orders that need to be updated rather than order_df_avg.

order_df = df.rank(axis=1, method="first", na_option="bottom")
mask = (df == 0)
orders_to_update = np.broadcast_to(np.expand_dims(order_df[mask].mean(axis = 1), 1), df.shape)
order_df.mask(mask, orders_to_update, inplace=True)
matrix_computed = norm.ppf(order_df / (len(df.columns) + 1))
df[:] = matrix_computed