مقدمه

در درس پیشین، با مفهوم ETL و سه مرحله‌ی اصلی آن یعنی استخراج، تبدیل و بارگذاری آشنا شدیم. دیدیم که هر کدام از این مراحل چه وظایفی دارند و با چه چالش‌هایی همراه هستند. اما دانش تئوری به تنهایی کافی نیست. برای تبدیل شدن به یک مهندس داده، باید بتوانیم این مفاهیم را به کدهای اجرایی تبدیل کنیم.

در این درس، با استفاده از پایتون و کتابخانه‌ی پانداس، یک خط‌لوله‌ی داده‌ی کامل و عملی می‌سازیم. این خط‌لوله، داده‌های فروش را از چندین منبع مختلف (فایل CSV، فایل JSON و یک پایگاه‌داده) استخراج می‌کند، آنها را تمیز و تبدیل می‌نماید و در نهایت نتیجه را در یک پایگاه‌داده‌ی تحلیلی بارگذاری می‌کند.

طراحی خط‌لوله

پیش از نوشتن کد، بهتر است یک نقشه‌ی راه برای خط‌لوله‌ی خود طراحی کنیم. این کار به ما کمک می‌کند تا ساختار کلی پروژه را بهتر درک کنیم و مراحل را به صورت منظم پیش ببریم.

معماری خط‌لوله‌ی داده:

برای درک بهتر کاری که در این بخش قرار است انجام دهیم، شکل زیر را به دقت بررسی کنید:

مراحل مختلف در یک پروژه ETL (یا خط‌لوله‌ی دیتای مد نظر ما)

مراحل خط‌لوله:

بعد از بررسی شکل فوق، می‌توانید مراحل مختلف را در جدول زیر نیز مشاهده نمائید که توضیحات بیشتری را شامل شده است.

مرحلهورودیخروجیعملیات
استخراجفایل CSV، فایل JSON، پایگاه‌دادهسه دیتافریم پانداسخواندن داده از منابع مختلف
تبدیلسه دیتافریم جداگانهیک دیتافریم تمیز و تجمیع‌شدهتمیزکاری، ادغام، گروه‌بندی
بارگذاریدیتافریم نهاییپایگاه‌داده‌ی SQLiteذخیره‌سازی در جدول تحلیلی

پیاده‌سازی مرحله‌ی استخراج

در این مرحله، داده‌ها را از سه منبع مختلف استخراج می‌کنیم. در ادامه تلاش می‌کنیم با ارئه‌ی کدهای متناسب با بحث در پایتون، کل داستان و مراحل را بصورت عملی بشما نشان دهیم.

کد مرحله‌ی استخراج:

import pandas as pd
import sqlite3

def extract_from_csv(file_path):
    """Extract data from a CSV file."""
    print(f"📂 Extracting data from CSV: {file_path}")
    df = pd.read_csv(file_path)
    print(f"   ✅ {len(df)} records extracted from CSV")
    return df

def extract_from_json(file_path):
    """Extract data from a JSON file."""
    print(f"📂 Extracting data from JSON: {file_path}")
    df = pd.read_json(file_path)
    print(f"   ✅ {len(df)} records extracted from JSON")
    return df

def extract_from_database(db_path, table_name):
    """Extract data from a SQLite database."""
    print(f"📂 Extracting data from database: {db_path} -> {table_name}")
    conn = sqlite3.connect(db_path)
    df = pd.read_sql_query(f"SELECT * FROM {table_name}", conn)
    conn.close()
    print(f"   ✅ {len(df)} records extracted from database")
    return df

# اجرای مرحله‌ی استخراج
csv_data = extract_from_csv('../data/sales_data.csv')

json_data = extract_from_json('../data/sales_data.json')

db_data = extract_from_database('../data/sales.db', 'sales')

پیاده‌سازی مرحله‌ی تبدیل

در این مرحله، داده‌های استخراج‌شده را تمیز، تبدیل و با یکدیگر ادغام می‌کنیم.

کد مرحله‌ی تبدیل:

def transform_data(df):
    """Clean, transform and prepare data for loading."""
    print("🔄 Starting data transformation...")

    # ایجاد یک کپی از داده برای جلوگیری از تغییر داده‌های اصلی
    df_clean = df.copy()

    # ۱. تبدیل ستون تاریخ به فرمت استاندارد
    if 'sale_date' in df_clean.columns:
        df_clean['sale_date'] = pd.to_datetime(df_clean['sale_date'])
        print("   ✅ Date column converted to datetime format")

    # ۲. حذف رکوردهای تکراری
    initial_count = len(df_clean)
    df_clean = df_clean.drop_duplicates()
    print(f"   ✅ {initial_count - len(df_clean)} duplicate records removed")

    # ۳. مدیریت مقادیر گم‌شده (با صفر پر کنید)
    missing_cols = df_clean.columns[df_clean.isnull().any()].tolist()
    if missing_cols:
        df_clean = df_clean.fillna(0)
        print(f"   ✅ Missing values in {missing_cols} filled with 0")

    # ۴. ایجاد ستون کل فروش
    if 'unit_price' in df_clean.columns and 'quantity_sold' in df_clean.columns:
        df_clean['total_sales'] = df_clean['unit_price'] * df_clean['quantity_sold']
        print("   ✅ Total sales column created")

    # ۵. فیلتر کردن رکوردهای با فروش مثبت
    if 'quantity_sold' in df_clean.columns:
        df_clean = df_clean[df_clean['quantity_sold'] > 0]
        print(f"   ✅ Records with zero/negative sales removed")

    # ۶. گروه‌بندی بر اساس منطقه و محصول
    if all(col in df_clean.columns for col in ['region', 'category']):
        summary = df_clean.groupby(['region', 'category'])['total_sales'].sum().reset_index()
        print("   ✅ Data grouped by region and category")

        # اضافه کردن درصد مشارکت هر گروه
        total_sales = summary['total_sales'].sum()
        summary['percentage'] = (summary['total_sales'] / total_sales * 100).round(2)
        print(f"   ✅ Percentage column added")

        return summary
    else:
        print("   ⚠️ Required columns ('region', 'category') not found")
        return df_clean

# اجرای مرحله‌ی تبدیل (ترکیب داده‌ها)
# ترکیب داده‌های CSV و JSON (و در صورت وجود، دیتابیس)

combined_data = pd.concat([csv_data, json_data], ignore_index=True)
transformed_data = transform_data(combined_data)

print(f"📊 Transformed data shape: {transformed_data.shape}")
print(transformed_data.head())

پیاده‌سازی مرحله‌ی بارگذاری

در این مرحله، داده‌های تبدیل‌شده را در مقصد نهایی ذخیره می‌کنیم.

کد مرحله‌ی بارگذاری:

def load_to_database(df, db_path, table_name):
    """Load transformed data to a SQLite database."""
    print(f"💾 Loading data to database: {db_path} -> {table_name}")

    # ایجاد اتصال به پایگاه‌داده
    conn = sqlite3.connect(db_path)

    # بارگذاری داده در جدول (جایگزینی اگر از قبل وجود داشته باشد)
    df.to_sql(table_name, conn, if_exists='replace', index=False)
    print(f"   ✅ {len(df)} records loaded to table '{table_name}'")

    # نمایش نمونه‌ای از داده‌های بارگذاری‌شده
    sample = pd.read_sql_query(f"SELECT * FROM {table_name} LIMIT 5", conn)
    print("   📊 Sample of loaded data:")
    print(sample)

    conn.close()
    return True

def load_to_csv(df, file_path):
    """Load transformed data to a CSV file."""
    print(f"💾 Loading data to CSV: {file_path}")
    df.to_csv(file_path, index=False)
    print(f"   ✅ {len(df)} records saved to {file_path}")
    return True

# اجرای مرحله‌ی بارگذاری
load_to_database(transformed_data, '../data/sales_analytics.db', 'sales_summary')

load_to_csv(transformed_data, '../data/sales_analytics.csv')

print("✅ ETL Pipeline completed successfully!")

اجرای کامل خط‌لوله

حالا همه‌ی بخش‌ها را در یک اسکریپت کامل ترکیب می‌کنیم:

import pandas as pd
import sqlite3

"""
مرحله‌ی اول خط‌لوله
استخراج
Extraction
"""


def extract_from_csv(file_path):
    """Extract data from a CSV file."""
    print(f"📂 Extracting data from CSV: {file_path}")
    df = pd.read_csv(file_path)
    print(f"   ✅ {len(df)} records extracted from CSV")
    return df


def extract_from_json(file_path):
    """Extract data from a JSON file."""
    print(f"📂 Extracting data from JSON: {file_path}")
    df = pd.read_json(file_path)
    print(f"   ✅ {len(df)} records extracted from JSON")
    return df


def extract_from_database(db_path, table_name):
    """Extract data from a SQLite database."""
    print(f"📂 Extracting data from database: {db_path} -> {table_name}")
    conn = sqlite3.connect(db_path)
    df = pd.read_sql_query(f"SELECT * FROM {table_name}", conn)
    conn.close()
    print(f"   ✅ {len(df)} records extracted from database")
    return df


# اجرای مرحله‌ی استخراج
csv_data = extract_from_csv("../data/sales_data.csv")

json_data = extract_from_json("../data/sales_data.json")

db_data = extract_from_database("../data/sales.db", "sales")


"""
مرحله‌ی دوم خط‌لوله
تبدیل
Data Transformation
"""


def transform_data(df):
    """Clean, transform and prepare data for loading."""
    print("🔄 Starting data transformation...")

    # ایجاد یک کپی از داده برای جلوگیری از تغییر داده‌های اصلی
    df_clean = df.copy()

    # ۱. تبدیل ستون تاریخ به فرمت استاندارد
    if "sale_date" in df_clean.columns:
        df_clean["sale_date"] = pd.to_datetime(df_clean["sale_date"])
        print("   ✅ Date column converted to datetime format")

    # ۲. حذف رکوردهای تکراری
    initial_count = len(df_clean)
    df_clean = df_clean.drop_duplicates()
    print(f"   ✅ {initial_count - len(df_clean)} duplicate records removed")

    # ۳. مدیریت مقادیر گم‌شده (با صفر پر کنید)
    missing_cols = df_clean.columns[df_clean.isnull().any()].tolist()
    if missing_cols:
        df_clean = df_clean.fillna(0)
        print(f"   ✅ Missing values in {missing_cols} filled with 0")

    # ۴. ایجاد ستون کل فروش
    if "unit_price" in df_clean.columns and "quantity_sold" in df_clean.columns:
        df_clean["total_sales"] = df_clean["unit_price"] * df_clean["quantity_sold"]
        print("   ✅ Total sales column created")

    # ۵. فیلتر کردن رکوردهای با فروش مثبت
    if "quantity_sold" in df_clean.columns:
        df_clean = df_clean[df_clean["quantity_sold"] > 0]
        print(f"   ✅ Records with zero/negative sales removed")

    # ۶. گروه‌بندی بر اساس منطقه و محصول
    if all(col in df_clean.columns for col in ["region", "category"]):
        summary = (
            df_clean.groupby(["region", "category"])["total_sales"].sum().reset_index()
        )
        print("   ✅ Data grouped by region and category")

        # اضافه کردن درصد مشارکت هر گروه
        total_sales = summary["total_sales"].sum()
        summary["percentage"] = (summary["total_sales"] / total_sales * 100).round(2)
        print(f"   ✅ Percentage column added")

        return summary
    else:
        print("   ⚠️ Required columns ('region', 'category') not found")
        return df_clean


# اجرای مرحله‌ی تبدیل (ترکیب داده‌ها)
# ترکیب داده‌های CSV و JSON (و در صورت وجود، دیتابیس)

combined_data = pd.concat([csv_data, json_data], ignore_index=True)
transformed_data = transform_data(combined_data)

print(f"\n📊 Transformed data shape: {transformed_data.shape}")
print(transformed_data.head())

"""
مرحله‌ی سوم خط‌لوله
بارگذاری
Load
"""


def load_to_database(df, db_path, table_name):
    """Load transformed data to a SQLite database."""
    print(f"💾 Loading data to database: {db_path} -> {table_name}")

    # ایجاد اتصال به پایگاه‌داده
    conn = sqlite3.connect(db_path)

    # بارگذاری داده در جدول (جایگزینی اگر از قبل وجود داشته باشد)
    df.to_sql(table_name, conn, if_exists="replace", index=False)
    print(f"   ✅ {len(df)} records loaded to table '{table_name}'")

    # نمایش نمونه‌ای از داده‌های بارگذاری‌شده
    sample = pd.read_sql_query(f"SELECT * FROM {table_name} LIMIT 5", conn)
    print("   📊 Sample of loaded data:")
    print(sample)

    conn.close()
    return True


def load_to_csv(df, file_path):
    """Load transformed data to a CSV file."""
    print(f"💾 Loading data to CSV: {file_path}")
    df.to_csv(file_path, index=False)
    print(f"   ✅ {len(df)} records saved to {file_path}")
    return True


# اجرای مرحله‌ی بارگذاری
load_to_database(transformed_data, "../data/sales_analytics.db", "sales_summary")
load_to_csv(transformed_data, "../data/sales_analytics.csv")

print("✅ ETL Pipeline completed successfully!")

توجه فرمائید که بعد از اجرای کد فوق خروجی زیر را در پاورشل ملاحظه می‌کنیم:

(.decourse) PS E:\Simple-Data-Engineering-Course\src\Chapter-04> py .\Whole-ETL-code.py

📂 Extracting data from CSV: ../data/sales_data.csv
   ✅ 20 records extracted from CSV
📂 Extracting data from JSON: ../data/sales_data.json
   ✅ 20 records extracted from JSON
📂 Extracting data from database: ../data/sales.db -> sales
   ✅ 2 records extracted from database

🔄 Starting data transformation...
   ✅ Date column converted to datetime format
   ✅ 20 duplicate records removed
   ✅ Total sales column created
   ✅ Records with zero/negative sales removed
   ✅ Data grouped by region and category
   ✅ Percentage column added

📊 Transformed data shape: (6, 4)
    region     category  total_sales  percentage
0  Isfahan  Accessories       5900.0        4.01
1  Isfahan  Electronics      20760.0       14.12
2   Shiraz  Accessories       7650.0        5.20
3   Shiraz  Electronics      43760.0       29.77
4   Tehran  Accessories      25380.0       17.27

💾 Loading data to database: ../data/sales_analytics.db -> sales_summary
   ✅ 6 records loaded to table 'sales_summary'

   📊 Sample of loaded data:
    region     category  total_sales  percentage
0  Isfahan  Accessories       5900.0        4.01
1  Isfahan  Electronics      20760.0       14.12
2   Shiraz  Accessories       7650.0        5.20
3   Shiraz  Electronics      43760.0       29.77
4   Tehran  Accessories      25380.0       17.27

💾 Loading data to CSV: ../data/sales_analytics.csv

   ✅ 6 records saved to ../data/sales_analytics.csv

✅ ETL Pipeline completed successfully!

چه اتفاقی افتاد؟

شما با موفقیت یک خط‌لوله‌ی کامل ETL را از مرحله‌ی استخراج تا بارگذاری اجرا کردید. در ادامه، تحلیل کامل آنچه اتفاق افتاده را با هم مرور می‌کنیم.

۱. مرحله‌ی استخراج (Extract)
این مرحله با موفقیت انجام شد و داده‌ها از سه منبع مختلف استخراج گردیدند.

منبع دادهتعداد رکوردهاوضعیت
فایل CSV (sales_data.csv)۲۰ رکورد✅ موفق
فایل JSON (sales_data.json)۲۰ رکورد✅ موفق
پایگاه‌داده SQLite (sales.db)۲ رکورد✅ موفق

نکته‌ی مهم: داده‌های پایگاه‌داده فقط ۲ رکورد داشتند (چون قبلن فقط دو رکورد نمونه در آن درج شده بود). در مجموع، ۴۲ رکورد از سه منبع مختلف استخراج شد.

۲. مرحله‌ی تبدیل (Transform)
در این مرحله، عملیات زیر روی داده‌های ترکیب‌شده (۴۲ رکورد) انجام شد:

عملیاتنتیجه
تبدیل ستون تاریخستون sale_date به فرمت تاریخ استاندارد تبدیل شد.
حذف رکوردهای تکراری۲۰ رکورد تکراری شناسایی و حذف شد. این یعنی داده‌های CSV و JSON کاملن مشابه بودند و ترکیب آنها باعث تکرار شد.
ایجاد ستون کل فروشستون جدید total_sales با ضرب قیمت واحد در تعداد فروش ایجاد شد.
فیلتر فروش مثبتتمام رکوردهای با فروش صفر یا منفی حذف شدند.
گروه‌بندیداده‌ها بر اساس دو ستون region و category گروه‌بندی شدند.
محاسبه‌ی درصدستون percentage اضافه شد که سهم هر گروه را از کل فروش نشان می‌دهد.

نتیجه‌ی نهایی تبدیل:
یک دیتافریم با ۶ سطر و ۴ ستون که نشان‌دهنده‌ی جمع‌بندی فروش در هر منطقه و دسته‌بندی است.

داده‌های تبدیل‌شده:

regioncategorytotal_salespercentage
IsfahanAccessories۵,۹۰۰۴.۰۱٪
IsfahanElectronics۲۰,۷۶۰۱۴.۱۲٪
ShirazAccessories۷,۶۵۰۵.۲۰٪
ShirazElectronics۴۳,۷۶۰۲۹.۷۷٪
TehranAccessories۲۵,۳۸۰۱۷.۲۷٪
TehranElectronics۴۳,۵۸۰۲۹.۶۳٪

تحلیل سریع داده‌ها:

  • بیشترین فروش: مربوط به منطقه‌ی تهران در دسته‌ی الکترونیک با ۴۳,۵۸۰ تومان.
  • کمترین فروش: مربوط به منطقه‌ی اصفهان در دسته‌ی لوازم جانبی با ۵,۹۰۰ تومان.
  • سهم عمده فروش: مناطق تهران و شیراز تقریبن ۶۰٪ از کل فروش را تشکیل می‌دهند.
  • الکترونیک در برابر لوازم جانبی: فروش الکترونیک در همه‌ی مناطق بیشتر از لوازم جانبی است.

۳. مرحله‌ی بارگذاری (Load)

اقداممسیروضعیت
ذخیره در پایگاه‌داده SQLite../data/sales_analytics.db → جدول sales_summary✅ موفق
ذخیره در فایل CSV../data/sales_analytics.csv✅ موفق

جمع‌بندی نهایی

این خط‌لوله‌ی ETL با موفقیت:
۱. داده‌های خام را از سه منبع مختلف (فایل CSV، فایل JSON و پایگاه‌داده) جمع‌آوری کرد.
۲. با حذف ۲۰ رکورد تکراری، داده‌ها را تمیز کرد.
۳. با محاسبه‌ی کل فروش و گروه‌بندی، داده‌ها را به سطح تحلیلی تبدیل نمود.
۴. نتایج را در دو فرمت مختلف (پایگاه‌داده و CSV) برای استفاده‌های بعدی ذخیره کرد.

نکات مهمی که از این تمرین یاد گرفتید

مفهومکاربرد
یکپارچه‌سازی دادهداده‌های پراکنده از منابع مختلف را به یک ساختار منسجم تبدیل کردید.
تمیزکاری دادهرکوردهای تکراری را شناسایی و حذف کردید.
تبدیل دادهستون‌های جدید ایجاد و داده‌ها را برای تحلیل آماده کردید.
گروه‌بندی و خلاصه‌سازیداده‌ها را در سطح منطقه و دسته‌بندی جمع‌بندی کردید.
ذخیره‌سازی در مقاصد مختلفداده را در پایگاه‌داده و فایل CSV ذخیره کردید.

جمع‌بندی و تمرین

در این درس، یک خط‌لوله‌ی ETL کامل با استفاده از پایتون و پانداس ساختیم که داده‌ها را از چندین منبع استخراج، تبدیل و در نهایت در یک پایگاه‌داده بارگذاری می‌کند. این خط‌لوله نشان می‌دهد که چگونه می‌توان مفاهیم تئوری را به کدهای عملی و قابل اجرا تبدیل کرد.

تمرین پیشنهادی:
۱. یک منبع داده‌ی جدید (مثلن یک فایل Excel) به خط‌لوله اضافه کنید و آن را در مرحله‌ی استخراج بگنجانید.
۲. یک ستون جدید به نام profit با فرض سود ۲۰٪ به داده‌ها اضافه کنید.
۳. یک فیلتر جدید برای حذف رکوردهای با قیمت واحد کمتر از ۱۰۰ تومان اضافه کنید.
۴. خط‌لوله را به گونه‌ای تغییر دهید که خروجی را در هر دو فرمت CSV و Parquet ذخیره کند.