pid-matching-script.py

import pandas as pd
import os

def match_product_ids():
    # Read both CSV files
    print("Reading CSV files...")
    pid_reference = pd.read_csv('./pid_reference.csv')
    consolidated_products = pd.read_csv('./output/consolidated_products_clean3.csv')
    
    # Display basic information about both datasets
    print(f"PID Reference rows: {len(pid_reference)}")
    print(f"PID Reference columns: {len(pid_reference.columns)}")
    print(f"PID Reference column names: {', '.join(pid_reference.columns)}")
    
    print(f"Consolidated Products rows: {len(consolidated_products)}")
    print(f"Consolidated Products columns: {len(consolidated_products.columns)}")
    print(f"Consolidated Products column names: {', '.join(consolidated_products.columns)}")
    
    # Create a lookup dictionary from PID Reference
    pid_lookup = {}
    for _, row in pid_reference.iterrows():
        if pd.notna(row['PID']):
            pid_lookup[row['PID']] = {
                'driveImageURL': row['DriveImageURL'] if pd.notna(row['DriveImageURL']) else '',
                'document': row['Document'] if pd.notna(row['Document']) else ''
            }
    
    print(f"Number of unique PIDs in reference data: {len(pid_lookup)}")
    
    # Preview a few entries from the PID reference dictionary
    print("Sample entries from PID reference dictionary:")
    count = 0
    for pid, data in list(pid_lookup.items())[:5]:
        print(f"PID: {pid}, DriveImageURL: {data['driveImageURL']}, Document: {data['document']}")
        count += 1
        if count >= 5:
            break
    
    # Check if there are any sample products
    print("Sample entries from consolidated products:")
    for i in range(min(5, len(consolidated_products))):
        product = consolidated_products.iloc[i]
        print(f"Product ID: {product['product-product_id']}, Title: {product['product-title']}")
    
    # Add new columns to the consolidated products dataframe
    consolidated_products['product-product_img_google_drive_url'] = ''
    consolidated_products['product-document_url'] = ''
    
    # Match and populate the new columns
    match_count = 0
    for i, row in consolidated_products.iterrows():
        product_id = row['product-product_id']
        if product_id in pid_lookup:
            consolidated_products.at[i, 'product-product_img_google_drive_url'] = pid_lookup[product_id]['driveImageURL']
            consolidated_products.at[i, 'product-document_url'] = pid_lookup[product_id]['document']
            if pid_lookup[product_id]['driveImageURL'] or pid_lookup[product_id]['document']:
                match_count += 1
    
    # Calculate match statistics
    print(f"Total products: {len(consolidated_products)}")
    print(f"Products with matched data: {match_count}")
    print(f"Match percentage: {(match_count / len(consolidated_products) * 100):.2f}%")
    
    # Preview a few matched entries
    print("Sample entries from matched products:")
    for i in range(min(5, len(consolidated_products))):
        product = consolidated_products.iloc[i]
        print(f"Product ID: {product['product-product_id']}, DriveImageURL: {product['product-product_img_google_drive_url']}, Document: {product['product-document_url']}")
    
    # Save the result to a new CSV file
    output_file = 'consolidated_products_with_images.csv'
    consolidated_products.to_csv(output_file, index=False)
    print(f"Matched data saved to {output_file}")
    print(f"File size: {os.path.getsize(output_file) / (1024 * 1024):.2f} MB")
    
    return output_file

if __name__ == "__main__":
    output_file = match_product_ids()
    print(f"Process completed. Output file: {output_file}")