enter image description here
My goal is:

  1. for each item in each store, find the number of items in the same store within $2 price difference (i.e. number of items with similar price within the same store).

  2. Then list all the items which has the greatest number of items.

The dataset has about 100,000 records so efficient is also important. My snapshot is a small subset for implementing ideas.

The first step has got me. I have tried groupby, count, sum. None of these get me anywhere to get the desired output. I have used df.sort_values on store_id and price. Can anyone give me a hint?

Below is some sample of my data.

import pandas as pd
data = {'item_id':  ['6dd5392a9991','363a268ae1bc','fcd248a3fe97','20d197a04656','54c6463ffc87', '1b62f63eac43', '4ed99ff1bcdf', '6e19d5b8e99b','89c9b4655a9d', '16740613e6af'],
        'store_id': ['1d632be3f72c','1d632be3f72c','1d632be3f72c','1d632be3f72c','b5d61bc3e6d1','b5d61bc3e6d1','b5d61bc3e6d1','b5d61bc3e6d1','b5d61bc3e6d1','b5d61bc3e61'],
        'price':  [23.54, 20.61, 13.63, 23.69, 13.79, 14.90,  4.09, 14.30, 4.47, 4.51]

df = pd.DataFrame(data)

1 Answer 1


It might not be the fastest, but it'll work:

def condition(row1, row2, threshold):
    return abs(row1['price']-row2['price'])<=threshold

def row_counter(this_df, this_row, threshold):
    return this_df.apply(lambda row: condition(this_row, row, threshold), axis=1).sum()

def counter(this_df, threshold):
    return this_df.apply(lambda row: row_counter(this_df, row, threshold), axis=1)

threshold = 2
result = df.set_index('item_id').groupby('store_id').apply(lambda store_data: counter(store_data, threshold))

The result is:

store_id      item_id     
1d632be3f72c  6dd5392a9991    2
              363a268ae1bc    1
              fcd248a3fe97    1
              20d197a04656    2
b5d61bc3e61   16740613e6af    1
b5d61bc3e6d1  54c6463ffc87    3
              1b62f63eac43    3
              4ed99ff1bcdf    2
              6e19d5b8e99b    3
              89c9b4655a9d    2
dtype: int64
  • $\begingroup$ Thank you so much for your help. It shines in the right direction on me. $\endgroup$
    – Kevin H
    Jul 20, 2020 at 8:28
  • $\begingroup$ You're welcome. If the this solves your question, don't forget to 'accept' it so others know it's solved. $\endgroup$ Jul 20, 2020 at 8:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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