Pandas 速查表

# Install pandas (pick one)
pip install pandas
conda install pandas

# Standard import convention
import pandas as pd
import numpy as np  # pandas relies heavily on numpy

# Check version
print(pd.__version__)

# Pandas is built on numpy: a Series is a 1D labeled array,
# a DataFrame is a 2D labeled table (dict of Series).

import pandas as pd

# Series: 1D labeled array
s = pd.Series([10, 20, 30, 40], index=["a", "b", "c", "d"])
# a    10
# b    20
# ...

# DataFrame: 2D table (dict of columns)
df = pd.DataFrame({
    "name":  ["Alice", "Bob", "Charlie"],
    "age":   [25, 30, 35],
    "city":  ["NYC", "LA", "SF"],
})

# From list of lists with column names
df2 = pd.DataFrame([[1, 2], [3, 4]], columns=["a", "b"])

import pandas as pd
import numpy as np

# Quick test DataFrame with random data
df = pd.DataFrame({
    "id":     range(1, 6),
    "name":   ["Alice", "Bob", "Charlie", "David", "Eve"],
    "age":    [25, 30, 35, 28, 40],
    "score":  [85.5, 92.0, 78.3, 88.9, 95.2],
    "pass":   [True, True, False, True, True],
})

# Larger random DataFrame
rng = np.random.default_rng(42)
big = pd.DataFrame(rng.normal(size=(100, 4)), columns=list("ABCD"))

# Dates as index
dates = pd.date_range("2024-01-01", periods=5)
ts = pd.DataFrame({"value": [1, 2, 3, 4, 5]}, index=dates)

import pandas as pd

# CSV — the most common
df = pd.read_csv("data.csv")
df = pd.read_csv("data.csv", sep=";", encoding="utf-8",
                 parse_dates=["date"], index_col="id")

# Excel (needs openpyxl)
df = pd.read_excel("data.xlsx", sheet_name="Sheet1")

# JSON
df = pd.read_json("data.json")

# Parquet (fast columnar format, great for large data)
df = pd.read_parquet("data.parquet")

# SQL (needs SQLAlchemy)
from sqlalchemy import create_engine
engine = create_engine("sqlite:///my.db")
df = pd.read_sql("SELECT * FROM users", engine)

# Clipboard (paste a table from a spreadsheet)
df = pd.read_clipboard()

import pandas as pd

# CSV (no index by default is usually what you want)
df.to_csv("output.csv", index=False)
df.to_csv("output.csv", index=False, encoding="utf-8-sig")  # Excel-friendly

# Excel (needs openpyxl)
df.to_excel("output.xlsx", sheet_name="Sheet1", index=False)

# Parquet (preserves dtypes, fast, compressed)
df.to_parquet("output.parquet")

# JSON
df.to_json("output.json", orient="records", indent=2)

# SQL
df.to_sql("users", engine, if_exists="replace", index=False)

# Multiple sheets to one Excel file
with pd.ExcelWriter("multi.xlsx") as writer:
    df1.to_excel(writer, sheet_name="sheet1", index=False)
    df2.to_excel(writer, sheet_name="sheet2", index=False)

import pandas as pd

# First/last rows
df.head()        # first 5 (default)
df.head(10)      # first 10
df.tail()        # last 5
df.tail(3)       # last 3

# Random sample (great for sanity checks)
df.sample(5)                    # 5 random rows
df.sample(frac=0.1, random_state=42)  # 10% of rows
df.sample(n=5, replace=True)    # sample with replacement (bootstrap)

# See the raw values (not truncated)
pd.set_option("display.max_rows", 200)
pd.set_option("display.max_columns", None)
pd.set_option("display.width", 1000)

# Shape and basic structure
df.shape         # (rows, cols)
df.size          # rows * cols
df.ndim          # 2 for DataFrame, 1 for Series
len(df)          # number of rows

# Column names and dtypes
df.columns       # column labels
df.index         # row labels
df.dtypes        # dtype of each column
df.info()        # concise summary: dtypes, non-null counts, memory

# Memory usage (helpful for big data)
df.memory_usage(deep=True)
df.info(memory_usage="deep")

# Numeric summary (count, mean, std, min, quartiles, max)
df.describe()
df.describe(include="all")   # include non-numeric too
df.describe(percentiles=[.1, .5, .9])

# Per-column stats
df["age"].mean()
df["age"].median()
df["age"].std()
df["age"].var()
df["age"].min(), df["age"].max()
df["age"].quantile(0.95)

# Non-numeric summaries
df["city"].value_counts()
df["city"].unique()
df["city"].nunique()

# Correlation and covariance
df.corr(numeric_only=True)
df.cov(numeric_only=True)

# Unique values
df["city"].unique()              # array of unique values
df["city"].nunique()             # count of unique
df["city"].nunique(dropna=False) # include NaN

# Value counts (frequency table)
df["city"].value_counts()
df["city"].value_counts(normalize=True)   # proportions
df["city"].value_counts(ascending=True)
df["city"].value_counts(dropna=False)     # count NaN too

# Cross tabulation (frequency table of two columns)
pd.crosstab(df["city"], df["pass"])

# Binning then counting
df["age_group"] = pd.cut(df["age"], bins=[0, 18, 30, 50, 100],
                          labels=["child", "young", "adult", "senior"])
df["age_group"].value_counts()

# Count missing per column
df.isnull().sum()
df.isna().sum()           # alias

# Fraction missing
df.isnull().mean().mul(100).round(2)  # % missing per column

# Total missing
df.isnull().sum().sum()

# Rows with any missing
df[df.isnull().any(axis=1)]

# Non-null counts
df.notnull().sum()

# Visualize missingness pattern
# (requires missingno: import missingno as msno; msno.matrix(df))

import pandas as pd
import numpy as np

# From a list
s = pd.Series([1, 2, 3, 4])

# From a list with custom index
s = pd.Series([10, 20, 30], index=["a", "b", "c"])

# From a dict (keys become index)
s = pd.Series({"a": 1, "b": 2, "c": 3})

# From a numpy array
s = pd.Series(np.random.randn(5))

# Scalar broadcast to an index
s = pd.Series(5, index=["a", "b", "c"])  # [5, 5, 5]

# Specify dtype
s = pd.Series([1, 2, 3], dtype="float64")
s = pd.Series([1, 2, 3], dtype="int8")    # save memory

s = pd.Series([10, 20, 30, 40], index=["a", "b", "c", "d"])

# By label (index)
s["a"]            # 10
s[["a", "c"]]     # multiple labels -> Series
s["a":"c"]        # slice INCLUSIVE of both ends (label-based)

# By position
s.iloc[0]         # first by position
s.iloc[-1]        # last
s.iloc[1:3]       # EXCLUSIVE of end (positional, like Python)

# By label with .loc (explicit)
s.loc["a"]
s.loc["a":"c"]    # inclusive

# Boolean indexing
s[s > 20]
s[(s > 10) & (s < 40)]

s = pd.Series([1, 2, 3, 4])

# Element-wise arithmetic (aligns on index)
s + 10          # [11, 12, 13, 14]
s * 2
s ** 2
1 / s           # [1.0, 0.5, 0.333, 0.25]

# Between two Series (aligns on index, NaN for mismatches)
s1 = pd.Series({"a": 1, "b": 2, "c": 3})
s2 = pd.Series({"b": 20, "c": 30, "d": 40})
s1 + s2         # a=NaN, b=22, c=33, d=NaN

# Comparison and boolean
s > 2           # [False, False, True, True]
s.between(2, 3) # inclusive

# Math methods
s.sum(), s.mean(), s.std(), s.cumsum()

s = pd.Series([3, 1, 4, 1, 5, 9, 2, 6, np.nan])

# Sorting
s.sort_values()                  # ascending
s.sort_values(ascending=False)
s.sort_index()

# Ranking
s.rank(method="average")         # 'min','max','first','dense'

# Unique, counts
s.unique()
s.nunique()
s.value_counts()
s.duplicated()                   # boolean: True if seen before
s.drop_duplicates()

# Statistical
s.sum(), s.prod(), s.cumsum(), s.cummax()
s.abs()
s.round(2)
s.clip(lower=0, upper=10)        # cap values to a range
s.quantile([0.25, 0.5, 0.75])

s = pd.Series([1.0, 2.0, np.nan, 4.0, np.nan])

# Detect
s.isna()             # boolean mask
s.notna()
s.isna().sum()       # count of NaN

# Drop
s.dropna()           # remove NaN

# Fill
s.fillna(0)                       # constant
s.fillna(s.mean())                # with mean
s.fillna(method="ffill")          # forward fill (old API)
s.ffill()                         # forward fill (new)
s.bfill()                         # backward fill
s.interpolate()                   # linear interpolation

# Replace sentinel values with NaN on read
# pd.read_csv("x.csv", na_values=["?", "N/A", ""])

# Single column (returns Series)
df["age"]
df.age            # only if name is a valid Python identifier

# Multiple columns (returns DataFrame)
df[["name", "age", "city"]]

# Columns by pattern
df.filter(like="age")               # columns containing "age"
df.filter(regex="^\d{4}$")          # columns matching regex
df.filter(items=["name", "age"])    # specific columns

# Select by dtype
df.select_dtypes(include="number")
df.select_dtypes(include=["int64", "float64"])
df.select_dtypes(exclude="object")
df.select_dtypes(include="datetime")

# .loc: label-based (inclusive slices)
df.loc[5]                         # row with index label 5
df.loc[5, "name"]                 # row 5, column 'name'
df.loc[5:10, ["name", "age"]]     # rows 5-10 (inclusive), 2 cols
df.loc[df["age"] > 30]            # boolean mask

# .iloc: position-based (exclusive slices)
df.iloc[0]                        # first row
df.iloc[-1]                       # last row
df.iloc[0:5]                      # first 5 rows (exclusive)
df.iloc[0:5, 0:2]                 # first 5 rows, first 2 cols
df.iloc[:, 0]                     # all rows, first column

# Setting values (use .loc for assignment!)
df.loc[df["age"] < 0, "age"] = 0  # cap negatives to 0

# AVOID chained indexing: df[df["a"]<5]["b"] = 0  # SettingWithCopyWarning

# Single condition
df[df["age"] > 30]
df[df["city"] == "NYC"]

# Multiple conditions (use & | ~, NOT and/or/not; parenthesize!)
df[(df["age"] > 25) & (df["city"] == "NYC")]
df[(df["city"] == "NYC") | (df["city"] == "LA")]
df[~df["pass"]]                       # NOT pass

# isin for membership
df[df["city"].isin(["NYC", "LA", "SF"])]

# String methods in filters
df[df["name"].str.startswith("A")]
df[df["name"].str.contains("li", case=False)]

# Between
df[df["age"].between(25, 35, inclusive="both")]

# Null checks
df[df["score"].isnull()]
df[df["score"].notnull()]

# query() — string-based filtering, often more readable
df.query("age > 30")
df.query("age > 25 and city == 'NYC'")
df.query("age > 25 and city in ['NYC', 'LA']")
df.query("age > 25 or score > 90")

# Refer to Python variables with @
cities = ["NYC", "LA"]
df.query("city in @cities")

# Compare columns
df.query("score > age")

# Negation
df.query("not pass")

# query is faster than boolean indexing on large frames
# because it uses numexpr to avoid intermediate arrays

# Random sample
df.sample(n=5)                          # 5 rows
df.sample(frac=0.1)                     # 10% of rows
df.sample(n=5, random_state=42)         # reproducible

# Sample with weights
df.sample(n=5, weights="score")         # higher score -> more likely

# Sample with replacement (bootstrap)
df.sample(n=len(df), replace=True)

# Train/test split
train = df.sample(frac=0.8, random_state=42)
test = df.drop(train.index)

# Stratified sample (preserve class proportions)
from sklearn.model_selection import train_test_split
train, test = train_test_split(df, test_size=0.2,
                                stratify=df["city"], random_state=42)

# Drop rows with any missing
df.dropna()                       # any NaN in any column
df.dropna(how="all")              # only if ALL columns NaN
df.dropna(subset=["age", "city"]) # only check these columns
df.dropna(thresh=3)               # keep rows with >=3 non-NaN

# Fill missing
df.fillna(0)
df.fillna({"age": df["age"].mean(),
           "city": "Unknown"})
df["age"] = df["age"].fillna(df["age"].median())

# Forward/backward fill (for time series)
df.ffill()
df.bfill()

# Interpolate
df.interpolate(method="linear")
df.interpolate(method="time")     # for DatetimeIndex

# Flag rows that were imputed
df["age_was_missing"] = df["age"].isnull()

# Find duplicates
df.duplicated()                   # True for rows that are duplicates of earlier
df.duplicated().sum()             # count
df[df.duplicated()]               # view duplicates

# Drop duplicates
df.drop_duplicates()              # full-row duplicates
df.drop_duplicates(subset=["email"])   # dup on email only
df.drop_duplicates(subset=["name", "dob"], keep="last")  # keep last occurrence
df.drop_duplicates(keep=False)    # drop ALL duplicates (keep none)

# Find rows where a column is duplicated
df[df.duplicated(subset=["email"], keep=False)]
# .keep=False marks ALL duplicates (including first)

# Rename columns
df.rename(columns={"old_name": "new_name"})
df.rename(columns=str.upper)        # uppercase all
df.rename(columns=str.strip)        # strip whitespace

# Replace all column names
df.columns = ["id", "name", "age"]

# Add prefix/suffix
df.add_prefix("col_")
df.add_suffix("_v2")

# Reorder columns
df[["name", "age", "city"]]          # select in new order

# Reorder rows by index
df.sort_index()

# Reset / set index
df.reset_index(drop=True)            # drop old index
df.set_index("id")                   # use 'id' as index

# Swap rows and columns
df.transpose()  # or df.T

# Convert a column's dtype
df["age"] = df["age"].astype(int)
df["price"] = df["price"].astype(float)
df["pass"] = df["pass"].astype(bool)
df["city"] = df["city"].astype("category")    # save memory

# Convert with pd.to_numeric (handles bad values)
df["price"] = pd.to_numeric(df["price"], errors="coerce")  # bad -> NaN
df["price"] = pd.to_numeric(df["price"], errors="ignore")  # leave as-is

# Parse dates
df["date"] = pd.to_datetime(df["date"], format="%Y-%m-%d")

# Convert multiple columns at once
df = df.astype({"age": "int8", "score": "float32", "city": "category"})

# Downcast to save memory
df["age"] = pd.to_numeric(df["age"], downcast="integer")

# Replace specific values
df["city"].replace({"NYC": "New York", "LA": "Los Angeles"})
df.replace(-999, np.nan)              # whole frame
df.replace(["?", "N/A", ""], np.nan)  # multiple sentinels

# Replace by condition (mask)
df.loc[df["age"] < 0, "age"] = np.nan

# Clip outliers
df["age"] = df["age"].clip(0, 120)

# Map values with a dict (like a lookup)
df["grade"] = df["score"].map({90: "A", 80: "B", 70: "C"})

# Where: keep where condition True, else replace
df["age"].where(df["age"] >= 0, 0)    # set negatives to 0

# Mask: opposite of where
df["age"].mask(df["age"] < 0, 0)

# Apply a function to each element of a column (Series)
df["age"] = df["age"].apply(lambda x: x + 1)
df["name"] = df["name"].apply(str.upper)

# Apply with multiple args
df["score"] = df["score"].apply(lambda x, n: round(x, n), n=2)

# Apply a function to each ROW (axis=1) — use columns
df["full"] = df.apply(lambda row: f"{row['name']} ({row['age']})", axis=1)

# Apply to every element of a DataFrame (newer: .map)
df = df.map(lambda x: str(x).strip() if isinstance(x, str) else x)

# Vectorized alternative (MUCH faster than apply)
df["age"] = df["age"] + 1  # vectorized, prefer this
df["name"] = df["name"].str.upper()

# Add a new column
df["full_name"] = df["first"] + " " + df["last"]
df["is_adult"] = df["age"] >= 18

# Conditional column with np.where
df["category"] = np.where(df["age"] >= 18, "adult", "minor")

# Multiple conditions with np.select
conditions = [df["age"] < 18, df["age"] < 65, df["age"] >= 65]
choices = ["minor", "adult", "senior"]
df["group"] = np.select(conditions, choices, default="unknown")

# insert at a specific position
df.insert(2, "score_pct", df["score"] / 100)  # at col index 2

# Assign multiple columns at once (returns new df)
df = df.assign(
    age_months=df["age"] * 12,
    score_squared=df["score"] ** 2,
)

# Drop columns
df.drop(columns=["temp", "junk"])

# Equal-width bins
df["age_bin"] = pd.cut(df["age"], bins=4)

# Custom bins with labels
df["age_group"] = pd.cut(
    df["age"],
    bins=[0, 18, 30, 50, 100],
    labels=["child", "young", "adult", "senior"],
    right=False,  # left-inclusive: [0, 18)
)

# Equal-frequency bins (quantiles)
df["income_quartile"] = pd.qcut(df["income"], q=4, labels=["Q1","Q2","Q3","Q4"])

# Get bin edges
df["age_bin"].value_counts(sort=False)
pd.cut(df["age"], bins=4).cat.categories  # see the bin ranges

# All vectorized string methods via .str
df["name"] = df["name"].str.upper()
df["name"] = df["name"].str.lower()
df["name"] = df["name"].str.strip()           # remove leading/trailing
df["name"] = df["name"].str.replace(" ", "_")
df["name"] = df["name"].str.title()           # Title Case

# Split
df["name"].str.split(" ")                     # list per row
df[["first", "last"]] = df["name"].str.split(" ", expand=True)

# Contains / startswith / match (returns boolean)
df["name"].str.contains("li", case=False, na=False)
df["name"].str.startswith("A")
df["name"].str.match(r"^A\w+$")              # regex match

# Extract with regex groups
df["name"].str.extract(r"(?P<first>\w+)\s+(?P<last>\w+)")

# Length
df["name"].str.len()

df["date"] = pd.to_datetime(df["date"])

# Extract parts
df["year"]      = df["date"].dt.year
df["month"]     = df["date"].dt.month
df["day"]       = df["date"].dt.day
df["weekday"]   = df["date"].dt.day_name()    # 'Monday'
df["hour"]      = df["date"].dt.hour
df["is_weekend"] = df["date"].dt.dayofweek >= 5

# Date arithmetic
df["age_days"] = (pd.Timestamp("today") - df["date"]).dt.days

# Format back to string
df["date_str"] = df["date"].dt.strftime("%Y-%m-%d")

# Timezone handling
df["date_utc"] = df["date"].dt.tz_localize("UTC")
df["date_local"] = df["date_utc"].dt.tz_convert("US/Eastern")

# Filter by date
df[df["date"] > "2024-01-01"]
df[df["date"].between("2024-01-01", "2024-12-31")]

# Split-Apply-Combine: group rows, compute per group
df.groupby("city")["age"].mean()         # one column, one agg
df.groupby("city")["age"].agg(["mean", "median", "std"])

# Multiple columns
df.groupby("city")[["age", "score"]].mean()

# Group by multiple columns (hierarchical)
df.groupby(["city", "pass"])["age"].mean()

# Quick counts per group
df.groupby("city").size()                # includes NaN groups
df["city"].value_counts()                # excludes NaN, sorted

# Iterate over groups
for name, group in df.groupby("city"):
    print(name, len(group))

# Different aggregations per column
df.groupby("city").agg({
    "age":   ["mean", "median", "std"],
    "score": ["min", "max", "count"],
})

# Named aggregations (cleaner output, no MultiIndex)
result = df.groupby("city").agg(
    avg_age=("age", "mean"),
    median_age=("age", "median"),
    n_people=("age", "count"),
    max_score=("score", "max"),
)

# Custom aggregation function
df.groupby("city").agg(
    age_range=("age", lambda x: x.max() - x.min()),
)

# Reset index to get a flat DataFrame
result = result.reset_index()

# transform: broadcast group result back to original shape
df["age_mean_city"] = df.groupby("city")["age"].transform("mean")
df["age_dev"] = df["age"] - df.groupby("city")["age"].transform("mean")

# Fill missing with group mean
df["score"] = df.groupby("city")["score"].transform(
    lambda s: s.fillna(s.mean()))

# filter: keep/drop entire groups based on a condition
# keep groups with more than 2 rows
big_groups = df.groupby("city").filter(lambda g: len(g) > 2)
# keep groups where average score > 80
df.groupby("city").filter(lambda g: g["score"].mean() > 80)

# Apply any function per group (most flexible)
def top_scorer(g):
    return g.nlargest(1, "score")

df.groupby("city").apply(top_scorer)
df.groupby("city").apply(lambda g: g.nlargest(2, "score"))

# Normalize within group
df.groupby("city").apply(
    lambda g: (g["score"] - g["score"].mean()) / g["score"].std()
)

# Multiple results per group
def summarize(g):
    return pd.Series({
        "count": len(g),
        "avg_score": g["score"].mean(),
        "top_name": g.loc[g["score"].idxmax(), "name"],
    })

df.groupby("city").apply(summarize)

# include_groups=False (newer pandas) to exclude group cols from g

# pivot_table: groupby + reshape in one step
pd.pivot_table(df, values="score", index="city", columns="pass",
               aggfunc="mean")
pd.pivot_table(df, values="score", index="city", columns="pass",
               aggfunc=["mean", "count"], margins=True)

# Multiple values
pd.pivot_table(df, values=["age", "score"], index="city",
               aggfunc={"age": "mean", "score": "max"})

# crosstab: frequency table of two (or more) categoricals
pd.crosstab(df["city"], df["pass"])
pd.crosstab(df["city"], df["pass"], normalize="index")  # row %
pd.crosstab(df["city"], df["pass"], values=df["score"],
            aggfunc="mean")  # like a pivot

# Melt a pivot back to long
wide = pd.pivot_table(df, values="score", index="city", columns="pass")
long = wide.reset_index().melt(id_vars="city", var_name="pass",
                                value_name="score")

# Like SQL JOIN
pd.merge(df1, df2, on="id")                     # inner join (default)
pd.merge(df1, df2, on="id", how="left")         # left join
pd.merge(df1, df2, on="id", how="right")
pd.merge(df1, df2, on="id", how="outer")        # full outer
pd.merge(df1, df2, on="id", how="cross")        # cartesian product

# Different column names in each frame
pd.merge(df1, df2, left_on="user_id", right_on="id")

# Join on index
pd.merge(df1, df2, left_index=True, right_index=True)

# Suffixes for overlapping non-key columns
pd.merge(df1, df2, on="id", suffixes=("_left", "_right"))

# Validate to catch unexpected duplicates
pd.merge(df1, df2, on="id", validate="m:1")  # many-to-one expected

# Stack vertically (rows) — same columns
pd.concat([df1, df2])
pd.concat([df1, df2], ignore_index=True)   # reindex 0..n-1

# Stack horizontally (columns)
pd.concat([df1, df2], axis=1)

# Concat with keys (hierarchical index)
pd.concat([df1, df2], keys=["q1", "q2"])

# Append (deprecated; use concat)
# df1.append(df2)  # deprecated, use pd.concat

# Verify columns match when stacking
pd.concat([df1, df2], verify_integrity=True)  # error on dup index

# Join options for mismatched columns
pd.concat([df1, df2], join="inner")  # keep only shared columns
pd.concat([df1, df2], join="outer")  # keep all, NaN for missing

# join is merge optimized for joining on the index
df1.join(df2)                          # join on index
df1.join(df2, how="left")              # left, right, inner, outer
df1.join([df2, df3])                   # join multiple at once

# join on a column (set index first)
df1.set_index("id").join(df2.set_index("id"), how="inner")

# Suffix for overlapping columns
df1.join(df2, lsuffix="_left", rsuffix="_right")

# When to use join vs merge:
# - join: keys are the index (faster, cleaner)
# - merge: keys are regular columns (more flexible)

# combine_first: fill NaN in df1 with values from df2
df1.combine_first(df2)
# Where df1 is NaN, take df2's value; otherwise keep df1.

# update: overwrite df1 in place with non-NaN from df2
df1.update(df2)

# combine with a custom function
import numpy as np
df1.combine(df2, lambda s1, s2: np.minimum(s1, s2))

# Concat then drop duplicates (union with dedup)
pd.concat([df1, df2]).drop_duplicates()

# indicator=True adds a '_merge' column showing the source
merged = pd.merge(df1, df2, on="id", how="outer", indicator=True)
# _merge values: 'left_only', 'right_only', 'both'

# Find rows in df1 but not df2 (anti-join)
only_left = merged[merged["_merge"] == "left_only"]

# Find rows in both (intersection)
both = merged[merged["_merge"] == "both"]

# Detect unexpected row explosion (duplicate keys)
print(len(df1), len(df2), len(merged))
# if len(merged) >> max(len(df1), len(df2)) you have duplicate keys

# isin for a simple semi-join (filter df1 to keys in df2)
df1[df1["id"].isin(df2["id"])]            # semi-join
df1[~df1["id"].isin(df2["id"])]           # anti-join

# melt: unpivot columns into rows
df_wide = pd.DataFrame({
    "id": [1, 2],
    "q1": [10, 20],
    "q2": [15, 25],
    "q3": [20, 30],
})

long = df_wide.melt(id_vars="id", var_name="quarter", value_name="score")
#    id quarter  score
# 0   1      q1     10
# 1   2      q1     20
# ...

# Melt only specific columns
df_wide.melt(id_vars="id", value_vars=["q1", "q2"])

# Keep the wide columns too (with identifier)
df_wide.melt(id_vars="id", value_vars=["q1","q2","q3"],
             var_name="quarter", value_name="score",
             ignore_index=True)

# pivot: long to wide (without aggregation)
df_long = pd.DataFrame({
    "id": [1, 1, 1, 2, 2, 2],
    "quarter": ["q1", "q2", "q3", "q1", "q2", "q3"],
    "score": [10, 15, 20, 30, 35, 40],
})

wide = df_long.pivot(index="id", columns="quarter", values="score")
# quarter  q1  q2  q3
# id
# 1        10  15  20
# 2        30  35  40

# pivot fails on duplicate index/value combos — use pivot_table then
df_long.pivot_table(index="id", columns="quarter",
                    values="score", aggfunc="sum")

# Restore a regular column from the index
wide = wide.reset_index()
wide.columns.name = None

# MultiIndex column example
df = pd.DataFrame({
    ("A", "x"): [1, 2],
    ("A", "y"): [3, 4],
    ("B", "x"): [5, 6],
})

# stack: turn column level into inner row index
stacked = df.stack()
# Now a Series with MultiIndex (row, col_level_2)

# unstack: inverse — turn row level into column level
stacked.unstack()

# Common use: reshape grouped result
g = df.groupby(["city", "year"])["score"].mean()
# city  year
# NYC   2023    80
# NYC   2024    85
# LA    2023    70

g.unstack("year")  # years become columns
#        2023  2024
# city
# NYC    80    85
# LA     70    NaN

# explode: turn list-like values into separate rows
df = pd.DataFrame({
    "id": [1, 2],
    "tags": [["python", "ml"], ["sql", "db", "etl"]],
})
#              id           tags
# 0             1  [python, ml]
# 1             2  [sql, db, etl]

exploded = df.explode("tags")
#    id    tags
# 0   1  python
# 0   1      ml
# 1   2     sql
# 1   2      db
# 1   2    etl

# Useful after str.split
df["tags"] = df["tags_str"].str.split(",")
exploded = df.explode("tags")

# Aggregate back: groupby + list
exploded.groupby("id")["tags"].apply(list)

# Create a MultiIndex DataFrame
idx = pd.MultiIndex.from_tuples([("NYC", 2023), ("NYC", 2024),
                                  ("LA", 2023)], names=["city", "year"])
df = pd.DataFrame({"score": [80, 85, 70]}, index=idx)

# Select by outer level
df.loc["NYC"]              # all NYC rows
df.xs("NYC", level="city") # same, more explicit

# Select by inner level
df.xs(2023, level="year")

# Swap levels
df.swaplevel()

# Sort by level
df.sort_index(level="year")

# Reset one level back to a column
df.reset_index(level="year")

# Flatten columns after a groupby
g = df.groupby(["city", "year"]).mean().reset_index()

# Sort by one column
df.sort_values("age")
df.sort_values("age", ascending=False)

# Sort by multiple columns (stable sort)
df.sort_values(["city", "age"], ascending=[True, False])
# city ascending, age descending within each city

# Put NaNs first or last
df.sort_values("score", na_position="first")

# In-place (modifies df)
df.sort_values("age", inplace=True)

# Sort by index
df.sort_index()
df.sort_index(axis=1)            # sort columns by name
df.sort_index(ascending=False)

# Reset index after sorting
df.sort_values("age").reset_index(drop=True)

# Default rank: average for ties (1, 2.5, 2.5, 4)
df["rank"] = df["score"].rank()

# Methods for handling ties
df["score"].rank(method="average")  # default
df["score"].rank(method="min")      # ties get the min rank
df["score"].rank(method="max")      # ties get the max rank
df["score"].rank(method="first")    # ties broken by order of appearance
df["score"].rank(method="dense")    # 1, 2, 2, 3 (no gaps)

# Ascending / descending
df["score"].rank(ascending=False)   # highest = rank 1

# Rank as percentile
df["pct"] = df["score"].rank(pct=True)

# Rank within groups
df["city_rank"] = df.groupby("city")["score"].rank(ascending=False)

# Top N rows — faster than sort_values + head on large data
df.nlargest(5, "score")            # top 5 by score
df.nlargest(5, "score", keep="first")  # tie-breaking
df.nsmallest(3, "age")             # youngest 3

# Top N per group
df.groupby("city").apply(lambda g: g.nlargest(2, "score"))

# Bottom N with multiple columns
df.nsmallest(5, ["age", "score"])

# Equivalent sort_values (slower for big data)
df.sort_values("score", ascending=False).head(5)

# Sort the row index
df.sort_index()
df.sort_index(ascending=False)

# Sort columns alphabetically
df.sort_index(axis=1)
df.sort_index(axis=1, ascending=False)

# Reorder columns explicitly
df[["name", "age", "city", "score"]]

# Reorder to a pattern
cols = sorted(df.columns)
df = df[cols]

# Move specific columns to the front
front = ["id", "name"]
df = df[front + [c for c in df.columns if c not in front]]

# Sort by a function of the values (key, like sorted())
df.sort_values("name", key=lambda s: s.str.lower())
# case-insensitive sort

# Sort by string length
df.sort_values("name", key=lambda s: s.str.len())

# Sort by a custom order (categorical)
df["city"] = pd.Categorical(df["city"],
    categories=["NYC", "LA", "SF"], ordered=True)
df.sort_values("city")  # follows the defined category order

# Natural sort (numbers in strings)
import re
def natural_key(s):
    return [int(t) if t.isdigit() else t for t in re.split(r"(\d+)", s)]
df.sort_values("version", key=lambda s: s.map(natural_key))

# Parse strings to datetime
df["date"] = pd.to_datetime(df["date"])
df["date"] = pd.to_datetime(df["date"], format="%Y-%m-%d")  # faster + safer

# Handle mixed formats
df["date"] = pd.to_datetime(df["date"], format="mixed")

# Coerce bad values to NaT (datetime NaN)
df["date"] = pd.to_datetime(df["date"], errors="coerce")

# Parse on read
df = pd.read_csv("data.csv", parse_dates=["date", "created_at"])

# Parse multiple columns into one
df["datetime"] = pd.to_datetime(df[["year", "month", "day"]])

# Timestamp (scalar)
pd.Timestamp("2024-06-15 14:30:00")
pd.Timestamp.now()
pd.Timestamp("2024-06-15").tz_localize("UTC")

# Generate a sequence of dates
pd.date_range("2024-01-01", "2024-01-10")           # daily
pd.date_range("2024-01-01", periods=10)             # 10 days
pd.date_range("2024-01-01", periods=10, freq="H")   # hourly
pd.date_range("2024-01-01", "2024-12-31", freq="MS") # month start
pd.date_range("2024-01-01", "2024-12-31", freq="W-MON") # weekly Mondays

# Business days only
pd.bdate_range("2024-01-01", periods=10)

# Period (represents an interval, like a month)
p = pd.Period("2024-06", freq="M")
p.to_timestamp()    # convert to the month's start
p.start_time, p.end_time

# Period range
pd.period_range("2024-01", "2024-12", freq="M")

ts = pd.Series(range(100), index=pd.date_range("2024-01-01", periods=100, freq="D"))

# Downsample (more frequent -> less frequent): use agg
ts.resample("W").sum()       # weekly sum
ts.resample("M").mean()      # monthly mean
ts.resample("QE").max()      # quarterly max

# Upsample (less -> more frequent): need fill
ts.resample("H").asfreq()            # hourly, NaN for gaps
ts.resample("H").ffill()             # forward fill
ts.resample("H").interpolate()       # linear interp

# Custom aggregation
ts.resample("W").agg(["mean", "std", "count"])

# Resample with multiple columns
df.resample("M").agg({"sales": "sum", "visits": "mean"})

# Use 'label' and 'closed' to control bin edges
ts.resample("M", label="right", closed="right").sum()

# Shift values forward/backward (for time-series features)
ts.shift(1)          # move values down by 1 (yesterday's value)
ts.shift(-1)         # move up by 1 (tomorrow's value)

# Difference (today - yesterday)
ts.diff()            # first difference
ts.diff(7)           # week-over-week difference

# Percentage change
ts.pct_change()
ts.pct_change(periods=7)  # 7-day pct change

# Lag features for ML
df["lag_1"]  = df["sales"].shift(1)
df["lag_7"]  = df["sales"].shift(7)
df["roll_7"] = df["sales"].rolling(7).mean()
df["target_next"] = df["sales"].shift(-1)  # next-day target

# Shift by a time frequency
ts.shift(1, freq="D")   # shift the INDEX by 1 day

s = pd.Series([1, 2, 3, 4, 5, 6, 7, 8, 9, 10], dtype=float)

# Rolling window (fixed-size sliding window)
s.rolling(3).mean()        # 3-period moving average
s.rolling(7).std()
s.rolling(3, min_periods=1).mean()  # allow partial windows

# Custom aggregation
s.rolling(3).apply(lambda x: x.max() - x.min())

# Exponentially weighted (decaying weights)
s.ewm(span=7).mean()       # EWMA — recent data weighted more
s.ewm(alpha=0.3).mean()

# Expanding window (grows from start)
s.expanding().mean()       # cumulative mean
s.expanding().max()        # running max

# Window with time-based offset (needs DatetimeIndex)
ts.rolling("7D").mean()    # 7-day window, not 7 rows

# Convert to category dtype
df["city"] = df["city"].astype("category")

# Create with explicit categories
s = pd.Categorical(["a", "b", "a", "c"],
                    categories=["a", "b", "c", "d"])

# Ordered categorical (for sorting & comparisons)
size = pd.Categorical(["M", "L", "S", "XL"],
                       categories=["S", "M", "L", "XL"],
                       ordered=True)
size > "M"  # works because it's ordered

# Memory savings
df["city"].memory_usage(deep=True)              # object
df["city"].astype("category").memory_usage(deep=True)  # much less

s = pd.Series(["a", "b", "a", "c"], dtype="category")

# Inspect categories
s.cat.categories        # Index(['a', 'b', 'c'])
s.cat.codes             # integer codes
s.value_counts()

# Add / remove categories
s = s.cat.add_categories(["d"])
s = s.cat.remove_categories(["d"])     # values in 'd' become NaN
s = s.cat.remove_unused_categories()

# Rename categories
s = s.cat.rename_categories({"a": "alpha", "b": "beta"})

# Reorder categories
s = s.cat.reorder_categories(["c", "b", "a"])

# Set / unset ordering
s = s.cat.as_ordered()
s = s.cat.as_unordered()

# Operations preserve categorical dtype where possible
s = pd.Series(["low", "high", "med", "low"], dtype="category")
s.value_counts()

# Comparison on ordered categoricals
size = pd.Series(["M", "L", "S"], dtype="category")
size = size.cat.as_ordered().cat.reorder_categories(["S","M","L","XL"])
size > "M"   # boolean Series

# Groupby works as expected
df.groupby("city")["score"].mean()

# Converting back
df["city"] = df["city"].astype(str)         # to string
df["city"] = df["city"].astype("object")    # to object

# Beware: merges between categoricals can be slow if categories differ
# Align categories before merging:
cats = sorted(set(df1["city"].cat.categories) |
              set(df2["city"].cat.categories))
df1["city"] = df1["city"].cat.set_categories(cats)
df2["city"] = df2["city"].cat.set_categories(cats)

# Check current memory
df.info(memory_usage="deep")

# Convert object columns to category when cardinality is low
for col in df.select_dtypes(include="object"):
    if df[col].nunique() / len(df) < 0.5:  # <50% unique
        df[col] = df[col].astype("category")

# Downcast numeric columns
df["age"] = pd.to_numeric(df["age"], downcast="integer")
df["score"] = pd.to_numeric(df["score"], downcast="float")

# Use string dtype (pandas 1.0+) instead of object
df["name"] = df["name"].astype("string")

# Compare memory before/after
before = df.memory_usage(deep=True).sum()
# ... optimize ...
after = df.memory_usage(deep=True).sum()
print(f"Reduced from {before/1e6:.1f}MB to {after/1e6:.1f}MB")

# One-hot encode categoricals
pd.get_dummies(df["city"])
pd.get_dummies(df, columns=["city", "pass"])  # encode multiple

# Drop first to avoid multicollinearity (for linear models)
pd.get_dummies(df["city"], drop_first=True)

# With a prefix
pd.get_dummies(df["city"], prefix="city")

# From categorical with all categories
pd.get_dummies(df["city"].astype("category"))

# Alternatively, sklearn's OneHotEncoder (for ML pipelines)
from sklearn.preprocessing import OneHotEncoder
ohe = OneHotEncoder(sparse_output=False, handle_unknown="ignore")
X_ohe = ohe.fit_transform(df[["city"]])

# BAD: iterrows is extremely slow
for idx, row in df.iterrows():
    df.loc[idx, "x"] = row["a"] + row["b"]

# GOOD: vectorized operations
df["x"] = df["a"] + df["b"]

# GOOD: .str accessor for strings
df["name"] = df["name"].str.upper()

# GOOD: np.where for conditionals
df["cat"] = np.where(df["a"] > 0, "pos", "neg")

# If you must iterate (rare), use itertuples (faster than iterrows)
for row in df.itertuples():
    process(row.a, row.b)

# Or .to_numpy() / .values for raw array loops
for row in df[["a", "b"]].to_numpy():
    ...

# Benchmark
%timeit df["a"] + df["b"]
%timeit df.apply(lambda r: r["a"] + r["b"], axis=1)

# eval: evaluate string expressions (uses numexpr, faster on big data)
df.eval("total = a + b + c", inplace=True)
df.eval("ratio = a / (b + c)")
df.eval("c = a > b")

# Multiple operations in one eval (efficient)
df.eval("""
total = a + b
ratio = a / b
flag = a > 10
""", inplace=True)

# query: boolean filter via string (same engine)
df.query("a > 0 and b < 10")

# Local variables with @
threshold = 5
df.query("a > @threshold")

# eval is faster than Python arithmetic on large frames
# because it avoids intermediate arrays

# Downcast numerics
df["age"] = pd.to_numeric(df["age"], downcast="unsigned")  # 0-255
df["score"] = pd.to_numeric(df["score"], downcast="float")

# Use category for low-cardinality strings
df["city"] = df["city"].astype("category")

# Use 'string' dtype instead of object
df["name"] = df["name"].astype("string")

# Use Nullable integers (Int64 with capital I) for ints with NaN
df["count"] = df["count"].astype("Int64")  # supports NaN

# Sparse for mostly-zero data
s = pd.Series([0, 0, 0, 1, 0, 0]).astype("Sparse[int]")

# Compare memory
df["city"].astype("object").memory_usage(deep=True)
df["city"].astype("category").memory_usage(deep=True)

# Process a large CSV in chunks (low memory)
chunk_iter = pd.read_csv("huge.csv", chunksize=100_000)

results = []
for chunk in chunk_iter:
    # process each chunk (filter, aggregate, etc.)
    result = chunk.groupby("city")["sales"].sum()
    results.append(result)

# Combine chunk results
total = pd.concat(results).groupby(level=0).sum()

# Or use the iterator directly
for i, chunk in enumerate(pd.read_csv("huge.csv", chunksize=50000)):
    chunk.to_parquet(f"part_{i:04d}.parquet")

# For SQL, use server-side cursors
chunks = pd.read_sql("SELECT * FROM big_table", engine,
                     chunksize=100000)

# Time an operation
%timeit df["a"] + df["b"]

# Line profiling (install line_profiler)
# %load_ext line_profiler
# %lprun -f my_function my_function(df)

# Memory profiling (install memory_profiler)
# %load_ext memory_profiler
# %memit df.groupby("city").sum()

# pandas profiling report (install ydata-profiling)
# from ydata_profiling import ProfileReport
# ProfileReport(df).to_file("report.html")

# Quick stats
df.info(memory_usage="deep")
df.describe(include="all")

# Find slow ops
import pandas as pd
pd.set_option("mode.chained_assignment", "warn")  # catch copy bugs

from sqlalchemy import create_engine
import pandas as pd

# Create an engine (connection pool)
engine = create_engine("sqlite:///my.db")
engine = create_engine("postgresql://user:pass@host:5432/db")
engine = create_engine("mysql+pymysql://user:pass@host/db")

# Read a whole table
df = pd.read_sql("users", engine)

# Read a query
df = pd.read_sql("SELECT * FROM users WHERE age > 18", engine)

# Parameterized queries (safe from SQL injection)
df = pd.read_sql(
    "SELECT * FROM users WHERE city = %s",
    engine,
    params=("NYC",),
)

# Chunked reads for large tables
for chunk in pd.read_sql("SELECT * FROM big", engine, chunksize=100000):
    process(chunk)

# Write a DataFrame to a SQL table
df.to_sql("users", engine, if_exists="replace", index=False)
df.to_sql("users", engine, if_exists="append", index=False)  # add rows

# Specify schema and dtype
from sqlalchemy.types import VARCHAR, INTEGER
df.to_sql("users", engine, if_exists="replace",
          dtype={"name": VARCHAR(100), "age": INTEGER()})

# Write in chunks (for large DataFrames)
df.to_sql("users", engine, if_exists="append",
          chunksize=10000, method="multi")

# method='multi' uses a single INSERT with multiple rows (faster)
# For PostgreSQL, method='multi' with chunksize ~10000 is optimal

# Don't write the index unless it's meaningful data
# index=False avoids a spurious 'index' column

# SELECT cols
df[["name", "age"]]
# WHERE
df[df["age"] > 18]
# GROUP BY + aggregations
df.groupby("city")["age"].mean()
# ORDER BY
df.sort_values("age", ascending=False)
# LIMIT
df.head(10)
# DISTINCT
df["city"].unique()
df.drop_duplicates(subset=["city"])
# JOIN
pd.merge(df1, df2, on="id", how="inner")
# UNION
pd.concat([df1, df2]).drop_duplicates()
# CASE WHEN
np.where(df["age"] >= 18, "adult", "minor")
# HAVING
g = df.groupby("city").filter(lambda g: g["age"].mean() > 30)

from sqlalchemy import text

# Use text() for explicit SQL (recommended over raw strings)
query = text("SELECT city, COUNT(*) as n FROM users GROUP BY city")
df = pd.read_sql(query, engine)

# Bind parameters safely
query = text("SELECT * FROM users WHERE age > :min_age AND city = :city")
df = pd.read_sql(query, engine,
                 params={"min_age": 18, "city": "NYC"})

# Execute DDL or DML (non-SELECT)
with engine.begin() as conn:  # transaction
    conn.execute(text("CREATE INDEX idx_city ON users(city)"))
    conn.execute(text("DELETE FROM users WHERE id = :id"),
                 {"id": 999})

# CSV: human-readable, slow, loses dtypes, large
df.to_csv("data.csv", index=False)
df = pd.read_csv("data.csv")  # re-infers dtypes (slow, error-prone)

# Parquet: binary, fast, preserves dtypes, compressed (best for storage)
df.to_parquet("data.parquet")
df = pd.read_parquet("data.parquet")  # dtypes preserved exactly

# Feather: fastest for short-lived storage (Apache Arrow)
df.to_feather("data.feather")  # requires a RangeIndex
df = pd.read_feather("data.feather")

# Pickle: Python-specific, fast, but version-fragile
df.to_pickle("data.pkl")
df = pd.read_pickle("data.pkl")

# SQL: shared, queryable, but slower for bulk reads
df.to_sql("table", engine)
df = pd.read_sql("SELECT * FROM table", engine)

# Rule of thumb:
# - CSV for human exchange
# - Parquet for storage/archival (best default)
# - Feather for fast cache between Python processes
# - SQL for shared, queryable data

# Ensure the date column is datetime, then set as index
df["date"] = pd.to_datetime(df["date"])
df = df.set_index("date").sort_index()

# Now you can slice by date strings
df.loc["2024"]                 # all of 2024
df.loc["2024-06"]              # June 2024
df.loc["2024-06-01":"2024-06-15"]  # date range (inclusive)

# Partial string indexing on Series
s = df["value"]
s["2024-06-15"]

# Filter with date strings
df[df.index >= "2024-01-01"]

# A DatetimeIndex unlocks resample, rolling, shift, etc.

# Daily -> monthly revenue
daily.resample("MS").sum()

# Common frequencies:
# D=day, B=business day, W=weekly, MS=month start, ME=month end,
# QS=quarter start, YE=year end, H=hour, T/min=minute, S=second

# Multiple aggregations
daily.resample("ME").agg({"sales": "sum", "users": "mean"})

# Resample per group
df.groupby("city").resample("W")["sales"].sum()

# Handle missing periods (fill gaps in the timeline)
daily.asfreq("D")           # add NaN rows for missing days
daily.asfreq("D").ffill()   # fill them

# Quarter-end reporting
daily.resample("QE").last()  # last value of each quarter

# Localize naive timestamps (assign a timezone)
s = pd.Series(pd.date_range("2024-01-01", periods=3, freq="H"))
s_localized = s.dt.tz_localize("UTC")
s_localized = s.dt.tz_localize("America/New_York")

# Convert between timezones
s_utc = s.dt.tz_localize("UTC")
s_est = s_utc.dt.tz_convert("US/Eastern")
s_hk  = s_utc.dt.tz_convert("Asia/Hong_Kong")

# On a DatetimeIndex
idx = pd.date_range("2024-01-01", periods=3, freq="H", tz="UTC")
idx = idx.tz_convert("Asia/Shanghai")

# Remove timezone info
s_naive = s_utc.dt.tz_localize(None)

# Ambiguous times (DST fall-back): use 'infer' or 'NaT'
s.dt.tz_localize("US/Eastern", ambiguous="infer")

# Rolling window with time offset
ts.rolling("7D").mean()      # 7-day window (handles irregular sampling)

# Rolling with custom function
ts.rolling("30D").apply(lambda x: np.quantile(x, 0.95))

# Expanding window (cumulative)
ts.expanding(min_periods=10).mean()

# Exponentially weighted
ts.ewm(span=20).mean()       # like a 20-period EWMA
ts.ewm(halflife=10).mean()

# Window per group
df.groupby("city")["sales"].rolling("7D").mean().reset_index(level=0, drop=True)

# Compare rolling vs ewm for smoothing
# ewm weights recent points more — better for fast-changing series

# Seasonal decomposition (needs statsmodels)
from statsmodels.tsa.seasonal import seasonal_decompose

# Additive model: y = trend + seasonal + residual
result = seasonal_decompose(ts, model="additive", period=12)
result.plot()
result.trend, result.seasonal, result.resid

# Multiplicative model: y = trend * seasonal * residual
result = seasonal_decompose(ts, model="multiplicative", period=12)

# Seasonal plots
ts.groupby(ts.index.month).mean()   # average by month
ts.groupby(ts.index.dayofweek).mean()  # by weekday

# Lag plot (autocorrelation check)
pd.plotting.lag_plot(ts, lag=1)
pd.plotting.autocorrelation_plot(ts)

import matplotlib.pyplot as plt

# Line plot (default for time series)
df["score"].plot()
df.plot(x="date", y="score")

# Bar chart
df["city"].value_counts().plot.bar()
df["city"].value_counts().plot.barh()  # horizontal

# Histogram
df["age"].plot.hist(bins=20)
df[["age", "score"]].plot.hist(alpha=0.5, bins=20)

# Scatter
df.plot.scatter(x="age", y="score", c="pass", colormap="viridis")

# Box plot (per group)
df.boxplot(column="score", by="city")

# Area, density, pie
df["city"].value_counts().plot.pie(autopct="%1.1f%%")
df["score"].plot.kde()  # kernel density estimate

plt.tight_layout(); plt.show()

import seaborn as sns
import matplotlib.pyplot as plt

# Statistical plots that pandas can't easily do
sns.histplot(df["score", kde=True])
sns.boxplot(data=df, x="city", y="score")
sns.violinplot(data=df, x="city", y="score")

# Relationships
sns.scatterplot(data=df, x="age", y="score", hue="city")
sns.pairplot(df[["age", "score", "pass"]], hue="pass")

# Correlation heatmap
sns.heatmap(df.corr(numeric_only=True), annot=True, cmap="coolwarm")

# Regression plot
sns.regplot(data=df, x="age", y="score")

# Facet grid (small multiples)
g = sns.FacetGrid(df, col="city", hue="pass")
g.map(sns.histplot, "score")

plt.tight_layout(); plt.show()

import plotly.express as px

# Interactive scatter (hover, zoom, pan)
fig = px.scatter(df, x="age", y="score", color="city",
                 hover_data=["name"], title="Score vs Age")
fig.show()

# Line chart
fig = px.line(df, x="date", y="score", color="city")

# Bar
fig = px.bar(df, x="city", y="score", color="pass", barmode="group")

# Histogram
fig = px.histogram(df, x="score", color="city", marginal="box")

# Faceted
fig = px.scatter(df, x="age", y="score", facet_col="city")

# 3D
fig = px.scatter_3d(df, x="age", y="score", z="hours", color="city")

# Save as HTML (interactive, no JS needed to view)
fig.write_html("plot.html")

import matplotlib.pyplot as plt

# pandas plot on specific axes
fig, axes = plt.subplots(2, 2, figsize=(12, 8))
df["age"].plot.hist(ax=axes[0, 0], title="Age")
df["score"].plot.hist(ax=axes[0, 1], title="Score")
df["city"].value_counts().plot.bar(ax=axes[1, 0], title="City")
df.plot.scatter(x="age", y="score", ax=axes[1, 1], title="Age vs Score")
plt.tight_layout(); plt.show()

# Shared x-axis
fig, (ax1, ax2) = plt.subplots(2, 1, sharex=True, figsize=(10, 6))
df["score"].plot(ax=ax1)
df["age"].plot(ax=ax2)

# Secondary y-axis
ax = df["score"].plot()
df["age"].plot(ax=ax, secondary_y=True)
ax.set_ylabel("Score"); ax.right_ax.set_ylabel("Age")

# Style a DataFrame for HTML/Jupyter display
styled = df.style.highlight_max(color="lightgreen")
styled = df.style.highlight_min(color="lightcoral")
styled = df.style.background_gradient(cmap="Blues", subset=["score"])

# Bar charts inside cells
df.style.bar(subset=["score"], color="#5fba7d")

# Format numbers
df.style.format({"score": "{:.2f}", "age": "{:.0f}"})
df.style.format({"score": "{:.2%}"})  # as percentage

# Conditional formatting
def color_neg_red(val):
    color = "red" if val < 0 else "black"
    return f"color: {color}"
df.style.applymap(color_neg_red, subset=["change"])

# Combine styles
(df.style
   .format({"score": "{:.2f}"})
   .background_gradient(cmap="RdYlGn", subset=["score"])
   .set_caption("Student Scores"))

# Export to HTML
styled.to_html("table.html")

# Global display options
pd.set_option("display.max_rows", 100)
pd.set_option("display.max_columns", 50)
pd.set_option("display.width", 1000)
pd.set_option("display.max_colwidth", 100)
pd.set_option("display.float_format", "{:.2f}".format)

# Reset to defaults
pd.reset_option("display.max_rows")

# Float formatting only for one operation
with pd.option_context("display.float_format", "{:.2f}".format):
    print(df)

# Temporarily show more rows
with pd.option_context("display.max_rows", None):
    print(df.head(100))

# CSV
df.to_csv("out.csv", index=False)
df.to_csv("out.tsv", sep="\t", index=False)

# Excel with formatting (needs openpyxl)
with pd.ExcelWriter("out.xlsx", engine="openpyxl") as w:
    df.to_excel(w, sheet_name="data", index=False)
    summary.to_excel(w, sheet_name="summary")

# Markdown (great for docs/reports)
print(df.to_markdown(index=False))
df.to_markdown("table.md", index=False)

# HTML
df.to_html("table.html", index=False, classes="table")

# LaTeX
print(df.to_latex(index=False, caption="Results"))

# JSON
df.to_json("out.json", orient="records", indent=2, date_format="iso")

# Dict / records
df.to_dict(orient="records")  # list of dicts
df.to_dict(orient="list")     # dict of lists

# Copy a DataFrame to the clipboard (paste into Excel/Sheets)
df.to_clipboard(index=False, excel=True)

# Read from clipboard (paste a table from a spreadsheet/webpage)
df = pd.read_clipboard()

# Convert to string with formatting
print(df.to_string(index=False))
print(df.to_string(max_rows=10))

# Pretty-print in Jupyter
from IPython.display import display
display(df.head(10))

# Render a Series as a string
print(s.to_string())

# Quick peek at value (useful in debugging)
df.head().to_dict("records")

from openpyxl.styles import Font, PatternFill
from openpyxl.utils import get_column_letter

# Write with conditional formatting
with pd.ExcelWriter("report.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="Data", index=False)

    # Access the workbook to style
    wb = writer.book
    ws = writer.sheets["Data"]

    # Bold header
    for cell in ws[1]:
        cell.font = Font(bold=True, color="FFFFFF")
        cell.fill = PatternFill("solid", fgColor="4472C4")

    # Auto-size columns
    for col in ws.columns:
        max_len = max(len(str(c.value)) for c in col if c.value is not None)
        ws.column_dimensions[get_column_letter(col[0].column)].width = max_len + 2

    # Freeze header row
    ws.freeze_panes = "A2"

# At the top of every analysis notebook
import pandas as pd
import numpy as np
pd.set_option("display.max_columns", None)
pd.set_option("display.float_format", "{:.4f}".format)

# Set a seed for reproducibility
np.random.seed(42)

# Show versions for reproducibility
pd.show_versions()

# Common imports grouped
import matplotlib.pyplot as plt
import seaborn as sns
sns.set_theme(style="whitegrid")

# Suppress chained-assignment warnings cleanly
import warnings
warnings.filterwarnings("ignore", category=FutureWarning)

# Validate data on load
def check_df(df, name="df"):
    print(f"=== {name} ===")
    print(f"Shape: {df.shape}")
    print(df.dtypes)
    print(df.head())
    print(df.isnull().sum())
check_df(df)

# BAD: chained indexing — may not modify df
df[df["age"] > 30]["city"] = "NYC"   # SettingWithCopyWarning

# GOOD: single .loc assignment
df.loc[df["age"] > 30, "city"] = "NYC"

# When subsetting, be explicit about copy vs view
subset = df[df["age"] > 30].copy()   # explicit copy
subset["city"] = "NYC"               # safe — modifies subset only

# Detect the warning
pd.set_option("mode.chained_assignment", "warn")   # default
pd.set_option("mode.chained_assignment", "raise")  # error (dev mode)
pd.set_option("mode.chained_assignment", None)     # suppress

# Rule: if you're going to modify, use .loc or .copy() first

# Adding Series aligns on index — NaN where they don't match
s1 = pd.Series([1, 2, 3], index=["a", "b", "c"])
s2 = pd.Series([10, 20, 30], index=["b", "c", "d"])
s1 + s2
# a     NaN
# b    12.0
# c    33.0
# d     NaN

# When assigning a Series to a column, alignment happens by index
df["new"] = some_series   # rows matched by INDEX, not position!

# To force positional alignment, use .values
df["new"] = some_series.values   # assign by position

# After sorting/resetting, indexes may not match
df1 = df.sort_values("age")
df1["rank"] = range(len(df1))   # WRONG if index isn't 0..n-1
df1["rank"] = np.arange(len(df1))  # also WRONG — aligns on index
df1["rank"] = pd.Series(range(len(df1)), index=df1.index)  # correct
# Or simply:
df1 = df1.reset_index(drop=True)
df1["rank"] = range(len(df1))

# Two equivalent styles:
df = df.sort_values("age")        # reassign (returns new df)
df.sort_values("age", inplace=True)  # modify in place

# Most pandas methods return a new object (functional style).
# inplace=True modifies the object — but:
# 1. It's being deprecated in some methods
# 2. It can prevent method chaining
# 3. It sometimes still makes a copy under the hood

# Prefer functional style for chaining:
(df
   .sort_values("age")
   .reset_index(drop=True)
   .rename(columns={"old": "new"}))

# inplace is fine for memory savings on huge DataFrames,
# but rarely matters otherwise.

# BAD: default mutable args are shared across calls
def add_row(df, new_data=[]):   # NEVER use mutable defaults
    new_data.append(1)
    return df

# GOOD: use None
def add_row(df, new_data=None):
    if new_data is None:
        new_data = []
    ...

# Building a DataFrame row by row (SLOW — anti-pattern)
df = pd.DataFrame()
for item in items:
    df = pd.append(df, item)  # deprecation + O(n^2)

# GOOD: build a list of records, then construct once
records = []
for item in items:
    records.append({"name": item.name, "value": item.value})
df = pd.DataFrame(records)

# Or use from_records
df = pd.DataFrame.from_records(items, columns=["name", "value"])

# NaN propagates through most operations
np.nan + 1          # nan
np.nan > 0          # False
pd.Series([1, 2, np.nan]).sum()    # 3.0 (skips NaN by default)
pd.Series([1, 2, np.nan]).mean()   # 1.5

# Some operations DON'T skip NaN
pd.Series([1, 2, np.nan]).sum(skipna=False)  # nan

# Comparisons with NaN are always False
pd.Series([1, np.nan]) == pd.Series([1, np.nan])
# [True, False]  — NaN != NaN!

# Check for NaN correctly
s.isna()            # True for NaN
s.isnull()          # alias
s == np.nan         # WRONG — always False!
s.fillna(0) == 0    # check after filling

# Integer columns with NaN become float
s = pd.Series([1, 2, None])   # becomes float64: [1.0, 2.0, NaN]
# Use nullable Int64 to keep integer with NaN
s = pd.Series([1, 2, None], dtype="Int64")

import pandas as pd
import numpy as np

def eda(df, name="df"):
    print(f"=== {name} ===")
    print(f"Shape: {df.shape}")
    print("\n--- dtypes ---")
    print(df.dtypes)
    print("\n--- head ---")
    print(df.head())
    print("\n--- missing ---")
    print(df.isnull().sum())
    print("\n--- numeric summary ---")
    print(df.describe())
    print("\n--- categorical summary ---")
    print(df.describe(include="object"))
    print("\n--- duplicates ---")
    print(f"{df.duplicated().sum()} duplicate rows")

# Usage
eda(df)

# Per-column deep dive
for col in df.columns:
    print(f"\n### {col}")
    print(f"dtype: {df[col].dtype}")
    print(f"unique: {df[col].nunique()}")
    if df[col].dtype == "object":
        print(df[col].value_counts().head(10))
    else:
        print(df[col].describe())

def clean(df):
    """A reusable cleaning pipeline."""
    return (df
        # Standardize column names
        .rename(columns=lambda c: c.strip().lower().replace(" ", "_"))
        # Drop empty columns
        .dropna(axis=1, how="all")
        # Parse dates
        .assign(date=lambda d: pd.to_datetime(d["date"], errors="coerce"))
        # Numeric coercion
        .assign(amount=lambda d: pd.to_numeric(d["amount"], errors="coerce"))
        # Fill missing
        .fillna({"category": "unknown"})
        # Dedupe
        .drop_duplicates()
        # Reset index
        .reset_index(drop=True)
    )

df_clean = clean(df_raw)

# Verify
assert df_clean["date"].notnull().all(), "Some dates failed to parse"
assert len(df_clean) <= len(df_raw), "Cleaning should not add rows"

# Multi-dimensional analysis in one shot
pivot = pd.pivot_table(
    df,
    values="revenue",
    index=["region", "product"],
    columns=["quarter"],
    aggfunc="sum",
    fill_value=0,
    margins=True,         # add row/column totals
    margins_name="Total",
)

# Heatmap of the pivot
import seaborn as sns
sns.heatmap(pivot, annot=True, fmt=".0f", cmap="YlGnBu")

# Convert back to long
long = pivot.reset_index().melt(
    id_vars=["region", "product"],
    var_name="quarter",
    value_name="revenue",
)

# Top product per region
top = (df
    .groupby(["region", "product"])["revenue"].sum()
    .reset_index()
    .sort_values("revenue", ascending=False)
    .groupby("region").head(1))

def add_features(df):
    """Add features for a churn model."""
    out = df.copy()

    # Time-based features
    out["tenure_years"] = out["tenure_months"] / 12
    out["is_new_customer"] = (out["tenure_months"] < 12).astype(int)

    # Aggregations per customer (need transaction history)
    # txns is a separate transactions DataFrame
    agg = txns.groupby("customer_id").agg(
        total_spend=("amount", "sum"),
        txn_count=("amount", "count"),
        avg_txn=("amount", "mean"),
        last_txn_date=("date", "max"),
    ).reset_index()
    out = out.merge(agg, on="customer_id", how="left")

    # Ratio features
    out["spend_per_month"] = out["total_spend"] / out["tenure_months"]

    # Binning
    out["age_group"] = pd.cut(out["age"], bins=[0,25,40,60,100],
                              labels=["young","adult","mid","senior"])

    # Encode categoricals
    out = pd.get_dummies(out, columns=["age_group", "plan"], drop_first=True)

    return out

df_ml = add_features(df)

# Common CSV issues and fixes

# Bad encoding
df = pd.read_csv("data.csv", encoding="latin-1")  # or utf-8-sig, cp1252

# Wrong separator
df = pd.read_csv("data.tsv", sep="\t")
df = pd.read_csv("data.csv", sep=";", decimal=",")  # European format

# Skip metadata rows at the top
df = pd.read_csv("data.csv", skiprows=3)

# Multiple header rows
df = pd.read_csv("data.csv", header=[0, 1])

# Custom NA values
df = pd.read_csv("data.csv", na_values=["?", "N/A", "NULL", -999])

# Parse dates with multiple formats
df = pd.read_csv("data.csv", parse_dates=["date"], dayfirst=True)

# Only load specific columns (saves memory)
df = pd.read_csv("data.csv", usecols=["id", "date", "amount"])

# Handle quoted fields with commas/newlines
df = pd.read_csv("data.csv", quotechar='"', escapechar="\\")

s = pd.Series(range(20), dtype=float)

# Built-in aggregations (fast, C-optimized)
s.rolling(5).mean()
s.rolling(5).std()
s.rolling(5).median()
s.rolling(5).quantile(0.75)
s.rolling(5).sum()

# Custom function (slower, but flexible)
s.rolling(5).apply(lambda x: x.max() - x.min())

# Weighted rolling average
weights = np.array([0.1, 0.2, 0.3, 0.25, 0.15])
s.rolling(5).apply(lambda x: np.dot(x, weights))

# Rolling regression (slope over window)
def slope(x):
    return np.polyfit(range(len(x)), x, 1)[0]
s.rolling(10).apply(slope)

# raw=False passes Series (with index) to the function
s.rolling(5).apply(lambda x: x.iloc[-1] / x.iloc[0], raw=False)

# Row-count vs time-based windows
ts = pd.Series(range(100), index=pd.date_range("2024-01-01", periods=100, freq="D"))

# Row-count: exactly 7 rows (may span more than 7 days if data is irregular)
ts.rolling(7).mean()

# Time-based: all rows within the last 7 days (handles gaps)
ts.rolling("7D").mean()

# Time-based is essential for irregular time series
irregular = pd.Series([1, 2, 3],
    index=pd.to_datetime(["2024-01-01", "2024-01-05", "2024-01-15"]))
irregular.rolling("7D").sum()   # 1, 3, 3 (third is just itself, no neighbors in 7d)

# Center the window
ts.rolling("7D", center=True).mean()  # window centered on current point

# Closed controls which endpoints are included
ts.rolling("7D", closed="left").mean()  # exclude current point

s = pd.Series([1, 2, 3, 4, 5, 6, 7, 8], dtype=float)

# Expanding: window grows from the start
s.expanding(min_periods=2).mean()   # cumulative mean
s.expanding().max()                 # running maximum
s.expanding().std()

# Exponentially weighted moving average
s.ewm(span=5).mean()       # span=N similar to N-period moving avg
s.ewm(alpha=0.3).mean()    # alpha = smoothing factor (0-1)
s.ewm(halflife=3).mean()   # halflife: weight halves every N periods

# EWMA volatility (finance)
returns.ewm(span=20).std()  # 20-period EWMA volatility

# Relationship: span = (2/alpha) - 1
# Higher alpha/span = more responsive to recent data

s = pd.Series([1, 2, 3, 4, 5])

# Direct cumulative methods (faster than expanding)
s.cumsum()    # [1, 3, 6, 10, 15]
s.cummax()    # [1, 2, 3, 4, 5]
s.cummin()    # [1, 1, 1, 1, 1]
s.cumprod()   # [1, 2, 6, 24, 120]

# Cumulative within groups
df["running_total"] = df.groupby("customer")["amount"].cumsum()
df["rank_in_group"] = df.groupby("customer")["amount"].rank(ascending=False)
df["pct_of_total"] = df["amount"] / df.groupby("customer")["amount"].transform("sum")

# Cumulative count of condition
df["days_since_event"] = (df["event"] == 1).cumsum()
df["event_number"] = df.groupby(
    (df["event"] == 1).cumsum()
).cumcount()

# Apply a function returning multiple values per window
def rolling_stats(x):
    return pd.Series({
        "mean": x.mean(),
        "std": x.std(),
        "min": x.min(),
        "max": x.max(),
    })

stats = s.rolling(10).apply(rolling_stats, raw=False)
# This creates a DataFrame with one row per window, columns = the Series index

# Alternative: compute each separately (often clearer)
window = s.rolling(10)
result = pd.DataFrame({
    "mean": window.mean(),
    "std":  window.std(),
    "min":  window.min(),
    "max":  window.max(),
})

# Rolling quantiles
q = pd.DataFrame({
    "q10": s.rolling(20).quantile(0.1),
    "q50": s.rolling(20).quantile(0.5),
    "q90": s.rolling(20).quantile(0.9),
})