I am working on the task to get all possible combinations (pairs) of ID's (companies) which participated in one bid and create a new data frame with ID_1, ID_2, matching parameter (tender ID).

I have prepared the following two functions which provide me with the required result however the execution time exceeds several hours when applying to df with more than 500k lines.

import pandas as pd
from itertools import combinations
def get_pairs(dataframe, items):
   return (
       (c1, c2, item)
       for item in items
       for c1, c2 in combinations(dataframe[dataframe['tender_product'] == item]['ID_name'], 2)
def get_all_pairs(df):
        pairs = get_pairs(df,df['tender_product'])
        df= pd.DataFrame(pairs, columns=['ID_name1', 'ID_name2','tender_product'])
        df=df.query('ID_name1 != ID_name2')
        df['pair'] = df['ID_name1'].astype(str)+'_'+df['ID_name2'].astype(str)
        df['reversed_pair'] =df['ID_name1'].astype(str)+'_'+df['ID_name2'].astype(str)
        return df

What are the options to optimize the code to make it work faster with the same result?

The sample of intial data:

ID tender_product
1 tender_1
2 tender_1
3 tender_2
4 tender_2

The sample of current output:

ID1 ID2 tender_product pair reversed_pair
1 2 tender_1 1_2 2_1
3 4 tender_2 3_4 4_3

1 Answer 1


What are the options to optimize the code

You wrote

       for item in items
       ... dataframe['tender_product'] == item ...

and complained that this takes a Long Time. Even in the example data we can see that the "find all rows containing item" scan happens multiple times. And then we discard duplicates.

What you wanted was to .groupby() items. That lets you generate just the required rows rather than generating tons of dups only to later drop them.

  • 1
    \$\begingroup\$ Thank you! First of all I've changed the items to df['tender_product'].unique() and series with IDs from grouped dataframe as advised. The execution time changed from several hours to 47 secs. \$\endgroup\$
    – Vasya_Ch
    Commented Sep 28, 2023 at 11:35

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.