0Pricing
Pandas & NumPy Academy · 课时

项目设置与数据导入

定义项目目标，从 CSV 和 SQLite 数据库加载数据，合并数据源，并对合并后的数据集执行完整审计

项目设置与数据导入 是 CoddyKit 上的免费 Pandas & NumPy Academy 课时。 这是第 1 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 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.

常见问题解答

「项目设置与数据导入」课时是免费的吗？

是的 — 「项目设置与数据导入」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 Pandas & NumPy Academy 课程的其余内容，请升级到 CoddyKit PRO。 Pandas & NumPy Academy 课程共包含 4 节课。

「项目设置与数据导入」这节课中我会学到什么？

定义项目目标，从 CSV 和 SQLite 数据库加载数据，合并数据源，并对合并后的数据集执行完整审计 你通过在浏览器中直接运行的动手代码来练习 Pandas & NumPy Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 Pandas & NumPy Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 Pandas & NumPy Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 1 节课，共 4 节。

「项目设置与数据导入」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 Pandas & NumPy Academy 课中编写并运行代码吗？

能。每节 Pandas & NumPy Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

  1. 项目设置与数据导入
  2. 数据清洗与特征工程
  3. 分析与 KPI 计算
  4. 最终可视化与报告导出
← 返回 Pandas & NumPy Academy