0Pricing
Pandas & NumPy Academy · درس

إعداد المشروع واستيعاب البيانات

حدّد أهداف المشروع، وحمّل البيانات من ملف CSV وقاعدة بيانات SQLite، وادمج المصادر، ونفّذ تدقيقًا شاملًا لمجموعة البيانات المدمجة

إعداد المشروع واستيعاب البيانات درس مجاني في Pandas & NumPy Academy على CoddyKit. هذا هو الدرس 1 من أصل 4. يمكنك قراءة الدرس كاملاً أدناه مجاناً — ثم تمرن عليه مباشرة في المتصفح باستخدام محرر أكواد مدمج ومدرس ذكاء اصطناعي متاح 24/7. هذا الدرس جزء من مسار التعلم في Pandas & NumPy Academy، وتقدمك يتزامن عبر الويب وتطبيق CoddyKit. تتضمن دورة Pandas & NumPy Academy 4 دروس في المجموع.

بعض أجزاء هذا الدرس لم تُترجم بعد وتظهر باللغة الإنجليزية.

The Capstone Project Goal

In this capstone project, you bring together every skill from the course — NumPy, Pandas, visualisation, statistical testing, database connectivity, and pipeline design — in a single end-to-end workflow. The project simulates a real analyst task: ingest raw data from two sources (a CSV file and a database table), merge them, audit data quality, compute advanced KPIs, visualise trends, and export a polished report. This mirrors what professional data analysts do every day in industry.

Defining Project Goals and KPIs

Before writing a single line of code, define your project goals and the Key Performance Indicators (KPIs) you will compute. Document: What business question are you answering? What data sources do you have? What output formats are needed? For this capstone: Analyse monthly revenue trends across product categories, compute cohort retention, identify top regions by profit margin, and export a report with visualisations. A clear goal prevents scope creep and keeps the pipeline focused.

# Project configuration — define goals upfront
CONFIG = {
    'csv_path': 'data/orders_2024.csv',
    'db_url': 'sqlite:///customer_db.sqlite',
    'db_table': 'customers',
    'output_dir': 'output/',
    'report_path': 'output/report.md',
    'analysis_year': 2024,
    'top_n_regions': 5,
    'rolling_window_days': 30
}

print('Project config loaded.')
print('Target KPIs: monthly revenue, cohort retention, top regions by margin')

Loading Data from CSV

Load the CSV with explicit dtype and parse_dates arguments to avoid inference errors. In a real project, the CSV may have been exported by a different system with different conventions — set encoding, sep, and thousands if needed. Immediately check shape, dtypes, and head() to confirm the load was correct before any transformation. This first inspection often reveals encoding problems, extra header rows, or unexpected column names.

import pandas as pd

df_orders = pd.read_csv(
    'data/orders_2024.csv',
    dtype={
        'order_id': 'int32',
        'customer_id': 'int32',
        'product_id': 'int32',
        'quantity': 'int16',
        'unit_price': 'float32',
        'region': 'category',
        'category': 'category'
    },
    parse_dates=['order_date'],
    encoding='utf-8'
)
print(f'Orders loaded: {df_orders.shape}')
print(df_orders.dtypes)
print(df_orders.head(3))

Loading Data from the Database

The second data source is the customer table in a SQLite database. Load only the columns needed for the analysis using a SQL SELECT, rather than loading the entire table. Join the two sources on customer_id to enrich each order with customer demographics. Before merging, confirm that the customer_id keys are the same type in both sources — a common source of silent merge failures is comparing an int32 key to a string key.

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine('sqlite:///customer_db.sqlite')

df_customers = pd.read_sql_query(
    '''
    SELECT customer_id, customer_name, country,
           acquisition_channel, signup_date
    FROM customers
    ''',
    con=engine,
    dtype={'customer_id': 'int32'},
    parse_dates=['signup_date']
)
print(f'Customers loaded: {df_customers.shape}')
print(df_customers.dtypes)
print(df_customers.head(3))

Merging the Two Sources

Merge orders with customers on customer_id using a left join to keep all orders even if a customer record is missing. After merging, check how many orders have no matching customer (customer_name.isna().sum()) — this is a data quality signal worth investigating. Inspect the resulting DataFrame's shape to confirm that the merge did not duplicate or drop unexpected rows. Document the merge logic in a comment for future maintainers.

import pandas as pd

# Left join: keep all orders, enrich with customer data where available
df = pd.merge(
    df_orders,
    df_customers,
    on='customer_id',
    how='left'
)

print(f'Orders: {len(df_orders)}, Customers: {len(df_customers)}')
print(f'Merged: {df.shape}')
unmatched = df['customer_name'].isna().sum()
print(f'Orders with no matching customer: {unmatched} ({unmatched/len(df):.1%})')

Full Data Audit Checklist

After loading and merging, perform a systematic data audit before any analysis. Check: total rows and columns, missing values per column (percentage), dtypes of all columns, duplicate order IDs, date range coverage, negative values in numeric columns, and cardinality of categorical columns. Document findings in a text summary. Catching data quality issues here prevents silent errors in downstream KPI calculations.

import pandas as pd

def audit_dataframe(df, name='DataFrame'):
    print(f'=== Audit: {name} ===')
    print(f'Shape: {df.shape}')
    print(f'Date range: {df["order_date"].min()} to {df["order_date"].max()}')
    print(f'Duplicate order_ids: {df["order_id"].duplicated().sum()}')
    print('Missing values:')
    missing = df.isnull().sum()
    print(missing[missing > 0].to_string())
    print('Negative quantities:', (df['quantity'] < 0).sum())
    print('Negative prices:', (df['unit_price'] < 0).sum())
    print()

audit_dataframe(df, 'Merged Orders + Customers')

Fixing Date Parsing Anomalies

Date columns from CSV exports often have edge cases: future dates that should not exist, dates from the wrong year (a common data entry error), or missing dates for cancelled orders. After parsing dates, validate the expected range and replace out-of-range values with NaT. Also check for orders where order_date < customer signup_date — impossible events that indicate data quality issues worth flagging in the audit report.

import pandas as pd
from datetime import datetime

# Flag impossible dates
df['date_valid'] = (
    (df['order_date'] >= '2024-01-01') &
    (df['order_date'] <= datetime.now())
)
print('Invalid order dates:', (~df['date_valid']).sum())

# Flag orders before customer signup
df['signup_date'] = pd.to_datetime(df['signup_date'])
df['date_before_signup'] = df['order_date'] < df['signup_date']
print('Orders before customer signup:', df['date_before_signup'].sum())

# Set invalid dates to NaT
df.loc[~df['date_valid'], 'order_date'] = pd.NaT

Handling Negative Quantities and Returns

Negative quantities in an orders table typically represent returns or refunds. Rather than simply dropping them, separate them into two DataFrames: positive orders and returns. This allows you to compute gross revenue (positive orders only) and net revenue (positive + negative) separately, giving a more accurate picture of the business. Tag each row with an is_return boolean column and keep both in the merged DataFrame for audit purposes.

import pandas as pd

# Tag returns
df['is_return'] = df['quantity'] < 0

returns = df[df['is_return']]
orders = df[~df['is_return']]

print(f'Normal orders: {len(orders)}')
print(f'Returns/refunds: {len(returns)} ({len(returns)/len(df):.1%})')
print(f'Return rate by category:')
print(
    df.groupby('category')['is_return']
    .mean()
    .sort_values(ascending=False)
    .round(3)
)

Checking Join Completeness

After a left join, verify that the join was complete as expected. Count how many unique customer_id values appear in orders but not in the customers table. These are orphan records — customers who placed orders but have no customer record. They may represent deleted accounts, guest checkouts, or a data pipeline gap. Document the count and decide whether to exclude them from retention analysis but include them in revenue totals.

import pandas as pd

order_customers = set(df_orders['customer_id'].unique())
customer_ids = set(df_customers['customer_id'].unique())

orphans = order_customers - customer_ids
print(f'Customer IDs in orders not in customer table: {len(orphans)}')
print(f'Coverage: {len(order_customers & customer_ids) / len(order_customers):.1%} of order customers have records')

# Revenue from unmatched customers
orphan_revenue = df[df['customer_id'].isin(orphans)]['unit_price'].sum()
print(f'Revenue from orphan customers: ${orphan_revenue:,.0f}')

Saving the Audited Dataset

After the audit, save the cleaned and merged dataset to Parquet so subsequent pipeline steps (feature engineering, KPI calculation, visualisation) can reload it instantly without repeating the merge and parse operations. Write separate files for the clean orders and the returns DataFrame. This checkpoint pattern makes the pipeline resumable and allows you to debug later stages without re-running ingestion.

import os
import pandas as pd

OS_DIR = 'output/'
os.makedirs(OS_DIR, exist_ok=True)

# Save clean orders (excluding returns and invalid dates)
clean_orders = df[
    (~df['is_return']) &
    df['order_date'].notna()
].copy()

clean_orders.to_parquet(OS_DIR + 'clean_orders.parquet', index=False)
df[df['is_return']].to_parquet(OS_DIR + 'returns.parquet', index=False)

print(f'Clean orders saved: {len(clean_orders):,} rows')
print(f'Returns saved: {len(df[df["is_return"]]):,} rows')
print(f'Files in {OS_DIR}: {os.listdir(OS_DIR)}')

Audit Summary Report

The final step of data ingestion is writing a brief audit summary that records what you found. This could be a printed log or a markdown file. Include: number of rows loaded from each source, merge match rate, date range, percentage of missing values, number of returns found, and any anomalies. The audit summary becomes part of the final deliverable and helps stakeholders trust that the data was properly cleaned before analysis.

import pandas as pd
from datetime import datetime

def write_audit_summary(df, path):
    lines = [
        '# Data Ingestion Audit',
        f'Generated: {datetime.now().strftime("%Y-%m-%d %H:%M")}',
        '',
        f'- Total records after merge: {len(df):,}',
        f'- Clean orders: {(~df["is_return"]).sum():,}',
        f'- Returns: {df["is_return"].sum():,}',
        f'- Date range: {df["order_date"].min().date()} to {df["order_date"].max().date()}',
        f'- Unique customers: {df["customer_id"].nunique():,}',
        f'- Unique categories: {df["category"].nunique()}',
        f'- Missing unit_price: {df["unit_price"].isna().sum()}'
    ]
    with open(path, 'w') as f:
        f.write('\n'.join(lines))

write_audit_summary(df, 'output/audit.md')
print('Audit summary written.')

Quick Check

Test your understanding of Data Analysis concepts from this lesson.

Lesson Recap

In this lesson you learned: defining goals and config upfront keeps the pipeline focused and maintainable, explicit dtype and parse_dates arguments in read_csv prevent silent type errors, and a systematic data audit checklist (duplicates, missing values, impossible dates, negative quantities) catches quality issues before they corrupt KPI calculations. Next up we clean the data and engineer features for the analysis.

الأسئلة الشائعة

هل درس «إعداد المشروع واستيعاب البيانات» مجاني؟

نعم — نص درس «إعداد المشروع واستيعاب البيانات» كامل متاح مجاناً هنا على الويب. لتمرينه بشكل تفاعلي (محرر أكواد مدمج ومدرس ذكاء اصطناعي متاح 24/7) وفتح باقي دورة Pandas & NumPy Academy، انتقل إلى CoddyKit PRO. تتضمن دورة Pandas & NumPy Academy 4 دروس في المجموع.

ماذا ستتعلم في «إعداد المشروع واستيعاب البيانات»؟

حدّد أهداف المشروع، وحمّل البيانات من ملف CSV وقاعدة بيانات SQLite، وادمج المصادر، ونفّذ تدقيقًا شاملًا لمجموعة البيانات المدمجة تتمرن على Pandas & NumPy Academy مع أكواد عملية تشغلها مباشرة في المتصفح، ومدرس ذكاء اصطناعي متاح 24/7 يجيب على أسئلتك أثناء عملك.

هل أحتاج إلى خبرة سابقة لأبدأ Pandas & NumPy Academy؟

لا تُشترط خبرة سابقة. Pandas & NumPy Academy على CoddyKit منظم للمبتدئين حتى المتقدمين، لذا يمكنك البدء من هنا أو من البداية والتقدم بسرعتك الخاصة. هذا هو الدرس 1 من أصل 4.

كم من الوقت يستغرق درس «إعداد المشروع واستيعاب البيانات»؟

معظم دروس CoddyKit تستغرق حوالي 5–10 دقائق. كل منها موجز وتفاعلي، لذا تحرز تقدماً مستمراً وتستأنف من حيث توقفت عبر الويب والتطبيق.

هل يمكنني كتابة وتشغيل أكواد في درس Pandas & NumPy Academy هذا؟

نعم. كل درس في Pandas & NumPy Academy يتضمن محرر أكواد مدمج، لذا تكتب وتشغل أكواداً حقيقية مباشرة في متصفحك وتحصل على تعليقات فورية من الذكاء الاصطناعي — بدون إعداد محلي.

جميع الدروس في هذه الدورة

  1. إعداد المشروع واستيعاب البيانات
  2. تنظيف البيانات وهندسة السمات
  3. التحليل وحساب مؤشرات الأداء الرئيسية
  4. التصور النهائي وتصدير التقرير
← العودة إلى Pandas & NumPy Academy