alfa

import glob
import os
import re
import polars as pl

print("polars", pl.__version__)

FOLDER = r"C:\path\to\folder"        # <-- change
OUTPUT = r"C:\path\to\result.xlsx"   # <-- change

# name, words that must be in the header, words that must NOT be
SPECS = [
   ("kod_anketa",    ["код анкет"],      []),
   ("minibank",      ["мини", "банк"],   []),
   ("mijoz",         ["мижоз"],          []),
   ("department",    ["департамент"],    []),
   ("brutto",        ["остаток"],        []),
   ("vidan",         ["выдан"],          []),
   ("pogashen",      ["погашен"],        []),
   ("npl",           ["npl"],            []),
   ("bez_straxovka", ["страхов", "без"], []),
   ("straxovka",     ["страхов"],        ["без"]),
   ("category",      ["класс"],          []),
]

STATIC = ["minibank", "mijoz", "department"]
DYNAMIC = ["brutto", "vidan", "pogashen", "npl", "straxovka", "bez_straxovka", "category"]
NUMERIC = ["brutto", "vidan", "pogashen", "npl", "straxovka", "bez_straxovka"]
ALL_TARGETS = [s[0] for s in SPECS]

frames = []

for path in glob.glob(os.path.join(FOLDER, "*.xlsb")):
   name = os.path.basename(path)
   if name.startswith("~$"): continue date_str = os.path.splitext(name)[0] # "01.01.2026" # no header -> the duplicate "Мини-банк" cannot break the reader raw = pl.read_excel(path, engine="calamine", has_header=False) header = [re.sub(r"\s+", " ", str(x).lower()).strip() for x in raw.row(0)] df = raw.slice(1) df.columns = ["c%d" % i for i in range(df.width)] df = df.select([pl.col(c).cast(pl.String) for c in df.columns]) # everything as text first picks = {} used = [] for target, need, avoid in SPECS: for i, h in enumerate(header): if i in used: continue if all(w in h for w in need) and not any(w in h for w in avoid): picks[target] = "c%d" % i used.append(i) break missing = [t for t in ALL_TARGETS if t not in picks] if missing: print(" !", name, "missing:", missing) df = df.select([pl.col(v).alias(k) for k, v in picks.items()]) for t in missing: df = df.with_columns(pl.lit(None, dtype=pl.String).alias(t)) df = df.select(ALL_TARGETS).with_columns(pl.lit(date_str).alias("report_date")) frames.append(df) print("ok:", name, df.shape)long = pl.concat(frames, how="diagonal")# empty -> null, key cleanup, numbers to float long = long.with_columns([ pl.when(pl.col(c).str.strip_chars() == "").then(None).otherwise(pl.col(c)).alias(c) for c in ALL_TARGETS ]) long = long.with_columns( pl.col("kod_anketa").str.strip_chars().str.replace(r"\.0$", "").alias("kod_anketa")
)
long = long.with_columns([pl.col(c).cast(pl.Float64, strict=False) for c in NUMERIC])
long = long.filter(pl.col("kod_anketa").is_not_null())

# real date for sorting
long = long.with_columns(
   pl.col("report_date").str.strptime(pl.Date, "%d.%m.%Y").alias("d")
)
dates = long.select(["report_date", "d"]).unique().sort("d")["report_date"].to_list()
print("months:", dates)

# newest first -> latest non-empty static value per anketa
long = long.sort("d", descending=True)
result = long.group_by("kod_anketa").agg(
   [pl.col(c).drop_nulls().first().alias(c) for c in STATIC]
).sort("kod_anketa")

# one column per metric per month
for m in DYNAMIC:
   try:
       p = long.pivot(on="report_date", index="kod_anketa",
                      values=m, aggregate_function="first")
   except TypeError:
       p = long.pivot(columns="report_date", index="kod_anketa",
                      values=m, aggregate_function="first")
   cols = [d for d in dates if d in p.columns]
   p = p.select(["kod_anketa"] + cols)
   p.columns = ["kod_anketa"] + [m + "_" + d for d in cols]
   result = result.join(p, on="kod_anketa", how="left")

print(result.shape)
try:
   result.write_excel(OUTPUT)
except Exception:
   result.to_pandas().to_excel(OUTPUT, index=False)
print("saved:", OUTPUT)