CODEWITHSUNDEEP
pythonpandasquantile
CestLaGalere
CestLaGalere

Reputation: 2969

DataFrame: how do I find value in one column for a quantile in a second column

I have a DataFrame shown below with dates, offset and count.

example, this is the start of the dataframe

df = pd.DataFrame(np.array([['2018-01-01', 0, 1], ['2018-01-01', 26, 2], ['2018-01-01', 178, 8], ['2018-01-01', 187, 10], ['2018-01-01', 197, 13], ['2018-01-01', 208, 15], ['2018-01-01', 219, 16], ['2018-01-01', 224, 19],['2018-01-01', 232, 21], ['2018-01-01', 233, 25], ['2018-01-01', 236, 32],['2018-01-02', 0, 1], ['2018-01-02', 11, 4], ['2018-01-02', 12, 7], ['2018-01-02', 20, 12], ['2018-01-02', 35, 24], ]), columns=['obs_date', 'offset', 'count'])

    obs_date    offset  count
0   2018-01-01  0       1
1   2018-01-01  26      2
2   2018-01-01  178     8
3   2018-01-01  187     10
4   2018-01-01  197     13
5   2018-01-01  208     15
6   2018-01-01  219     16
7   2018-01-01  224     19
8   2018-01-01  232     21
9   2018-01-01  233     25
10  2018-01-01  236     32
11  2018-01-02  0       1
12  2018-01-02  11      4
13  2018-01-02  12      7
14  2018-01-02  20      12
15  2018-01-02  35      24

etc

I'd like to get the (cumulative) ['count'] quantile [0.25, 0.5, 0.75] for each date and find the row with the ['offset'] that that quantile applies to. the total count for each date will be different, and the offsets are not regular so for 2018-01-01 the date & offset that correspond to a counts of 8, 16 & 24 (0.25, 0.5, 0.75 * 32)

something like

0   2018-01-01  178     0.25
1   2018-01-01  219     0.5
2   2018-01-01  232.75  0.75
3   2018-01-02  43      0.25
etc

Upvotes: 2

Views: 489

Answers (1)

Josh Friedlander
Josh Friedlander

Reputation: 11657

This worked for me:

df['count'] = df['count'].astype(int)
quantiles = [.25, .5, .75]

def get_offset(x):
    s = x['count']
    indices = [(s.sort_values()[::-1] <= s.quantile(q)).idxmax() for q in quantiles]
    return df.iloc[indices, x.columns.get_loc('offset')]

res = df.groupby('obs_date').apply(get_offset).reset_index(level=0)

Then you can concat with quantiles:

pd.concat([res.reset_index(drop=True), pd.Series(quantiles * df.obs_date.nunique())], axis=1)

    obs_date    offset  0
0   2018-01-01  178     0.25
1   2018-01-01  208     0.50
2   2018-01-01  224     0.75
3   2018-01-02  11      0.25
4   2018-01-02  12      0.50
5   2018-01-02  20      0.75

Upvotes: 2

Related Questions