如何使用python计算一列数据相对于另一列的百分位排名 [英] How to calculate a percentile ranking of a column of data relative to another column using python

问题描述

我有两列表示相同数量的数据；一列来自我的训练数据，另一列来自我的验证数据.

I have two columns of data representing the same quantity; one column is from my training data, the other is from my validation data.

我知道如何有效地计算训练数据的百分位排名:

I know how to calculate the percentile rankings of the training data efficiently using:

pandas.DataFrame(training_data).rank(pct = True).values

我的问题是，我怎样才能有效获得一组与训练数据列相关的验证数据列的相似百分位排名?也就是说，对于验证数据列中的每个值，我如何才能找到其相对于训练数据列中所有值的百分位排名?

My question is, how can I efficiently get a similar set of percentile rankings of the validation data column relative to the training data column? That is, for each value in the validation data column, how can I find what its percentile ranking would be relative to all the values in the training data column?

我试过这样做:

def percentrank(input_data,comparison_data):
    rescaled_data = np.zeros(input_data.size)
    for idx,datum in enumerate(input_data):
        rescaled_data[idx] =scipy.stats.percentileofscore(comparison_data,datum)
    return rescaled_data/100

但我不确定这是否正确，而且速度非常慢，因为它对 for 循环中的每个值进行了大量冗余计算.

But I'm not sure if this is even correct, and on top of that it's incredibly slow because it is doing a lot of redundant calculations for each value in the for loop.

任何帮助将不胜感激！

推荐答案

这是一个解决方案.对训练数据进行排序.然后对验证数据使用 searchsorted.

Here's a solution. Sort the training data. Then use searchsorted on the validation data.

import pandas as pd
import numpy as np

# Generate Dummy Data
df_train = pd.DataFrame({'Values': 1000*np.random.rand(15712)})

#Sort Data
df_train = df_train.sort_values('Values')

# Calculating Rank and Rank_Pct for demo purposes 
#but note that it is not needed for the solution
# The ranking of the validation data below does not depend on this
df_train['Rank'] = df_train.rank()
df_train['Rank_Pct']= df_train.Values.rank(pct=True)

# Demonstrate how Rank Percentile is calculated
# This gives the same value as .rank(pct=True)
pct_increment = 1./len(df_train)
df_train['Rank_Pct_Manual'] = df_train.Rank*pct_increment

df_train.head()

       Values  Rank  Rank_Pct  Rank_Pct_Manual
2724  0.006174   1.0  0.000064         0.000064
3582  0.016264   2.0  0.000127         0.000127
5534  0.095691   3.0  0.000191         0.000191
944   0.141442   4.0  0.000255         0.000255
7566  0.161766   5.0  0.000318         0.000318

现在使用 searchsorted 来获取验证数据的 Rank_Pct

Now use searchsorted to get Rank_Pct of validation data

# Generate Dummy Validation Data
df_validation = pd.DataFrame({'Values': 1000*np.random.rand(1000)})

# Note searchsorted returns array index. 
# In sorted list rank is the same as the array index +1
df_validation['Rank_Pct'] = (1 + df_train.Values.searchsorted(df_validation.Values))*pct_increment

这是最终 df_validation 数据帧的前几行:

Here is first few lines of final df_validation dataframe:

print df_validation.head()
      Values  Rank_Pct
0  307.378334  0.304290
1  744.247034  0.744208
2  669.223821  0.670825
3  149.797030  0.145621
4  317.742713  0.314218

这篇关于如何使用python计算一列数据相对于另一列的百分位排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！