4
\$\begingroup\$

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,

\$\endgroup\$
2
\$\begingroup\$

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
\$\endgroup\$
3
  • \$\begingroup\$ Thank you so much for your answer @GZ0. Would you kindly explain what orders_to_update and order_df.mask(mask, orders_to_update, inplace=True) do? I would love you to elaborate more on why those operations make them more efficient? \$\endgroup\$
    – aBiologist
    Sep 10 '20 at 20:06
  • \$\begingroup\$ order_df.mask(mask, orders_to_update, inplace=True) is equivalent to order_df.where(~mask, orders_to_update, inplace=True) so their performances are more or less the same. The second solution could be faster because theoretically, the computation of order_df_avg has time complexity O(nlogn) -- here n is the number of columns -- while the computation of orders_to_update has time complexity O(n). However, since n is relatively small in this case, you need to measure the performance on your data to see whether the second solution is actually faster. \$\endgroup\$
    – GZ0
    Sep 10 '20 at 22:08
  • \$\begingroup\$ According to your code, the numbers that need to be updated in order_df are the orders of 0s in df. So orders_to_update computes only the correct orders of 0s (rather than doing a complete reranking) and broadcast it to the shape of order_df. \$\endgroup\$
    – GZ0
    Sep 10 '20 at 22:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.