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

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.

• Welcome to Code Review! Please edit your question so that the title describes the purpose of the code, rather than its mechanism. We really need to understand the motivational context to give good reviews. Thanks! Nov 12 '19 at 13:41

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.

• Hi, implementing your suggestion results in TypeError: float() argument must be a string or a number, not 'pandas._libs.interval.Interval'. This happens because the intervals in the pd.cut function should be determined as a list of [0.999, 2.333, 3, 4, 7.333, 8.667, 11.0, 13.333, 18.667, 26.333, 34.333, 54.0]. Any idea how to fix this problem instead of inserting the intervals manually? Thank you Graipher!! Nov 12 '19 at 11:37
• @NestorasChalkidis: Fixed. It must've been some leftover from your code that made it working. The trick is to return the bins from pandas.qcut and use that. Nov 12 '19 at 11:42