Spaces:
Sleeping
Sleeping
File size: 5,524 Bytes
654558b 9ad00b2 e2855b7 | 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 162 163 164 165 | 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
|