3
\$\begingroup\$

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.

\$\endgroup\$
  • 1
    \$\begingroup\$ 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! \$\endgroup\$ – Toby Speight Nov 12 at 13:41
3
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ 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!! \$\endgroup\$ – Nestoras Chalkidis Nov 12 at 11:37
  • \$\begingroup\$ @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. \$\endgroup\$ – Graipher Nov 12 at 11:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.