Map price values to bins where bins are of a larger price range

Question

I have two different pandas data frames where in the first data frame (price), I have two columns. The first column named value has some values in it and the second column amount has the available amount for each price. The second data frame (bins) has as index some price intervals which are produced from the price data frame. For each row of the price data frame I check each row of the value column to find the interval that it belongs from the bins data frame and if the value is in an interval I assign the available amount in the bins data frame. If another value is again in the same interval I sum up these amounts in the group_bins data frame.

import pandas as pd

bins = pd.DataFrame({
    'value': [1, 2, 5, 7, 8, 16, 20, 3, 9, 11, 35, 12, 54, 33, 3, 22, 23]
})

price = pd.DataFrame({
    'value': [2, 5, 7, 8, 16, 20, 3, 9, 11, 2.5, 3.4],
    'amount': [50, 112, 130, 157, 146, 148, 300, 124, 151, 100, 32]
})

bins['bins'] = pd.qcut(bins['value'], 12)
group_bins = bins.groupby(['bins']).sum()
group_bins['amount'] = 0
del group_bins['value']

for j in range(price.shape[0]):
    for i in range(group_bins.shape[0]):
        if price.loc[j, 'value'] in group_bins.index[i]:
            group_bins.loc[group_bins.index[i], 'amount'] += price.loc[j, 'amount']

            break

Expected Result:

                 amount
bins                    
(0.999, 2.333]        50
(2.333, 3.0]         400
(3.0, 5.0]           144
(5.0, 7.333]         130
(7.333, 8.667]       157
(8.667, 11.0]        275
(11.0, 13.333]         0
(13.333, 18.667]     146
(18.667, 22.0]       148
(22.0, 26.333]         0
(26.333, 34.333]       0
(34.333, 54.0]         0

My problem is that I have 100k data and all this process takes too long to finish. Is there any elegant and much faster way to replace these nested for loops and the if condition?

The expected result is the final column in the group_bins amount column. Any help would be much appreciated! Thank you.

Answer 1

This can be solved using groupby directly. First, get the bins you want to use, then just pandas.cut the actual values into those bins.

binning = pd.qcut(bins['value'], 12, retbins=True)[1]
group_bins = price.groupby(pd.cut(price.value, binning)).amount.sum()

This produces basically the same output for the given example, except that it is a pandas.Series instead of a pandas.DataFrame:

value
(1.0, 2.333]         50
(2.333, 3.0]        400
(3.0, 5.0]          144
(5.0, 7.333]        130
(7.333, 8.667]      157
(8.667, 11.0]       275
(11.0, 13.333]        0
(13.333, 18.667]    146
(18.667, 22.0]      148
(22.0, 26.333]        0
(26.333, 34.333]      0
(34.333, 54.0]        0
Name: amount, dtype: int64

In addition, you should probaly put this into a function and only call it from a if __name__ == "__main__": guard to allow importing from the file.

I am not quite sure why you need to determine the binning the way you do, though. From your variable names I would have assumed that bins.value are the bin edges to be used instead.