CODEWITHSUNDEEP
pythonpandas
Robert King
Robert King

Reputation: 994

Get the percentile of a column ordered by another column

I have a dataframe with two columns, score and order_amount. I want to find the score Y that represents the Xth percentile of order_amount. I.e. if I sum up all of the values of order_amount where score <= Y I will get X% of the total order_amount.

I have a solution below that works, but it seems like there should be a more elegant way with pandas.

import pandas as pd
test_data = {'score': [0.3,0.1,0.2,0.4,0.8],
             'value': [10,100,15,200,150]
}

df = pd.DataFrame(test_data)
df

    score   value
0   0.3 10
1   0.1 100
2   0.2 15
3   0.4 200
4   0.8 150


# Now we can order by `score` and use `cumsum` to calculate what we want 
df_order = df.sort_values('score')
df_order['percentile_value'] = 100*df_order['value'].cumsum()/df_order['value'].sum()
df_order

    score   value   percentile_value
1   0.1 100 21.052632
2   0.2 15  24.210526
0   0.3 10  26.315789
3   0.4 200 68.421053
4   0.8 150 100.000000

# Now can find the first value of score with percentile bigger than 50% (for example)
df_order[df_order['percentile_value']>50]['score'].iloc[0]

Upvotes: 1

Views: 670

Answers (2)

anky
anky

Reputation: 75150

here is another way starting from the oriinal dataframe using np.percentile:

df = df.sort_values('score')

df.loc[np.searchsorted(df['value'],np.percentile(df['value'].cumsum(),50)),'score']

Or series.quantile

df.loc[np.searchsorted(df['value'],df['value'].cumsum().quantile(0.5)),'score']

Or similarly with iloc, if index is not default:

df.iloc[np.searchsorted(df['value']
        ,np.percentile(df['value'].cumsum(),50)),df.columns.get_loc('score')]

0.4

Upvotes: 2

jezrael
jezrael

Reputation: 863611

Use Series.searchsorted:

idx = df_order['percentile_value'].searchsorted(50)
print (df_order.iloc[idx, df.columns.get_loc('score')])
0.4

Or get first value of filtered Series with next and iter, if no match returned some default value:

s = df_order.loc[df_order['percentile_value'] > 50, 'score']
print (next(iter(s), 'no match'))
0.4

One line solution:

out = next(iter((df.sort_values('score')
           .assign(percentile_value = lambda x: 100*x['value'].cumsum()/x['value'].sum())
           .query('percentile_value > 50')['score'])),'no matc')
print (out)
0.4

Upvotes: 3

Related Questions