Agile_Lab / transformers.py
jonels1988's picture
Update transformers.py
654558b verified
Raw History Blame Contribute Delete
5.52 kB
import logging
from typing import Optional, List
import pandas as pd
from invoice_schema import invoice_pipeline_schema
from schemas import DatasetSchema
from validator import ColumnValidator
logger = logging.getLogger(__name__)
CANONICAL_COLUMNS: List[str] = [
"id",
"document_type",
"document_number",
"reference_number",
"po_number",
"invoice_date",
"due_date",
"total_billed",
"amount_outstanding",
"discount",
"credit_advances_applied",
"payment_amount",
"ar_status",
"open_balance",
]
def _parse_date(series: pd.Series, col_name: str, dataset_name: str) -> pd.Series:
parsed = pd.to_datetime(series, errors="coerce")
invalid_count = parsed.isna().sum()
if invalid_count > 0:
logger.warning(
"Found %d invalid dates in column '%s' for dataset '%s'.",
invalid_count, col_name, dataset_name
)
return parsed
def _parse_currency(series: pd.Series, col_name: str, dataset_name: str) -> pd.Series:
cleaned = (
series.astype("string")
.str.replace(r"[\$,]", "", regex=True)
.str.strip()
)
parsed = pd.to_numeric(cleaned, errors="coerce")
invalid_count = parsed.isna().sum()
if invalid_count > 0:
logger.warning(
"Found %d invalid currency values in column '%s' for dataset '%s'.",
invalid_count, col_name, dataset_name
)
return parsed
def normalize_open_invoices(
df: pd.DataFrame,
*,
schema: Optional[DatasetSchema] = None,
dataset_name: str = "Open Invoices",
validate_positive_amounts: bool = True,
) -> pd.DataFrame:
logger.info("Normalizing dataset '%s' (open invoices).", dataset_name)
before = len(df)
df = df.dropna(how="all").copy()
dropped = before - len(df)
if dropped > 0:
logger.debug("Dropped %d fully empty rows from '%s'.", dropped, dataset_name)
df["id"] = range(1, len(df) + 1)
invoice_date = _parse_date(df["Date"], "Date", dataset_name)
due_date = _parse_date(df["Due date"], "Due date", dataset_name)
total_billed = pd.to_numeric(df["Amount"], errors="coerce")
open_balance = pd.to_numeric(df["Open balance"], errors="coerce")
if validate_positive_amounts:
if (total_billed < 0).any():
logger.warning("Negative 'Amount' values found in '%s'.", dataset_name)
if (open_balance < 0).any():
logger.warning("Negative 'Open balance' values found in '%s'.", dataset_name)
out = pd.DataFrame({
"id": df["id"],
"document_type": df["Transaction type"].astype("string"),
"document_number": df["Num"].astype("string"),
"reference_number": pd.NA,
"po_number": df["P.O. Number"].astype("string"),
"invoice_date": invoice_date,
"due_date": due_date,
"total_billed": total_billed,
"amount_outstanding": open_balance,
"discount": pd.NA,
"credit_advances_applied": pd.NA,
"payment_amount": pd.NA,
"ar_status": df["A/R paid"].astype("string"),
"open_balance": open_balance,
})
out = out[CANONICAL_COLUMNS]
schema_to_use = schema or invoice_pipeline_schema
validator = ColumnValidator(schema_to_use)
validator.validate(out, dataset_name=f"{dataset_name} (normalized)")
return out
def normalize_payment_notification(
df: pd.DataFrame,
*,
schema: Optional[DatasetSchema] = None,
dataset_name: str = "Payment Notification",
validate_positive_amounts: bool = True,
) -> pd.DataFrame:
logger.info("Normalizing dataset '%s' (payment notification).", dataset_name)
df = df.dropna(how="all").copy()
df["id"] = range(1, len(df) + 1)
invoice_date = _parse_date(df["Date"], "Date", dataset_name)
total_billed = _parse_currency(df["Total billed"], "Total billed", dataset_name)
amount_outstanding = _parse_currency(df["Amount outstanding"], "Amount outstanding", dataset_name)
discount = _parse_currency(df["Discount"], "Discount", dataset_name)
credit_adv = _parse_currency(df["Credit/advances applied"], "Credit/advances applied", dataset_name)
payment_amount = _parse_currency(df["Payment amount"], "Payment amount", dataset_name)
if validate_positive_amounts:
for col_name, series in [
("Total billed", total_billed),
("Amount outstanding", amount_outstanding),
("Discount", discount),
("Credit/advances applied", credit_adv),
("Payment amount", payment_amount),
]:
if (series < 0).any():
logger.warning("Negative values found in '%s' for dataset '%s'.", col_name, dataset_name)
out = pd.DataFrame({
"id": df["id"],
"document_type": "Invoice",
"document_number": df["Bill number"].astype("string"),
"reference_number": df["Reference"].astype("string"),
"po_number": pd.NA,
"invoice_date": invoice_date,
"due_date": pd.NA,
"total_billed": total_billed,
"amount_outstanding": amount_outstanding,
"discount": discount,
"credit_advances_applied": credit_adv,
"payment_amount": payment_amount,
"ar_status": pd.NA,
"open_balance": amount_outstanding,
})
out = out[CANONICAL_COLUMNS]
schema_to_use = schema or invoice_pipeline_schema
validator = ColumnValidator(schema_to_use)
validator.validate(out, dataset_name=f"{dataset_name} (normalized)")
return out