pandas essentials: loading, cleaning and transforming data

import pandas as pd

df = pd.read_csv("applications.csv", parse_dates=["applied_on"])
df.shape            # (rows, columns)
df.head()           # first rows
df.info()           # columns, types, non-null counts
df.describe()       # summary statistics for numeric columns
df["status"].value_counts(dropna=False)

df[["student_id", "job_id", "score"]]                 # columns
df.loc[df["score"] >= 8, ["student_id", "score"]]     # rows by condition, chosen columns
df[(df["city"] == "Pune") & (df["status"] == "hired")]  # & and |, with brackets
df.query("score >= 8 and city in ['Pune', 'Mumbai']")

# Standardise text labels
df["city"] = df["city"].str.strip().str.title().replace({"Bangalore": "Bengaluru", "Bombay": "Mumbai"})

# Fix types
df["salary"] = pd.to_numeric(df["salary"].str.replace(",", ""), errors="coerce")

# Missing values
df.isna().sum()                         # how many per column
df["score"] = df["score"].fillna(df["score"].median())
df = df.dropna(subset=["student_id"])   # rows without an id are useless

# Duplicates
df = df.drop_duplicates(subset=["student_id", "job_id"], keep="first")

# Outliers: look before you delete
q1, q3 = df["salary"].quantile([0.25, 0.75])
iqr = q3 - q1
outliers = df[(df["salary"] < q1 - 1.5 * iqr) | (df["salary"] > q3 + 1.5 * iqr)]

df["month"] = df["applied_on"].dt.to_period("M")
df["band"] = pd.cut(df["score"], bins=[0, 4, 7, 10], labels=["Low", "Mid", "High"])
df["is_hired"] = (df["status"] == "hired").astype(int)

summary = (df.groupby("city")
             .agg(applications=("job_id", "count"),
                  hired=("is_hired", "sum"),
                  avg_score=("score", "mean"))
             .assign(hire_rate=lambda t: t["hired"] / t["applications"])
             .sort_values("hire_rate", ascending=False))

merged = df.merge(jobs, left_on="job_id", right_on="id", how="left", validate="many_to_one")
pivot = df.pivot_table(index="month", columns="city", values="job_id", aggfunc="count", fill_value=0)
long = pivot.reset_index().melt(id_vars="month", var_name="city", value_name="applications")