Day 16: Dates, Times & Text Columns

import pandas as pd
sales = pd.read_csv("sales_data.csv", parse_dates=["order_date"])
print(sales["order_date"].dtype, sales["order_date"].min().date(), "→", sales["order_date"].max().date())
sales["month"] = sales["order_date"].dt.month
sales["month_name"] = sales["order_date"].dt.strftime("%b")
sales["weekday"] = sales["order_date"].dt.day_name()
sales["is_weekend"] = sales["order_date"].dt.dayofweek >= 5
print(sales[["order_date", "month", "month_name", "weekday", "is_weekend"]].head(3))

import pandas as pd
sales = pd.read_csv("sales_data.csv", parse_dates=["order_date"])
order = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"]
by_day = sales["order_date"].dt.day_name().value_counts().reindex(order)
print(by_day)
print("Best day:", by_day.idxmax())

import pandas as pd
sales = pd.read_csv("sales_data.csv", parse_dates=["order_date"])
sales["festive"] = sales["order_date"].between("2024-10-01", "2024-11-15")
sales["revenue"] = sales["quantity"] * sales["unit_price"] * (1 - sales["discount"])
days = {True: 46, False: 366 - 46}
per_day = sales.groupby("festive")["revenue"].sum() / pd.Series(days)
print(per_day.round())
print("Festive days earn", round(per_day[True] / per_day[False], 1), "x a normal day")

import pandas as pd
emp = pd.read_csv("employees.csv", parse_dates=["join_date"])
today = pd.Timestamp("2025-01-01")
emp["tenure_days"] = (today - emp["join_date"]).dt.days
emp["tenure_years"] = (emp["tenure_days"] / 365.25).round(1)
emp["review_due"] = emp["join_date"] + pd.DateOffset(years=5)
print(emp[["name", "join_date", "tenure_years", "review_due"]].head(4))
print("Average tenure:", emp["tenure_years"].mean().round(1), "years")

import pandas as pd
w = pd.read_csv("delhi_weather.csv", parse_dates=["date"]).set_index("date")
monthly = w["aqi"].resample("ME").mean().round()
print(monthly["2024"].to_string())
print("Worst month:", monthly.idxmax().strftime("%b %Y"), "AQI", int(monthly.max()))

import pandas as pd
import matplotlib.pyplot as plt
w = pd.read_csv("delhi_weather.csv", parse_dates=["date"]).set_index("date")
fig, ax = plt.subplots(figsize=(10, 4.2))
ax.plot(w.index, w["aqi"], color="#cbd5e1", lw=0.8, label="Daily AQI")
ax.plot(w.index, w["aqi"].rolling(30).mean(), color="#dc2626", lw=2.2, label="30-day average")
ax.axhline(200, color="#f59e0b", ls="--", lw=1, label="Poor (200)")
ax.set_title("Delhi air quality 2022–2024: every winter is a crisis")
ax.set_ylabel("AQI"); ax.legend()
plt.show()

import pandas as pd
rv = pd.read_csv("product_reviews.csv")
print(rv.head(3))
rv["clean"] = rv["review"].str.lower().str.replace(r"[^a-z ]", "", regex=True).str.strip()
rv["words"] = rv["clean"].str.split().str.len()
print(rv[["clean", "words"]].head(3))
print("Average words per review:", rv["words"].mean().round(1))

import pandas as pd
rv = pd.read_csv("product_reviews.csv")
text = rv["review"].str.lower()
topics = {"delivery": "deliver|late|courier", "quality": "quality|broke|stopped|faded|cheap",
          "size": "size|fit|small|tight", "refund": "refund|return|replace"}
for name, pat in topics.items():
    hits = text.str.contains(pat)
    print(f"{name:9s} mentioned in {hits.sum():3d} reviews | avg stars {rv.loc[hits, 'stars'].mean():.2f}")

import pandas as pd
s = pd.Series(["Order #4521 delivered in 3 days", "Refund of Rs 1,299 for order #4610", "Call 98765-43210 for help"])
print(s.str.extract(r"#(\d+)"))
print(s.str.findall(r"\d+").tolist())
print(s.str.startswith("Order").tolist())