Skip to content
The Chat Archive
gpt-3.5-turbo-0613· OpenAI WildChat-4.8M (AI2)

I have a table with columns Document Date, Customer Code, SCU Code, Итого отгрузка, л.,…

  1. User

    I have a table with columns Document Date, Customer Code, SCU Code, Итого отгрузка, л., Отгрузка по акции, л., start_date, end_date, comment, Type. I have a function

    def find_matching_rows(data): # Filter rows with type ‘Рег. продажи’ and empty comment b_rows = data[(data[‘Type’] == ‘Рег. продажи’) & (data[‘comment’].isnull())]

    # Iterate over each row in b_rows
    matching_rows = []
    for index, row in b_rows.iterrows():
        # Find matching rows in data with same customer, SKU, and end_date
        matched_rows = data[
            (data[‘Customer Code’] == row[‘Customer Code’]) &
            (data[‘SCU Code’] == row[‘SCU Code’]) &
            (data[‘end_date’] == row[‘Document Date’]) &
            (data[‘Type’] == ‘Промо продажи’)
        ]
        if not matched_rows.empty:
            matching_rows.append(row)  # Append the current row from b_rows
        
    return pd.DataFrame(matching_rows) if matching_rows else None  # Return the matched rows from b_rows as a DataFrame
    

    I have a table with 1 million rows. Does any more efficient algorithm exist to get the same result as the function find_matching_rows?

  2. Assistant

    The efficiency of the algorithm depends on the specific requirements and constraints of your data. However, there are some potential optimizations you can consider to improve the performance of the function.

    1. Indexing: Ensure that your table has appropriate indexing on the columns used for filtering, such as "Type", "comment", "Customer Code", "SCU Code", "Document Date", and "end_date". This can significantly speed up the filtering process.

    2. Use vectorized operations: Instead of iterating over each row in b_rows and checking for matches one by one, you can use vectorized operations to perform the matching in bulk. Consider using methods like pd.merge() or pd.concat() to join the two datasets based on the matching conditions.

    3. Use set operations: If the customer and SKU codes have a limited number of unique values, you can create sets of unique combinations in both datasets and find the intersections between them. This can help reduce the number of comparisons needed.

    4. Parallel processing: Depending on your hardware capabilities, you can explore parallel computing techniques to distribute the computation across multiple cores or machines. This can speed up the processing time significantly.

    It's important to profile and test different approaches on your specific dataset to determine which one provides the best performance in your case.

  3. User

    Rewrite code using vectorized operations

  4. Assistant

    Sure! Here's an alternative implementation of the find_matching_rows function using vectorized operations:

    import pandas as pd
    
    def find_matching_rows(data):
        # Filter rows with type ‘Рег. продажи’ and empty comment
        b_rows = data[(data['Type'] == 'Рег. продажи') & 
                      (data['comment'].isnull())]
    
        # Find matching rows in data with same customer, SKU, and end_date using merge
        matched_rows = pd.merge(
            b_rows,
            data[data['Type'] == 'Промо продажи'],
            left_on=['Customer Code', 'SCU Code', 'Document Date'],
            right_on=['Customer Code', 'SCU Code', 'end_date'],
            how='inner'
        )
        
        return matched_rows if not matched_rows.empty else None
    

    In this implementation, the matching rows are found by performing an inner join (how='inner') between the filtered b_rows and data DataFrame. The join is based on the matching conditions of the customer code, SKU code, and end date.

    This vectorized approach leverages the optimized join operations in pandas, which can be more efficient than iterating over the rows manually.