Find and count similar price for each store and output the max count and its corresponding rows

Question

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)

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