itayyamin/csv-joiner

1import pandas as pd2import json3import re4 5# Load the two CSV files6file1_path = '/Users/itayyamin/Downloads/extract-2025-12-08T10_24_53.586Z.csv'7file2_path = '/Users/itayyamin/Downloads/extract-2025-12-08T10_24_51.220Z.csv'8 9print("Loading CSV files...")10df1 = pd.read_csv(file1_path)11df2 = pd.read_csv(file2_path)12 13print(f"File 1 shape: {df1.shape}")14print(f"File 2 shape: {df2.shape}")15 16# Join the two dataframes on '@CommunicationId'17print("\nJoining dataframes on '@CommunicationId'...")18df_joined = pd.merge(df1, df2, on='@CommunicationId', how='outer', suffixes=('_file1', '_file2'))19 20print(f"Joined dataframe shape: {df_joined.shape}")21 22# Function to extract customerId from request_body23def extract_customer_id(request_body):24    if pd.isna(request_body):25        return None26    27    try:28        # Try to parse as JSON first29        # Replace double quotes that are escaped30        cleaned = request_body.replace('""', '"')31        data = json.loads(cleaned)32        return data.get('customerId', None)33    except (json.JSONDecodeError, AttributeError):34        # If JSON parsing fails, use regex as fallback35        try:36            match = re.search(r'"customerId"\s*:\s*"([^"]+)"', str(request_body))37            if match:38                return match.group(1)39        except:40            pass41    return None42 43# Extract customerId from request_body44# Check which columns have request_body45if 'request_body' in df_joined.columns:46    print("\nExtracting customerId from 'request_body' column...")47    df_joined['customerId'] = df_joined['request_body'].apply(extract_customer_id)48elif 'request_body_file1' in df_joined.columns:49    print("\nExtracting customerId from 'request_body_file1' column...")50    df_joined['customerId'] = df_joined['request_body_file1'].apply(extract_customer_id)51    # If file1 doesn't have it, try file252    mask = df_joined['customerId'].isna()53    if 'request_body_file2' in df_joined.columns and mask.any():54        print("Filling missing customerIds from 'request_body_file2'...")55        df_joined.loc[mask, 'customerId'] = df_joined.loc[mask, 'request_body_file2'].apply(extract_customer_id)56 57# Display some statistics58print(f"\nTotal rows: {len(df_joined)}")59print(f"Rows with customerId extracted: {df_joined['customerId'].notna().sum()}")60print(f"Rows with missing customerId: {df_joined['customerId'].isna().sum()}")61 62# Show a sample of the extracted customerIds63print("\nSample of extracted customerIds:")64print(df_joined[['@CommunicationId', 'customerId']].head(10))65 66# Save the result to a new CSV file67output_path = '/Users/itayyamin/Downloads/joined_with_customerId.csv'68df_joined.to_csv(output_path, index=False)69print(f"\nResult saved to: {output_path}")70 71