I have data like this
| High | Low | Price |
|---|---|---|
| 10 | 5 | 7 |
I want to know at which quantile is the price What are the quantile boundaries
I tried posting code but getting following error again and again
Your post appears to contain code that is not properly formatted as code
Expected Output:
| High | Low | Price | Qtr | Qtr_Ranges |
|---|---|---|---|---|
| 10 | 5 | 8 | Q3 | 10, 8.75, 7.5,6.25,5 |
| 10 | 5 | 9 | Q1 | 10, 8.75, 7.5,6.25,5 |
| 10 | 5 | 6 | Q4 | 10, 8.75, 7.5,6.25,5 |
| 10 | 5 | 6.5 | Q3 | 10, 8.75, 7.5,6.25,5 |
Can you guys help? Thanks!!!
CodePudding user response：
A one-liner df['Qtr'] = df.apply(lambda row: f'Q{int(1 4*(row.Price-row.Low)/(row.High-row.Low))}', axis=1).
No need to calculate the boundaries if your need is to only find the quartile (splitting by 4).
You can generalize it to quantiles and generate boudaries like so:
nb_quantiles = 4
df[f'q_{nb_quantiles}'] = df.apply(lambda row: f'Q{int(1 nb_quantiles*(row.Price-row.Low)/(row.High-row.Low))}', axis=1)
df[f'q_{nb_quantiles}_boundaries'] = df[['High', 'Low']].apply(lambda row: np.append(np.arange(row.Low, row.High, (row.High-row.Low)/nb_quantiles), row.High), axis=1)
CodePudding user response：
Try this one.
a = df[['High','Low','Price']].values
bins = np.quantile(a[:,:2], q=[0,0.25,.5,.75,1.], axis=1)
qrng = bins.T[:,::-1].tolist()
df['Qtr_Ranges'] = qrng
c =np.apply_along_axis(np.searchsorted,1, bins.T, a[:,2], side='left')
#print(c)
qtr = np.diag(c)
df['Qtr'] = qtr
df['Qtr'] = 'Q' df['Qtr'].astype(str)