I'm trying to calculate the weighted percentiles. Solution_1 creates accurate results but is slow and not very efficient if the dataset is large. I tried out a different approach by using the weighted percentile formula I found, but it's yielding different results. I'm hoping someone can help me figure out how can I replicate the results from solution_1 in a different way. I'm new to the community so please let me know if I didn't include enough information, or if something's not clear, I'll do my best to update it as soon as I can.
def modify_data():
data = {'num': [1, 4, 11, 14, 45, 56, 67, 88, 99, np.nan],
'n_obs': [13, 34, 52, 33, 31, 14, 55, 73, 12, 34],
"perc": [10, 20, 30, 40, 50, 60, 70, 80, 90, 10]}
df = pd.DataFrame(data=data)
# solution 1 - correct results
weighted_values = pd.DataFrame(df["num"].values.repeat(df["n_obs"], axis=0))
solution_1 = []
for i in np.arange(0, 100, 10):
output = np.nanpercentile(weighted_values, i)
solution_1.append(output)
df['solution_1'] = np.array(solution_1).tolist()
# solution 2 - results are off
solution_2 = []
exclude_nans = df[df["num"].notnull()]
cdf = (np.cumsum(exclude_nans["n_obs"]) - 0.5 * exclude_nans["n_obs"]) / np.sum(exclude_nans["n_obs"]) # 'like' a CDF function
for i in np.arange(0, 1, 0.1):
abc = np.interp(i, cdf, exclude_nans["num"])
solution_2.append(abc)
df["solution_2"] = np.array(solution_2).tolist()
return df
Output:
num n_obs perc solution_1 solution_2
0 1.0 13 10 1.0 1.000000
1 4.0 34 20 4.0 4.276744
2 11.0 52 30 11.0 9.437209
3 14.0 33 40 11.0 12.560000
4 45.0 31 50 14.0 24.946875
5 56.0 14 60 45.0 50.377778
6 67.0 55 70 67.0 62.440580
7 88.0 73 80 67.0 72.709375
8 99.0 12 90 88.0 83.110938
9 NaN 34 100 88.0 92.348235
