chech if the below methods 
nonLocalBrokerMapCREATE(nonLocalMapFile="nonLocalBrokersToEIN")
localBrokerRevertMapCREATE
Create_Broker_Comm_Fee()
are present in
# -*- coding: utf-8 -*-
"""
Created on Tue May  2 14:15:28 2023

@author: akovvuri
"""
import pandas as pd
import numpy as np
import os


from math import radians, cos, sin, asin, sqrt, atan2


def haversine(lat1, lon1, lat2, lon2):
    R = 6378137  # this is in miles.  For Earth radius in kilometers use 6372.8 km

    dLat = radians(lat2 - lat1)
    dLon = radians(lon2 - lon1)

    lat1 = radians(lat1)
    lat2 = radians(lat2)

    a = sin(dLat / 2) ** 2 + cos(lat1) * cos(lat2) * sin(dLon / 2) ** 2
    a = min(a, 1)
    c = 2 * asin(sqrt(a))
    c = c * R
    dist = atan2(sqrt(a), sqrt(1 - a)) * R * 2
    # print(c,dist)
    return c


haversine_v = np.vectorize(haversine)

print("\nSource brokerLocation.py", dot20)


def nonLocalBrokerMapCREATE(
    stdInFileBrok="brokers.all.yrs.Standardized", 
    nonLocalMapFile="nonLocalBrokersToEIN"
):
    if "nonLocalBrokerMap" in globals():
        del globals()["nonLocalBrokerMap"]
        print("Remove nonLocalBrokerMap and recreate")

    all = pd.read_pickle(os.path.join(dataDir, "brokers", stdInFileBrok + ".pkl"))
    all["BROKER_ZIPCODE"] = np.where(
        (all.BROKER_ZIPCODE != "") & (all.BROKER_ZIPCODE != "0"),
        all.BROKER_ZIPCODE.astype(str).str.split(".").str[0].str.zfill(5),
        all["BROKER_ZIPCODE"],
    )
    
    all["Zipcode"] = np.where(
        (all.Zipcode != "") & (all.Zipcode != "0"),
        all.Zipcode.astype(str).str.split(".").str[0].str.zfill(5),
        all["Zipcode"],
    )

    all = all[(all["BROKER_STATE"] != "") & (all["State_HQ"] != "")]

    all = addBrokerLocationAdjustmentFields(dt=all.copy())

    all["LocCt"] = all.groupby(["State_HQ", "EIN", "BROKER"])["NonLoc"].transform(
        lambda x: sum(1 - x)
    )

    dt = all[all["NonLoc"] == 1]
    obs = len(dt)
    begVars = dt.columns
    print("\nNonLoc obs on initial dataset ", len(dt), "out of", len(all))

    
    rule = "\n \n1.If the broker X group is sometimes local, assume it is actualy local (just entered HQ)"
    dt["NonLocCt"] = dt.groupby(["State_HQ", "EIN", "BROKER"])["Members"].transform(
        "count"
    )
    dt["LOCAL"] = np.where((dt["PlaysInHQ"] == 1) & (dt["LocCt"] > 0), 1, 0)
    print(rule, dt["LOCAL"].sum(), "out of", len(dt))
    dt = dt[dt["LOCAL"] == 0]
    print(" remaining possible non-locals ", len(dt))

    dt[dt.BROKER_ZIPCODE == "04101"].BROKER.value_counts(dropna=False)

    dt["BROKER_ZIPCODE"] = dt["BROKER_ZIPCODE"].astype(str)
    dt["BROKER_ZIPCODE"] = np.where(
        (dt.BROKER_ZIPCODE != "") & (dt.BROKER_ZIPCODE != "0"),
        dt.BROKER_ZIPCODE.astype(str).str.split(".").str[0].str.zfill(5),
        dt.BROKER_ZIPCODE,
    )

    # dt['BROKER_ZIPCODE']=dt['BROKER_ZIPCODE'].astype(str).str.split(".").str[0].str.zfill(5)
    dt["BROKER_HQ_ZIP"] = np.where(
        dt["BROKER_HQ_ZIP"] != "",
        dt["BROKER_HQ_ZIP"].astype(str).str.split(".").str[0].str.zfill(5),
        dt["BROKER_HQ_ZIP"],
    )

    dt["BROKER_HQ_ZIP2"] = np.where(
        dt["BROKER_HQ_ZIP2"] != "",
        dt["BROKER_HQ_ZIP2"].astype(str).str.split(".").str[0].str.zfill(5),
        dt["BROKER_HQ_ZIP2"],
    )

    dt[(dt["BROKER_ZIPCODE"] == dt["BROKER_HQ_ZIP2"])].BROKER_HQ_ZIP2.value_counts(
        dropna=False
    )

    rule = "\n \n2.If the broker DOES play in the group's HQ state & they entered the broker's HQ assume local"
    dt["LOCAL"] = np.where(
        (dt["PlaysInHQ"] == 1)
        & (
            (dt["BROKER_ZIPCODE"] == dt["BROKER_HQ_ZIP"])
            | (dt["BROKER_ZIPCODE"] == dt["BROKER_HQ_ZIP2"])
        ),
        1,
        0,
    )
    print(rule, dt["LOCAL"].sum(), "out of", len(dt))
    dt = dt[dt["LOCAL"] == 0]
    print(" remaining ", len(dt))

    rule = "\nAssume the rest are non-local "
    dt = dt[
        ["State_HQ", "EIN", "BROKER", "BROKER_STATE", "BROKER_ZIPCODE"]
    ].drop_duplicates()

    dt.to_pickle(os.path.join(dataDir, "brokers", nonLocalMapFile + ".pkl"))
    print(
        "\nObs saved in",
        nonLocalMapFile,
        "in dataDir/brokers ",
        len(dt),
        " non-local broker to group pairs",
    )
    globals()["nonLocalBrokerMap"] = dt.copy()
    print("  saved to global Envt as nonLocalBrokerMap")


def localBrokerRevertMapCREATE(
    addressMapFile="BrokerAddressZipcodeMap",
    stdInFileBrok="brokers.all.yrs.Standardized",
    localRevertMapFile="localBrokerReversionMap",
):
    if "revertToLocalMap" in globals():
        del globals()["revertToLocalMap"]
        print("Remove revertToLocalMap and recreate")

    all = pd.read_pickle(os.path.join(dataDir, "brokers", stdInFileBrok + ".pkl"))
    all = all[
        (all["BROKER_STATE"] != "")
        & (all["State_HQ"] != "")
        & (~all["BROKER"].isin(badBrokTerms))
    ]
    all["BROKER_ZIPCODE"] = np.where(
        (all.BROKER_ZIPCODE != "") & (all.BROKER_ZIPCODE != "0"),
        all.BROKER_ZIPCODE.astype(str).str.split(".").str[0].str.zfill(5),
        all["BROKER_ZIPCODE"],
    )
    all["Zipcode"] = np.where(
        (all.Zipcode != "") & (all.Zipcode != "0"),
        all.Zipcode.astype(str).str.split(".").str[0].str.zfill(5),
        all["Zipcode"],
    )

    all = addBrokerLocationAdjustmentFields(dt=all.copy())

    print(
        "\n\nStart localBrokerRevertMapCREATE NonLoc ",
        all["NonLoc"].mean(),
        " out of ",
        len(all),
    )
    # all.columns
    # addMap[addMap.EIN==460743136]['BROKER_ADDRESS']

    print(" pct of obs OnNonLocMap ", round(100 * all["OnNonLocMap"].mean()))
    all["BROKER_ZIPCODE"] = (
        all["BROKER_ZIPCODE"].astype(str).str.split(".").str[0].str.zfill(5)
    )

    dt = all[(all["OnNonLocMap"] == 0) & (all["NonLoc"] == 1) & (all["Zipcode"] != "")][
        ["State_HQ", "EIN", "Zipcode", "BROKER", "BROKER_STATE", "BROKER_ZIPCODE"]
    ].drop_duplicates()

    print(
        "\nUnique obs we need to revert ", len(dt), "  out of initial on all ", len(all)
    )
    dt = dt.drop(["BROKER_STATE", "BROKER_ZIPCODE"], axis=1)
    dt["KEY"] = dt["BROKER"] + dt["State_HQ"]

    addMap = pd.read_pickle(os.path.join(brokerDir, addressMapFile + ".pkl"))

    addMap["KEY"] = addMap["BROKER_NAME"] + addMap["BROKER_STATE"]
    addMap["OnRevert"] = np.where(addMap["KEY"].isin(dt["KEY"]), 1, 0)
    print("\naddMap to keep ", addMap["OnRevert"].sum(), "out of ", len(addMap))
    addMap = addMap[addMap["OnRevert"] == 1]
    print("\nObs in addMap on the revert dt.", len(addMap))
    rule = "\n1.If a single-office-by-state broker use that info "
    single = addMap[["BROKER_NAME", "BROKER_STATE", "BROKER_ZIPCODE"]].drop_duplicates()
    single["CT"] = single.groupby(["BROKER_NAME", "BROKER_STATE"])[
        "BROKER_ZIPCODE"
    ].transform("count")
    single = single[single["CT"] == 1]
    single = single.drop("CT", axis=1)
    print("\nObs in single ", len(single), " and vars ", single.columns)
    dt.isnull().sum()

    dt = pd.merge(
        dt,
        single,
        left_on=["BROKER", "State_HQ"],
        right_on=["BROKER_NAME", "BROKER_STATE"],
        how="left",
    )
    dt = dt.drop(["BROKER_NAME", "BROKER_STATE"], axis=1)

    for v in ["BROKER_ZIPCODE"]:
        dt[v] = dt[v].fillna("")
    revert2 = dt[dt["BROKER_ZIPCODE"] != ""].drop_duplicates()
    print("\nSingle-office obs to update ", len(revert2), "out of ", len(dt))
    na = revert2.columns[revert2.isna().any()].tolist()
    if len(na) > 0:
        raise Exception(na + " - NAs in revert2")

    dt = cleanupJunk(dt=dt)

    dt = dt[dt["BROKER_ZIPCODE"] == ""].drop_duplicates()
    print(" and obs remaining to revert in dt ", len(dt))

    rule = "\n3.If the exact ZIP_HQ (Zipcode) and State_HQ are associated with the broker use them"
    addMap["CT"] = addMap.groupby(["BROKER_NAME", "BROKER_STATE"])[
        "BROKER_ZIPCODE"
    ].transform("count")
    addMap = addMap[addMap["CT"] > 1]

    print("\nObs in map remaining with 2+ offices per state ", len(addMap))
    dt["KEY"] = dt["BROKER"] + dt["State_HQ"] + dt["Zipcode"].astype(str)
    addMap["KEY"] = (
        addMap["BROKER_NAME"] + addMap["BROKER_STATE"] + addMap["BROKER_ZIPCODE"]
    )
    revert3 = dt[dt["KEY"].isin(addMap["KEY"])].drop_duplicates()
    print(rule, " in revert3 ", len(revert3))
    revert3["BROKER_ZIPCODE"] = revert3["Zipcode"]

    te = revert3.drop_duplicates()
    if len(te) != len(revert3):
        raise Exception("\nNon-uq obs in revert3 ", len(revert) - len(te))
    na = revert3.columns[revert3.isna().any()].tolist()
    if len(na) > 0:
        raise Exception(str(na) + " - NAs in revert3")

    dt = dt[~dt["KEY"].isin(addMap["KEY"])].drop_duplicates()
    print(" and remaining to revert ", len(dt))

    rule = "\n4.Nearest distance "
    zipMap = pd.DataFrame(zipcode_to_lat_lng)
    zipMap = zipMap.drop("ZIP", axis=1)
    dt["Zipcode"] = dt["Zipcode"].astype(str)
    zipMap.ZIPCODE = zipMap.ZIPCODE.astype(str).str.zfill(5)
    dt["Zipcode"] = dt["Zipcode"].astype(str)
    dt = pd.merge(dt, zipMap, left_on="Zipcode", right_on="ZIPCODE", how="left")

    dt["LAT"] = dt["LAT"].fillna(0)
    dt["LNG"] = dt["LNG"].fillna(0)
    dt = dt.rename(columns={"LAT": "LAT_HQ", "LNG": "LNG_HQ"})

    addMap = pd.merge(
        addMap, zipMap, left_on="BROKER_ZIPCODE", right_on="ZIPCODE", how="left"
    )

    addMap = addMap.drop(["ZIPCODE"], axis=1)

    addMap["LAT"] = addMap["LAT"].fillna(0)
    addMap["LNG"] = addMap["LNG"].fillna(0)

    dtZips = dt[["Zipcode", "LAT_HQ", "LNG_HQ", "State_HQ", "BROKER"]].drop_duplicates()

    mapZips = addMap[
        ["BROKER_ZIPCODE", "LAT", "LNG", "BROKER_STATE", "BROKER_NAME"]
    ].drop_duplicates()

    brokZips = pd.merge(
        dtZips,
        mapZips,
        left_on=["BROKER", "State_HQ"],
        right_on=["BROKER_NAME", "BROKER_STATE"],
    )
            
    brokZips = brokZips.drop(["BROKER_NAME", "BROKER_STATE"], axis=1)
    na = brokZips.columns[brokZips.isna().any()].tolist()
    if len(na) > 0:
        raise Exception(na + " - NAs in brokZips @145")

    print(
        "\nObs in brokZips ",
        len(brokZips),
        "from obs in dtZips ",
        len(dtZips),
        " and mapZips ",
        len(mapZips),
    )

    # brokZips["dist"] = haversine(brokZips[["LNG_HQ", "LAT_HQ"]], brokZips[["LNG", "LAT"]])
    brokZips["dist"] = haversine_v(
        brokZips["LAT_HQ"], brokZips["LNG_HQ"], brokZips["LAT"], brokZips["LNG"]
    )

    brokZips = brokZips.sort_values(["BROKER", "Zipcode", "dist"])
    
    brokZips["IND"] = brokZips.groupby(["BROKER", "State_HQ", "Zipcode"]).cumcount() + 1
    brokZips = brokZips[brokZips["IND"] == 1][
        ["BROKER", "State_HQ", "Zipcode", "BROKER_ZIPCODE"]
    ]

    try:
        dt = dt.drop(["Unnamed: 0"], axis=1)
    except:
        print("no unnamed")

    dt = dt.drop(["ZIPCODE"], axis=1)
    na = dt.columns[dt.isna().any()].tolist()
    if len(na) > 0:
        raise Exception(na + " - NAs in dt @145")

    sub = dt.copy()
    sub = sub.drop("BROKER_ZIPCODE", axis=1)
    revert4 = pd.merge(sub, brokZips, on=["BROKER", "State_HQ", "Zipcode"], how="left")
    revert4["BROKER_ZIPCODE"] = revert4["BROKER_ZIPCODE"].fillna(revert4["Zipcode"])
    te = revert4[revert4["BROKER_ZIPCODE"] == ""]
    print("\nrevert4 miss broker zip ", len(te), " of", len(revert4))

    na = revert4.columns[revert4.isna().any()].tolist()
    if len(na) > 0:
        raise Exception(na + " - NAs in revert4")
    print(rule, " obs saved in revert4 ", len(revert4))

    revert2 = cleanupJunk(dt=revert2)
    revert3 = cleanupJunk(dt=revert3)
    revert4 = cleanupJunk(dt=revert4)

    te = revert2.drop_duplicates()
    if len(te) != len(revert2):
        raise Exception("\nNon-unique obs in revert2 ", len(revert2) - len(te))

    revert2["RevertMethod"] = "revert2"
    revert3["RevertMethod"] = "revert3"
    revert4["RevertMethod"] = "revert4"

    vars = ["State_HQ", "EIN", "Zipcode", "BROKER", "BROKER_ZIPCODE", "RevertMethod"]

    all = pd.concat([revert2[vars], revert3[vars], revert4[vars]])

    map = pd.read_pickle(brokerDir + "BrokerAddressZipcodeMap.pkl")
    map = map[
        ["BROKER_NAME", "BROKER_ZIPCODE", "BROKER_ADDRESS", "Groups", "GroupsXBrokXSt"]
    ]
    map = map.sort_values(
        ["BROKER_NAME", "BROKER_ZIPCODE", "Groups"], ascending=[True, True, False]
    )

    map["IND"] = map.groupby(["BROKER_NAME", "BROKER_ZIPCODE"]).cumcount() + 1
    map = map[map["IND"] == 1]
    map = map.drop(["IND", "Groups"], axis=1)

    all.BROKER_ZIPCODE.str.len().value_counts(dropna=False)
    all["BROKER_ZIPCODE"] = all["BROKER_ZIPCODE"].astype(str).str.zfill(5)
    all = pd.merge(
        all,
        map,
        left_on=["BROKER", "BROKER_ZIPCODE"],
        right_on=["BROKER_NAME", "BROKER_ZIPCODE"],
        how="left",
    )
    all["BROKER_ADDRESS"] = all["BROKER_ADDRESS"].fillna("")

    try:
        all = all.drop("KEY", axis=1)
    except:
        print("key np")
    print(
        "\nObs to save on",
        localRevertMapFile,
        " ",
        len(all),
        " and var names ",
        all.columns,
    )

    all.to_pickle(os.path.join(dataDir, "brokers", localRevertMapFile + ".pkl"))
    globals()["revertToLocalMap"] = all.copy()

    print(
        "\nSaved in",
        localRevertMapFile,
        " in dataDir/brokers ",
        len(all),
        "in Global as revertToLocaMap",
    )


def addBrokerLocationAdjustmentFields(dt):
    # dt=all.copy()

    global cleanupJunk

    print("\nStart fn to addBrokerLocationAdjustmentFields, Obs in dt ", len(dt))

    rule = "\nIf no BROKER_STATE use the STATE_HQ "
    dt["FL"] = np.where(dt["BROKER_STATE"] == "", 1, 0)
    print(rule, dt["FL"].sum())
    dt["BROKER_STATE"] = np.where(dt["FL"] == 1, dt["State_HQ"], dt["BROKER_STATE"])
    dt[dt["BROKER_ZIPCODE"] == ""]

    dt["BROKER_ZIPCODE"] = np.where(
        dt["BROKER_ZIPCODE"].isin(["","00000","0"]), dt["Zipcode"], dt["BROKER_ZIPCODE"]
    )
    
    dt["BROKER_ZIPCODE"]=np.where(dt["BROKER_ZIPCODE"].isin(["00000","0"]),"",
                                  dt["BROKER_ZIPCODE"])
    dt["BROKER_ADDRESS"] = np.where(
        (dt["FL"] == 1) & (dt["BROKER_ADDRESS"] == ""),
        "MISSING BROKER LOCATION",
        dt["BROKER_ADDRESS"],
    )
    dt[dt["BROKER_ZIPCODE"] == "80111"]
    te = dt[dt["FL"] == 1]
    pct = round(100 * len(te) / len(dt), 1)
    if pct > 2:
        print(pct, " obs missing broker state -- too high @185 ")

    dt["NonLoc"] = np.where(dt["BROKER_STATE"] != dt["State_HQ"], 1, 0)

    print("\nAdd the location adjustment fields. Obs in dt ", len(dt))
    print(" Pct NonLoc initially ", dt["NonLoc"].mean())

    dt = localIfBordering(
        dt=dt.copy(), nonLocalVar="NonLoc", otherStateVar="BROKER_STATE"
    )

    # Add pays in global HQ

    map = broker_exact[["BROKER"] + state_list].drop_duplicates()
    obs = len(dt)
    begVars = dt.columns

    if "FL" in dt.columns:
        dt = dt.drop("FL", axis=1)
    dt = pd.merge(dt, map, on="BROKER", how="left")

    if len(dt) != obs:
        raise Exception("\nObs in dt post merge ", len(dt), " and obs before ", obs)
    addV = np.setdiff1d(dt.columns, begVars)
    for v in addV:
        dt[v] = dt[v].fillna(1)
    dt["PlaysInHQ"] = 1
    
    for s in state_list:
        dt["PlaysInHQ"] = np.where(
            (dt["State_HQ"] == s) & (dt[s] == 0), 0, dt["PlaysInHQ"]
        )
        #  dt.loc[(dt["State_HQ"] == s) & (dt[s] == 0), "PlaysInHQ"] = 0
        print(s, ":", dt["PlaysInHQ"].sum(), " ")
    print(
        "\nPct of state x broker obs playing in groups state_hq ",
        dt["PlaysInHQ"].mean(),
    )
    dt = dt.drop(state_list, axis=1)

    map = broker_exact[["BROKER", "BROKER_HQ_ZIP", "BROKER_HQ_ZIP2"]].drop_duplicates()
   
    dt = pd.merge(dt, map, on="BROKER", how="left")
    print("\nObs in dt post merge @276", len(dt), "vs initial obs ", obs)
    for v in ["BROKER_HQ_ZIP", "BROKER_HQ_ZIP2"]:
        dt[v] = dt[v].fillna("")

    if "nonLocalBrokerMap" in globals():
        nonLocalBrokerMap["KEY"] = (
            nonLocalBrokerMap["EIN"].astype(str)
            + nonLocalBrokerMap["BROKER"]
            + nonLocalBrokerMap["BROKER_STATE"]
        )
        dt["OnNonLocMap"] = np.where(
            (dt["EIN"].astype(str) + dt["BROKER"] + dt["BROKER_STATE"]).isin(
                nonLocalBrokerMap["KEY"]
            )
            & (dt["NonLoc"] == 1),
            1,
            0,
        )
        print("\nBrokStd obs on nonLocalMap ", dt["OnNonLocMap"].sum())

        dt["REVERT"] = np.where((dt["NonLoc"] == 1) & (dt["OnNonLocMap"] == 0), 1, 0)
        print("\nObs to revert ", dt["REVERT"].sum(), " ", dt["REVERT"].mean())

        if "revertToLocalMap" in globals():
            revertToLocalMap["KEY"] = (
                revertToLocalMap["EIN"].astype(str)
                + revertToLocalMap["BROKER"]
                + revertToLocalMap["State_HQ"]
            )
            dt["OnRevertMap"] = np.where(
                (dt["NonLoc"] == 1)
                & (dt["EIN"].astype(str) + dt["BROKER"] + dt["State_HQ"]).isin(
                    revertToLocalMap["KEY"]
                ),
                1,
                0,
            )
            print("\nBrokStd obs on revertToLocalMap ", dt["OnRevertMap"].sum())
            print(
                "\nNot accounted for in revertmap ",
                len(dt[(dt["OnRevertMap"] == 0) & (dt["REVERT"] == 1)]),
            )

    dt = cleanupJunk(dt=dt, otherJunk=["BROKER_ZIP_ORIG_DELME"])
    if "nonLocalBrokerMap" in globals():
        print(
            "\nEnd addLocationFields Pct NonLoc ",
            dt["NonLoc"].mean(),
            " obs in dt ",
            len(dt),
            "percent REVERT ",
            dt["REVERT"].mean(),
            " \n\n",
        )
    else:
        print(
            "\nEnd addLocationFields Pct NonLoc ",
            dt["NonLoc"].mean(),
            " obs in dt ",
            len(dt),
            "percent REVERT FIRST RUN, HENCE no % revert")
    return dt


def brokerToHQandState(brokerMap):
    # global brokAddressMap
    print(
        "\nStart brokerToHqandSTate to create brokHQ and BrokToState dts in global envt "
    )
    try:
        brokAddressMap = pd.read_pickle(brokerDir + "BrokerAddressZipcodeMap" + ".pkl")
    except:
        raise Exception(" - address map not present")
    mapInit = brokAddressMap.copy()

    mapInit["MaxBrokAtAdd"] = mapInit.groupby(["BROKER_NAME"])[
        "PctBrokAtAdd"
    ].transform("max")
    mapInit = mapInit.rename(
        columns={
            "BROKER_NAME": "BROKER",
            "BROKER_ZIPCODE": "ZIP",
            "BROKER_STATE": "STATE",
            "BROKER_ADDRESS": "ADDRESS",
        }
    )
    mapInit = mapInit[~mapInit["STATE"].isin(["PH", "PW", "", "MH"] + states_to_drop)]
    mapInit = mapInit[
        [
            "BROKER",
            "ZIP",
            "STATE",
            "ADDRESS",
            "Groups",
            "GroupsXBrok",
            "MaxBrokAtAdd",
            "PctBrokAtAdd",
        ]
    ].drop_duplicates()

    hq = mapInit[
        (mapInit["PctBrokAtAdd"] == mapInit["MaxBrokAtAdd"]) & (mapInit["Groups"] > 150)
    ]
    hq = hq.drop("MaxBrokAtAdd", axis=1)
    thresh = np.quantile(hq["PctBrokAtAdd"], 0.20)
    print("\nHQ if the % of all broker obs at that zipcode is >", thresh)
    hq = hq[hq["PctBrokAtAdd"] >= thresh][["BROKER", "STATE", "ZIP", "ADDRESS"]]
    hq = hq.rename(
        columns={
            "STATE": "BROKER_HQ_STATE",
            "ZIP": "BROKER_HQ_ZIP",
            "ADDRESS": "BROKER_HQ_ADD",
        }
    )

    ag = mapInit.groupby(["BROKER", "STATE"])["Groups"].sum().reset_index()
    uq = ag["BROKER"].nunique()
    print("\nUnique brokers in mapInit ", uq)
    ag["GroupsXBrok"] = ag.groupby("BROKER")["Groups"].transform("sum")
    ag["PctGroups"] = round(100 * ag["Groups"] / ag["GroupsXBrok"], 1)
    ag = ag.drop("GroupsXBrok", axis=1)

    ag = ag[ag["Groups"] > 4]
    ag["StateTot"] = ag.groupby("STATE")["Groups"].transform("sum")
    ag["StateTot"] = round(100 * ag["StateTot"] / ag["Groups"].sum(), 2)
    ag.loc[ag["StateTot"] < 0.1, "StateTot"] = 0.1
    ag["PctGroupsAdj"] = round(ag["PctGroups"] / ag["StateTot"], 1)
    ag = ag.sort_values(["BROKER", "PctGroups"], ascending=[True, False])

    ag["MaxPctGroups"] = round(ag.groupby("BROKER")["PctGroups"].transform("max"), 3)
    ag["Spread"] = ag.groupby("BROKER")["STATE"].transform("nunique")
    ag["Var"] = round(ag.groupby("BROKER")["PctGroups"].transform(np.var), 1)
    ag.loc[ag["Var"].isna(), "Var"] = 0
    ag["VarAdj"] = round(ag.groupby("BROKER")["PctGroupsAdj"].transform(np.var), 1)
    ag["Score"] = np.where(ag["Var"] < 1, 1000, round(100 * ag.PctGroups / ag.Var, 2))
    ag["ScoreAdj"] = np.where(
        ag["VarAdj"] < 1, 1000, round(100 * ag.PctGroupsAdj / ag.VarAdj, 2)
    )

    rule = "\nSet aside single state or 0 var (mapped1) "
    mapped1 = ag[
        (ag["Spread"] == 1)
        | (ag["Var"] <= 75)
        | (ag["VarAdj"] <= 75)
        | ((ag["Spread"] >= 8) & (ag["MaxPctGroups"] < 60))
    ]
    ag = ag[~ag["BROKER"].isin(mapped1["BROKER"])]
    print(rule, len(mapped1), "left ", len(ag))

    rule = "\nFlag single state when they have others but are relatively small "
    ag["IND"] = ag.groupby("BROKER").cumcount() + 1
    ag.loc[ag["IND"] == 2, "DELME"] = ag["PctGroups"]
    ag["Max2"] = ag.groupby("BROKER")["DELME"].transform("max")
    ag["FL"] = np.where(
        (ag["MaxPctGroups"] > 80)
        & (ag["Max2"] < 10)
        & ((ag["Score"] < 1) | (ag["ScoreAdj"] < 1)),
        1,
        0,
    )
    ag["SINGLE"] = ag.groupby("BROKER")["FL"].transform("sum")
    ag.loc[(ag["SINGLE"] > 0) & (ag["FL"] == 0), "SINGLE"] = 1
    ag = ag[ag["FL"] == 0]
    mapped2 = ag[ag["SINGLE"] == 1]
    globals()["mapped2"] = mapped2
    ag = ag[ag["SINGLE"] == 0]
    print(rule, len(mapped2), "extracted and flagged as single state")

    rule = "\nDrop if very rarely in "
    ag.loc[
        ((ag["Score"] + ag["ScoreAdj"]) < 5)
        & (((ag["PctGroups"] < 1) & (ag["PctGroupsAdj"] < 1)) | (ag["Groups"] < 10)),
        "DROP",
    ] = 1
    print(rule, ag["DROP"].sum(), " out of ", len(ag))

    vars = set(mapped1.columns).intersection(mapped2.columns).intersection(ag.columns)
    mapped1 = mapped1[vars]
    mapped1["Method"] = "SingleState"
    mapped2 = mapped2[vars]
    mapped2["Method"] = "DerivedSingle"
    mapped3 = ag[vars]
    mapped3["Method"] = "Final"

    all = pd.concat([mapped1, mapped2, mapped3])
    all.loc[all["ScoreAdj"].isna(), "ScoreAdj"] = 1000
    all = all[["BROKER", "STATE", "Groups", "PctGroups", "Score", "ScoreAdj", "Method"]]

    brokToState = all.sort_values(
        ["BROKER", "STATE", "Groups"], ascending=[True, True, False]
    ).drop_duplicates()
    globals()["brokToState"] = brokToState
    print("\nObs saved to GlobalEnv as brokToState ", len(brokToState))