Pandas分位数计算与Excel结果差异问题求助

问题背景 #

我有一批长度在80-120之间的数据集，需要计算各数据集的90%分位数。测试发现当数据量为81、91、101或111时，Excel与Python（Pandas、NumPy）的计算结果存在差异。以下是已从小到大排序的示例数据集：

Samples = [0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 
    0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 
    0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 
    0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.15, 0.25, 0.25, 0.3, 0.44, 0.45,
    0.47, 0.54, 0.58, 0.6, 0.68, 0.69, 0.74, 0.82, 0.84, 1.1, 1.1, 1.2, 1.2, 1.5,
    1.7, 1.8, 1.9, 1.9, 2.3, 2.6, 3.5, 4.5, 4.8] 

Excel的计算逻辑（预期结果） #

Excel的计算步骤如下：

核心逻辑：计算0.9*样本量后向下取整，取排序后数据中对应1-based索引的元素。

之前尝试的Python代码及结果 #

Pandas实现 #

import pandas as pd
myS = pd.Series(Samples)
q_lower = myS.quantile(q=0.9, interpolation='lower')
print(q_lower)  # 输出：1.7

NumPy实现 #

import numpy as np
myindex = int(np.ceil(0.9 * len(Samples))) - 1
print(myS.iloc[myindex])  # 输出：1.7

解决方案：匹配Excel的计算逻辑 #

要让Python得到和Excel一致的结果，需严格遵循Excel的计算步骤：

1. 计算0.9 * 样本数量
2. 对结果向下取整，得到1-based的位置索引
3. 从排序后的数据集里取出对应位置的元素（注意Python是0-based索引，需减1）

实现代码 #

直接基于列表的实现

import math

n = len(Samples)
k = 0.9 * n
# 向下取整得到1-based位置
pos_1based = math.floor(k)
# 转换为0-based索引
pos_0based = pos_1based - 1
excel_result = Samples[pos_0based]
print(excel_result)  # 输出：1.5

Pandas实现

import pandas as pd
import math

myS = pd.Series(Samples)
n = len(myS)
pos_1based = math.floor(0.9 * n)
excel_result = myS.iloc[pos_1based - 1]
print(excel_result)  # 输出：1.5

NumPy实现

import numpy as np

n = len(Samples)
pos_1based = np.floor(0.9 * n).astype(int)
excel_result = Samples[pos_1based - 1]
print(excel_result)  # 输出：1.5

关键说明 #

Python的Pandas/NumPy默认分位数计算采用了不同的插值规则（比如interpolation='lower'是基于线性插值的定位逻辑），和Excel的“直接取向下取整后位置的元素”逻辑不一致。通过手动复现Excel的计算步骤，即可得到完全一致的结果。

内容的提问来源于stack exchange，提问作者Shane S