File size: 5,872 Bytes
972349b
 
034b2de
972349b
 
 
 
7ad2215
 
 
d4b4379
972349b
 
 
 
 
d4b4379
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
972349b
 
 
d4b4379
 
 
 
972349b
 
 
 
d4b4379
 
 
 
972349b
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
d4b4379
 
 
 
 
972349b
 
 
 
 
 
 
 
7ad2215
d4b4379
 
 
 
 
 
 
 
 
7ad2215
972349b
 
 
 
7ad2215
 
972349b
 
 
 
 
 
7ad2215
 
972349b
7ad2215
034b2de
d4b4379
972349b
 
 
 
 
 
7ad2215
 
972349b
 
 
 
 
 
 
 
 
d4b4379
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
"""Read and validate production records before analysis."""

import hashlib
import io

import numpy as np
import pandas as pd
import requests
import streamlit as st

from config import DATA_URLS, SAMPLE_BASE_WEIGHTS, SAMPLE_MATERIALS, SAMPLE_SHIFTS, get_translation


REQUIRED_COLUMNS = {"date", "weight_kg", "material_type"}


def _read_table(content: bytes) -> pd.DataFrame:
    for encoding in ("utf-8-sig", "cp1252"):
        try:
            text = content.decode(encoding)
            break
        except UnicodeDecodeError:
            continue
    else:
        raise ValueError("The file could not be decoded as UTF-8 or cp1252.")
    try:
        return pd.read_csv(
            io.StringIO(text), sep=None, engine="python", dtype=str,
            keep_default_na=False, skipinitialspace=True,
        )
    except pd.errors.ParserError as error:
        raise ValueError("Could not read the table. Use tab, comma, or semicolon columns.") from error


def _parse_dates(values: pd.Series) -> pd.Series:
    text = values.astype("string").str.strip()
    parsed = pd.Series(pd.NaT, index=values.index, dtype="datetime64[ns]")
    for date_format in ("%d.%m.%Y", "%Y-%m-%d", "%m/%d/%Y", "%d/%m/%Y"):
        parsed = parsed.fillna(pd.to_datetime(text, format=date_format, errors="coerce"))
    return parsed


def _normalize_weight(value) -> str:
    text = str(value).strip().replace(" ", "").replace("\u00a0", "").replace("\u202f", "")
    if "," in text and "." in text:
        if text.rfind(",") > text.rfind("."):
            return text.replace(".", "").replace(",", ".")
        return text.replace(",", "")
    return text.replace(",", ".")


def validate_dataframe(source: pd.DataFrame) -> pd.DataFrame:
    missing = REQUIRED_COLUMNS - set(source.columns)
    if missing:
        raise ValueError(
            f"Missing columns: {', '.join(sorted(missing))}. "
            "Expected tab, comma, or semicolon-separated date, weight_kg, material_type."
        )
    if source.empty:
        raise ValueError("No production records were provided.")

    df = source.copy()
    df["date"] = _parse_dates(df["date"])
    df["weight_kg"] = pd.to_numeric(df["weight_kg"].map(_normalize_weight), errors="coerce")
    material = (df["material_type"].astype("string").str.strip()
                .str.replace(r"\s+", " ", regex=True).str.casefold())
    valid_date = df["date"].notna()
    valid_weight = df["weight_kg"].notna() & np.isfinite(df["weight_kg"]) & df["weight_kg"].ge(0)
    valid_material = material.notna() & material.ne("")
    valid = valid_date & valid_weight & valid_material

    errors = []
    for index in df.index[~valid][:5]:
        bad_fields = []
        if not valid_date.loc[index]:
            bad_fields.append("date")
        if not valid_weight.loc[index]:
            bad_fields.append("weight_kg")
        if not valid_material.loc[index]:
            bad_fields.append("material_type")
        errors.append(f"Data row {index + 1}: {', '.join(bad_fields)}")

    df = df.loc[valid].copy()
    if df.empty:
        raise ValueError("No valid production records remain after validation.")
    df["material_type"] = material.loc[valid].astype(str)
    if "shift" in df.columns:
        df["shift"] = df["shift"].astype("string").str.strip().replace("", pd.NA)
    df["day_name"] = df["date"].dt.day_name()
    df.attrs["excluded_rows"] = len(source) - len(df)
    df.attrs["validation_errors"] = errors
    return df


@st.cache_data(ttl=3600)
def _download_preset_data(year: str) -> pd.DataFrame:
    response = requests.get(DATA_URLS[year], timeout=10)
    response.raise_for_status()
    raw = _read_table(response.content)
    df = validate_dataframe(raw)
    df.attrs.update(
        source_kind="preset",
        source_year=year,
        source_sha256=hashlib.sha256(response.content).hexdigest(),
    )
    return df


def load_preset_data(year: str) -> pd.DataFrame:
    if year not in DATA_URLS:
        raise ValueError(f"Unknown sample year: {year}")
    try:
        return _download_preset_data(year)
    except requests.RequestException:
        return generate_sample_data(year)


def generate_sample_data(year: str) -> pd.DataFrame:
    rng = np.random.default_rng(42 if year == "2024" else 84)
    dates = pd.date_range(f"{year}-01-01", f"{year}-12-31", freq="B")
    records = []
    for date in dates:
        for material in SAMPLE_MATERIALS:
            for shift in SAMPLE_SHIFTS:
                base = SAMPLE_BASE_WEIGHTS[material]
                weight = max(rng.normal(base, base * 0.2), base * 0.3)
                records.append((date, round(weight, 1), material, shift))
    df = pd.DataFrame(records, columns=["date", "weight_kg", "material_type", "shift"])
    df["day_name"] = df["date"].dt.day_name()
    df.attrs.update(source_kind="generated", source_year=year, excluded_rows=0)
    return df


def load_uploaded_data(file) -> pd.DataFrame:
    content = file.getvalue()
    raw = _read_table(content)
    df = validate_dataframe(raw)
    df.attrs.update(
        source_kind="upload",
        source_label=file.name,
        source_sha256=hashlib.sha256(content).hexdigest(),
    )
    return df


def create_validated_tsv(df: pd.DataFrame) -> str:
    """Export the accepted rows in a format the upload control can read again."""
    export = df.drop(columns="day_name", errors="ignore").copy()
    export["date"] = export["date"].dt.strftime("%m/%d/%Y")
    return export.to_csv(index=False, sep="\t")


def data_health_check(df: pd.DataFrame, lang: str = "English") -> dict:
    t = get_translation(lang)
    return {
        t["health_valid_records"]: f"{len(df):,}",
        t["health_recorded_days"]: f"{df['date'].nunique():,}",
        t["health_excluded_rows"]: str(df.attrs.get("excluded_rows", "—")),
        t["health_last_date"]: df["date"].max().strftime("%Y-%m-%d"),
    }