Python 等效于 Excel 的 PERCENTILE.EXC [英] Python equivalent of Excel's PERCENTILE.EXC

问题描述

我正在使用 Pandas 计算一些金融风险分析，包括风险价值.简而言之，要计算风险价值 (VaR)，您需要模拟投资组合价值变化的时间序列，然后计算特定的尾部百分位损失.例如，95% VaR 是该时间序列中的第 5 个百分位数.

I am using Pandas to compute some financial risk analytics, including Value at Risk. In short, to compute Value at Risk (VaR), you take a time series of simulated portfolio changes in value, and then compute a specific tail percentile loss. For example, 95% VaR is the 5th percentile figure in that time series.

我在 Pandas 数据框中有我的时间序列，目前我正在使用 pd.quantile() 函数来计算百分位数.我的问题是，VaR 的典型市场惯例是使用排除百分位(即:95% VaR 被解释为:您的投资组合有 95% 的机会不会比计算出的数字损失更多) - 类似于 MS Excel PERECENTILE.EXC() 作品.Pandas quantile() 的工作方式类似于 Excel 的 PERCENTILE.INC() 的工作方式 - 它包括指定的百分位数.我已经搜索了几个 python 数学包以及这个论坛，寻找一个 python 解决方案，它使用与 Excel 中的 PERCENTILE.EXC() 相同的方法，但没有运气.我希望这里有人可以提出建议?

I have my time series in a Pandas dataframe, and am currently using the pd.quantile() function to compute the percentile. My question is, typical market convention for VaR is use an exclusionary percentile (ie: 95% VaR is interpreted as: there is a 95% chance your portfolio will not loose MORE than the computed number) - akin to how MS Excel PERECENTILE.EXC() works. Pandas quantile() works akin to how Excel's PERCENTILE.INC() works - it includes the specified percentile. I have scoured several python math packages as well as this forum for a python solution that uses the same methodology as PERCENTILE.EXC() in Excel with no luck. I was hoping someone here might have a suggestion?

这是示例代码.

import pandas as pd
import numpy as np

test_pd = pd.Series([15,14,18,-2,6,-78,31,21,98,-54,-2,-36,5,2,46,-72,3,-2,7,9,34])
test_np = np.array([15,14,18,-2,6,-78,31,21,98,-54,-2,-36,5,2,46,-72,3,-2,7,9,34])

print 'pandas: ' + str(test_pd.quantile(.05))
print 'numpy: '+ str(np.percentile(test_np,5))

我要找的答案是-77.4

The answer i am looking for is -77.4

谢谢，

瑞恩

推荐答案

它不会像 Pandas 自己的百分位数那么有效，但它应该可以工作:

It won't be as efficient as Pandas' own percentile but it should work:

def quantile_exc(ser, q):
    ser_sorted = ser.sort_values()
    rank = q * (len(ser) + 1) - 1
    assert rank > 0, 'quantile is too small'
    rank_l = int(rank)
    return ser_sorted.iat[rank_l] + (ser_sorted.iat[rank_l + 1] - 
                                     ser_sorted.iat[rank_l]) * (rank - rank_l)

ser = pd.Series([15,14,18,-2,6,-78,31,21,98,-54,-2,-36,5,2,46,-72,3,-2,7,9,34])

quantile_exc(ser, 0.05)
Out: -77.400000000000006

quantile_exc(ser, 0.1)
Out: -68.399999999999991

quantile_exc(ser, 0.3)
Out: -2.0

请注意，Excel 对于小百分位数失败；这不是错误.这是因为低于最小值的秩不适合插值.因此，您可能需要检查 quantile_exc 函数中的 rank > 0(参见断言部分).

Note that Excel fails for small percentiles; it is not a bug. It is because ranks that go below the minimum value is not suitable for interpolation. So you might want to check if rank > 0 in the quantile_exc function (see the assertion part).

这篇关于Python 等效于 Excel 的 PERCENTILE.EXC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！