import pandas as pd
import numpy as np

df = pd.read_csv("data/pandas/titanic.csv")
df.groupby("Pclass")["Survived"].mean().round(2)

print(len(df), "passengers")
print(df["Age"].count(), "ages recorded")

df.info()

df.isna().sum()

missing_pct = df.isna().mean().mul(100).round(1)
missing_pct.sort_values(ascending=False).head(4)

has_gap = df.isna().any(axis=1)
print(has_gap.sum(), "rows have at least one gap")
df.loc[has_gap, ["Pclass", "Age", "Cabin", "Embarked"]].head()

gaps_per_row = df.isna().sum(axis=1)
gaps_per_row.value_counts().sort_index()

print((df["Age"] == np.nan).sum(), "found with ==")
print(df["Age"].isna().sum(), "found with isna")

survey = pd.read_csv("data/pandas/salary_survey.csv")
city = survey["city"]
print(city.isna().sum(), "missing before")
fakes = ["N/a", "Na", "na", "-", "--"]
city[city.isin(fakes)].value_counts()

city = city.replace(fakes, np.nan)
print(city.isna().sum(), "missing after")

path = "data/pandas/salary_survey.csv"
fixed = pd.read_csv(path, na_values=fakes)
print(fixed["city"].isna().sum(), "missing with na_values")

import matplotlib.pyplot as plt
import seaborn as sns

fig, ax = plt.subplots(figsize=(10, 4))
sns.heatmap(df.isna(), cbar=False, yticklabels=False, ax=ax)
ax.set_title("Missing values in the Titanic data (light = missing)")
ax.set_xlabel("Column")
ax.set_ylabel("Passenger (one row each)")
plt.show()

cabin_gap = df["Cabin"].isna()
cabin_gap.groupby(df["Pclass"]).mean().round(2)

df.groupby(cabin_gap)["Survived"].mean().round(2)

df["Age"].isna().groupby(df["Pclass"]).mean().round(2)

age_known = df.dropna(subset=["Age"])
print("Before:", df.shape)
print("After: ", age_known.shape)

df.dropna().shape

min_filled = int(len(df) * 0.7)
slim = df.dropna(axis=1, thresh=min_filled)
print("Need at least", min_filled, "values")
print("Dropped:", set(df.columns) - set(slim.columns))

port_known = df.dropna(subset=["Embarked"])
print(len(df) - len(port_known), "rows dropped")
print(len(port_known), "rows left")

median_age = df["Age"].median()
age_filled = df["Age"].fillna(median_age)
print("Median age:", median_age)
print("Missing now:", age_filled.isna().sum())

print(age_filled.value_counts().head(3))
print("Spread before:", df["Age"].std().round(2))
print("Spread after: ", age_filled.std().round(2))

df.groupby("Pclass")["Age"].median()

class_median = df.groupby("Pclass")["Age"].transform("median")
df["Age_filled"] = df["Age"].fillna(class_median)
df.loc[df["Age"].isna(), ["Pclass", "Age", "Age_filled"]].head()

df["Age_filled"].value_counts().head(3)

fig, ax = plt.subplots(figsize=(8, 4))
age_filled.plot.hist(bins=40, alpha=0.6, ax=ax, label="One median")
df["Age_filled"].plot.hist(bins=40, alpha=0.6, ax=ax,
                           label="Class medians")
ax.set_title("Filled ages: one median vs class medians")
ax.set_xlabel("Age (years)")
ax.set_ylabel("Passengers")
ax.legend()
plt.show()

ages = pd.DataFrame({"original": df["Age"], "one median": age_filled,
                     "class medians": df["Age_filled"]})
ages.agg(["mean", "std"]).round(2)

top_port = df["Embarked"].mode()[0]
df["Embarked"] = df["Embarked"].fillna(top_port)
print("Most common port:", top_port)
print("Missing now:", df["Embarked"].isna().sum())

labelled = df.fillna({"Cabin": "Unknown", "Age": median_age})
labelled[["Age", "Cabin"]].isna().sum()

df["Cabin_known"] = df["Cabin"].notna().astype(int)
df[["Cabin", "Cabin_known"]].head()

df.groupby(["Pclass", "Cabin_known"])["Survived"].mean().unstack().round(2)

df["Age"].astype(int)

age_whole = df["Age"].round().astype("Int64")
print(age_whole.head(6))
print(age_whole.isna().sum(), "still missing")

aapl = pd.read_csv("data/pandas/aapl.csv", parse_dates=["Date"],
                   index_col="Date")
close = aapl["AAPL.Close"]
print(len(close), "trading days")

daily = close.asfreq("D")
print(len(daily), "calendar days,", daily.isna().sum(), "empty")
daily.head(8).round(2)

daily[daily.isna()].index.day_name().value_counts()

daily.fillna(method="ffill")

compare = pd.DataFrame({"gap": daily, "ffill": daily.ffill(),
                        "bfill": daily.bfill()})
compare.loc["2015-02-20":"2015-02-23"].round(2)

daily_filled = daily.ffill()
print(daily_filled.isna().sum(), "gaps left")
daily_filled.loc["2015-02-20":"2015-02-23"].round(2)

smooth = daily.interpolate()
smooth.loc["2015-02-20":"2015-02-23"].round(2)

span = slice("2015-02-17", "2015-03-08")
fig, ax = plt.subplots(figsize=(8, 4))
daily_filled.loc[span].plot(ax=ax, drawstyle="steps-post",
                            label="Forward fill")
smooth.loc[span].plot(ax=ax, style="--", label="Interpolate")
ax.set_title("Apple close over weekends: ffill vs interpolate")
ax.set_xlabel("Date")
ax.set_ylabel("Closing price (USD)")
ax.legend()
plt.show()

short_fill = daily.ffill(limit=1)
left = short_fill[short_fill.isna()]
print(len(left), "gaps left")
left.index.day_name().value_counts()

age_gap = df["Age"].isna()
df.groupby(age_gap)["Survived"].mean().round(2)

df.loc[age_gap, "Pclass"].value_counts(normalize=True).round(2)

by_class = df.groupby(["Pclass", age_gap])["Survived"].mean()
by_class.unstack().round(2)

clean = df.assign(Age=df["Age_filled"],
                  Age_missing=age_gap.astype(int))
clean = clean.drop(columns=["Cabin", "Age_filled"])
print(clean.isna().sum().sum(), "missing values left")
print(clean.shape)

import os
os.makedirs("output/pandas", exist_ok=True)
clean.to_csv("output/pandas/l16_titanic_clean.csv", index=False)
check = pd.read_csv("output/pandas/l16_titanic_clean.csv")
print(check.shape, check.isna().sum().sum(), "missing")