labs/Lab-2/Lab-2-Practical/05_Pandas_Log_Cleaning_Lab.ipynb

Module 2 — Lab 5: Pandas — Loading, Inspecting, and Cleaning Logs

Topics: CSV, DataFrame, missing values, duplicates, datetime, filtering

Instructions: Run the cells in order. Complete every TODO, then run the Self-check cell. Do not open the Solution notebook before attempting the tasks yourself.

Scenario

The data/auth_log.csv file contains duplicate rows, invalid timestamps, missing critical fields, and inconsistent capitalization. The goal is to produce a clean DataFrame ready for analysis.

In [ ]:

from pathlib import Path
import pandas as pd

DATA_PATH = Path("data/auth_log.csv")
OUTPUT_DIR = Path("output")
OUTPUT_DIR.mkdir(exist_ok=True)

log_df = pd.read_csv(DATA_PATH)
log_df.head()

1. Inspect data quality

In [ ]:

# TODO 1: print the shape, duplicate count, missing-value counts, and dtypes
print("Shape:", None)
print("Duplicate rows:", None)
print("Missing values by column:")
print(None)
print("\nData types:")
print(None)

2. Remove duplicates and normalize timestamps and critical fields

In [ ]:

clean_df = log_df.copy()

# TODO 2: remove duplicate rows

# TODO 3: parse timestamp with pd.to_datetime using errors="coerce" and utc=True

# TODO 4: drop rows missing timestamp, source_ip, or status

3. Normalize text and create time-based fields

In [ ]:

# TODO 5: strip surrounding whitespace from source_ip
# TODO 6: fill missing usernames with "unknown", strip whitespace, and convert to lowercase
# TODO 7: strip whitespace and convert status to uppercase

# TODO 8: retain only SUCCESS and FAILED status values
# TODO 9: create the hour column from timestamp
# TODO 10: sort by timestamp and reset the index

clean_df.head()

4. Filter and aggregate

In [ ]:

# TODO 11: filter FAILED rows
failed_df = None

# TODO 12: count FAILED events by source_ip
failed_by_ip = None

# TODO 13: count status values
status_counts = None

print("Status counts:")
print(status_counts)
print("\nFailed attempts by IP:")
print(failed_by_ip)

5. Export clean data

In [ ]:

clean_df.to_csv(OUTPUT_DIR / "clean_auth_log.csv", index=False)
clean_df.to_json(
    OUTPUT_DIR / "clean_auth_log.json",
    orient="records",
    indent=2,
    date_format="iso",
)
failed_by_ip.to_csv(
    OUTPUT_DIR / "failed_login_by_ip.csv",
    header=True,
)

print("Exported files to:", OUTPUT_DIR.resolve())

Self-check

In [ ]:

assert log_df.duplicated().sum() >= 1
assert clean_df.duplicated().sum() == 0
assert clean_df["timestamp"].isna().sum() == 0
assert set(clean_df["status"]) <= {"SUCCESS", "FAILED"}
assert clean_df["username"].str.strip().eq(clean_df["username"]).all()
assert failed_by_ip.index[0] == "198.51.100.14"
assert (OUTPUT_DIR / "clean_auth_log.csv").exists()
assert (OUTPUT_DIR / "clean_auth_log.json").exists()
print("✓ Lab 5 completed")