"""Step 1.2: Match ЖК ↔ Objective projects by name; compute district economics. Output tables: jk_objective_match — site_id ↔ objective project name (best fuzzy match) district_economics — per Objective-район aggregates over last 90 days site_district — which Objective-район each site belongs to """ import math import pathlib import re import sqlite3 from difflib import SequenceMatcher DB = pathlib.Path(__file__).parent / "analysis.db" SCHEMA = """ CREATE TABLE IF NOT EXISTS jk_objective_match ( site_id TEXT PRIMARY KEY REFERENCES sites(site_id), project TEXT, score REAL, method TEXT ); CREATE TABLE IF NOT EXISTS district_economics ( district TEXT PRIMARY KEY, n_projects INTEGER, n_corpuses INTEGER, median_price_m2 REAL, -- тыс.Р/м² weighted_price_m2 REAL, -- weighted by sold m² avg_price_m2_offer REAL, -- prices in stock right now deals_per_month_avg REAL, -- avg per корпус sold_volume_m2_90d REAL, -- last 90 days sold m² stock_m2 REAL, -- current stock stock_lots INTEGER, avg_area_sold_m2 REAL, -- average flat size sold months_to_sellout REAL -- stock_m2 / monthly_velocity ); CREATE TABLE IF NOT EXISTS site_district ( site_id TEXT PRIMARY KEY REFERENCES sites(site_id), district TEXT, method TEXT, -- 'name_match' | 'nearest_jk' nearest_jk_obj_id INTEGER, nearest_jk_dist_m REAL ); """ def normalize(s): if not s: return "" s = s.lower().strip() s = re.sub(r'^(жк|жилой\s+комплекс|жилой\s+квартал|жилые\s+кварталы)\s+', '', s) s = re.sub(r'["«»\'`]+', '', s) s = re.sub(r'\s+', ' ', s) return s.strip() def fuzzy(a, b): return SequenceMatcher(None, normalize(a), normalize(b)).ratio() def hav(la1, lo1, la2, lo2): R = 6371000 p1, p2 = math.radians(la1), math.radians(la2) dp = math.radians(la2 - la1) dl = math.radians(lo2 - lo1) a = math.sin(dp / 2) ** 2 + math.cos(p1) * math.cos(p2) * math.sin(dl / 2) ** 2 return 2 * R * math.asin(math.sqrt(a)) def main(): conn = sqlite3.connect(DB) conn.executescript(SCHEMA) # 1. ЖК ↔ Objective project name match sites = conn.execute("SELECT site_id, name FROM sites WHERE kind='jk' AND name IS NOT NULL").fetchall() objective_projects = [r[0] for r in conn.execute("SELECT DISTINCT project FROM objective_corp_month").fetchall() if r[0]] print(f"Sites: {len(sites)}, Objective projects: {len(objective_projects)}") conn.execute("DELETE FROM jk_objective_match") matched = 0 for site_id, name in sites: best = (0.0, None) for proj in objective_projects: s = fuzzy(name, proj) if s > best[0]: best = (s, proj) if best[0] >= 0.80: conn.execute("INSERT INTO jk_objective_match VALUES (?,?,?,?)", (site_id, best[1], best[0], "fuzzy")) matched += 1 print(f"Name-matched: {matched}/{len(sites)}") # 2. district_economics — last 90 days # Last 3 calendar months from data last3 = [r[0] for r in conn.execute( "SELECT DISTINCT month FROM objective_corp_month ORDER BY month DESC LIMIT 3").fetchall()] print(f"Months window: {last3}") conn.execute("DELETE FROM district_economics") placeholders = ",".join(["?"]*len(last3)) # Latest stock per (project, corpus) — taken from the single most-recent month in the window # (#1512): using a per-corpus latest caused mixed periods (stale stock for inactive corpuses). # Fix: pin stock to last3[0] (the most recent month) so numerator and denominator of MTS # are aligned to the same period for all corpuses in the district. latest_month = last3[0] latest_stock = {} for r in conn.execute(""" SELECT project, corpus, district, stock_m2, stock_lots FROM objective_corp_month WHERE month = ? """, (latest_month,)).fetchall(): latest_stock[(r[0], r[1])] = (r[2], r[3] or 0, r[4] or 0) # Per-district aggregates from window rows = conn.execute(f""" SELECT district, project, corpus, deals_total, deals_priced, sold_volume_m2, avg_price_m2, avg_area_m2, stock_avg_price_m2 FROM objective_corp_month WHERE month IN ({placeholders}) """, last3).fetchall() by_dist = {} for r in rows: d = r[0] if not d: continue by_dist.setdefault(d, []).append(r) for d, rs in by_dist.items(): projects = {r[1] for r in rs} corpuses = {(r[1], r[2]) for r in rs} # weighted price (by sold m²) wn = sum((r[6] or 0) * (r[5] or 0) for r in rs) wd = sum((r[5] or 0) for r in rs) wp = wn / wd if wd else None # median price — volume-weighted by sold_volume_m2 (#1511) # Each corpus×month row contributes avg_price_m2 weighted by sold_volume_m2 so that # high-volume months/rooms count proportionally (not equal-weight per row). # Falls back to weight=1 for rows with priced deals but missing volume figure. pw = [(r[6], max(r[5] or 0, 1.0)) for r in rs if r[6] and (r[4] or 0) > 0] if pw: pw.sort(key=lambda x: x[0]) total_w = sum(w for _, w in pw) half = total_w / 2.0 cum = 0.0 med = None for price, w in pw: cum += w if cum >= half: med = price break else: med = None # offer prices offers = [r[8] for r in rs if r[8] and r[8] > 0] avg_off = sum(offers)/len(offers) if offers else None # velocity (avg deals_total per row, where row = corpus×month) vels = [r[3] or 0 for r in rs] avg_v = sum(vels)/len(vels) if vels else None # sold volume 90d sv = sum(r[5] or 0 for r in rs) # stock from latest_stock for this district stock_m2 = sum(s[1] for k, s in latest_stock.items() if s[0] == d) stock_lots = sum(s[2] for k, s in latest_stock.items() if s[0] == d) # avg area areas = [r[7] for r in rs if r[7] and r[7] > 0] avg_a = sum(areas)/len(areas) if areas else None # months to sellout mts = (stock_m2 / (sv / 3.0)) if sv > 0 and stock_m2 else None conn.execute("""INSERT INTO district_economics VALUES (?,?,?,?,?,?,?,?,?,?,?,?)""", (d, len(projects), len(corpuses), med, wp, avg_off, avg_v, sv, stock_m2 or None, stock_lots or None, avg_a, mts)) conn.commit() print("\nDistrict economics (last 90d):") for r in conn.execute("""SELECT district, n_projects, ROUND(weighted_price_m2,1) wp, ROUND(deals_per_month_avg,1) v, ROUND(months_to_sellout,1) mts FROM district_economics ORDER BY wp DESC""").fetchall(): print(f" {r[0]:<25} projects={r[1]:>3} price={r[2] or '—':>7}тыс vel={r[3]:>5} to_sellout={r[4] or '—'} мес") # 3. site_district: name-matched gets Objective district directly; others — nearest matched ЖК conn.execute("DELETE FROM site_district") # 3a. matched sites — pull district from Objective directly rows = conn.execute(""" SELECT s.site_id, s.lat, s.lon, m.project, (SELECT district FROM objective_corp_month WHERE project=m.project ORDER BY month DESC LIMIT 1) AS district FROM sites s LEFT JOIN jk_objective_match m USING (site_id) """).fetchall() matched_with_dist = [(sid, lat, lon, proj, dist) for sid, lat, lon, proj, dist in rows if dist is not None] matched_idx = {sid: (lat, lon, dist) for sid, lat, lon, _, dist in matched_with_dist} from collections import Counter for sid, lat, lon, proj, dist in rows: if dist: conn.execute("INSERT INTO site_district VALUES (?,?,?,?,?)", (sid, dist, "name_match", None, 0)) else: # vote among k=5 nearest matched ЖК within 2 km cands = [] for sid2, (lat2, lon2, dist2) in matched_idx.items(): if sid2 == sid: continue d = hav(lat, lon, lat2, lon2) cands.append((d, sid2, dist2)) cands.sort() top = [c for c in cands[:7] if c[0] <= 2500] if top: votes = Counter(c[2] for c in top) # pick the most-voted district; tie-break: closest representative top_dist, _ = votes.most_common(1)[0] rep = next(c for c in top if c[2] == top_dist) obj_id = None if rep[1] and rep[1].startswith("jk:"): try: obj_id = int(rep[1][3:]) except ValueError: pass conn.execute("INSERT INTO site_district VALUES (?,?,?,?,?)", (sid, top_dist, "knn_vote_5", obj_id, rep[0])) conn.commit() parcel_dist = conn.execute( "SELECT district, method, nearest_jk_dist_m FROM site_district WHERE site_id='parcel:66:41:0204016:10'" ).fetchone() print(f"\nParcel district: {parcel_dist}") print("\nSites assigned per district:") for r in conn.execute("SELECT district, count(*) FROM site_district GROUP BY 1 ORDER BY 2 DESC LIMIT 15").fetchall(): print(f" {r[0]:<25} {r[1]}") conn.close() if __name__ == "__main__": main()