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

مراحل خطلوله:
بعد از بررسی شکل فوق، میتوانید مراحل مختلف را در جدول زیر نیز مشاهده نمائید که توضیحات بیشتری را شامل شده است.
| مرحله | ورودی | خروجی | عملیات |
|---|---|---|---|
| استخراج | فایل 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 اضافه شد که سهم هر گروه را از کل فروش نشان میدهد. |
نتیجهی نهایی تبدیل:
یک دیتافریم با ۶ سطر و ۴ ستون که نشاندهندهی جمعبندی فروش در هر منطقه و دستهبندی است.
دادههای تبدیلشده:
| region | category | total_sales | percentage |
|---|---|---|---|
| Isfahan | Accessories | ۵,۹۰۰ | ۴.۰۱٪ |
| Isfahan | Electronics | ۲۰,۷۶۰ | ۱۴.۱۲٪ |
| Shiraz | Accessories | ۷,۶۵۰ | ۵.۲۰٪ |
| Shiraz | Electronics | ۴۳,۷۶۰ | ۲۹.۷۷٪ |
| Tehran | Accessories | ۲۵,۳۸۰ | ۱۷.۲۷٪ |
| Tehran | Electronics | ۴۳,۵۸۰ | ۲۹.۶۳٪ |
تحلیل سریع دادهها:
- بیشترین فروش: مربوط به منطقهی تهران در دستهی الکترونیک با ۴۳,۵۸۰ تومان.
- کمترین فروش: مربوط به منطقهی اصفهان در دستهی لوازم جانبی با ۵,۹۰۰ تومان.
- سهم عمده فروش: مناطق تهران و شیراز تقریبن ۶۰٪ از کل فروش را تشکیل میدهند.
- الکترونیک در برابر لوازم جانبی: فروش الکترونیک در همهی مناطق بیشتر از لوازم جانبی است.
۳. مرحلهی بارگذاری (Load)
| اقدام | مسیر | وضعیت |
|---|---|---|
| ذخیره در پایگاهداده SQLite | ../data/sales_analytics.db → جدول sales_summary | ✅ موفق |
| ذخیره در فایل CSV | ../data/sales_analytics.csv | ✅ موفق |
جمعبندی نهایی
این خطلولهی ETL با موفقیت:
۱. دادههای خام را از سه منبع مختلف (فایل CSV، فایل JSON و پایگاهداده) جمعآوری کرد.
۲. با حذف ۲۰ رکورد تکراری، دادهها را تمیز کرد.
۳. با محاسبهی کل فروش و گروهبندی، دادهها را به سطح تحلیلی تبدیل نمود.
۴. نتایج را در دو فرمت مختلف (پایگاهداده و CSV) برای استفادههای بعدی ذخیره کرد.
نکات مهمی که از این تمرین یاد گرفتید
| مفهوم | کاربرد |
|---|---|
| یکپارچهسازی داده | دادههای پراکنده از منابع مختلف را به یک ساختار منسجم تبدیل کردید. |
| تمیزکاری داده | رکوردهای تکراری را شناسایی و حذف کردید. |
| تبدیل داده | ستونهای جدید ایجاد و دادهها را برای تحلیل آماده کردید. |
| گروهبندی و خلاصهسازی | دادهها را در سطح منطقه و دستهبندی جمعبندی کردید. |
| ذخیرهسازی در مقاصد مختلف | داده را در پایگاهداده و فایل CSV ذخیره کردید. |
جمعبندی و تمرین
در این درس، یک خطلولهی ETL کامل با استفاده از پایتون و پانداس ساختیم که دادهها را از چندین منبع استخراج، تبدیل و در نهایت در یک پایگاهداده بارگذاری میکند. این خطلوله نشان میدهد که چگونه میتوان مفاهیم تئوری را به کدهای عملی و قابل اجرا تبدیل کرد.
تمرین پیشنهادی:
۱. یک منبع دادهی جدید (مثلن یک فایل Excel) به خطلوله اضافه کنید و آن را در مرحلهی استخراج بگنجانید.
۲. یک ستون جدید به نام profit با فرض سود ۲۰٪ به دادهها اضافه کنید.
۳. یک فیلتر جدید برای حذف رکوردهای با قیمت واحد کمتر از ۱۰۰ تومان اضافه کنید.
۴. خطلوله را به گونهای تغییر دهید که خروجی را در هر دو فرمت CSV و Parquet ذخیره کند.
