Machine Learning: Pandasによるデータ処理 — 読み込み・クリーニング・変換・集約の完全ガイド

import pandas as pd

# Load and clean in a pipeline
df = pd.read_csv("orders.csv")
df_clean = (df
    .drop_duplicates()
    .fillna({"amount": df["amount"].median()})
    .assign(amount_usd=lambda x: x["amount"] * x["exchange_rate"])
)
print(f"Clean rows: {len(df_clean)}, Original: {len(df)}")

import pandas as pd
import numpy as np

# From dictionary
sales_data = pd.DataFrame({
    "date": pd.date_range("2024-01-01", periods=5, freq="D"),
    "category": ["Electronics", "Clothing", "Food", "Books", "Home"],
    "revenue_k_usd": [250, 145, 90, 70, 400],
    "orders": [1200, 800, 3000, 500, 600],
})

# From NumPy array
arr = np.random.rand(3, 4)
df_from_arr = pd.DataFrame(arr, columns=["A", "B", "C", "D"])

print(sales_data)
print(f"\nShape: {sales_data.shape}")
print(f"Dtypes:\n{sales_data.dtypes}")

import pandas as pd

# Load CSV with type hints
df = pd.read_csv("sales_data.csv", parse_dates=["order_date"])

# Quick overview
print(f"Shape: {df.shape}")
print(f"\nFirst 5 rows:\n{df.head()}")
print(f"\nData types:\n{df.dtypes}")
print(f"\nMemory usage:\n{df.memory_usage(deep=True)}")

# Executed successfully

import pandas as pd

df = pd.DataFrame({
    "product": ["Laptop", "Phone", "Tablet", "Monitor", "Keyboard"],
    "category": ["Electronics", "Electronics", "Electronics", "Electronics", "Accessories"],
    "price": [999, 699, 399, 299, 49],
    "stock": [50, 200, 100, 80, 500],
})

# Column selection
prices = df["price"]           # Series
subset = df[["product", "price"]]  # DataFrame

# Row selection with loc (label) and iloc (position)
row = df.loc[0]                # First row by label
rows = df.iloc[1:3]            # Rows 1-2 by position

# Conditional filtering
expensive = df[df["price"] > 400]
elec_cheap = df[(df["category"] == "Electronics") & (df["price"] < 500)]

print(f"Expensive items:\n{expensive}")

# Executed successfully

import pandas as pd
import numpy as np

# Simulate data with missing values
df = pd.DataFrame({
    "order_id": [1001, 1002, 1003, 1004, 1005, 1006],
    "amount_usd": [150, np.nan, 280, np.nan, 95, 320],
    "category": ["Electronics", "Clothing", np.nan, "Food", "Books", "Electronics"],
    "user_rating": [4.5, 3.8, np.nan, 4.2, np.nan, 5.0],
})

# Diagnose missing values
print(f"Missing count:\n{df.isnull().sum()}")
print(f"\nMissing ratio:\n{df.isnull().mean().round(3)}")

# Strategy 1: Drop rows with any missing
df_drop = df.dropna()

# Strategy 2: Fill with median/mode
df_fill = df.fillna({
    "amount_usd": df["amount_usd"].median(),
    "category": df["category"].mode()[0],
    "user_rating": df["user_rating"].mean(),
})
print(f"\nFilled data:\n{df_fill}")

# Executed successfully

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "order_id": [1001, 1002, 1002, 1003, 1004],
    "amount": [150, 280, 280, 95000, 95],
})

# Duplicate detection
dupes = df.duplicated(subset=["order_id", "amount"])
print(f"Duplicated rows:\n{df[dupes]}")

# Remove duplicates
df_clean = df.drop_duplicates(subset=["order_id"], keep="first")

# Outlier detection with IQR method
def detect_outliers_iqr(series, factor=1.5):
    q1, q3 = series.quantile([0.25, 0.75])
    iqr = q3 - q1
    lower, upper = q1 - factor * iqr, q3 + factor * iqr
    return (series < lower) | (series > upper)

outlier_mask = detect_outliers_iqr(df_clean["amount"])
print(f"\nOutliers:\n{df_clean[outlier_mask]}")

# Cap outliers at 99th percentile
cap = df_clean["amount"].quantile(0.99)
df_capped = df_clean.assign(amount=df_clean["amount"].clip(upper=cap))

# Functions defined successfully

import pandas as pd

df = pd.DataFrame({
    "product": ["Laptop", "Phone", "Tablet"],
    "price_usd": [999, 699, 399],
    "cost_usd": [600, 350, 180],
})

# map: element-wise transform on Series
df["price_level"] = df["price_usd"].map(
    lambda x: "High" if x > 700 else ("Mid" if x > 400 else "Low")
)

# apply: row/column-wise transform
df["margin_pct"] = df.apply(
    lambda row: (row["price_usd"] - row["cost_usd"]) / row["price_usd"] * 100,
    axis=1
)

# transform: same-shape output
df["price_zscore"] = df["price_usd"].transform(
    lambda x: (x - x.mean()) / x.std()
)

print(df)

# Executed successfully

import pandas as pd

df = pd.DataFrame({
    "category": ["Elec", "Elec", "Cloth", "Cloth", "Food", "Food"],
    "month": ["Jan", "Feb", "Jan", "Feb", "Jan", "Feb"],
    "revenue_k": [250, 260, 145, 150, 90, 95],
    "orders": [1200, 1250, 800, 820, 3000, 3100],
})

# Single aggregation
cat_revenue = df.groupby("category")["revenue_k"].sum()
print(f"Revenue by category:\n{cat_revenue}")

# Multiple aggregations
cat_stats = df.groupby("category").agg({
    "revenue_k": ["sum", "mean", "std"],
    "orders": ["sum", "mean"],
})
print(f"\nCategory stats:\n{cat_stats}")

# Named aggregations
cat_named = df.groupby("category").agg(
    total_revenue=("revenue_k", "sum"),
    avg_revenue=("revenue_k", "mean"),
    total_orders=("orders", "sum"),
    avg_order_value=("revenue_k", lambda x: x.sum() / df.loc[x.index, "orders"].sum() * 1000),
)

# Executed successfully

import pandas as pd

df = pd.DataFrame({
    "category": ["Elec"]*3 + ["Cloth"]*3 + ["Food"]*3,
    "month": ["Jan", "Feb", "Mar"]*3,
    "revenue_k": [250, 260, 270, 145, 150, 155, 90, 95, 100],
})

# Pivot: categories as rows, months as columns
pivot = df.pivot_table(
    values="revenue_k",
    index="category",
    columns="month",
    aggfunc="sum",
    margins=True,  # Add row/column totals
)
print(pivot)

# Executed successfully

import pandas as pd

# Alice's US orders
us_orders = pd.DataFrame({
    "order_id": ["US001", "US002", "US003"],
    "amount_usd": [150, 280, 95],
    "product": ["Laptop", "Phone", "Book"],
})

# Bob's China orders (need currency conversion)
cn_orders = pd.DataFrame({
    "order_id": ["CN001", "CN002"],
    "amount_cny": [1200, 3500],
    "product": ["Phone", "Laptop"],
})

# Concat vertically (append rows)
cn_orders_usd = cn_orders.assign(
    amount_usd=cn_orders["amount_cny"] * 0.14  # CNY to USD
).drop(columns=["amount_cny"])
all_orders = pd.concat([us_orders, cn_orders_usd], ignore_index=True)
print(f"Combined orders:\n{all_orders}")

# Merge with product catalog
catalog = pd.DataFrame({
    "product": ["Laptop", "Phone", "Book", "Tablet"],
    "category": ["Electronics", "Electronics", "Books", "Electronics"],
    "margin_pct": [35, 45, 20, 30],
})
enriched = all_orders.merge(catalog, on="product", how="left")
print(f"\nEnriched orders:\n{enriched}")

# Executed successfully

import pandas as pd
import numpy as np

# Daily sales data
rng = pd.date_range("2024-01-01", periods=90, freq="D")
daily = pd.DataFrame({
    "date": rng,
    "revenue": np.random.normal(5000, 1000, 90).cumsum(),
}, index=rng)

# Resample to monthly
monthly = daily["revenue"].resample("M").agg(["first", "last", "mean", "sum"])
print(f"Monthly stats:\n{monthly}")

# Rolling window: 7-day moving average
daily["ma_7d"] = daily["revenue"].rolling(window=7).mean()
daily["ma_30d"] = daily["revenue"].rolling(window=30).mean()

# Percentage change
daily["pct_change"] = daily["revenue"].pct_change()

print(f"\nLast 5 rows with rolling stats:\n{daily.tail()}")

# Executed successfully