from config import Config
S3_PATH_ROOT = "s3://463002504035-shoreline/raw/uploads"
[docs]
class FUNDUtil:
"""Keep records of all transfered funding file from SBO."""
bead_data_pre = {
"OR": {
"dedup": f"{S3_PATH_ROOT}/Oregon/unzipped/Funding/OR_Fed_Dedup_BDC_030524.csv",
"state": "",
"other": [],
"version_date": "030524",
},
"CA": {
"dedup": f"{S3_PATH_ROOT}/California/unzipped/CA_Fed_Dedup/CA_Fed_Dedup_BDC_051424.csv",
"state": f"{S3_PATH_ROOT}/California/unzipped/funding/CASF_approved.geojson",
"other": [
f"{S3_PATH_ROOT}/California/unzipped/funding/PublicHousingAccountProjectLocations_geocoded.xlsx",
],
"version_date": "051424",
},
"MS": {
"dedup": f"{S3_PATH_ROOT}/Mississippi/unzipped/Funding/MS_Fed_Dedup_BDC_052824.csv",
"state": f"{S3_PATH_ROOT}/Mississippi/unzipped/Funding/MS_CPF_Data_clean.csv",
"other": [f"{S3_PATH_ROOT}/Mississippi/unzipped/cpf_round2_data_source/"],
"version_date": "052824",
},
"OK": {
"fabric_version": "4",
"dedup": f"{S3_PATH_ROOT}/Oklahoma/unzipped/v4/OK_Fed_Dedup_BDC_052824.csv",
"state": f"{S3_PATH_ROOT}/Oklahoma/unzipped/CPF_prepare/ARPA_SLFRF_Awarded.csv",
"other": [
f"{S3_PATH_ROOT}/Oklahoma/unzipped/BEAD_prepare/SLFRFlocations.csv"
],
"version_date": "052824",
},
"IN": {
"dedup": f"{S3_PATH_ROOT}/Indiana/unzipped/IN_State_and_Fed_Dedup/IN_State_and_Fed_Dedup_BDC_020724.csv",
"state": "",
"other": [],
"version_date": "020724",
},
"TX": {
# "dedup": f"{S3_PATH_ROOT}/Texas/unzipped/bead_pre_challenge/TX_Fed_Dedup_BDC_091724.csv",
"dedup": f"{S3_PATH_ROOT}/Texas/unzipped/bead-pre-challenge/TX_Fed_Dedup_BDC_111324.csv",
"dedup_v4": f"{S3_PATH_ROOT}/Texas/unzipped/bead-pre-challenge/TX_Fed_Dedup_BDC_103024.csv",
"state": f"{S3_PATH_ROOT}/Texas/unzipped/bead-pre-challenge/state_funding_data/BOOT I.csv",
"other": [
f"{S3_PATH_ROOT}/Texas/unzipped/bead-pre-challenge/state_funding_data/BOOT II_combined.csv",
f"{S3_PATH_ROOT}/Texas/unzipped/bead-pre-challenge/state_funding_data/cdbg.csv",
],
"other_v4": [
f"{S3_PATH_ROOT}/Texas/unzipped/bead-pre-challenge/state_funding_data/BOOT II_combined.csv",
f"{S3_PATH_ROOT}/Texas/unzipped/bead-pre-challenge/state_funding_data/CDBG_v4.csv",
],
"version_date": "111324",
},
"NJ": {
"dedup": f"{S3_PATH_ROOT}/NewJersey/unzipped/funding/NJ_Fed_Dedup_BDC_051424.csv",
"version_date": "051424",
},
"WY": {
"dedup": f"{S3_PATH_ROOT}/Wyoming/unzipped/Funding/WY_Fed_Dedup_BDC_030524.csv",
"state": f"{S3_PATH_ROOT}/Wyoming/unzipped/Funding/WY_StateGrant_ALL_Locations_ver2_04162024.csv",
"other": [
f"{S3_PATH_ROOT}/Wyoming/unzipped/Funding/WY_FedGrant_ALL_Locations_ver3_04262024.csv"
],
"version_date": "030524",
},
"AZ": {
"dedup": f"{S3_PATH_ROOT}/Arizona/NBAM_Deduplication_Data/AZ_State_and_Fed_Dedup_v7/AZ_State_and_Fed_Dedup_BDC_022024.csv",
"state": "",
"other": [],
"version_date": "022024",
},
"GU": {
"dedup": f"{S3_PATH_ROOT}/Guam/pre-challenge/GU_Fed_Dedup_BDC_090624.csv",
"version_date": "090624",
},
"AR": {
"dedup": f"{S3_PATH_ROOT}/Arkansas/unzipped/dedup/AR_Fed_Dedup_NicksVersion_March18.csv",
"state": f"{S3_PATH_ROOT}/Arkansas/unzipped/dedup/AR_StateFundedLocationsList_FabricV3_2_March18_2024.xlsx",
"other": [],
"version_date": "072123",
},
"NY": {
"dedup": f"{S3_PATH_ROOT}/NewYork/unzipped/Funding/NY_Fed_Dedup_BDC_022024.csv",
"state": f"{S3_PATH_ROOT}/NewYork/unzipped/Funding/commitment_list_03082024.csv",
"other": [],
"version_date": "022024",
},
"NM": {
"dedup": f"{S3_PATH_ROOT}/NewMexico/unzipped/NM_Fed_Dedup_BDC_020724_UpdatedFeb23.csv",
"state": f"{S3_PATH_ROOT}/NewMexico/unzipped/StateGrants_PRC_CNM_Project_Areas_NTIA_20240227_V2.geojson",
"other": [],
"version_date": "020724",
},
"OH": {
"dedup": "s3://463002504035-readydatalake/raw/uploads/boss_orgs/2503/OH_Fed_Dedup_BDC_050524-1718297793438.csv",
"state": "",
"other": [],
"version_date": "050524_post", # this is a post challenge data, same version date, but different data.
},
"HI": {
"dedup": f"{S3_PATH_ROOT}/Hawaii/unzipped/V4/HI_Fed_Dedup_BDC_051424.csv",
"other": [f"{S3_PATH_ROOT}/Hawaii/unzipped/V4/Funding/"],
"version_date": "051424",
},
"AL": {
"dedup": f"{S3_PATH_ROOT}/Alabama/AL_Fed_Dedup (1)/AL_Fed_Dedup_BDC_051424.csv",
"state": f"{S3_PATH_ROOT}/Alabama/Alabama_ABAF_grant_served_locations_revised_July2.xlsx",
"other": [],
"version_date": "051424",
},
"ND": {
"dedup": f"{S3_PATH_ROOT}/NorthDakota/ND_Fed_Dedup_BDC_121223.csv",
"state": "",
"other": [],
"version_date": "121223",
},
"NC": {
"dedup": f"{S3_PATH_ROOT}/NorthCarolina/Federal_Deduplication/NC_Fed_Dedup_BDC_062524.csv",
"state": f"{S3_PATH_ROOT}/NorthCarolina/NC_Combined_State_And_Local_Funded_Locations_BSLv4_240801.csv",
"other": [],
"version_date": "062524",
},
"XY":{
"dedup": "",
"state": "",
"other": [],
"version_date":"052824"
}
}
bead_data_post = {
"ND": {
"dedup": f"{S3_PATH_ROOT}/NorthDakota/v3_v4_trueup/ND_Fed_Dedup_BDC_070924.csv",
"state": "",
"other": [],
"version_date": "070924",
},
"OR": {
"dedup": f"{S3_PATH_ROOT}/Oregon/unzipped/Funding/OR_Fed_Dedup_BDC_050524.csv",
"state": "",
"other": [
f"{S3_PATH_ROOT}/Oregon/unzipped/Funding/post-challenge-revision/"
],
"version_date": "050524",
},
"CA": {
"dedup": f"{S3_PATH_ROOT}/California/unzipped/bead_postchallenge/CA_Fed_Dedup_BDC_100124.csv",
"state": f"{S3_PATH_ROOT}/California/unzipped/funding/CASF_approved.geojson",
"other": [
f"{S3_PATH_ROOT}/California/unzipped/funding/PublicHousingAccountProjectLocations_geocoded.xlsx",
# post challenge data update added
f"{S3_PATH_ROOT}/California/unzipped/bead_postchallenge/Updated-2024-09-26_FFA_Awarded.geojson",
# additional CASF data shared
f"{S3_PATH_ROOT}/California/unzipped/bead_postchallenge/Surfnet CASF Project Locations_11042024.csv",
# TBCP defaults
f"{S3_PATH_ROOT}/California/unzipped/bead_postchallenge/ca_tbcp_default.csv",
# CTC manual changes
f"{S3_PATH_ROOT}/California/unzipped/bead_postchallenge/CAFII_CASF_OverlapLocations_12192024.csv",
# f"{S3_PATH_ROOT}/California/unzipped/bead_postchallenge/tbcp_indian_tribes_default.csv",
# f"{S3_PATH_ROOT}/California/unzipped/bead_postchallenge/tbcp_yurok_default.csv",
],
"version_date": "100124",
},
"IN": {
"dedup": f"{S3_PATH_ROOT}/Indiana/unzipped/funding_post_challenge/IN_State_and_Fed_Dedup_BDC_111423.csv",
"state": f"{S3_PATH_ROOT}/Indiana/unzipped/funding_post_challenge/NEW_NLC_ICP_ARPA_enforceable_commitments(in).csv",
"other": [
f"{S3_PATH_ROOT}/Indiana/unzipped/funding_post_challenge/funding_support/",
f"{S3_PATH_ROOT}/Indiana/unzipped/funding_post_challenge/ICP11_fcc_ids.csv",
f"{S3_PATH_ROOT}/Indiana/unzipped/funding_post_challenge/495MissingNLCLocs.csv",
f"{S3_PATH_ROOT}/Indiana/unzipped/funding_post_challenge/MercuryUniqueIDsFiberv1.csv",
f"{S3_PATH_ROOT}/Indiana/unzipped/funding_post_challenge/MercuryFWALocIDs.csv",
f"{S3_PATH_ROOT}/Indiana/unzipped/funding_post_challenge/MercuryRDOFDefault11_18_24.csv",
f"{S3_PATH_ROOT}/Indiana/unzipped/funding_post_challenge/Mercury_Davies_Martin.csv",
f"{S3_PATH_ROOT}/Indiana/unzipped/bad_ec_in_daviess/bad_EC_IN_Daviess.csv",
],
"version_date": "111423",
},
"WY": {
"dedup": f"{S3_PATH_ROOT}/Wyoming/unzipped/post_challenge_funding_update/WY_Fed_Dedup_BDC_050524_ISPResponse_Defaults_08262024.csv",
"state": f"{S3_PATH_ROOT}/Wyoming/unzipped/post_challenge_funding_update/WY_StateGrant_ALL_Locations_ver2_04162024.csv",
"other": [
f"{S3_PATH_ROOT}/Wyoming/unzipped/post_challenge_funding_update/WY_ISP_Provided_GrantLocations.xlsx",
f"{S3_PATH_ROOT}/Wyoming/unzipped/post_challenge_funding_update/WY BEAD - defaulted APRA CPF enforceable commitments.csv",
f"{S3_PATH_ROOT}/Wyoming/unzipped/post_challenge_funding_update/WY BEAD - new APRA CPF enforceable commitments (1).csv",
],
"version_date": "050524",
},
"AZ": {
"dedup": "",
"state": f"{S3_PATH_ROOT}/Arizona/bead_funding/fundingbylocation.csv",
# f"{S3_PATH_ROOT}/Arizona/State Funding- TechSpeed/location_fundingtechspeed_09252024.csv",
"other": [
# f"{S3_PATH_ROOT}/Arizona/bead_funding/fundingbylocation.csv"
# f"{S3_PATH_ROOT}/Arizona/NBAM_Deduplication_Data/AZ_dedupe_comparison/AZ_dedupe_comparison_022024_061124.csv",
# f"{S3_PATH_ROOT}/Arizona/State Funding- TechSpeed/ABDG_v2_Broadband_Infrastructure_Location_Data(03-04-2024) CORRECTED PROVIDER IDS(in).csv",
],
"version_date": "022024", # this is the deduplication funding date, do not change if we don't have a new data.
},
"AR": {
"dedup": f"{S3_PATH_ROOT}/Arkansas/unzipped/dedup/AR_Fed_Dedup_BDC_061124.csv",
"state": f"{S3_PATH_ROOT}/Arkansas/unzipped/dedup/AR_StateFundedLocations_Fabric_v4_providerIDs.csv",
"other": [],
"version_date": "061124",
},
"NY": {
"dedup": f"{S3_PATH_ROOT}/NewYork/unzipped/v4/funding/NY_Fed_Dedup_BDC_070924.csv",
# "state": f"{S3_PATH_ROOT}/NewYork/unzipped/Funding/commitment_list_03082024.csv",
"state": f"{S3_PATH_ROOT}/NewYork/unzipped/v4/state/post-challenge local commitments 7.24.24.csv",
"other": [
f"{S3_PATH_ROOT}/NewYork/unzipped/Funding/post_challenge_commitments_to_add_10222024.csv",
f"{S3_PATH_ROOT}/NewYork/unzipped/Funding/post_challenge_commitments_to_remove_10222024.csv",
],
"version_date": "070924",
},
"NH": {
"dedup": f"{S3_PATH_ROOT}/NewHampshire/unzipped/NH_Fed_Dedup_BDC_100124.csv",
"state": f"{S3_PATH_ROOT}/NewHampshire/unzipped/NH_State_Dedup_Hub66Haverhill_101824.csv",
"other": [
f"{S3_PATH_ROOT}/NewHampshire/unzipped/NH_Fed_Dedup_BMGI_101824.csv",
f"{S3_PATH_ROOT}/NewHampshire/unzipped/NH_Fed_Dedup_Hub66Haverhill_101824.csv",
],
"version_date": "100124",
},
"NM": {
"dedup": f"{S3_PATH_ROOT}/NewMexico/unzipped/NM_Fed_Dedup_BDC_080624.csv",
"state": f"{S3_PATH_ROOT}/NewMexico/unzipped/NM_StateDedup_BSLv4Updated20240822_CNM-PRC.geojson",
"other": [
f"{S3_PATH_ROOT}/NewMexico/unzipped/USDA_Reconnect_5_Awards.xlsx",
f"{S3_PATH_ROOT}/NewMexico/unzipped/bead_post/BEAD_ManualFlipDEFAULTS_BSLs_forReadyNet.csv",
f"{S3_PATH_ROOT}/NewMexico/unzipped/bead_post/BEAD_ManualFlipsNEW_BSLs_forReadyNet.csv",
f"{S3_PATH_ROOT}/NewMexico/unzipped/bead_post/Summary_of_Grant_ManualFlips.csv",
f"{S3_PATH_ROOT}/NewMexico/unzipped/bead_post/NM_Programmatic_Review_020325_SBO.xlsx",
],
"version_date": "080624",
},
"MS": {
# no data shared yet.
# "dedup": f"{S3_PATH_ROOT}/Mississippi/unzipped/Funding/MS_Fed_Dedup_BDC_052824.csv", # this is the one used for current challenge process.
"dedup": f"{S3_PATH_ROOT}/Mississippi/unzipped/FED_DEDUP/MS_Fed_Dedup_BDC_091724.csv",
"dedup_v5": f"{S3_PATH_ROOT}/Mississippi/unzipped/FED_DEDUP/MS_Fed_Dedup_BDC_112524.csv",
"state": "",
"other": [
f"{S3_PATH_ROOT}/Mississippi/unzipped/bead_post_challenge/CPF Round 1 Provisional Awarded BSLs.csv",
f"{S3_PATH_ROOT}/Mississippi/unzipped/bead_post_challenge/cpf_r1_ancillary_bsls.csv",
f"{S3_PATH_ROOT}/Mississippi/unzipped/bead_post_challenge/MS_Programmatic_Review_011025_SBO_fed_ee.csv",
f"{S3_PATH_ROOT}/Mississippi/unzipped/bead_post_challenge/beam_bip_combined_060624_updated.csv",
],
"version_date": "091724", # this is the final version date used for the post challenge data.
},
"OH": {
"dedup": f"{S3_PATH_ROOT}/Ohio/unzipped/bead_post/2503_OH_Fed_Dedup_BDC_050524-1718297793438.csv",
"state": f"{S3_PATH_ROOT}/Ohio/unzipped/bead_post/OH-State-ORBEG-post-challenge-dedupe.csv",
"other": [
f"{S3_PATH_ROOT}/Ohio/unzipped/Funding Data/altafiber-CPF-post-challenge.csv"
],
"version_date": "050524",
},
"HI": {
"dedup": f"{S3_PATH_ROOT}/Hawaii/unzipped/Funding/Funding Data/HI_Fed_Dedup_BDC_103024.csv",
"other": [
f"{S3_PATH_ROOT}/Hawaii/unzipped/Funding/Funding Data/Un_Underserved_in_DHHL_dedup_20231231.csv",
],
"version_date": "103024",
},
"AL": {
"dedup": f"{S3_PATH_ROOT}/Alabama/AL_Fed_Dedup_BDC_103024.csv",
"state": f"{S3_PATH_ROOT}/Alabama/ABAF_State_enforceable_commitment_AL_Dec2024.csv",
"other": [
f"{S3_PATH_ROOT}/Alabama/AL_CPF1_approved_Dec5_2024_EnforceableCommitments.csv",
],
"version_date": "103024",
},
"OK": {
"dedup": f"{S3_PATH_ROOT}/Oklahoma/unzipped/OK_Fed_Dedup/OK_Fed_Dedup_BDC_120224.csv",
"other": [
f"{S3_PATH_ROOT}/Oklahoma/unzipped/cpf_2025/Updated_CPF_Agreements.csv",
f"{S3_PATH_ROOT}/Oklahoma/unzipped/BEAD_prepare/OK_CUST-2856_dup_funding_upd.csv", # updated version from CUST-3252
f"{S3_PATH_ROOT}/Oklahoma/unzipped/bead_post/UpdatedCPF_Ready.csv", # shared on 2025-01-16 via email
f"{S3_PATH_ROOT}/Oklahoma/unzipped/bead_post/SLFRF_V4_S2.csv", # CUST-3252
f"{S3_PATH_ROOT}/Oklahoma/unzipped/bead_post/TBCP2_choctaw.csv", # CUST-3321
f"{S3_PATH_ROOT}/Oklahoma/unzipped/bead_post/Muscogee Creek-20250317T020546Z-001/Muscogee Creek/fundingdata_project14548_J24_05mar2025/areapolygons_program41_project14548_J24_05mar2025.gpkg",
f"{S3_PATH_ROOT}/Oklahoma/unzipped/bead_post/Osage Nation/fundingdata_project14551_J24_05mar2025/locationlist_program27_project14551_J24_05mar2025.csv",
f"{S3_PATH_ROOT}/Oklahoma/unzipped/bead_post/Osage Nation/fundingdata_project14550_J24_05mar2025/areapolygons_program27_project14550_J24_05mar2025.gpkg",
f"{S3_PATH_ROOT}/Oklahoma/unzipped/bead_post/Muscogee Creek-20250317T020546Z-001/Muscogee Creek/fundingdata_project14549_J24_05mar2025/locationlist_program41_project14549_J24_05mar2025.csv",
f"{S3_PATH_ROOT}/Oklahoma/unzipped/bead_post/Seminole Nation/Seminole Nation_proposed BSLs_03102025.csv",
f"{S3_PATH_ROOT}/Oklahoma/unzipped/bead_post/Cherokee Nation/Cherokee Nation_proposed BSLs_above100_20_03182025.csv",
f"{S3_PATH_ROOT}/Oklahoma/unzipped/bead_post/Cherokee Nation/Cherokee Nation_proposed BSLs_below100_20_03182025.csv",
],
"version_date": "120224",
},
"TX": {
"dedup": f"{S3_PATH_ROOT}/Texas/unzipped/funding/simplified_dedupe_tx_v5_31925BFM.csv",
"state": f"{S3_PATH_ROOT}/Texas/unzipped/funding/Bowie County Master List.csv",
"other": [
f"{S3_PATH_ROOT}/Texas/unzipped/funding/BOOT II/Bastrop Location Data.xlsx",
f"{S3_PATH_ROOT}/Texas/unzipped/funding/BOOT II/Burleson Location Data.xlsx",
f"{S3_PATH_ROOT}/Texas/unzipped/funding/BOOT II/Caldwell Location Data.xlsx",
f"{S3_PATH_ROOT}/Texas/unzipped/funding/BOOT II/Carson Location Data.xlsx",
f"{S3_PATH_ROOT}/Texas/unzipped/funding/BOOT II/Coleman Location Data.xlsx",
f"{S3_PATH_ROOT}/Texas/unzipped/funding/BOOT II/Colorado Location Data.xlsx",
f"{S3_PATH_ROOT}/Texas/unzipped/funding/BOOT II/Edwards Location Data.xlsx",
f"{S3_PATH_ROOT}/Texas/unzipped/funding/BOOT II/Fall Location Data.xlsx",
f"{S3_PATH_ROOT}/Texas/unzipped/funding/BOOT II/Fannin Location Data.xlsx",
f"{S3_PATH_ROOT}/Texas/unzipped/funding/BOOT II/Irion Location Data.xlsx",
f"{S3_PATH_ROOT}/Texas/unzipped/funding/BOOT II/Karnes Location Data.xlsx",
f"{S3_PATH_ROOT}/Texas/unzipped/funding/BOOT II/Kenedy Location Data.csv",
f"{S3_PATH_ROOT}/Texas/unzipped/funding/BOOT II/Liberty Location Data.xlsx",
f"{S3_PATH_ROOT}/Texas/unzipped/funding/BOOT II/Marion Location Data.xlsx",
f"{S3_PATH_ROOT}/Texas/unzipped/funding/BOOT II/Morris Location Data.xlsx",
f"{S3_PATH_ROOT}/Texas/unzipped/funding/BOOT II/Navarro Location Data.xlsx",
f"{S3_PATH_ROOT}/Texas/unzipped/funding/BOOT II/Newton Location Data.xlsx",
f"{S3_PATH_ROOT}/Texas/unzipped/funding/BOOT II/Reagan Location Data.xlsx",
f"{S3_PATH_ROOT}/Texas/unzipped/funding/BOOT II/Roberts Location Data.xlsx",
f"{S3_PATH_ROOT}/Texas/unzipped/funding/BOOT II/Throckmorton Location Data.xlsx",
f"{S3_PATH_ROOT}/Texas/unzipped/funding/BOOT II/Trinity Location Data.xlsx",
],
"version_date": "030425",
},
"MN": {
"dedup": f"{S3_PATH_ROOT}/Minnesota/bead_prepare/MN_Fed_Dedup_20240711/MN_Fed_Dedup_BDC_061124.csv",
"state": "",
"other": [],
"version_date": "061124",
},
"GU": {
"dedup": f"{S3_PATH_ROOT}/Guam/pre-challenge/GU_Fed_Dedup_BDC_090624.csv", # this is the version 4, used for primary post challenge submission
# "dedup_v5": f"{S3_PATH_ROOT}/Guam/bead_post/GU_Fed_Dedup_BDC_070924.csv",
"dedup_v5": f"{S3_PATH_ROOT}/Guam/bead_post/GU_Fed_Dedup_BDC_121024.csv",
"version_date": "090624",
},
"NJ": {
"dedup": "",
"state": "",
"other": [
f"{S3_PATH_ROOT}/NewJersey/unzipped/bead_post/simplified_dedupe_nj_v4_03052025bfm 2.csv"
], # this is a revised version 4 from CUST-3656
"version_date": "103024",
},
"NC": {
"dedup": f"{S3_PATH_ROOT}/NorthCarolina/funding/NC_Fed_Dedup_BDC_062524_LC.csv", # this is the actual dedup file. # make this empty so that we don't break the dag
"state": f"{S3_PATH_ROOT}/NorthCarolina/funding/NC_state_funded_locations_v4_BEADprotect_250324.csv",
"other": [
f"{S3_PATH_ROOT}/NorthCarolina/funding/LumosOrangeCounty_UPDATEDfundedBSLv4_250321.csv",
f"{S3_PATH_ROOT}/NorthCarolina/funding/NC_state_funded_locations_v4_BEADprotectGREATRounds2and3AddOn_250324(in).csv",
f"{S3_PATH_ROOT}/NorthCarolina/funding/NCBIO_state_funded_BSLv4_GREAT1000013247_withdrawn_250424 (1).csv", # DATASME-831
f"{S3_PATH_ROOT}/NorthCarolina/funding/CharterTribalRDOFWithdrawals_FromLetterToFCC_250425.csv", # DATASME-831
f"{S3_PATH_ROOT}/NorthCarolina/funding/NC_state_funded_ATMC_v4_1000013247_withdrawal_250430.csv", # DATASME-834
],
"version_date": "062524post",
},
}
cpf_data_pre = {
"OK": {
"dedup": f"{S3_PATH_ROOT}/Oklahoma/unzipped/v4/OK_Fed_Dedup_BDC_052824.csv",
"state": f"{S3_PATH_ROOT}/Oklahoma/unzipped/CPF_prepare/ARPA_SLFRF_Awarded.csv",
"other": [],
"version_date": "052824",
},
"MS": {
# no data shared yet.
"dedup": "",
"state": "",
"other": [],
"version_date": "052824",
},
"XY":{
"dedup": "",
"state": "",
"other": [],
"version_date":"052824"
}
}
ulfw_data_pre = {
"CA":{
"dedup": "",
"state": "",
"other":[
f"{S3_PATH_ROOT}/California/unzipped/bead_postchallenge/tbcp_indian_tribes_default.csv",
f"{S3_PATH_ROOT}/California/unzipped/bead_postchallenge/tbcp_yurok_default.csv",
],
"version_date":"06162025"
},
"MS":{
"dedup": f"{S3_PATH_ROOT}/Mississippi/unzipped/indicated_locations/dedupe_request_MS_2025-05-28.csv",
"state": "",
"other":[],
"version_date":"05282025"
},
}
cpf_data_post = {"OK": {}, "MS": {}}
data_precha = {
"bead": bead_data_pre,
"cpf": cpf_data_pre,
"ulfw": ulfw_data_pre,
}
data_postcha = {
"bead": bead_data_post,
"cpf": cpf_data_post,
"ulfw": {},
}
[docs]
@staticmethod
def get_funding_data(state: str, program: str, program_stage: str) -> dict:
"""Initialize the class.
state: the state abbreviation
program: the grant program
program_stage: the stage of the program, pre or post
"""
program = program.lower()
state = state.upper()
program_stage = program_stage.lower()
if program_stage == "pre":
return FUNDUtil.data_precha[program][state]
else:
try:
return FUNDUtil.data_postcha[program][state]
except Exception as e:
print(f"Can't find state: {state}, exception: {e}")
return
[docs]
@staticmethod
def bdc_dedup_v3():
"""Deduplication of BDC data."""
v3_s3 = {
"ND": f"{S3_PATH_ROOT}/NorthDakota/ND_Fed_Dedup_BDC_121223.csv",
"LA": f"{S3_PATH_ROOT}/Louisiana/unzipped/LA_Fed_Dedup_v3/LA_Fed_Dedup_BDC_042024.csv",
# "IN": f"{S3_PATH_ROOT}/Indiana/unzipped/IN_State_and_Fed_Dedup/IN_State_and_Fed_Dedup_BDC_020724.csv",
"IN": f"{S3_PATH_ROOT}/Indiana/unzipped/funding_post_challenge/IN_State_and_Fed_Dedup_BDC_111423.csv",
# "AR": f"{S3_PATH_ROOT}/Arkansas/unzipped/dedup/AR_Fed_Dedup_NicksVersion_Feb19.csv", # this file does not share same format
"AR": f"{S3_PATH_ROOT}/Arkansas/unzipped/dedup/AR_Fed_Dedup_NicksVersion_March18.csv",
# "OH": f"{S3_PATH_ROOT}/Ohio/unzipped/Funding Data/dedup_v3/OH_Fed_Dedup_BDC_012324.csv",
"OH": "s3://463002504035-readydatalake/raw/uploads/boss_orgs/2503/OH_Fed_Dedup_BDC_050524-1718297793438.csv",
# "DE": f"{S3_PATH_ROOT}/Delaware/unzipped/Funding/DE_Fed_Dedup_BDC092623/DE_Fed_Dedup_BDC_092623.csv",
"DE": f"{S3_PATH_ROOT}/Delaware/unzipped/Funding/DE_Fed_Dedup_BDC_041624.csv",
"CT": f"{S3_PATH_ROOT}/Connecticut/unzipped/Funding/CT_Fed_Dedup_BDC_010924_0820DOWNLOAD.csv",
"NY": f"{S3_PATH_ROOT}/NewYork/unzipped/Funding/NY_Fed_Dedup_BDC_022024.csv",
"NM": f"{S3_PATH_ROOT}/NewMexico/unzipped/NM_Fed_Dedup_BDC_020724_UpdatedFeb23.csv",
"PA": f"{S3_PATH_ROOT}/Pennsylvania/Federal_Deduplication/PA_Fed_Dedup_BDC_022024.csv",
# "NH": f"{S3_PATH_ROOT}/NewHampshire/Fed Dedup 3.15.24/NH_Fed_Dedup_BDC_030524.csv",
# "OR": f"{S3_PATH_ROOT}/Oregon/unzipped/Funding/OR_Fed_Dedup_BDC_030524.csv",
"OR": f"{S3_PATH_ROOT}/Oregon/unzipped/Funding/OR_Fed_Dedup_BDC_050524.csv",
# "WY": f"{S3_PATH_ROOT}/Wyoming/unzipped/Funding/WY_Fed_Dedup_BDC_030524.csv",
"WY": f"{S3_PATH_ROOT}/Wyoming/unzipped/post_challenge_funding_update/WY_Fed_Dedup_BDC_050524_ISPResponse_Defaults_08262024.csv",
"HI": f"{S3_PATH_ROOT}/Hawaii/unzipped/Funding/Funding Data/HI_Fed_Dedup_BDC_032624.csv",
"CA": f"{S3_PATH_ROOT}/California/unzipped/CA_Fed_Dedup/CA_Fed_Dedup_BDC_040324.csv",
"OK": f"{S3_PATH_ROOT}/Oklahoma/unzipped/OK_Fed_Dedup/OK_Fed_Dedup_BDC_041624.csv", # the client uses the portal uploads, cannot send to dev.
# "MT": f"{S3_PATH_ROOT}/Montana/unzipped/funding/Montana Grant Served locations_fed.csv",
"MT": f"{S3_PATH_ROOT}/Montana/unzipped/CUST-571/MT_Fed_Dedup_BDC/MT_Fed_Dedup_BDC_121223.csv",
# they don't want to use this for anything
}
return v3_s3
[docs]
@staticmethod
def bdc_dedup_v4():
"""Deduplication of BDC data per fabric v4."""
v4_s3 = {
"HI": f"{S3_PATH_ROOT}/Hawaii/unzipped/V4/HI_Fed_Dedup_BDC_051424.csv",
"CA": f"{S3_PATH_ROOT}/California/unzipped/CA_Fed_Dedup/CA_Fed_Dedup_BDC_051424.csv",
"NH": f"{S3_PATH_ROOT}/NewHampshire/unzipped/NH_Fed_Dedup_BDC_052824.csv",
"AL": f"{S3_PATH_ROOT}/Alabama/AL_Fed_Dedup (1)/AL_Fed_Dedup_BDC_051424.csv",
"MS": f"{S3_PATH_ROOT}/Mississippi/unzipped/Funding/MS_Fed_Dedup_BDC_052824.csv",
"OK": f"{S3_PATH_ROOT}/Oklahoma/unzipped/v4/OK_Fed_Dedup_BDC_052824.csv",
"AR": f"{S3_PATH_ROOT}/Arkansas/unzipped/dedup/AR_Fed_Dedup_BDC_061124.csv",
"NY": f"{S3_PATH_ROOT}/NewYork/unzipped/v4/funding/NY_Fed_Dedup_BDC_070924.csv",
"CT": f"{S3_PATH_ROOT}/Connecticut/unzipped/Funding/CT_Fed_Dedup_BDC_070924.csv",
"PA": f"{S3_PATH_ROOT}/Pennsylvania/Federal_Deduplication/PA_Fed_Dedup_BDC_062524.csv",
"ND": f"{S3_PATH_ROOT}/NorthDakota/v3_v4_trueup/ND_Fed_Dedup_BDC_070924.csv",
"NC": f"{S3_PATH_ROOT}/NorthCarolina/Federal_Deduplication/NC_Fed_Dedup_BDC_062524.csv",
"NM": f"{S3_PATH_ROOT}/NewMexico/unzipped/NM_Fed_Dedup_BDC_080624.csv",
"NJ": f"{S3_PATH_ROOT}/NewJersey/unzipped/funding/NJ_Fed_Dedup_BDC_051424.csv",
}
return v4_s3
[docs]
@staticmethod
def bdc_dedup_v2():
"""Deduplication of BDC data."""
v2_s3 = {
"LA": f"{S3_PATH_ROOT}/Louisiana/unzipped/LA_Fed_Dedup_BDC082923.csv",
"NV": f"{S3_PATH_ROOT}/Nevada/unzipped/NV_Fed_Dedup_BDC110723/dedup/NV_Fed_Dedup_BDC110723.csv",
"OH": f"{S3_PATH_ROOT}/Ohio/unzipped/Funding Data/dedup/OH_Fed_Dedup_BDC081623.csv",
"WV": f"{S3_PATH_ROOT}/WestVirginia/unzipped/WV_Fed_Dedup_BDC/dedup/dedup_010424/WV_Fed_Dedup_BDC_2024.01.04.csv",
}
return v2_s3
[docs]
@staticmethod
def state_funding(version="3"):
"""State funding."""
state_funding_v3 = {
"NY": f"{S3_PATH_ROOT}/NewYork/unzipped/Funding/commitment_list_03082024.csv",
# "NY": f"{S3_PATH_ROOT}/NewYork/unzipped/v4/state/post-challenge local commitments 7.24.24.csv",
"NV": f"{S3_PATH_ROOT}/Nevada/unzipped/funding/OSIT Final Deduplication List - final_deduped_list_29437_05042024.csv",
"CT": f"{S3_PATH_ROOT}/Connecticut/unzipped/Funding/CT_grant_served_v3_2_July11_2024_updated.csv",
"OR": f"{S3_PATH_ROOT}/Oregon/unzipped/Funding/Grant-Served locations_20240305.xlsx",
"AR": f"{S3_PATH_ROOT}/Arkansas/unzipped/dedup/AR_StateFundedLocationsList_FabricV3_2_March18_2024.xlsx",
# "WY": f"{S3_PATH_ROOT}/Wyoming/unzipped/Funding/WY_StateGrant_ALL_Locations_ver2_04162024.csv",
"WY": f"{S3_PATH_ROOT}/Wyoming/unzipped/post_challenge_funding_update/WY_StateGrant_ALL_Locations_ver2_04162024.csv",
"NM": f"{S3_PATH_ROOT}/NewMexico/unzipped/StateGrants_PRC_CNM_Project_Areas_NTIA_20240227_V2.geojson",
"OK": f"{S3_PATH_ROOT}/Oklahoma/unzipped/CPF_prepare/ARPA_SLFRF_Awarded.csv",
"DE": f"{S3_PATH_ROOT}/Delaware/unzipped/Funding/DE_ARPA_CARES_GRANTserved_20240422update.csv",
"MT": f"{S3_PATH_ROOT}/Montana/unzipped/funding/Montana Grant Served locations_local.csv",
"AL": f"{S3_PATH_ROOT}/Alabama/Alabama_ABAF_grant_served_locations_revised_July2.xlsx",
"IN": f"{S3_PATH_ROOT}/Indiana/unzipped/funding_post_challenge/NEW_NLC_ICP_ARPA_enforceable_commitments(in).csv",
"NC": f"{S3_PATH_ROOT}/NorthCarolina/NC_Combined_State_And_Local_Funded_Locations_BSLv4_240801.csv",
}
state_funding_v4 = {
"NY": f"{S3_PATH_ROOT}/NewYork/unzipped/v4/state/post-challenge local commitments 7.24.24.csv",
"NV": f"{S3_PATH_ROOT}/Nevada/unzipped/funding/OSIT Final Deduplication List - final_deduped_list_29437_05042024.csv",
"CT": f"{S3_PATH_ROOT}/Connecticut/unzipped/Funding/CT_grant_served_v3_2_July11_2024_updated.csv",
# "AR": f"{S3_PATH_ROOT}/Arkansas/unzipped/dedup/AR_StateFundedLocationsList_FabricV3_2_March18_2024.xlsx",
"AR": f"{S3_PATH_ROOT}/Arkansas/unzipped/dedup/AR_StateFundedLocations_Fabric_v4_providerIDs.csv",
"NM": f"{S3_PATH_ROOT}/NewMexico/unzipped/NM_StateDedup_BSLv4Updated20240822_CNM-PRC.geojson",
"OK": f"{S3_PATH_ROOT}/Oklahoma/unzipped/CPF_prepare/ARPA_SLFRF_Awarded.csv",
"DE": f"{S3_PATH_ROOT}/Delaware/unzipped/Funding/DE_ARPA_CARES_GRANTserved_20240422update.csv",
"MT": f"{S3_PATH_ROOT}/Montana/unzipped/funding/Montana Grant Served locations_local.csv",
"CA": f"{S3_PATH_ROOT}/California/unzipped/funding/CASF_approved.geojson",
"AL": f"{S3_PATH_ROOT}/Alabama/Alabama_ABAF_grant_served_locations_revised_July2.xlsx",
"MS": f"{S3_PATH_ROOT}/Mississippi/unzipped/Funding/MS_CPF_Data_clean.csv",
"NC": f"{S3_PATH_ROOT}/NorthCarolina/NC_Combined_State_And_Local_Funded_Locations_BSLv4_240801.csv",
}
allstate_funding = {
"3": state_funding_v3,
"4": state_funding_v4,
}
return allstate_funding[version]
[docs]
@staticmethod
def state_other_funding(version="3"):
"""State funding. These are additional fundint files that may in different formats"""
cpc_funding_other_v3 = {
# these are additional files shared by the states. These states have additional requests.
"WY": [
# f"{S3_PATH_ROOT}/Wyoming/unzipped/Funding/WY_FedGrant_ALL_Locations_ver3_04262024.csv" -- archived version.
f"{S3_PATH_ROOT}/Wyoming/unzipped/post_challenge_funding_update/WY_ISP_Provided_GrantLocations.xlsx"
],
"MT": [
f"{S3_PATH_ROOT}/Montana/unzipped/funding/MT_Readynet_Updated_EACAM_TriangleTele.csv",
f"{S3_PATH_ROOT}/Montana/unzipped/funding/MT BEAD - missing CPF enforceable commitments - Readynet schema.csv",
],
"LA": [f"{S3_PATH_ROOT}/Louisiana/unzipped/Fiberhood.geojson"],
"IN": [
f"{S3_PATH_ROOT}/Indiana/unzipped/funding_post_challenge/funding_support/"
],
# "NH": f"{S3_PATH_ROOT}/NewHampshire/unzipped/CPF Funded Locations/", # processed in a different way, no need to process here anymore
}
cpc_funding_other_v4 = {
# these are additional files shared by the states. These states have additional requests.
"HI": [f"{S3_PATH_ROOT}/Hawaii/unzipped/V4/Funding/"],
"LA": [f"{S3_PATH_ROOT}/Louisiana/unzipped/Fiberhood.geojson"],
"CA": [
f"{S3_PATH_ROOT}/California/unzipped/funding/PublicHousingAccountProjectLocations_geocoded.xlsx",
# post challenge data update
f"{S3_PATH_ROOT}/California/unzipped/bead_postchallenge/FFA_awarded_projects_08282024.geojson",
],
"MS": [f"{S3_PATH_ROOT}/Mississippi/unzipped/cpf_round2_data_source/"],
"PA": [f"{S3_PATH_ROOT}/Pennsylvania/v3_v4_post_challenge_raw/"],
"OK": [f"{S3_PATH_ROOT}/Oklahoma/unzipped/BEAD_prepare/SLFRFlocations.csv"],
# "NH": f"{S3_PATH_ROOT}/NewHampshire/unzipped/CPF Funded Locations/", # processed in a different way, no need to process here anymore
}
cpc_funding_other = {
"3": cpc_funding_other_v3,
"4": cpc_funding_other_v4,
}
return cpc_funding_other[version]