import pandas as pd import re from datetime import datetime, timedelta import calendar # --- Load files --- contacts = pd.read_csv("ResultsGrid_ExportData.csv", low_memory=False, encoding="utf-8-sig") ac = pd.read_csv("All_Contacts.csv", low_memory=False, encoding="utf-8-sig") contacts_raw = contacts.copy() # already loaded as ResultsGrid_ExportData.csv contacts_raw.columns = contacts_raw.columns.str.strip().str.lower() # --- Normalize column names --- contacts.columns = contacts.columns.str.strip().str.lower() ac.columns = ac.columns.str.strip() # --- Keep raw Work/Home phone columns for international check --- contacts["raw_work_phone"] = contacts["work phone"] contacts["raw_home_phone"] = contacts["home phone"] # --- Step 0: Deduplicate contacts by email, keeping latest paid thru --- if "paid thru" in contacts.columns: contacts["paid thru"] = pd.to_datetime(contacts["paid thru"], errors="coerce") contacts = contacts.sort_values("paid thru", ascending=False) contacts = contacts.drop_duplicates(subset=["email"], keep="first") # --- Step 1: Merge on Email --- merged_email = pd.merge( contacts, ac[["Email", "*INDIVIDUAL ID", "*Members Type", "*DO ID", "*Lead Status"]], left_on="email", right_on="Email", how="inner", suffixes=("_portal", "_ac") ) # --- Step 2: Find unmatched by Email --- contacts_unmatched = contacts[~contacts["email"].isin(merged_email["email"])] # --- Step 3: Merge unmatched on ID *INDIVIDUAL ID --- merged_id = pd.merge( contacts_unmatched, ac[["Email", "*INDIVIDUAL ID", "*Members Type", "*DO ID", "*Lead Status"]], left_on="id", right_on="*INDIVIDUAL ID", how="inner", suffixes=("_portal", "_ac") ) # --- Step 4: Combine results --- final_matched = pd.concat([merged_email, merged_id], ignore_index=True) # ============================================================ # FORMATTING LAYER (dates + phones + phone extension) # ============================================================ # --- Format dates --- date_like_cols = [ col for col in final_matched.columns if ("date" in col.lower() or "paid thru" in col.lower()) or col.strip().lower() in ["last act", "last infoline", "last conf"] ] for col in date_like_cols: final_matched[col] = pd.to_datetime(final_matched[col], errors="coerce").dt.strftime("%Y-%m-%d") # --- Normalize phone numbers & extract extension --- def format_phone(val): """ Normalize phone numbers and extract extensions. Rules: - Anything in parentheses at the end () → extension - Keywords: ext, x, poste, post, #, p., option → extension - Canadian numbers (10 digits) → keep as main - Long numbers with no spaces/parentheses → keep as-is """ if pd.isna(val) or not str(val).strip(): return "", "" val = str(val).strip() ext = "" # 1️ Look for parentheses at the end e.g., 5142254304 (1128) paren_match = re.search(r"\((\d+)\)\s*$", val) if paren_match: ext = paren_match.group(1) val = re.sub(r"\(\d+\)\s*$", "", val) # 2️ Look for extension with keywords if not ext: ext_patterns = [ r"(?:ext\.?|x[:.\s]?|poste|post)\s*(\d+)$", r"(?:#|p\.|option)\s*(\d+)$", ] for pat in ext_patterns: match = re.search(pat, val, flags=re.IGNORECASE) if match: ext = match.group(1) val = re.sub(pat, "", val, flags=re.IGNORECASE) break # 3️ Remove all non-digit characters except leading + digits_only = re.sub(r"[^\d+]", "", val) # 4️ Handle Canadian numbers: if 10 digits (or starts with 1 + 10 digits), keep 10 digits if re.fullmatch(r"\+?1?\d{10}", digits_only): digits = re.sub(r"\D", "", digits_only)[-10:] else: # Keep long unformatted numbers as-is digits = digits_only return digits, ext # --- Keep raw Work/Home phone columns (after merge, before formatting) final_matched["raw_work_phone"] = final_matched["work phone"] final_matched["raw_home_phone"] = final_matched["home phone"] # --- Format all phone numbers except raw_ columns for col in final_matched.columns: if "phone" in col.lower() and not col.startswith("raw_"): phone_col = col ext_col = f"{col}_ext" final_matched[[phone_col, ext_col]] = final_matched[phone_col].apply( lambda x: pd.Series(format_phone(x)) ) # --- Step 4b: Processed member type --- def transform_type(val): """Apply transformation rules to a raw member type value.""" val = str(val).strip().upper() if val in ["AST", "CAND", "PRO", "CORP"]: return val elif val == "DEL": return "CORP" elif val == "DELAS": return "AST" elif val == "DELCD": return "CAND" elif val == "DELPR": return "PRO" elif val in ["HON", "NDEL", "NM", "RET"]: return None # special case → handled outside else: return val if val else None def safe_str(val): """Convert NaN/None to empty string, otherwise uppercase stripped string.""" if pd.isna(val): return "" return str(val).strip().upper() def process_member_type(row): mbr = transform_type(safe_str(row.get("member type", ""))) cancel = transform_type(safe_str(row.get("mbr type cancellation", ""))) ac_val = safe_str(row.get("*Members Type", "")) raw_mbr = safe_str(row.get("member type", "")) raw_cancel = safe_str(row.get("mbr type cancellation", "")) # --- NEW RULE: If member type = NM and cancellation = FTS → CORP if raw_mbr == "NM" and raw_cancel == "FTS": return "CORP" # Special NM rule if raw_mbr == "NM": if raw_cancel in ["HON", "NDEL", "NM", "RET", ""]: return ac_val if ac_val else mbr else: return cancel if cancel else mbr # If member type is HON, NDEL, NM, RET → use cancellation value if raw_mbr in ["HON", "NDEL", "NM", "RET"]: return cancel if cancel else raw_mbr return mbr if mbr else cancel final_matched["Processed member type"] = final_matched.apply(process_member_type, axis=1) # === NEW: helpers to compute lapsed date from paid_thru === def _to_date_or_none(ts): """Return a Python date or None for a pandas Timestamp/NaT.""" if pd.isna(ts): return None if isinstance(ts, pd.Timestamp): return ts.date() if isinstance(ts, datetime): return ts.date() try: return pd.to_datetime(ts, errors="coerce").date() except Exception: return None def add_one_month_rollover(dt): """ Add 1 month to a date with overflow behavior: - If next-month doesn't have the same day, overflow into subsequent days. e.g. 2025-08-31 + 1 month -> attempts 2025-09-31 -> becomes 2025-10-01 Returns a Python date or None. """ if dt is None: return None year = dt.year month = dt.month day = dt.day # target next month target_month = month + 1 target_year = year if target_month == 13: target_month = 1 target_year += 1 days_in_target = calendar.monthrange(target_year, target_month)[1] if day <= days_in_target: # straightforward case return datetime(target_year, target_month, day).date() else: # overflow: compute leftover days and add them to the last day of target month overflow = day - days_in_target base = datetime(target_year, target_month, days_in_target).date() return base + timedelta(days=overflow) def first_day_of_next_month(dt): """Return first day of the month after dt (Python date).""" if dt is None: return None year = dt.year month = dt.month + 1 if month == 13: month = 1 year += 1 return datetime(year, month, 1).date() # --- Step 6: Add "Company paid thru" from original ResultsGrid_ExportData.csv --- # Ensure 'id' and 'co id' are strings contacts_raw["id"] = contacts_raw["id"].fillna("").astype(str).str.strip() final_matched["co id"] = final_matched["co id"].fillna("").astype(str).str.strip() # Create mapping: CORP Id -> Paid Thru corp_paid_thru_map = contacts_raw.loc[ contacts_raw["member type"].str.upper() == "CORP", ["id", "paid thru"] ].dropna().set_index("id")["paid thru"].to_dict() # Map Paid Thru to delegates (DEL*), using Co Id def get_paid_thru_for_corp_from_raw(row): member_type = str(row.get("member type", "")).upper() if member_type.startswith("DEL"): co_id = row.get("co id", "") paid_thru_raw = corp_paid_thru_map.get(co_id, "") if pd.notna(paid_thru_raw) and paid_thru_raw != "": # Convert to datetime and then format as yyyy-mm-dd try: dt = pd.to_datetime(paid_thru_raw, errors="coerce") if pd.notna(dt): return dt.strftime("%Y-%m-%d") except: return "" return "" return "" # empty for others # Apply to final_matched final_matched["Company paid thru"] = final_matched.apply(get_paid_thru_for_corp_from_raw, axis=1) # --- Step 4c: New Lead Status (updated to use lapsed_date computed as you requested) --- today = pd.Timestamp.today().date() cutoff = today + timedelta(days=75) cutoff_date = cutoff # for comparing with lapsed_date # --- Helper: check if phone number is international --- def is_international_phone(row): """ Detect if any phone number is international: - If phone starts with '+1' or '+(1' → domestic - If phone starts with '+' but not '+1' or '+(1' → international - Otherwise → domestic """ phone_vals = [row.get("raw_work_phone", ""), row.get("raw_home_phone", "")] for val in phone_vals: val = str(val).strip() if not val or val.lower() in ["nan", "none"]: continue # Remove spaces for checking val_no_space = val.replace(" ", "") if val_no_space.startswith("+"): if val_no_space.startswith("+1") or val_no_space.startswith("+(1"): continue # domestic else: return True # international return False def determine_new_lead_status(row): lead_status_raw = row.get("*Lead Status", "") lead_status = "" if pd.isna(lead_status_raw) else str(lead_status_raw).strip() processed_type_raw = row.get("Processed member type", "") processed_type = "" if pd.isna(processed_type_raw) else str(processed_type_raw).strip() members_type_raw = row.get("*Members Type", "") members_type = "" if pd.isna(members_type_raw) else str(members_type_raw).strip().upper() raw_member_type = str(row.get("member type", "")).strip().upper() # Normalize for comparison lead_status_check = lead_status.upper() processed_type_check = processed_type.upper() # --- NEW RULE: If RET, force "Refused" if raw_member_type == "RET": return "Refused" # --- NEW RULE: If international phone, force "Not Relevant" relevant_statuses = {"LAPSED MEMBER", "MEMBER", "RENEWED LAPSED", "RENEWED AST", "RENEWED APX", "NOT RELEVANT"} if is_international_phone(row) and lead_status_check in relevant_statuses : return "Not Relevant" # --- NEW RULE: If raw member type = NM or NDEL --- if raw_member_type in ["NM", "NDEL"] and lead_status_check in relevant_statuses: status_in_portal = str(row.get("status", "")).strip().upper() if raw_member_type == "NDEL": if status_in_portal == "I": return "Lapsed Member" else: return "Not Relevant" else: return "Lapsed Member" # --- compute lapsed_date using the later of "Company paid thru" or "paid thru" paid_thru_corp_raw = row.get("Company paid thru", "") paid_thru_raw_val = row.get("paid thru", "") # convert both to datetime dt_corp = pd.to_datetime(paid_thru_corp_raw, errors="coerce") dt_raw = pd.to_datetime(paid_thru_raw_val, errors="coerce") # take the later date if pd.notna(dt_corp) and pd.notna(dt_raw): paid_thru_final = max(dt_corp, dt_raw) elif pd.notna(dt_corp): paid_thru_final = dt_corp else: paid_thru_final = dt_raw paid_thru_date = _to_date_or_none(paid_thru_final) candidate = add_one_month_rollover(paid_thru_date) # paid_thru + 1 month (with overflow) lapsed_date = first_day_of_next_month(candidate) # rounded to next month first day # --- Rule 1: If lead status = Lapsed AND lapsed_date is in the future (or beyond cutoff) -> treat as renewed --- # (keeps original intent: treat someone labeled "LAPSED MEMBER" but whose lapsed date hasn't arrived as renewed) if lead_status_check in ["LAPSED MEMBER"] and pd.notna(paid_thru_date) and paid_thru_date > cutoff: if processed_type_check == "AST": return "Renewed AST" elif processed_type_check not in ["HON", "NDEL", "NM", "RET", "AST"]: return "Renewed Lapsed" # --- Rule 2: If not AST, lapsed_date older than (today - 60 days), and status is Member or Renewed Lapsed -> mark Lapsed Member --- if members_type != "AST" and lapsed_date is not None and lapsed_date < today: if lead_status_check in ["MEMBER", "RENEWED LAPSED", "NOT RELEVANT"]: return "Lapsed Member" elif lead_status_check in ["REFUSED", "PARTNER"]: return lead_status # keep same return "Lapsed Member" if members_type != "AST" and paid_thru_date is not None and paid_thru_date > today: if lead_status_check in ["LAPSED MEMBER"]: return "Not Relevant" # --- Default: keep the current lead status (empty instead of NaN) --- return lead_status final_matched["new lead status"] = final_matched.apply(determine_new_lead_status, axis=1) # --- Step 4d: New DO ID assignment (fixed: case-insensitive checks + width preservation) --- def extract_prefix_number(do_id): """ Return (prefix_upper, int_number, num_len) for strings like "AST00027" or "L25A00225". If no match, return (None, None, 0). """ if pd.isna(do_id): return None, None, 0 s = str(do_id).strip() if not s: return None, None, 0 match = re.match(r"^([A-Za-z0-9]+?)(\d+)$", s, flags=re.IGNORECASE) if not match: return None, None, 0 prefix = match.group(1).upper() num_str = match.group(2) try: num = int(num_str) except ValueError: return prefix, None, len(num_str) return prefix, num, len(num_str) # prefixes we care about prefixes = ["AST", "L25A", "L25C", "L25P", "L25O"] # initialise maps prefix_max = {p: 0 for p in prefixes} prefix_width = {p: 5 for p in prefixes} # default width if none found # scan All_Contacts '*DO ID' column for current maxima and widths if "*DO ID" in ac.columns: for do_id in ac["*DO ID"].dropna().astype(str): prefix, num, num_len = extract_prefix_number(do_id) if prefix in prefix_max and num is not None: prefix_max[prefix] = max(prefix_max[prefix], num) prefix_width[prefix] = max(prefix_width[prefix], num_len) # counters start from the current max counters = prefix_max.copy() def get_next_id(prefix): # ensure prefix known if prefix not in counters: counters[prefix] = 0 prefix_width[prefix] = prefix_width.get(prefix, 5) counters[prefix] += 1 width = prefix_width.get(prefix, 5) return f"{prefix}{counters[prefix]:0{width}d}" # normalized helper def norm(val): if pd.isna(val): return "" return str(val).upper().strip() def assign_new_do_id(row): processed_type_norm = norm(row.get("Processed member type", "")) new_lead_status_norm = norm(row.get("new lead status", "")) current_do_id_raw = "" if pd.isna(row.get("*DO ID", "")) else str(row.get("*DO ID", "")).strip() current_do_id_norm = current_do_id_raw.upper().strip() memberish_statuses = {"MEMBER", "NOT RELEVANT", "RENEWED AST", "RENEWED LAPSED", "RENEWED APX"} active_ast_statuses = {"MEMBER", "RENEWED APX", "RENEWED AST", "RENEWED LAPSED"} # 1) If CORP/PRO/CAND and member-ish statuses -> leave New DO ID empty if processed_type_norm in {"CORP", "PRO", "CAND"} and new_lead_status_norm in memberish_statuses: return "" # 2) AST & active statuses -> keep existing AST DO ID if already AST*, else assign next AST if processed_type_norm == "AST" and new_lead_status_norm in active_ast_statuses: if current_do_id_norm.startswith("AST"): return current_do_id_raw return get_next_id("AST") # 3) AST & Lapsed -> keep existing L25A if present, else assign next L25A if processed_type_norm == "AST" and new_lead_status_norm == "LAPSED MEMBER": if current_do_id_norm.startswith("L25A"): return current_do_id_raw return get_next_id("L25A") # 4) CAND & Lapsed -> keep existing L25C if present, else assign next L25C if processed_type_norm == "CAND" and new_lead_status_norm == "LAPSED MEMBER": if current_do_id_norm.startswith("L25C"): return current_do_id_raw return get_next_id("L25C") # 5) PRO & Lapsed -> keep existing L25P if present, else assign next L25P if processed_type_norm == "PRO" and new_lead_status_norm == "LAPSED MEMBER": if current_do_id_norm.startswith("L25P"): return current_do_id_raw return get_next_id("L25P") # 6) CORP & Lapsed -> keep existing L25O if present, else assign next L25O if processed_type_norm == "CORP" and new_lead_status_norm == "LAPSED MEMBER": if current_do_id_norm.startswith("L25O"): return current_do_id_raw return get_next_id("L25O") # 7) Default: keep existing DO ID if present, otherwise empty return current_do_id_raw if current_do_id_raw else "" final_matched["New DO ID"] = final_matched.apply(assign_new_do_id, axis=1) # --- Convert 'french' column to English/French labels --- if "french" in final_matched.columns: final_matched["french"] = final_matched["french"].apply( lambda x: "French" if str(x).strip().upper() == "TRUE" else "English" ) # --- Step 5: Save output --- final_matched.to_csv("Contacts_Matched.csv", index=False, encoding="utf-8-sig") print(f"Done! {len(final_matched)} contacts from contacts.csv matched in All_Contacts.csv") print("File saved: Contacts_Matched.csv")