173 lines
6.6 KiB
Python
173 lines
6.6 KiB
Python
"""
|
|
Amazon transaction date parsing — multilingual, multi-format.
|
|
|
|
Formats seen in real reports (Jan-2026 files):
|
|
USA/Canada "Jan 15, 2026 5:45:40 PM PST" / "Jan 1, 2026 12:03:09 a.m. PST"
|
|
UK/IE/AU "1 Jan 2026 00:00:06 UTC" / "31 Dec 2025 10:35:21 pm GMT+9" (day-first)
|
|
France "31 déc. 2025 23:00:16 UTC"
|
|
Germany "31.12.2025 23:00:01 UTC" (dotted numeric)
|
|
Italy/Spain "31 dic 2025 23:00:10 UTC"
|
|
Netherlands "31 dec 2025 23:04:31 UTC"
|
|
Poland "1 sty 2026 00:20:40 UTC"
|
|
Sweden "1 jan. 2026 03:28:09 UTC"
|
|
Turkey "1 Oca 2026 ..."
|
|
|
|
All are parsed to a naive datetime (the trailing tz label kept separately) — month-end
|
|
aging only needs the calendar date in the report's own timezone.
|
|
"""
|
|
from __future__ import annotations
|
|
|
|
import re
|
|
import unicodedata
|
|
from datetime import date, datetime
|
|
|
|
|
|
def _fold(s: str) -> str:
|
|
"""Lowercase + strip accents/dots so 'déc.' -> 'dec', 'Ağu' -> 'agu'."""
|
|
s = unicodedata.normalize("NFD", s.lower())
|
|
s = "".join(ch for ch in s if not unicodedata.combining(ch))
|
|
return s.rstrip(".")
|
|
|
|
|
|
# Merged month table across EN/FR/DE/IT/ES/NL/PL/SV/TR (folded keys, no conflicts).
|
|
_MONTHS: dict[str, int] = {}
|
|
_MONTH_SOURCES = {
|
|
1: ["jan", "january", "janv", "janvier", "januar", "gen", "gennaio", "ene", "enero",
|
|
"januari", "sty", "stycznia", "styczen", "oca", "ocak"],
|
|
2: ["feb", "february", "fevr", "fevrier", "februar", "febbraio", "febrero", "februari",
|
|
"lut", "lutego", "luty", "sub", "subat"],
|
|
3: ["mar", "march", "mars", "marz", "maerz", "marzo", "mrt", "maart", "marca", "marzec",
|
|
"mart"],
|
|
4: ["apr", "april", "avr", "avril", "aprile", "abr", "abril", "kwi", "kwietnia",
|
|
"kwiecien", "nis", "nisan"],
|
|
5: ["may", "mai", "mag", "maggio", "mayo", "mei", "maj", "maja", "mayis"],
|
|
6: ["jun", "june", "juin", "juni", "giu", "giugno", "junio", "cze", "czerwca",
|
|
"czerwiec", "haz", "haziran"],
|
|
7: ["jul", "july", "juil", "juillet", "juli", "lug", "luglio", "julio", "lip", "lipca",
|
|
"lipiec", "tem", "temmuz"],
|
|
8: ["aug", "august", "aout", "ago", "agosto", "augustus", "augusti", "sie", "sierpnia",
|
|
"sierpien", "agu", "agustos"],
|
|
9: ["sep", "sept", "september", "septembre", "set", "settembre", "septiembre",
|
|
"wrz", "wrzesnia", "wrzesien", "eyl", "eylul"],
|
|
10: ["oct", "october", "octobre", "okt", "oktober", "ott", "ottobre", "octubre",
|
|
"paz", "pazdziernika", "pazdziernik", "eki", "ekim"],
|
|
11: ["nov", "november", "novembre", "noviembre", "lis", "listopada", "listopad",
|
|
"kas", "kasim"],
|
|
12: ["dec", "december", "decembre", "dez", "dezember", "dic", "dicembre", "diciembre",
|
|
"gru", "grudnia", "grudzien", "ara", "aralik"],
|
|
}
|
|
for _m, _names in _MONTH_SOURCES.items():
|
|
for _n in _names:
|
|
_MONTHS[_n] = _m
|
|
|
|
# Date-prefix patterns (matched at the start of the string).
|
|
_P_US = re.compile(r"^\s*([A-Za-z]{3,10})\.?\s+(\d{1,2}),\s*(\d{4})") # Jan 15, 2026
|
|
_P_DAYFIRST = re.compile(r"^\s*(\d{1,2})\.?\s+([^\W\d_]+)\.?\s+(\d{4})", re.U) # 31 déc. 2025
|
|
_P_DOTTED = re.compile(r"^\s*(\d{1,2})\.(\d{1,2})\.(\d{4})") # 31.12.2025
|
|
_P_ISO = re.compile(r"^\s*(\d{4})-(\d{2})-(\d{2})") # 2026-01-31
|
|
|
|
# Time + am/pm (handles "a.m." / "p.m." / "pm") + trailing tz label.
|
|
_P_TIME = re.compile(
|
|
r"(\d{1,2}):(\d{2})(?::(\d{2}))?\s*([AaPp])?\.?\s*[Mm]?\.?\s*([A-Za-z]{2,5}(?:\+\d+)?)?\s*$"
|
|
)
|
|
|
|
|
|
def _month_of(token: str) -> int | None:
|
|
return _MONTHS.get(_fold(token))
|
|
|
|
|
|
def _date_prefix(s: str) -> tuple[date | None, int]:
|
|
"""Parse the leading date; return (date, chars consumed)."""
|
|
m = _P_ISO.match(s)
|
|
if m:
|
|
try:
|
|
return date(int(m.group(1)), int(m.group(2)), int(m.group(3))), m.end()
|
|
except ValueError:
|
|
return None, 0
|
|
m = _P_US.match(s)
|
|
if m:
|
|
mon = _month_of(m.group(1))
|
|
if mon:
|
|
try:
|
|
return date(int(m.group(3)), mon, int(m.group(2))), m.end()
|
|
except ValueError:
|
|
return None, 0
|
|
m = _P_DAYFIRST.match(s)
|
|
if m:
|
|
mon = _month_of(m.group(2))
|
|
if mon:
|
|
try:
|
|
return date(int(m.group(3)), mon, int(m.group(1))), m.end()
|
|
except ValueError:
|
|
return None, 0
|
|
m = _P_DOTTED.match(s)
|
|
if m:
|
|
try:
|
|
return date(int(m.group(3)), int(m.group(2)), int(m.group(1))), m.end()
|
|
except ValueError:
|
|
return None, 0
|
|
return None, 0
|
|
|
|
|
|
def parse_amazon_datetime(text: str | None) -> tuple[datetime | None, str | None]:
|
|
"""Return (naive datetime, tz_label) or (None, None) if unparseable."""
|
|
if not text:
|
|
return None, None
|
|
s = str(text).strip()
|
|
d, used = _date_prefix(s)
|
|
if d is None:
|
|
return None, None
|
|
rest = s[used:].strip()
|
|
hour = minute = sec = 0
|
|
tz = None
|
|
if rest:
|
|
tm = _P_TIME.search(rest)
|
|
if tm:
|
|
hour = int(tm.group(1))
|
|
minute = int(tm.group(2))
|
|
sec = int(tm.group(3) or 0)
|
|
ap = (tm.group(4) or "").lower()
|
|
if ap == "p" and hour != 12:
|
|
hour += 12
|
|
elif ap == "a" and hour == 12:
|
|
hour = 0
|
|
tz = tm.group(5)
|
|
try:
|
|
return datetime(d.year, d.month, d.day, hour, minute, sec), (tz.upper() if tz else None)
|
|
except ValueError:
|
|
return datetime(d.year, d.month, d.day), (tz.upper() if tz else None)
|
|
|
|
|
|
def parse_amazon_date(text: str | None) -> date | None:
|
|
dt, _ = parse_amazon_datetime(text)
|
|
return dt.date() if dt else None
|
|
|
|
|
|
# Fast path: only the calendar date matters, and there are ~31 distinct date prefixes per
|
|
# month across millions of rows. Cache on the prefix so parsing runs dozens of times, not
|
|
# millions.
|
|
_MISSING = object()
|
|
_date_cache: dict[str, "date | None"] = {}
|
|
_PREFIX_KEY = re.compile(
|
|
r"^\s*(?:[A-Za-z]{3,10}\.?\s+\d{1,2},\s*\d{4}" # US: Jan 15, 2026
|
|
r"|\d{1,2}\.?\s+[^\W\d_]+\.?\s+\d{4}" # dayfirst: 31 déc. 2025
|
|
r"|\d{1,2}\.\d{1,2}\.\d{4}" # dotted: 31.12.2025
|
|
r"|\d{4}-\d{2}-\d{2})", # ISO
|
|
re.U,
|
|
)
|
|
|
|
|
|
def parse_amazon_date_fast(text: str | None) -> date | None:
|
|
if not text:
|
|
return None
|
|
m = _PREFIX_KEY.match(text)
|
|
if not m:
|
|
return parse_amazon_date(text)
|
|
key = m.group(0)
|
|
cached = _date_cache.get(key, _MISSING)
|
|
if cached is not _MISSING:
|
|
return cached # type: ignore[return-value]
|
|
d, _ = _date_prefix(key)
|
|
_date_cache[key] = d
|
|
return d
|