我有以下数据帧:
date = ['2015-02-03 23:00:00','2015-02-03 23:30:00','2015-02-04 00:00:00','2015-02-04 00:30:00','2015-02-04 01:00:00','2015-02-04 01:30:00','2015-02-04 02:00:00','2015-02-04 02:30:00','2015-02-04 03:00:00','2015-02-04 03:30:00','2015-02-04 04:00:00','2015-02-04 04:30:00','2015-02-04 05:00:00','2015-02-04 05:30:00','2015-02-04 06:00:00','2015-02-04 06:30:00','2015-02-04 07:00:00','2015-02-04 07:30:00','2015-02-04 08:00:00','2015-02-04 08:30:00','2015-02-04 09:00:00','2015-02-04 09:30:00','2015-02-04 10:00:00','2015-02-04 10:30:00','2015-02-04 11:00:00','2015-02-04 11:30:00','2015-02-04 12:00:00','2015-02-04 12:30:00','2015-02-04 13:00:00','2015-02-04 13:30:00','2015-02-04 14:00:00','2015-02-04 14:30:00','2015-02-04 15:00:00','2015-02-04 15:30:00','2015-02-04 16:00:00','2015-02-04 16:30:00','2015-02-04 17:00:00','2015-02-04 17:30:00','2015-02-04 18:00:00','2015-02-04 18:30:00','2015-02-04 19:00:00','2015-02-04 19:30:00','2015-02-04 20:00:00','2015-02-04 20:30:00','2015-02-04 21:00:00','2015-02-04 21:30:00','2015-02-04 22:00:00','2015-02-04 22:30:00','2015-02-04 23:00:00','2015-02-04 23:30:00']
value = [33.24 , 31.71 , 34.39 , 34.49 , 34.67 , 34.46 , 34.59 , 34.83 , 35.78 , 33.03 , 35.49 , 33.79 , 36.12 , 37.09 , 39.54 , 41.19 , 45.99 , 50.23 , 46.72 , 47.47 , 48.46 , 48.38 , 48.40 , 48.13 , 38.35 , 38.19 , 38.12 , 38.05 , 38.06 , 37.83 , 37.49 , 37.41 , 41.84 , 42.26 , 44.09 , 48.85 , 50.07 , 50.94 , 51.09 , 50.60 , 47.39 , 45.57 , 45.03 , 44.98 , 41.32 , 40.37 , 41.12 , 39.33 , 35.38 , 33.44 ]
df = pd.DataFrame({'value':value,'index':date})
df.index = pd.to_datetime(df['index'],format='%Y-%m-%d %H:%M')
df.drop(['index'],axis=1,inplace=True)
print(df)
value
index
2015-02-03 23:00:00 33.24
2015-02-03 23:30:00 31.71
2015-02-04 00:00:00 34.39
2015-02-04 00:30:00 34.49
2015-02-04 01:00:00 34.67
2015-02-04 01:30:00 34.46
我想对值列进行装箱,以查看该值是否优于该年份的 90% 百分位数,还是在该年不包括的 80% 到 90% 百分位数之间。
我知道我可以使用熊猫切割函数,我的问题是如何将每年的给定百分位数传入其中(称为"PERCENTILE80_of_considered_year"和"PERCENTILE90_of_considered_year"的变量(:
binned = pd.cut(x=df.value, bins=[-np.inf,PERCENTILE80_of_considered_year, PERCENTILE90_of_considered_year, np.inf], right=False, labels=['<P80', 'P80_90', '>P90'])
预期结果将是这样的(仅供说明(:
value bin
index
2015-02-03 23:00:00 33.24 P80_90
2015-02-03 23:30:00 31.71 <P80
2015-02-04 00:00:00 34.39 P80_90
2015-02-04 00:30:00 34.49 P80_90
2015-02-04 01:00:00 34.67 >P90
2015-02-04 01:30:00 34.46 P80_90
有谁知道如何有效地做到这一点?还是任何其他有效的方法?
非常感谢,
不确定我是否完全理解您的问题,但我会按如下方式计算百分位数:
p80 = df.value.quantile(0.8)
p90= df.value.quantile(0.9)
df['binned'] = pd.cut(x=df.value, bins=[-np.inf, p80, p90, np.inf], right=False, labels=['<P80', 'P80_90', '>P90'])
您的示例只有一年,在多年的情况下,您可以做同样的事情,但groups
而不是完整的df
。有很多方法可以做到这一点,但一种选择是:
for year in df.index.year.unique():
mask = df.index.year == year
df.loc[mask, 'binned'] = pd.cut(x=df.value
, bins=[-np.inf, df[mask].value.quantile(0.8), df[mask].value.quantile(0.9), np.inf]
, right=False, labels=['<P80', 'P80_90', '>P90'])
df.head()
您可以groupby
年份,并为每个组apply
一个函数。
def get_bin(group):
p80 = group.value.quantile(0.8)
p90 = group.value.quantile(0.9)
group['bin'] = pd.cut(
x=group.value,
bins=[-np.inf, p80, p90, np.inf],
right=False,
labels=['<P80', 'P80_90', '>P90'])
return group
df.groupby(lambda x: x.year).apply(get_bin)
# value bin
# index
# 2015-02-03 23:00:00 33.24 <P80
# 2015-02-04 07:00:00 45.99 <P80
# 2015-02-04 07:30:00 50.23 >P90
# 2015-02-04 09:00:00 48.46 P80_90
# 2015-02-04 10:00:00 48.40 P80_90