import pandas as pd

titanic = pd.read_csv("data/pandas/titanic.csv")
titanic.isna().sum().sort_values(ascending=False).head(3)

print(titanic.duplicated().sum(), "duplicate rows")
print(titanic["PassengerId"].is_unique)

retail = pd.read_csv("data/pandas/online_retail.csv.gz")
print(retail.shape)
retail.head(3)

dup_mask = retail.duplicated()
print(dup_mask.sum(), "duplicate rows")
print(round(dup_mask.mean() * 100, 2), "percent of the table")

copies = retail[retail.duplicated(keep=False)]
print(len(copies), "rows are involved")
copies.sort_values(["InvoiceNo", "StockCode"]).head(4)

copies.value_counts(dropna=False).value_counts().sort_index()

revenue = retail["Quantity"] * retail["UnitPrice"]
print("Total revenue:", round(revenue.sum(), 2))
print("From copies:  ", round(revenue[dup_mask].sum(), 2))

print(retail.loc[dup_mask, "InvoiceNo"].nunique(), "invoices hit")
print(retail["InvoiceNo"].nunique(), "invoices in total")

clean = retail.drop_duplicates()
print(len(retail), "rows before")
print(len(clean), "rows after")
print(clean.duplicated().sum(), "duplicates left")

print(clean.index[-3:].tolist())
clean = retail.drop_duplicates(ignore_index=True)
print(clean.index[-3:].tolist())

orders = clean[clean["InvoiceNo"] == "536446"]
orders = orders[["StockCode", "Description", "Quantity"]]
orders = orders.iloc[[0, 5, 6, 9, 29]]
orders

orders.drop_duplicates(subset="StockCode")

orders.drop_duplicates(subset="StockCode", keep="last")

orders.drop_duplicates(subset="StockCode", keep=False)

retail.drop_duplicates(subset=["Invoice", "StockCode"])

print(retail.columns.tolist())
pairs = retail.drop_duplicates(subset=["InvoiceNo", "StockCode"])
print(len(pairs), "rows")

survey = pd.read_csv("data/pandas/salary_survey.csv")
print(survey.shape)
print(survey.duplicated().sum(), "exact duplicates")

answers = survey.columns.drop("timestamp")
repeats = survey.duplicated(subset=answers)
print(repeats.sum(), "repeat submissions")

twins = survey[survey.duplicated(subset=answers, keep=False)]
twins = twins[twins["job_title"] != "-"]
cols = ["timestamp", "job_title", "annual_salary"]
twins.sort_values(cols[1:] + ["timestamp"])[cols].head(4)

survey = survey.drop_duplicates(subset=answers, keep="first")
print(len(survey), "responses kept")

same_item = retail.duplicated(["InvoiceNo", "StockCode"], keep=False)
differs = same_item & ~retail.duplicated(keep=False)
print(differs.sum(), "rows share an item but differ elsewhere")
show = ["InvoiceNo", "StockCode", "Quantity", "UnitPrice"]
retail.loc[differs, show].sort_values(show[:2]).head(4)

buyers = clean.dropna(subset=["CustomerID"])
print(buyers["CustomerID"].nunique(), "customers")
moves = buyers.groupby("CustomerID")["Country"].nunique()
moves[moves > 1]

when = pd.to_datetime(buyers["InvoiceDate"], format="%m/%d/%Y %H:%M")
buyers = buyers.assign(InvoiceDate=when)
latest = (buyers.sort_values("InvoiceDate")
                .drop_duplicates("CustomerID", keep="last"))
print(len(latest), "rows,", latest["CustomerID"].is_unique)

latest.loc[latest["CustomerID"] == 12394,
           ["CustomerID", "Country", "InvoiceDate"]]

first = (buyers.sort_values("InvoiceDate")
               .drop_duplicates("CustomerID", keep="first"))
first.loc[first["CustomerID"] == 12394,
          ["CustomerID", "Country", "InvoiceDate"]]

survey["country"].value_counts().head(8)

print(survey["country"].nunique(), "different spellings")

survey["country"].lower()

country = survey["country"].str.strip().str.lower()
print(country.nunique(), "after strip and lower")

country = country.str.replace(".", "", regex=False)
print(country.nunique(), "after removing dots")
country.value_counts().head(6)

us_names = {"usa": "united states", "us": "united states",
            "united states of america": "united states",
            "america": "united states", "u s": "united states",
            "united state": "united states",
            "unites states": "united states"}
country = country.replace(us_names)
print((country == "united states").sum(), "in the United States")

uk_names = {"uk": "united kingdom", "england": "united kingdom",
            "scotland": "united kingdom",
            "wales": "united kingdom"}
country = country.replace(uk_names)
country.value_counts().head(5)

survey["country_clean"] = country
print(survey["country"].nunique(), "->",
      survey["country_clean"].nunique(), "values")

country.value_counts().tail(5)

main = ["united states", "canada", "united kingdom", "australia"]
odd = ~survey["country_clean"].isin(main)
print(odd.sum(), "answers outside the main four")
survey.loc[odd, "country_clean"].value_counts().head(4)

exp = survey["overall_years_of_professional_experience"]
print(exp.unique()[:4])
exp = exp.str.replace(" - ", "-", regex=False)
print(exp.unique()[:4])

survey["gender"] = survey["gender"].replace(
    "Prefer not to answer", "Other or prefer not to answer")
survey["gender"].value_counts(dropna=False)

names = clean.groupby("StockCode")["Description"].nunique()
print(names.max(), "names for one stock code")
clean.loc[clean["StockCode"] == "20713", "Description"].value_counts()

raw_top = survey["country"].value_counts(normalize=True)
raw_top.head(3).mul(100).round(1)

clean_top = survey["country_clean"].value_counts(normalize=True)
clean_top.head(4).mul(100).round(1)

naive = survey["country"] == "United States"
tidy = survey["country_clean"] == "united states"
usd = survey["currency"] == "USD"
print(f"{naive.sum()} vs {tidy.sum()} US respondents")
print(survey.loc[naive & usd, "annual_salary"].median())
print(survey.loc[tidy & usd, "annual_salary"].median())

import os
os.makedirs("output/pandas", exist_ok=True)
survey.to_csv("output/pandas/l17_survey_clean.csv", index=False)
check = pd.read_csv("output/pandas/l17_survey_clean.csv")
print(check.shape, check.duplicated(subset=answers).sum(), "repeats")