使用Pandas遍历目录CSV文件，关联数据库ID并合并至finalDf

Got it, let's walk through a practical, step-by-step implementation that covers all your requirements. I'll use common tools like pandas for data handling, os for directory traversal, and sqlalchemy for database interactions (you can swap this with your preferred DB driver if needed).

Step 1: Import Required Libraries #

First, make sure you have these packages installed (pip install pandas sqlalchemy if not), then import them:

import pandas as pd
import os
from sqlalchemy import create_engine

Step 2: Initialize Your Final DataFrame #

We'll start with an empty DataFrame that includes context columns (category ID and marketplace ID) alongside the product data—this will make downstream operations easier:

# Define the structure for our combined dataset
finalDf = pd.DataFrame(columns=['PRODUCT', 'CATEGORY_ID', 'MARKETPLACE_ID'])

Step 3: Set Up Database Connection #

Replace the connection string with your actual database credentials. Here's an example for PostgreSQL (adjust for MySQL, SQLite, etc.):

# Update this with your DB's connection details
db_engine = create_engine('postgresql://username:password@host:port/db_name')

Step 4: Traverse the Target Directory and Process Each CSV #

Now we'll loop through every CSV in your specified folder. For each file, we'll:

• Extract the category and marketplace (assuming a filename pattern like category_marketplace.csv—adjust this logic to match your actual file naming or metadata source)
• Fetch the corresponding IDs from your database
• Load the CSV, add the ID columns, and append the product data to finalDf

# Replace with your target directory path
target_dir = '/path/to/your/csv/files'

# Optional: Collect DataFrames in a list first for better performance (vs. concat in loop)
df_list = []

for filename in os.listdir(target_dir):
    if filename.endswith('.csv'):
        # Extract category and marketplace from filename (tweak this to fit your workflow!)
        # Example: "electronics_amazon.csv" → category="electronics", marketplace="amazon"
        name_parts = filename.replace('.csv', '').split('_')
        category = name_parts[0]
        marketplace = name_parts[1]
        
        # Fetch IDs from the database
        with db_engine.connect() as conn:
            # Get category ID
            category_query = "SELECT id FROM categories WHERE name = %s"
            category_id = conn.execute(category_query, (category,)).scalar()
            
            # Get marketplace ID
            marketplace_query = "SELECT id FROM marketplaces WHERE name = %s"
            marketplace_id = conn.execute(marketplace_query, (marketplace,)).scalar()
        
        # Load the CSV file
        csv_path = os.path.join(target_dir, filename)
        df = pd.read_csv(csv_path)
        
        # Add the ID columns to the current CSV's data
        df['CATEGORY_ID'] = category_id
        df['MARKETPLACE_ID'] = marketplace_id
        
        # Add the relevant columns to our list (avoids repeated concat overhead)
        df_list.append(df[['PRODUCT', 'CATEGORY_ID', 'MARKETPLACE_ID']])

# Combine all collected DataFrames into the final dataset
finalDf = pd.concat(df_list, ignore_index=True)

Key Adjustments & Tips #

• File Naming Logic: If your CSV filenames don't follow the category_marketplace pattern, adjust how you fetch category/marketplace info—for example, use a config file or a database lookup based on filename.
• Database Schema: Make sure your table names (categories, marketplaces) and column names (name, id) match your actual database setup.
• Error Handling: Add try-except blocks to handle cases where a category/marketplace isn't found in the DB, or a CSV fails to load:

  try:
      # ... your processing code here ...
  except Exception as e:
      print(f"Failed to process {filename}: {str(e)}")
      continue
  

• Performance: Collecting DataFrames in a list before concatenating is much faster than appending to finalDf in each loop, especially with large datasets.

内容的提问来源于stack exchange，提问作者Cyrille MODIANO