Lesson 2.2 - Loading Real Data

import pandas as pd
import numpy as np
import io

# Fix the random seed so the "random" data generated below is reproducible
# (running this script twice will always produce identical output)
np.random.seed(42)

# --- Build a synthetic multi-site daily energy & temperature dataset ---

# Define the 5 sites we're simulating, each belonging to a sector
# that has different typical energy usage patterns
sites = pd.DataFrame({
    "site": [
                "Alpha Plant",
                "Bravo Fab",
                "Charlie DC",
                "Delta Mill",
                "Echo Lab"
            ],

    "sector":[
                "Chemical",
                "Semiconductor",
                "Data Center",
                "Manufacturing",
                "R&D"
            ],
})

# Generate every calendar day from Jan 1, 2021 through Dec 31, 2023 (3 full years)
dates = pd.date_range("2021-01-01", "2023-12-31", freq="D")

# Baseline daily energy consumption (kWh) per sector — used as the
# starting point before seasonal/weekday adjustments and noise are applied
base_kwh = {"Chemical": 2600, "Semiconductor": 4200, "Data Center": 5200,
            "Manufacturing": 3100, "R&D": 900}

# Container to collect one row per (site, date) combination
rows = []

# Loop over every site...
for _, s in sites.iterrows():
    site, sector = s["site"], s["sector"]
    base = base_kwh[sector]

    # ...and every day in the 3-year range
    for d in dates:
        doy = d.dayofyear  # day-of-year number (1–365/366)

        # Simulate higher energy use in warmer months (roughly spring–fall)
        # due to cooling/AC load: 18% boost between day 60 and day 305
        cooling = 1.18 if 60 <= doy <= 305 else 1.0

        # Simulate lighter energy use on weekends (fewer staff/operations)
        # weekday() < 5 means Mon–Fri; weekends get an 18% reduction
        weekday = 1.0 if d.weekday() < 5 else 0.82

        # Combine base load, seasonal effect, weekday effect, and
        # ~6% random noise (np.random.normal(0, 0.06)) to mimic real
        # sensor variability, then round to 1 decimal place
        energy = round(float(base * cooling * weekday * (1 + np.random.normal(0, 0.06))), 1)

        # Simulate ambient outdoor temperature using a sine wave that
        # peaks around day 105 (to mimic a seasonal temperature cycle),
        # plus small random noise for day-to-day fluctuation
        ambient = round(27 + 3 * np.sin((doy - 105) / 365 * 2 * np.pi) + np.random.normal(0, 0.8), 1)

        # Store this (site, date) record
        rows.append((site, sector, d, energy, ambient))

# Convert the list of row-tuples into a proper DataFrame
energy_all = pd.DataFrame(rows, columns=["site", "sector", "date", "energy_kwh", "ambient_c"])

# --- Inject realistic messiness so this dataset needs cleaning, like real data would ---

# Randomly blank out some energy_kwh values (without repeats) to simulate
# missing / dropped sensor readings
missing_idx = np.random.choice(energy_all.index, size=60, replace=False)
energy_all.loc[missing_idx, "energy_kwh"] = np.nan

# Randomly sample 5 existing rows and append them again as duplicates —
# simulates duplicate log entries that sometimes occur in real data pipelines
energy_all = pd.concat([energy_all, energy_all.sample(5, random_state=1)], ignore_index=True)

# Sanity check: confirm the final shape and preview the data
print("Shape:", energy_all.shape)
energy_all.head()

# Save the DataFrame to an in-memory CSV. io.StringIO() behaves like a file
# but lives in RAM, so this is the same as exporting to disk without the file.
csv_buffer = io.StringIO()
energy_all.to_csv(csv_buffer, index=False)   # index=False: don't write the row numbers
csv_buffer.seek(0)                            # rewind to the start before reading

# Read it back with read_csv - the exact call you'd use on a real download.
# parse_dates=["date"] loads that column as real datetime values instead of
# plain strings, which unlocks .dt accessors and date arithmetic later.
energy_df = pd.read_csv(csv_buffer, parse_dates=["date"])
print(energy_df.dtypes)   # confirm "date" shows as datetime64[ns]
energy_df.head()

df = pd.read_csv("scada_export.csv")                    # UnicodeDecodeError ...
df = pd.read_csv("scada_export.csv", encoding="latin-1")  # the fix

# Try the practice exercises here