<jupyter_start><jupyter_text>Creating 2 Tables: Sites for Map, Allocations for Table  General Approach:1) Read in csv sheet of data.  Export fields into new database.2) Remove bad rows and elements.3) Fix date datatype.4) Include ID int value for beneficial use.5) Fix and remove misisng or nan values for allocatoin flow and volume.6) Remove duplicates based on AllocationNativeID.7) Create API link for each siteUUID.8) Export completed df as processed csv. Files Needed1) dontopen_SiteLJAllow.csv, 2) dontopen_AlloLFSite.csv  Sites LF Allow, for creating Site Points, Popups and Legend Colors on Map Only<jupyter_code>#Needed Libaraies
import os
import numpy as np
import pandas as pd

#Setting work directory, reading inputs, creating dataframe
workingDir = "C:/Users/rjame/Documents/WSWC Documents/Portal Creation Research"
os.chdir(workingDir)
#Sites LeftJoin Allo Input
SitefileInput = "dontopen_SiteLJAllo.csv"
dfsite = pd.read_csv(SitefileInput)
dfsite
#removeing bad rows of df because they are null for some odd reason
dfsite = dfsite[(dfsite.WaterSourceName != 'test')]
dfsite = dfsite[dfsite['SiteUUID'].notna()]
# dfsite = dfsite[dfsite['AllocationPriorityDateID'].notna()]
dfsite = dfsite[dfsite['WaDENameBenUse'].notna()]
dfsite = dfsite[dfsite['Longitude'].notna()]
dfsite = dfsite[dfsite['Latitude'].notna()]
dfsite.reset_index()
#AllocationOwner
AllocationOwnerDic = {
"DREAMWORK MANAGEMENT INC; U.S.-FOREST SERVICE; <NAME>." : "US Forest Service",
"FOREST SERVICE LINE SPG" : "US Forest Service",
"FOREST SERVICE SPRING PIPELINE" : "US Forest Service",
"FOREST SERVICE,UNITED STATES" : "US Forest Service",
"FOREST SERVICE,UNITED STATES OF AMERICA" : "US Forest Service",
"Forest Service,United States of America -" : "US Forest Service",
"FOREST SERVICE,USDA" : "US Forest Service",
"MAGDALENA RANGER DISTRICT,US Forest Service" : "US Forest Service",
"N.D. Forest Service" : "US Forest Service",
"Rowser and et al.,Lillian C.,USA Forest Service,Rasmussen,Roger J." : "US Forest Service",
"Rowser and et al.,Lillian C.,USA Forest Service,Rasmusson,Roger J.,Hood,E. Ward & Linnea" : "US Forest Service",
"SANTA FE NATIONAL FOREST,USDA FOREST SERVICE" : "US Forest Service",
"State of Utah Division of Wildlife Resources,USA Forest Service" : "US Forest Service",
"Steed,Newel,USA Forest Service" : "US Forest Service",
"TEXAS A&M FOREST SERVICE" : "US Forest Service",
"U S FOREST SERVICE" : "US Forest Service",
"U S Forest Service" : "US Forest Service",
"U. S. FOREST SERVICE" : "US Forest Service",
"U. S. Forest Service, Lassen National Forest, Hat Creek Ranger District" : "US Forest Service",
"U.S. Forest Service" : "US Forest Service",
"U.S. FOREST SERVICE" : "US Forest Service",
"U.S. FOREST SERVICE" : "US Forest Service",
"U.S. FOREST SERVICE; APPLEGATE RANGER DISTRICT" : "US Forest Service",
"U.S. FOREST SERVICE; BEND RANGER DISTRICT" : "US Forest Service",
"U.S. FOREST SERVICE; COLUMBIA RIVER GORGE SCENIC AREA" : "US Forest Service",
"U.S. FOREST SERVICE; MIDDLE FORK RANGER DISTRICT" : "US Forest Service",
"U.S.-FOREST SERVICE" : "US Forest Service",
"U.S.-FOREST SERVICE; U.S.-AGRICULTURE DEPARTMENT" : "US Forest Service",
"UNITED STATES FOREST SERVICE" : "US Forest Service",
"UNITED STATES FOREST SERVICE" : "US Forest Service",
"United States Of America Acting Through Usda Forest Service" : "US Forest Service",
"UNITED STATES OF AMERICA FOREST SERVICE" : "US Forest Service",
"UNITED STATES OF AMERICA, FOREST SERVICE" : "US Forest Service",
"US Forest Service" : "US Forest Service",
"US Forest Service" : "US Forest Service",
"US Forest Service Mount Baker," : "US Forest Service",
"US Forest Service NW International," : "US Forest Service",
"US Forest Service Snoqualmie," : "US Forest Service",
"US Forest Service Wenatchee," : "US Forest Service",
"US Forest Service," : "US Forest Service",
"US Forest Service, LOS PADRES NATIONAL FOREST" : "US Forest Service",
"USA FOREST SERVICE" : "US Forest Service",
"USA Forest Service" : "US Forest Service",
"USA Forest Service - Dixie National Forest" : "US Forest Service",
"USA Forest Service - Dixie National Service" : "US Forest Service",
"USA Forest Service - Fishlake National Forest" : "US Forest Service",
"USA Forest Service / Fishlake National Forest" : "US Forest Service",
"USA Forest Service,Ackerman,Wilford" : "US Forest Service",
"USA Forest Service,Dixie National Forest,USA FOREST SERVICE" : "US Forest Service",
"USA Forest Service,Gardner,<NAME>,Gardner,<NAME>.,<NAME>,Gardner,<NAME>." : "US Forest Service",
"USA Forest Service,Heirs of E. <NAME>" : "US Forest Service",
"USA Forest Service,Jones,Richard N." : "US Forest Service",
"USA Forest Service,USA Forest Service 2" : "US Forest Service",
"USA Forest Service,USA Forestt Service" : "US Forest Service",
"USA Forest Service,USA Forsest Service" : "US Forest Service",
"USA Forest Service." : "US Forest Service",
"USA National Forest 2,USA Forest Service" : "US Forest Service",
"USA USDA FOREST SERVICE" : "US Forest Service",
"USDA FOREST SERVICE" : "US Forest Service",
"USDA Forest Service" : "US Forest Service",
"USDA FOREST SERVICE CIBOLA NF" : "US Forest Service",
"USDA FOREST SERVICE LAKE TAHOE BASIN MANAGEMENT UNIT" : "US Forest Service",
"USDA FOREST SERVICE(CARSON NF)" : "US Forest Service",
"USDA Forest Service," : "US Forest Service",
"USDA FOREST SERVICE, SEQUOIA NATIONAL FOREST" : "US Forest Service",
"USDA Forest Service, USA" : "US Forest Service",
"USDA FOREST SERVICE,CARSON NF" : "US Forest Service",
"USDA FOREST SERVICE; DESCHUTES NATIONAL FOREST" : "US Forest Service",
"USDA FOREST SERVICES" : "US Forest Service",
"USDA, FOREST SERVICE,CARSON NF" : "US Forest Service",
"West Panguitch Irrigation and Reservoir Company,USA Forest Service" : "US Forest Service",
"Department of Energy,United States of America" : "US Department of Energy",
"M-K Ferguson Co. for USA Department of Energy" : "US Department of Energy",
"MK-Ferguson Co. for the USA Department of Energy" : "US Department of Energy",
"U S DEPARTMENT OF ENERGY" : "US Department of Energy",
"U.S. Department of Energy" : "US Department of Energy",
"U.S.-DEPARTMENT OF ENERGY,  YUCCA MOUNTAIN SITE CHARACTERIZATION PROJECT" : "US Department of Energy",
"U.S-DEPARTMENT OF ENERGY, YUCCA MOUNTAIN SITE CHARACTERIZATION PROJECT" : "US Department of Energy",
"UNITED STATES DEPARTMENT OF ENERGY" : "US Department of Energy",
"United States of America,U.S. Department of Energy" : "US Department of Energy",
"US Department of Energy," : "US Department of Energy",
"USA Department of Energy Grand Junction Projects" : "US Department of Energy",
"Bankhead,S. Clair,UT Reclamation Mitigation and Conservation Comm,United States of America Bureau of Reclamation,Corbridge,William B." : "US Bureau Reclamation",
"Bryan, <NAME> & Jennifer,Fletcher, <NAME>,Teanaway Associates,,Perkins, Charles J & Catherine,US Bureau Reclamation - Yakima," : "US Bureau Reclamation",
"Bureau Of Reclamation" : "US Bureau Reclamation",
"BUREAU OF RECLAMATION" : "US Bureau Reclamation",
"Bureau Of Reclamation," : "US Bureau Reclamation",
"Bureau of Reclamation, Provo Area Office,United States of America,Richardson,<NAME>.,<NAME>,Richardson,<NAME>.,Sandy City,Richardson,Duane G." : "US Bureau Reclamation",
"Bureau of Reclamation,The Secretary of the Interior" : "US Bureau Reclamation",
"Bureau of Reclamation,United State of America" : "US Bureau Reclamation",
"BUREAU OF RECLAMATION,UNITED STATES D.O.I." : "US Bureau Reclamation",
"Bureau of Reclamation,United States of America" : "US Bureau Reclamation",
"Bureau of Reclamation,United States of America," : "US Bureau Reclamation",
"Bureau of Reclamation,United States of America,Corbridge,William B." : "US Bureau Reclamation",
"BUREAU OF RECLAMATION; UMATILLA FIELD OFFICE" : "US Bureau Reclamation",
"Heart K Ranch,,US Bureau Reclamation - Yakima," : "US Bureau Reclamation",
"Hirst,Paul and Bonnie,Weber River Water Users Association,USA Bureau of Reclamation" : "US Bureau Reclamation",
"Lambert,Paul,USA Bureau of Reclamation" : "US Bureau Reclamation",
"Morgan County,Weber River Water Users` Assocation,USA Bureau of Reclamation" : "US Bureau Reclamation",
"Provo Area Office,U.S.A. Bureau of Reclamation" : "US Bureau Reclamation",
"Provo Area Office,USA Bureau of Reclamation" : "US Bureau Reclamation",
"Remund,Glen & Unice,United States of America Bureau of Reclamation" : "US Bureau Reclamation",
"Smith,Clarence,Siddoway,Benjamin and Beryl,Vernon,James,Vernon,Francis,Gines,<NAME>. and <NAME>.,USA Bureau of Reclamation,Smith,Dick,Siddoway,Al<NAME> and LouDean,Crandall Creek Irrigation Company" : "US Bureau Reclamation",
"U S BUREAU OF RECLAMATION" : "US Bureau Reclamation",
"U. S. DEPT. OF THE INTERIOR,BUREAU OF RECLAMATION" : "US Bureau Reclamation",
"U.S. BUREAU OF RECLAMATION" : "US Bureau Reclamation",
"U.S. Bureau Of Reclamation" : "US Bureau Reclamation",
"U.S. BUREAU OF RECLAMATION" : "US Bureau Reclamation",
"U.S. BUREAU OF RECLAMATION" : "US Bureau Reclamation",
"U.S.A. Bureau of Reclamation" : "US Bureau Reclamation",
"U.S.A. Bureau of Reclamation,Fitzgerald,Jackson R." : "US Bureau Reclamation",
"U.S.-BUREAU OF RECLAMATION" : "US Bureau Reclamation",
"United States Bureau of Reclamation" : "US Bureau Reclamation",
"United States of America Bureau of Reclamation,<NAME> and <NAME> Family Trust" : "US Bureau Reclamation",
"United States of America, Bureau of Reclamation" : "US Bureau Reclamation",
"US BUREAU OF RECLAMATION" : "US Bureau Reclamation",
"US Bureau of Reclamation" : "US Bureau Reclamation",
"US Bureau of Reclamation - Boise," : "US Bureau Reclamation",
"US Bureau Of Reclamation," : "US Bureau Reclamation",
"USA Bureau of Reclamation" : "US Bureau Reclamation",
"USA Bureau of Reclamation -- Provo Area Office" : "US Bureau Reclamation",
"USA Bureau of Reclamation,Boyden,John" : "US Bureau Reclamation",
"USA Bureau of Reclamation,Ivie,James" : "US Bureau Reclamation",
"USA Bureau of Reclamation,Miller,Max" : "US Bureau Reclamation",
"USA Bureau of Reclamation,Weber River Water Users Association,Sanders Properties" : "US Bureau Reclamation",
"UT Reclamation Mitigation and Conservation Comm,United States of America Bureau of Reclamation,Bankhead,S. Clair,Corbridge,<NAME>" : "US Bureau Reclamation",
"Weber River Water Users` Association,Morgan County,USA Bureau of Reclamation" : "US Bureau Reclamation",
"Wilde Revocable Trust,Debra A.,Wilde Land and Livestock Company,Wilde `A` Ditch Company,Faddis,Andrew,Wilde,Henry,Wilson,John,Wilde,<NAME>,USA Bureau of Reclamation,Beech,Thomas L.,Wilde Revocable Trust,<NAME>,Wilde,Mary" : "US Bureau Reclamation",
"Wilkinson,Allen,United States of America Bureau of Reclamation" : "US Bureau Reclamation",
"<NAME>.,USA Bureau of Reclamation,Eldredge,Edmund,Robinson,<NAME>. and <NAME>,North Chalk Creek Irrigation Company" : "US Bureau Reclamation",
" - Cedar City Field Office,US Bureau of Land Management" : "US Bureau of Land Management",
" - Cedar City Field Office,US Department of Interior, Bureau of Land Management" : "US Bureau of Land Management",
" - Cedar City Field Office,US Department of the Interior, Bureau of Land Management" : "US Bureau of Land Management",
" - Cedar City Field Office,USDI Bureau of Land Management" : "US Bureau of Land Management",
"#NAME?,US Department of Interior, Bureau of Land Management" : "US Bureau of Land Management",
"BUREAU OF LAND MANAGEMENT" : "US Bureau of Land Management",
"BUREAU OF LAND MANAGEMENT" : "US Bureau of Land Management",
"Bureau of Land Management" : "US Bureau of Land Management",
"Bureau of Land Management," : "US Bureau of Land Management",
"Bureau of Land Management, Bakersfield" : "US Bureau of Land Management",
"Bureau of Land Management,Box Elder County" : "US Bureau of Land Management",
"Bureau of Land Management,Davis,<NAME> (II) and Shawnee,Tebbs Living Trust,<NAME> and <NAME>." : "US Bureau of Land Management",
"Bureau of Land Management,Flinspach,<NAME>" : "US Bureau of Land Management",
"BUREAU OF LAND MANAGEMENT,SOCORRO FIELD OFFICE" : "US Bureau of Land Management",
"Bureau of Land Management,Stuntz Valley Ranch LC" : "US Bureau of Land Management",
"Bureau of Land Management,T Y Cattle Company" : "US Bureau of Land Management",
"BUREAU OF LAND MANAGEMENT,UNITED STATES" : "US Bureau of Land Management",
"BUREAU OF LAND MANAGEMENT,UNITED STATES GOVERNMENT" : "US Bureau of Land Management",
"BUREAU OF LAND MANAGEMENT,UNITED STATES OF AMERICA" : "US Bureau of Land Management",
"BUREAU OF LAND MANAGEMENT,US DEPARTMENT OF INTERIOR" : "US Bureau of Land Management",
"BUREAU OF LAND MANAGEMENT,US DEPT LAND MANAGEMENT" : "US Bureau of Land Management",
"BUREAU OF LAND MANAGEMENT,US DEPT OF INTERIOR" : "US Bureau of Land Management",
"BUREAU OF LAND MANAGEMENT,USDI" : "US Bureau of Land Management",
"BUREAU OF LAND MANAGEMENT; OWYHEE FIELD OFFICE" : "US Bureau of Land Management",
"Carbon County,USA Bureau of Land Management" : "US Bureau of Land Management",
"Cedar City Field Office,USDI Bureau of Land Management;" : "US Bureau of Land Management",
"Chew Livestock Inc,Bureau of Land Management" : "US Bureau of Land Management",
"Department of Interior Bureau of Land Management" : "US Bureau of Land Management",
"Department of Natural Resources,State of Utah,,USA Bureau of Land Management" : "US Bureau of Land Management",
"Division of Wildlife Resources,State of Utah,,USA Bureau of Land Management,Price Field Office" : "US Bureau of Land Management",
"Gemstone Mining Inc.,USA Bureau of Land Management" : "US Bureau of Land Management",
"Grouse Creek Ranch LC,USA Bureau of Land Management" : "US Bureau of Land Management",
"<NAME>,USA Bureau of Land Management" : "US Bureau of Land Management",
"Materials Energy Research and Recovery,USA Bureau of Land Management" : "US Bureau of Land Management",
"Moab District USA Bureau of Land Management" : "US Bureau of Land Management",
"<NAME>.,USA Bureau of Land Management" : "US Bureau of Land Management",
"NORTHERN WASHOE RANCHING MANAGEMENT, LLC; DUCK LAKE RANCH, LLC; U.S.-BUREAU OF LAND MANAGEMENT" : "US Bureau of Land Management",
"NORTHERN WASHOE RANCHING MANAGEMENT, LLC; U.S.-BUREAU OF LAND MANAGEMENT; DUCK LAKE RANCH, LLC" : "US Bureau of Land Management",
"Richfield District USA Bureau of Land Management" : "US Bureau of Land Management",
"Saint George Field Office,USDI Bureau of Land Management" : "US Bureau of Land Management",
"Saint George Field Office,USDI Bureau of Land Management;" : "US Bureau of Land Management",
"Skull Valley Company Ltd.,USA Bureau of Land Management" : "US Bureau of Land Management",
"Staker,<NAME>,USA Bureau of Land Management" : "US Bureau of Land Management",
"State of Utah Division of Wildlife Resources,USA Bureau of Land Management" : "US Bureau of Land Management",
"Thousand Peaks Ranches, Inc.,USA Bureau of Land Management,Richfield District" : "US Bureau of Land Management",
"U S BUREAU OF LAND MANAGEMENT" : "US Bureau of Land Management",
"U.S. Bureau Of Land Management" : "US Bureau of Land Management",
"U.S. BUREAU OF LAND MANAGEMENT" : "US Bureau of Land Management",
"U.S. DEPARTMENT OF THE INTERIOR; BUREAU OF LAND MANAGEMENT" : "US Bureau of Land Management",
"U.S.-BUREAU OF LAND MANAGEMENT" : "US Bureau of Land Management",
"U.S.-BUREAU OF LAND MANAGEMENT; BRIGHT-HOLLAND CORPORATION" : "US Bureau of Land Management",
"U.S.-BUREAU OF LAND MANAGEMENT; WHITE PINE LUMBER CO." : "US Bureau of Land Management",
"U.S.D.I., Bureau of Land Management" : "US Bureau of Land Management",
"United States Of America Acting Through The Department Of Interior Bureau Of Land Management" : "US Bureau of Land Management",
"United States of America Bureau of Land Management,Fillmore District" : "US Bureau of Land Management",
"UNITED STATES OF AMERICA, DEPARTMENT OF THE INTERIOR, BUREAU OF LAND MANAGEMENT; WULFENSTEIN TRUST R-501; SPRING MOUNTAIN MOTORSPORTS RANCH; PAHRUMP GATEWAY UTILITY; SPRING MOUNTAIN RACEWAY LLC" : "US Bureau of Land Management",
"US BUREAU OF LAND MANAGEMENT" : "US Bureau of Land Management",
"US BUREAU OF LAND MANAGEMENT" : "US Bureau of Land Management",
"US BUREAU OF LAND MANAGEMENT / BARSTOW FIELD OFFICE" : "US Bureau of Land Management",
"US BUREAU OF LAND MANAGEMENT / PALM SPRINGS FIELD OFFICE" : "US Bureau of Land Management",
"US BUREAU OF LAND MANAGEMENT, ALTURAS FIELD OFFICE" : "US Bureau of Land Management",
"US BUREAU OF LAND MANAGEMENT, RIDGECREST FIELD OFFICE" : "US Bureau of Land Management",
"US BUREAU OF LAND MANAGEMENT, UKIAH FIELD OFFICE" : "US Bureau of Land Management",
"US BUREAU OF LAND MANAGEMENT/ ARCATA FIELD OFFICE" : "US Bureau of Land Management",
"US BUREAU OF LAND MANAGEMENT/NEEDLES FIELD OFFICE" : "US Bureau of Land Management",
"US DEPT OF THE INTERIOR,BUREAU OF LAND MANAGEMENT" : "US Bureau of Land Management",
"USA Bureau of Land Management" : "US Bureau of Land Management",
"USA Bureau of Land Management - Filmore Field Office" : "US Bureau of Land Management",
"USA Bureau of Land Management (Richfield District)" : "US Bureau of Land Management",
"USA Bureau of Land Management Vernal District" : "US Bureau of Land Management",
"USA Bureau of Land Management, Richfield District" : "US Bureau of Land Management",
"USA Bureau of Land Management,Cedar City District" : "US Bureau of Land Management",
"USA Bureau of Land Management,Fillmore District" : "US Bureau of Land Management",
"USA Bureau of Land Management,Fillmore Field Office" : "US Bureau of Land Management",
"USA Bureau of Land Management,Kanab District" : "US Bureau of Land Management",
"USA Bureau of Land Management,Kanab Field Office" : "US Bureau of Land Management",
"USA Bureau of Land Management,Moab District" : "US Bureau of Land Management",
"USA Bureau of Land Management,Monticello  District" : "US Bureau of Land Management",
"USA Bureau of Land Management,Monticello District" : "US Bureau of Land Management",
"USA Bureau of Land Management,Ogden District" : "US Bureau of Land Management",
"USA Bureau of Land Management,Price District" : "US Bureau of Land Management",
"USA Bureau of Land Management,Price Field Office" : "US Bureau of Land Management",
"USA Bureau of Land Management,Richfield District" : "US Bureau of Land Management",
"USA Bureau of Land Management,Vernal District" : "US Bureau of Land Management",
"USDI BUREAU OF LAND MANAGEMENT" : "US Bureau of Land Management",
"USDI BUREAU OF LAND MANAGEMENT" : "US Bureau of Land Management",
"Utah School and Institutional Trust Lands Admin.,USA Bureau of Land Management" : "US Bureau of Land Management",
"West Panguitch Irrigation and Reservoir Company,State of Utah Board of Water Resources,USA Bureau of Land Management" : "US Bureau of Land Management",
"WHITE PINE LUMBER CO; U.S.-BUREAU OF LAND MANAGEMENT" : "US Bureau of Land Management",
"ARMY CAMP SPRING" : "US Army",
"CORPS OF ENGRS. ABIQUIU DAM,US ARMY ENGR DIST ALBQ." : "US Army",
"Cummins, Dale F,US Army Corps Of Engineers," : "US Army",
"Dept Of The Army Corps Of Engineers," : "US Army",
"LONGHORN ARMY AMMUNITION PLANT,U.S. DEPARTMENT OF THE INTERIOR FISH AND WILDLIFE SERVICE" : "US Army",
"U S ARMY CORPS OF ENGINEERS" : "US Army",
"U S Army Corps of Engineers" : "US Army",
"U S Army, Department of" : "US Army",
"U.S Army Corps Of Engineers" : "US Army",
"U.S. ARMY CORP OF ENGINEERS" : "US Army",
"U.S. Army Corps Of Engineers" : "US Army",
"U.S. ARMY CORPS OF ENGINEERS" : "US Army",
"U.S. ARMY CORPS OF ENGINEERS SEATTLE" : "US Army",
"U.S. ARMY CORPS OF ENGINEERS WALLA WALLA" : "US Army",
"U.S. ARMY CORPS OF ENGINEERS, WALLA WALLA DISTRICT" : "US Army",
"U.S. ARMY CORPS OF ENGINEERS; PORTLAND DISTRICT" : "US Army",
"U.S. ARMY CORPS OF ENGINEERSSEATTLE" : "US Army",
"U.S. ARMY CORPS OF ENGINEERSWALLA WALLA" : "US Army",
"U.S. Army Dugway Proving Ground" : "US Army",
"U.S. ARMY WHITE SANDS MISSILE" : "US Army",
"U.S. ARMY-WHITE SANDS MISSILE" : "US Army",
"UNITED STATES ARMY CORPS OF ENGINEERS" : "US Army",
"US ARMY" : "US Army",
"Us Army Corps Of Engineers" : "US Army",
"US ARMY CORPS OF ENGINEERS" : "US Army",
"US Army Corps Of Engineers," : "US Army",
"US ARMY CORPS OF ENGINGEERS" : "US Army",
"US DEPARTMENT OF THE ARMY" : "US Army",
"USA Army Dugway Proving Ground" : "US Army",
"USA Army Dugway Proving Grounds" : "US Army",
"USA Army Engineer District Corps of Engineers" : "US Army",
"USA Army for Dugway Proving Ground" : "US Army",
"USA Army Ogden Sub-Depot of Tooele Ordinance Depot" : "US Army",
"USA Army Tooele Army Depot South" : "US Army",
"USA Department of the Army" : "US Army",
"USA Department of the Army - Dugway Proving Ground" : "US Army",
"USA Department of the Army - Tooele Army Depot" : "US Army",
"USA Department of the Army Arsenal" : "US Army",
"USARMY Corp Of Engineers," : "US Army",
"USARMY Corps Engineers Columbia Cnty," : "US Army",
"USARMY Corps Engineers Seattle," : "US Army",
"USARMY Corps Engineers Walla Walla," : "US Army",
"USARMY Corps Engineers," : "US Army",
"USARMY Corps Engineers,,USDA Agricultural Research Service WRPIS," : "US Army",
"WA Fish & Wildlife Dept - ERO,,USARMY Corps Engineers," : "US Army",
"Camelot International Resort Corporation,United States of America" : "United States of America",
"Christensen,<NAME>.,Fish and Wildlife Service,United States of America" : "United States of America",
"CIBOLA NATIONAL FOREST,UNITED STATES OF AMERICA," : "United States of America",
"Ekker,Sheena,Utah School and Institutional Trust Lands Admin.,Hodges,Jimmy,McArthur,Ty,United States of America" : "United States of America",
"Fish and Wildlife Service,United States of America" : "United States of America",
"Fish and Wildlife Service,United States of America," : "United States of America",
"INTERMOUNTAIN LAND CO.; MOONROCKS GATEWAY LLC; UNITED STATES OF AMERICA" : "United States of America",
"LAMB, <NAME>. & <NAME>.; UNITED STATES OF AMERICA" : "United States of America",
"Llewellyn,<NAME>.,United States of America" : "United States of America",
"<NAME>; UNITED STATES OF AMERICA" : "United States of America",
"The United States of America" : "United States of America",
"THE UNITED STATES OF AMERICA IN TRUST FOR THE PYRAMID LAKE PAIUTE TRIBE; GARAVANTA LAND AND LIVESTOCK COMPANY SUCCESSOR TO J. GARAVANTA" : "United States of America",
"<NAME>.; <NAME>.; UNITED STATES OF AMERICA" : "United States of America",
"United States Of America" : "United States of America",
"UNITED STATES OF AMERICA" : "United States of America",
"UNITED STATES OF AMERICA" : "United States of America",
"United States of America" : "United States of America",
"UNITED STATES OF AMERICA (BLM)" : "United States of America",
"United States Of America Acting Through" : "United States of America",
"United States of America in trust for <NAME>,Burton,<NAME>" : "United States of America",
"UNITED STATES OF AMERICA IN TRUST FOR SUCH SHOSHONE INDIANS SOUTHERN NEVADA" : "United States of America",
"UNITED STATES OF AMERICA IN TRUST FOR THE WASHOE TRIBE OF INDIANS" : "United States of America",
"UNITED STATES OF AMERICA IN TRUST FOR YOMBA TRIBE OF SHOSHONE INDIANS" : "United States of America",
"UNITED STATES OF AMERICA NAS" : "United States of America",
"UNITED STATES OF AMERICA NAVAL AIR STATION FALLON" : "United States of America",
"United States Of America Trustee" : "United States of America",
"United States Of America," : "United States of America",
"UNITED STATES OF AMERICA,  C/O COMMANDER AND DISTRICT ENGINEER,   UNITED STATES ARMY ENGINEER  DISTRICT, LOUISVILLE" : "United States of America",
"United States of America, Utah" : "United States of America",
"United States of America,BBRD, LC,Ottesen,Nellie" : "United States of America",
"United States of America,Owen Probst and Rosetta B. Probst Trust" : "United States of America",
"United States of America,The,Conger,Wm. B. and Florance H" : "United States of America",
"United States of America,Virgin River Land Preservation Inc." : "United States of America",
"UNITED STATES OF AMERICA; STREETS; UNITED STATES OF AMERICA IN TRUST FOR THE RENO SPARKS INDIAN COLONY; NEVADA-DEPARTMENT OF TRANSPORTATION; SIERRA PACIFIC POWER COMPANY; RENO SPARKS INDIAN COLONY; RENO LODGE #13 F AND A M" : "United States of America",
"United States of American" : "United States of America",
"USA-United States Of America" : "United States of America",
"Utah Reclamation Mitigation and Conservation Commission,United States Of America" : "United States of America",
"WASHOE COUNTY; PRATT, TOM & LANNETTE; UNITED STATES OF AMERICA" : "United States of America",
"WS-BUCKLAND, LLC; UNITED STATES OF AMERICA" : "United States of America"}
 
def assignAllocationOwner(colrowValue):
    if colrowValue == '' or pd.isnull(colrowValue):
        return "Other"
    else:
        String1 = colrowValue.strip()  # remove whitespace chars
        try:
            outList = AllocationOwnerDic[String1]
        except:
            outList = "Other"
    return outList

dfsite['AllocationOwner'] = dfsite.apply(lambda row: assignAllocationOwner(row['AllocationOwner']), axis=1)
#Color for WBenUse for the Site Map per point
#Mapdeck uses a build color pacakge called 'colourvalues library', which does use HEX color code, but has to end in an "FF".
WSWCBenUseColorDict = { 
"Agricultural" : "#006400FF",
"Aquaculture" : "#9ACD32FF",
"Commercial" : "#FFFF00FF",
"Domestic" : "#0000FFFF",
"Environmental" : "#32CD32FF",
"Fire" : "#FF4500FF",
"Fish" : "#9370DBFF",
"Flood Control" : "#00FFFFFF",
"Heating and Cooling" : "#FF69B4FF",
"Industrial" : "#800080FF",
"Instream Flow" : "#00BFFFFF",
"Livestock" : "#FFD700FF",
"Mining" : "#A52A2AFF",
"Municipal" : "#4B0082FF",
"Power" : "#FFA500FF",
"Recharge" : "#D2691EFF",
"Recreation" : "#FFC0CBFF",
"Snow Making" : "#F0FFF0FF",
"Storage" : "#F5DEB3FF",
"Wildlife" : "#FF0000FF",
"Other" : "#808080FF",
"Unknown" : "#D3D3D3FF"}
 
def assignWSWCBUColor(colrowValue):
    if colrowValue == '' or pd.isnull(colrowValue):
        return "#D3D3D3FF"
    else:
        String1 = colrowValue.strip()  # remove whitespace chars
        try:
            outList = WSWCBenUseColorDict[String1]
        except:
            outList = "#D3D3D3FF"
    return outList

dfsite['WBenUseColor'] = dfsite.apply(lambda row: assignWSWCBUColor(row['WaDENameBenUse']), axis=1)
#Color for SiteType for the Site Map per point
#Mapdeck uses a build color pacakge called 'colourvalues library', which does use HEX color code, but has to end in an "FF".
WSWCSiteTypeColorDict = {
"Abandoned" : "#FFC0CBFF",
"Aquifer" : "#FF1493FF",
"Canal / Ditch / Stream" : "#191970FF",
"Discharge Point" : "#EE82EEFF",
"Diversion Point" : "#BA55D3FF",
"Drain" : "#8A2BE2FF",
"Effluent" : "#800080FF",
"Exchange Plan" : "#483D8BFF",
"Gravity" : "#4B0082FF",
"Groundwater" : "#FFA07AFF",
"Lake" : "#F08080FF",
"Mine" : "#FF0000FF",
"Minimum Flow" : "#FFA500FF",
"Monitoring Well" : "#FF6347FF",
"Other" : "#FFFF00FF",
"Pipeline" : "#FAFAD2FF",
"Point of Rediversion" : "#EEE8AAFF",
"Point of Return" : "#ADFF2FFF",
"Power Plant" : "#32CD32FF",
"Pump" : "#00FF7FFF",
"Reach" : "#228B22FF",
"Recharge Area" : "#9ACD32FF",
"Reservoir" : "#800000FF",
"Seep" : "#20B2AAFF",
"Spring" : "#00FFFFFF",
"Storage" : "#AFEEEEFF",
"Sump" : "#48D1CCFF",
"Surface & Groundwater" : "#808000FF",
"Surface Water" : "#4682B4FF",
"Unknown" : "#D3D3D3FF",
"Well" : "#6495EDFF",
"winter runoff" : "#1E90FFFF"}
 
def assignWSWCSiteTypeColor(colrowValue):
    if colrowValue == '' or pd.isnull(colrowValue):
        return "#D3D3D3FF"
    else:
        String1 = colrowValue.strip()  # remove whitespace chars
        try:
            outList = WSWCSiteTypeColorDict[String1]
        except:
            outList = "#D3D3D3FF"
    return outList

dfsite['SiteTypeColor'] = dfsite.apply(lambda row: assignWSWCSiteTypeColor(row['WaDENameS']), axis=1)
#Color for WaterSourceType for the Site Map per point
#Mapdeck uses a build color pacakge called 'colourvalues library', which does use HEX color code, but has to end in an "FF".
WSWCWaterSourceTypeColorDict = {
"Groundwater" : "#008000FF",
"Other" : "#FFA500FF",
"Reservoir / Storage" : "#FFFF00FF",
"Surface & Groundwater" : "#800080FF",
"Surface Water" : "#FF4500FF",
"Unknown" : "#0000FFFF"}
 
def assignWSWCWaterSourceTypeColor(colrowValue):
    if colrowValue == '' or pd.isnull(colrowValue):
        return "#D3D3D3FF"
    else:
        String1 = colrowValue.strip()  # remove whitespace chars
        try:
            outList = WSWCWaterSourceTypeColorDict[String1]
        except:
            outList = "#D3D3D3FF"
    return outList

dfsite['WaterSourceTypeColor'] = dfsite.apply(lambda row: assignWSWCWaterSourceTypeColor(row['WaDENameWS']), axis=1)
#Color for Owner for the Site Map per point
#Mapdeck uses a build color pacakge called 'colourvalues library', which does use HEX color code, but has to end in an "FF".
WSWCOwnerColorDict = { 
"US Forest Service" : "#006400FF",
"US Department of Energy" : "#FFA500FF",
"US Bureau Reclamation" : "#FFFF00FF",
"US Bureau of Land Management" : "#800080FF",
"US Army" : "#FF4500FF",
"United States of America" : "#0000FFFF",
"Other" : "#D3D3D3FF"}
 
def assignWSWCOwnerColor(colrowValue):
    if colrowValue == '' or pd.isnull(colrowValue):
        return "#D3D3D3FF"
    else:
        String1 = colrowValue.strip()  # remove whitespace chars
        try:
            outList = WSWCOwnerColorDict[String1]
        except:
            outList = "#D3D3D3FF"
    return outList

dfsite['OwnerColor'] = dfsite.apply(lambda row: assignWSWCOwnerColor(row['AllocationOwner']), axis=1)
#Sort by SiteUUID and BenUse (this way Agriculture should show up first in the below list)
dfsite = dfsite.sort_values(by=['SiteUUID', 'WaDENameBenUse'], ascending=True).reset_index()
dfsite
## Create Sites Dataframe - with attached Allo and Benuse info ##
########################################
outdfSite = pd.DataFrame()

#The Columns
outdfSite['SiteUUID'] = dfsite['SiteUUID'].astype(str) 
outdfSite['Lat'] = dfsite['Latitude'].astype(float) 
outdfSite['Long'] = dfsite['Longitude'].astype(float)
outdfSite['WBenUse'] = dfsite['WaDENameBenUse'].astype(str)
outdfSite['WBenUseColor'] = dfsite['WBenUseColor'].astype(str)
outdfSite['SiteTypeColor'] = dfsite['SiteTypeColor'].astype(str)
outdfSite['WaterSourceTypeColor'] = dfsite['WaterSourceTypeColor'].astype(str)
outdfSite['OwnerColor'] = dfsite['OwnerColor'].astype(str)


#Things we don't actually need for the site csv, but will need for the allowcation.
# outdfSite['SiteTypeCV'] = dfsite['SiteTypeCV'].astype(str)
# outdfSite['WaterSourceTypeCV'] = dfsite['WaterSourceTypeCV'].astype(str)
# outdfSite['AllocationOwner'] = dfsite['AllocationOwner'].astype(str)

#Drop Duplicates - SiteUUID
outdfSite = outdfSite.drop_duplicates(['SiteUUID'], keep="first").reset_index(drop=True)

outdfSite
#Exporting Outbound DataFrame to working csv file.
outdfSite.to_csv('P_SiteLJAllo.csv', index=False)  # The output<jupyter_output><empty_output><jupyter_text>Allo LF Site, for filtering and Table Return Only<jupyter_code>#Needed Libaraies
import os
import numpy as np
import pandas as pd

#Setting work directory, reading inputs, creating dataframe
workingDir = "C:/Users/rjame/Documents/WSWC Documents/Portal Creation Research"
os.chdir(workingDir)
#Allo LeftJoin Sites Input
AllofileInput = "dontopen_AlloLJSite.csv"
dfallo = pd.read_csv(AllofileInput)
dfallo
#removeing bad rows of df because they are null for some odd reason
dfallo = dfallo[(dfallo.WaterSourceName != 'test')]
dfallo = dfallo[dfallo['SiteUUID'].notna()]
dfallo = dfallo[dfallo['WaDENameBenUse'].notna()]
dfallo.reset_index()
#changing data type of 'Date' to datatype, then changing format of 'Date' to yyyy-mm-dd
dfallo['Date'] = pd.to_datetime(dfallo['Date'], errors = 'coerce')
dfallo['Date'] = pd.to_datetime(dfallo["Date"].dt.strftime('%m/%d/%Ym'))
# #Removing NaN, and missing (999) values from AllocaitonAmount, AllocaitonMaxium, IrrigatedAcerage.
# dfallo['AllocationFlow_CFS'] = dfallo['AllocationFlow_CFS'].fillna(0)
# dfallo['AllocationFlow_CFS'] = dfallo['AllocationFlow_CFS'].replace(999, 0)

# dfallo['AllocationVolume_AF'] = dfallo['AllocationVolume_AF'].fillna(0)
# dfallo['AllocationVolume_AF'] = dfallo['AllocationVolume_AF'].replace(999, 0)
#AllocationOwner
AllocationOwnerDic = {
"DREAMWORK MANAGEMENT INC; U.S.-FOREST SERVICE; <NAME>." : "US Forest Service",
"FOREST SERVICE LINE SPG" : "US Forest Service",
"FOREST SERVICE SPRING PIPELINE" : "US Forest Service",
"FOREST SERVICE,UNITED STATES" : "US Forest Service",
"FOREST SERVICE,UNITED STATES OF AMERICA" : "US Forest Service",
"Forest Service,United States of America -" : "US Forest Service",
"FOREST SERVICE,USDA" : "US Forest Service",
"MAGDALENA RANGER DISTRICT,US Forest Service" : "US Forest Service",
"N.D. Forest Service" : "US Forest Service",
"Rowser and et al.,Lillian C.,USA Forest Service,Rasmussen,Roger J." : "US Forest Service",
"Rowser and et al.,Lillian C.,USA Forest Service,Rasmusson,Roger J.,Hood,E. Ward & Linnea" : "US Forest Service",
"SANTA FE NATIONAL FOREST,USDA FOREST SERVICE" : "US Forest Service",
"State of Utah Division of Wildlife Resources,USA Forest Service" : "US Forest Service",
"Steed,Newel,USA Forest Service" : "US Forest Service",
"TEXAS A&M FOREST SERVICE" : "US Forest Service",
"U S FOREST SERVICE" : "US Forest Service",
"U S Forest Service" : "US Forest Service",
"U. S. FOREST SERVICE" : "US Forest Service",
"U. S. Forest Service, Lassen National Forest, Hat Creek Ranger District" : "US Forest Service",
"U.S. Forest Service" : "US Forest Service",
"U.S. FOREST SERVICE" : "US Forest Service",
"U.S. FOREST SERVICE" : "US Forest Service",
"U.S. FOREST SERVICE; APPLEGATE RANGER DISTRICT" : "US Forest Service",
"U.S. FOREST SERVICE; BEND RANGER DISTRICT" : "US Forest Service",
"U.S. FOREST SERVICE; COLUMBIA RIVER GORGE SCENIC AREA" : "US Forest Service",
"U.S. FOREST SERVICE; MIDDLE FORK RANGER DISTRICT" : "US Forest Service",
"U.S.-FOREST SERVICE" : "US Forest Service",
"U.S.-FOREST SERVICE; U.S.-AGRICULTURE DEPARTMENT" : "US Forest Service",
"UNITED STATES FOREST SERVICE" : "US Forest Service",
"UNITED STATES FOREST SERVICE" : "US Forest Service",
"United States Of America Acting Through Usda Forest Service" : "US Forest Service",
"UNITED STATES OF AMERICA FOREST SERVICE" : "US Forest Service",
"UNITED STATES OF AMERICA, FOREST SERVICE" : "US Forest Service",
"US Forest Service" : "US Forest Service",
"US Forest Service" : "US Forest Service",
"US Forest Service Mount Baker," : "US Forest Service",
"US Forest Service NW International," : "US Forest Service",
"US Forest Service Snoqualmie," : "US Forest Service",
"US Forest Service Wenatchee," : "US Forest Service",
"US Forest Service," : "US Forest Service",
"US Forest Service, LOS PADRES NATIONAL FOREST" : "US Forest Service",
"USA FOREST SERVICE" : "US Forest Service",
"USA Forest Service" : "US Forest Service",
"USA Forest Service - Dixie National Forest" : "US Forest Service",
"USA Forest Service - Dixie National Service" : "US Forest Service",
"USA Forest Service - Fishlake National Forest" : "US Forest Service",
"USA Forest Service / Fishlake National Forest" : "US Forest Service",
"USA Forest Service,Ackerman,Wilford" : "US Forest Service",
"USA Forest Service,Dixie National Forest,USA FOREST SERVICE" : "US Forest Service",
"USA Forest Service,Gardner,<NAME>,Gardner,<NAME>.,<NAME>,Gardner,<NAME>." : "US Forest Service",
"USA Forest Service,Heirs of <NAME>" : "US Forest Service",
"USA Forest Service,Jones,<NAME>." : "US Forest Service",
"USA Forest Service,USA Forest Service 2" : "US Forest Service",
"USA Forest Service,USA Forestt Service" : "US Forest Service",
"USA Forest Service,USA Forsest Service" : "US Forest Service",
"USA Forest Service." : "US Forest Service",
"USA National Forest 2,USA Forest Service" : "US Forest Service",
"USA USDA FOREST SERVICE" : "US Forest Service",
"USDA FOREST SERVICE" : "US Forest Service",
"USDA Forest Service" : "US Forest Service",
"USDA FOREST SERVICE CIBOLA NF" : "US Forest Service",
"USDA FOREST SERVICE LAKE TAHOE BASIN MANAGEMENT UNIT" : "US Forest Service",
"USDA FOREST SERVICE(CARSON NF)" : "US Forest Service",
"USDA Forest Service," : "US Forest Service",
"USDA FOREST SERVICE, SEQUOIA NATIONAL FOREST" : "US Forest Service",
"USDA Forest Service, USA" : "US Forest Service",
"USDA FOREST SERVICE,CARSON NF" : "US Forest Service",
"USDA FOREST SERVICE; DESCHUTES NATIONAL FOREST" : "US Forest Service",
"USDA FOREST SERVICES" : "US Forest Service",
"USDA, FOREST SERVICE,CARSON NF" : "US Forest Service",
"West Panguitch Irrigation and Reservoir Company,USA Forest Service" : "US Forest Service",
"Department of Energy,United States of America" : "US Department of Energy",
"M-K Ferguson Co. for USA Department of Energy" : "US Department of Energy",
"MK-Ferguson Co. for the USA Department of Energy" : "US Department of Energy",
"U S DEPARTMENT OF ENERGY" : "US Department of Energy",
"U.S. Department of Energy" : "US Department of Energy",
"U.S.-DEPARTMENT OF ENERGY,  YUCCA MOUNTAIN SITE CHARACTERIZATION PROJECT" : "US Department of Energy",
"U.S-DEPARTMENT OF ENERGY, YUCCA MOUNTAIN SITE CHARACTERIZATION PROJECT" : "US Department of Energy",
"UNITED STATES DEPARTMENT OF ENERGY" : "US Department of Energy",
"United States of America,U.S. Department of Energy" : "US Department of Energy",
"US Department of Energy," : "US Department of Energy",
"USA Department of Energy Grand Junction Projects" : "US Department of Energy",
"Bank<NAME>,UT Reclamation Mitigation and Conservation Comm,United States of America Bureau of Reclamation,Corbridge,William B." : "US Bureau Reclamation",
"Bryan, <NAME> & Jennifer,Fletcher, <NAME>,Teanaway Associates,,Perkins, <NAME> & Catherine,US Bureau Reclamation - Yakima," : "US Bureau Reclamation",
"Bureau Of Reclamation" : "US Bureau Reclamation",
"BUREAU OF RECLAMATION" : "US Bureau Reclamation",
"Bureau Of Reclamation," : "US Bureau Reclamation",
"Bureau of Reclamation, Provo Area Office,United States of America,Richardson,<NAME>.,<NAME>,<NAME>.,Sandy City,Richardson,Du<NAME>." : "US Bureau Reclamation",
"Bureau of Reclamation,The Secretary of the Interior" : "US Bureau Reclamation",
"Bureau of Reclamation,United State of America" : "US Bureau Reclamation",
"BUREAU OF RECLAMATION,UNITED STATES D.O.I." : "US Bureau Reclamation",
"Bureau of Reclamation,United States of America" : "US Bureau Reclamation",
"Bureau of Reclamation,United States of America," : "US Bureau Reclamation",
"Bureau of Reclamation,United States of America,Corbridge,William B." : "US Bureau Reclamation",
"BUREAU OF RECLAMATION; UMATILLA FIELD OFFICE" : "US Bureau Reclamation",
"Heart K Ranch,,US Bureau Reclamation - Yakima," : "US Bureau Reclamation",
"Hirst,Paul and Bonnie,Weber River Water Users Association,USA Bureau of Reclamation" : "US Bureau Reclamation",
"Lambert,Paul,USA Bureau of Reclamation" : "US Bureau Reclamation",
"Morgan County,Weber River Water Users` Assocation,USA Bureau of Reclamation" : "US Bureau Reclamation",
"Provo Area Office,U.S.A. Bureau of Reclamation" : "US Bureau Reclamation",
"Provo Area Office,USA Bureau of Reclamation" : "US Bureau Reclamation",
"Remund,Glen & Unice,United States of America Bureau of Reclamation" : "US Bureau Reclamation",
"Smith,Clarence,Siddoway,Benjamin and Beryl,Vernon,James,Vernon,Francis,Gines,<NAME>. and <NAME>.,USA Bureau of Reclamation,Smith,Dick,Siddoway,Alan Siddoway and LouDean,Crandall Creek Irrigation Company" : "US Bureau Reclamation",
"U S BUREAU OF RECLAMATION" : "US Bureau Reclamation",
"U. S. DEPT. OF THE INTERIOR,BUREAU OF RECLAMATION" : "US Bureau Reclamation",
"U.S. BUREAU OF RECLAMATION" : "US Bureau Reclamation",
"U.S. Bureau Of Reclamation" : "US Bureau Reclamation",
"U.S. BUREAU OF RECLAMATION" : "US Bureau Reclamation",
"U.S. BUREAU OF RECLAMATION" : "US Bureau Reclamation",
"U.S.A. Bureau of Reclamation" : "US Bureau Reclamation",
"U.S.A. Bureau of Reclamation,Fitzgerald,<NAME>." : "US Bureau Reclamation",
"U.S.-BUREAU OF RECLAMATION" : "US Bureau Reclamation",
"United States Bureau of Reclamation" : "US Bureau Reclamation",
"United States of America Bureau of Reclamation,<NAME> and <NAME> Family Trust" : "US Bureau Reclamation",
"United States of America, Bureau of Reclamation" : "US Bureau Reclamation",
"US BUREAU OF RECLAMATION" : "US Bureau Reclamation",
"US Bureau of Reclamation" : "US Bureau Reclamation",
"US Bureau of Reclamation - Boise," : "US Bureau Reclamation",
"US Bureau Of Reclamation," : "US Bureau Reclamation",
"USA Bureau of Reclamation" : "US Bureau Reclamation",
"USA Bureau of Reclamation -- Provo Area Office" : "US Bureau Reclamation",
"USA Bureau of Reclamation,Boyden,John" : "US Bureau Reclamation",
"USA Bureau of Reclamation,Ivie,James" : "US Bureau Reclamation",
"USA Bureau of Reclamation,Miller,Max" : "US Bureau Reclamation",
"USA Bureau of Reclamation,Weber River Water Users Association,Sanders Properties" : "US Bureau Reclamation",
"UT Reclamation Mitigation and Conservation Comm,United States of America Bureau of Reclamation,Bankhead,S. Clair,Corbridge,William B" : "US Bureau Reclamation",
"Weber River Water Users` Association,Morgan County,USA Bureau of Reclamation" : "US Bureau Reclamation",
"Wilde Revocable Trust,<NAME>.,Wilde Land and Livestock Company,Wilde `A` Ditch Company,Faddis,Andrew,Wilde,Henry,Wilson,John,Wilde,Richard & Betty,USA Bureau of Reclamation,Beech,Thomas L.,Wilde Revocable Trust,<NAME>,Wilde,Mary" : "US Bureau Reclamation",
"Wilkinson,Allen,United States of America Bureau of Reclamation" : "US Bureau Reclamation",
"Allen,<NAME>.,USA Bureau of Reclamation,Eldredge,Edmund,Robinson,<NAME>. and <NAME>,North Chalk Creek Irrigation Company" : "US Bureau Reclamation",
" - Cedar City Field Office,US Bureau of Land Management" : "US Bureau of Land Management",
" - Cedar City Field Office,US Department of Interior, Bureau of Land Management" : "US Bureau of Land Management",
" - Cedar City Field Office,US Department of the Interior, Bureau of Land Management" : "US Bureau of Land Management",
" - Cedar City Field Office,USDI Bureau of Land Management" : "US Bureau of Land Management",
"#NAME?,US Department of Interior, Bureau of Land Management" : "US Bureau of Land Management",
"BUREAU OF LAND MANAGEMENT" : "US Bureau of Land Management",
"BUREAU OF LAND MANAGEMENT" : "US Bureau of Land Management",
"Bureau of Land Management" : "US Bureau of Land Management",
"Bureau of Land Management," : "US Bureau of Land Management",
"Bureau of Land Management, Bakersfield" : "US Bureau of Land Management",
"Bureau of Land Management,Box Elder County" : "US Bureau of Land Management",
"Bureau of Land Management,Davis,<NAME> (II) and Shawnee,Tebbs Living Trust,<NAME> and <NAME>." : "US Bureau of Land Management",
"Bureau of Land Management,Flinspach,Richard and Colleen" : "US Bureau of Land Management",
"BUREAU OF LAND MANAGEMENT,SOCORRO FIELD OFFICE" : "US Bureau of Land Management",
"Bureau of Land Management,Stuntz Valley Ranch LC" : "US Bureau of Land Management",
"Bureau of Land Management,T Y Cattle Company" : "US Bureau of Land Management",
"BUREAU OF LAND MANAGEMENT,UNITED STATES" : "US Bureau of Land Management",
"BUREAU OF LAND MANAGEMENT,UNITED STATES GOVERNMENT" : "US Bureau of Land Management",
"BUREAU OF LAND MANAGEMENT,UNITED STATES OF AMERICA" : "US Bureau of Land Management",
"BUREAU OF LAND MANAGEMENT,US DEPARTMENT OF INTERIOR" : "US Bureau of Land Management",
"BUREAU OF LAND MANAGEMENT,US DEPT LAND MANAGEMENT" : "US Bureau of Land Management",
"BUREAU OF LAND MANAGEMENT,US DEPT OF INTERIOR" : "US Bureau of Land Management",
"BUREAU OF LAND MANAGEMENT,USDI" : "US Bureau of Land Management",
"BUREAU OF LAND MANAGEMENT; OWYHEE FIELD OFFICE" : "US Bureau of Land Management",
"Carbon County,USA Bureau of Land Management" : "US Bureau of Land Management",
"Cedar City Field Office,USDI Bureau of Land Management;" : "US Bureau of Land Management",
"Chew Livestock Inc,Bureau of Land Management" : "US Bureau of Land Management",
"Department of Interior Bureau of Land Management" : "US Bureau of Land Management",
"Department of Natural Resources,State of Utah,,USA Bureau of Land Management" : "US Bureau of Land Management",
"Division of Wildlife Resources,State of Utah,,USA Bureau of Land Management,Price Field Office" : "US Bureau of Land Management",
"Gemstone Mining Inc.,USA Bureau of Land Management" : "US Bureau of Land Management",
"Grouse Creek Ranch LC,USA Bureau of Land Management" : "US Bureau of Land Management",
"<NAME>,USA Bureau of Land Management" : "US Bureau of Land Management",
"Materials Energy Research and Recovery,USA Bureau of Land Management" : "US Bureau of Land Management",
"Moab District USA Bureau of Land Management" : "US Bureau of Land Management",
"<NAME>.,USA Bureau of Land Management" : "US Bureau of Land Management",
"NORTHERN WASHOE RANCHING MANAGEMENT, LLC; DUCK LAKE RANCH, LLC; U.S.-BUREAU OF LAND MANAGEMENT" : "US Bureau of Land Management",
"NORTHERN WASHOE RANCHING MANAGEMENT, LLC; U.S.-BUREAU OF LAND MANAGEMENT; DUCK LAKE RANCH, LLC" : "US Bureau of Land Management",
"Richfield District USA Bureau of Land Management" : "US Bureau of Land Management",
"Saint George Field Office,USDI Bureau of Land Management" : "US Bureau of Land Management",
"Saint George Field Office,USDI Bureau of Land Management;" : "US Bureau of Land Management",
"Skull Valley Company Ltd.,USA Bureau of Land Management" : "US Bureau of Land Management",
"<NAME>,USA Bureau of Land Management" : "US Bureau of Land Management",
"State of Utah Division of Wildlife Resources,USA Bureau of Land Management" : "US Bureau of Land Management",
"Thousand Peaks Ranches, Inc.,USA Bureau of Land Management,Richfield District" : "US Bureau of Land Management",
"U S BUREAU OF LAND MANAGEMENT" : "US Bureau of Land Management",
"U.S. Bureau Of Land Management" : "US Bureau of Land Management",
"U.S. BUREAU OF LAND MANAGEMENT" : "US Bureau of Land Management",
"U.S. DEPARTMENT OF THE INTERIOR; BUREAU OF LAND MANAGEMENT" : "US Bureau of Land Management",
"U.S.-BUREAU OF LAND MANAGEMENT" : "US Bureau of Land Management",
"U.S.-BUREAU OF LAND MANAGEMENT; BRIGHT-HOLLAND CORPORATION" : "US Bureau of Land Management",
"U.S.-BUREAU OF LAND MANAGEMENT; WHITE PINE LUMBER CO." : "US Bureau of Land Management",
"U.S.D.I., Bureau of Land Management" : "US Bureau of Land Management",
"United States Of America Acting Through The Department Of Interior Bureau Of Land Management" : "US Bureau of Land Management",
"United States of America Bureau of Land Management,Fillmore District" : "US Bureau of Land Management",
"UNITED STATES OF AMERICA, DEPARTMENT OF THE INTERIOR, BUREAU OF LAND MANAGEMENT; WULFENSTEIN TRUST R-501; SPRING MOUNTAIN MOTORSPORTS RANCH; PAHRUMP GATEWAY UTILITY; SPRING MOUNTAIN RACEWAY LLC" : "US Bureau of Land Management",
"US BUREAU OF LAND MANAGEMENT" : "US Bureau of Land Management",
"US BUREAU OF LAND MANAGEMENT" : "US Bureau of Land Management",
"US BUREAU OF LAND MANAGEMENT / BARSTOW FIELD OFFICE" : "US Bureau of Land Management",
"US BUREAU OF LAND MANAGEMENT / PALM SPRINGS FIELD OFFICE" : "US Bureau of Land Management",
"US BUREAU OF LAND MANAGEMENT, ALTURAS FIELD OFFICE" : "US Bureau of Land Management",
"US BUREAU OF LAND MANAGEMENT, RIDGECREST FIELD OFFICE" : "US Bureau of Land Management",
"US BUREAU OF LAND MANAGEMENT, UKIAH FIELD OFFICE" : "US Bureau of Land Management",
"US BUREAU OF LAND MANAGEMENT/ ARCATA FIELD OFFICE" : "US Bureau of Land Management",
"US BUREAU OF LAND MANAGEMENT/NEEDLES FIELD OFFICE" : "US Bureau of Land Management",
"US DEPT OF THE INTERIOR,BUREAU OF LAND MANAGEMENT" : "US Bureau of Land Management",
"USA Bureau of Land Management" : "US Bureau of Land Management",
"USA Bureau of Land Management - Filmore Field Office" : "US Bureau of Land Management",
"USA Bureau of Land Management (Richfield District)" : "US Bureau of Land Management",
"USA Bureau of Land Management Vernal District" : "US Bureau of Land Management",
"USA Bureau of Land Management, Richfield District" : "US Bureau of Land Management",
"USA Bureau of Land Management,Cedar City District" : "US Bureau of Land Management",
"USA Bureau of Land Management,Fillmore District" : "US Bureau of Land Management",
"USA Bureau of Land Management,Fillmore Field Office" : "US Bureau of Land Management",
"USA Bureau of Land Management,Kanab District" : "US Bureau of Land Management",
"USA Bureau of Land Management,Kanab Field Office" : "US Bureau of Land Management",
"USA Bureau of Land Management,Moab District" : "US Bureau of Land Management",
"USA Bureau of Land Management,Monticello  District" : "US Bureau of Land Management",
"USA Bureau of Land Management,Monticello District" : "US Bureau of Land Management",
"USA Bureau of Land Management,Ogden District" : "US Bureau of Land Management",
"USA Bureau of Land Management,Price District" : "US Bureau of Land Management",
"USA Bureau of Land Management,Price Field Office" : "US Bureau of Land Management",
"USA Bureau of Land Management,Richfield District" : "US Bureau of Land Management",
"USA Bureau of Land Management,Vernal District" : "US Bureau of Land Management",
"USDI BUREAU OF LAND MANAGEMENT" : "US Bureau of Land Management",
"USDI BUREAU OF LAND MANAGEMENT" : "US Bureau of Land Management",
"Utah School and Institutional Trust Lands Admin.,USA Bureau of Land Management" : "US Bureau of Land Management",
"West Panguitch Irrigation and Reservoir Company,State of Utah Board of Water Resources,USA Bureau of Land Management" : "US Bureau of Land Management",
"WHITE PINE LUMBER CO; U.S.-BUREAU OF LAND MANAGEMENT" : "US Bureau of Land Management",
"ARMY CAMP SPRING" : "US Army",
"CORPS OF ENGRS. ABIQUIU DAM,US ARMY ENGR DIST ALBQ." : "US Army",
"Cummins, Dale F,US Army Corps Of Engineers," : "US Army",
"Dept Of The Army Corps Of Engineers," : "US Army",
"LONGHORN ARMY AMMUNITION PLANT,U.S. DEPARTMENT OF THE INTERIOR FISH AND WILDLIFE SERVICE" : "US Army",
"U S ARMY CORPS OF ENGINEERS" : "US Army",
"U S Army Corps of Engineers" : "US Army",
"U S Army, Department of" : "US Army",
"U.S Army Corps Of Engineers" : "US Army",
"U.S. ARMY CORP OF ENGINEERS" : "US Army",
"U.S. Army Corps Of Engineers" : "US Army",
"U.S. ARMY CORPS OF ENGINEERS" : "US Army",
"U.S. ARMY CORPS OF ENGINEERS SEATTLE" : "US Army",
"U.S. ARMY CORPS OF ENGINEERS WALLA WALLA" : "US Army",
"U.S. ARMY CORPS OF ENGINEERS, WALLA WALLA DISTRICT" : "US Army",
"U.S. ARMY CORPS OF ENGINEERS; PORTLAND DISTRICT" : "US Army",
"U.S. ARMY CORPS OF ENGINEERSSEATTLE" : "US Army",
"U.S. ARMY CORPS OF ENGINEERSWALLA WALLA" : "US Army",
"U.S. Army Dugway Proving Ground" : "US Army",
"U.S. ARMY WHITE SANDS MISSILE" : "US Army",
"U.S. ARMY-WHITE SANDS MISSILE" : "US Army",
"UNITED STATES ARMY CORPS OF ENGINEERS" : "US Army",
"US ARMY" : "US Army",
"Us Army Corps Of Engineers" : "US Army",
"US ARMY CORPS OF ENGINEERS" : "US Army",
"US Army Corps Of Engineers," : "US Army",
"US ARMY CORPS OF ENGINGEERS" : "US Army",
"US DEPARTMENT OF THE ARMY" : "US Army",
"USA Army Dugway Proving Ground" : "US Army",
"USA Army Dugway Proving Grounds" : "US Army",
"USA Army Engineer District Corps of Engineers" : "US Army",
"USA Army for Dugway Proving Ground" : "US Army",
"USA Army Ogden Sub-Depot of Tooele Ordinance Depot" : "US Army",
"USA Army Tooele Army Depot South" : "US Army",
"USA Department of the Army" : "US Army",
"USA Department of the Army - Dugway Proving Ground" : "US Army",
"USA Department of the Army - Tooele Army Depot" : "US Army",
"USA Department of the Army Arsenal" : "US Army",
"USARMY Corp Of Engineers," : "US Army",
"USARMY Corps Engineers Columbia Cnty," : "US Army",
"USARMY Corps Engineers Seattle," : "US Army",
"USARMY Corps Engineers Walla Walla," : "US Army",
"USARMY Corps Engineers," : "US Army",
"USARMY Corps Engineers,,USDA Agricultural Research Service WRPIS," : "US Army",
"WA Fish & Wildlife Dept - ERO,,USARMY Corps Engineers," : "US Army",
"Camelot International Resort Corporation,United States of America" : "United States of America",
"Christensen,<NAME>.,Fish and Wildlife Service,United States of America" : "United States of America",
"CIBOLA NATIONAL FOREST,UNITED STATES OF AMERICA," : "United States of America",
"Ekker,Sheena,Utah School and Institutional Trust Lands Admin.,Hodges,Jimmy,McArthur,Ty,United States of America" : "United States of America",
"Fish and Wildlife Service,United States of America" : "United States of America",
"Fish and Wildlife Service,United States of America," : "United States of America",
"INTERMOUNTAIN LAND CO.; MOONROCKS GATEWAY LLC; UNITED STATES OF AMERICA" : "United States of America",
"LAMB, <NAME>. & <NAME>.; UNITED STATES OF AMERICA" : "United States of America",
"Llewellyn,<NAME>.,United States of America" : "United States of America",
"<NAME>AMILY TRUST; UNITED STATES OF AMERICA" : "United States of America",
"The United States of America" : "United States of America",
"THE UNITED STATES OF AMERICA IN TRUST FOR THE PYRAMID LAKE PAIUTE TRIBE; GARAVANTA LAND AND LIVESTOCK COMPANY SUCCESSOR TO J. GARAVANTA" : "United States of America",
"<NAME>.; THOMAS, <NAME>.; UNITED STATES OF AMERICA" : "United States of America",
"United States Of America" : "United States of America",
"UNITED STATES OF AMERICA" : "United States of America",
"UNITED STATES OF AMERICA" : "United States of America",
"United States of America" : "United States of America",
"UNITED STATES OF AMERICA (BLM)" : "United States of America",
"United States Of America Acting Through" : "United States of America",
"United States of America in trust for <NAME>,Burton,<NAME>" : "United States of America",
"UNITED STATES OF AMERICA IN TRUST FOR SUCH SHOSHONE INDIANS SOUTHERN NEVADA" : "United States of America",
"UNITED STATES OF AMERICA IN TRUST FOR THE WASHOE TRIBE OF INDIANS" : "United States of America",
"UNITED STATES OF AMERICA IN TRUST FOR YOMBA TRIBE OF SHOSHONE INDIANS" : "United States of America",
"UNITED STATES OF AMERICA NAS" : "United States of America",
"UNITED STATES OF AMERICA NAVAL AIR STATION FALLON" : "United States of America",
"United States Of America Trustee" : "United States of America",
"United States Of America," : "United States of America",
"UNITED STATES OF AMERICA,  C/O COMMANDER AND DISTRICT ENGINEER,   UNITED STATES ARMY ENGINEER  DISTRICT, LOUISVILLE" : "United States of America",
"United States of America, Utah" : "United States of America",
"United States of America,BBRD, LC,Ottesen,Nellie" : "United States of America",
"United States of America,Owen Probst and Rosetta B. Probst Trust" : "United States of America",
"United States of America,The,Conger,Wm. B. and Florance H" : "United States of America",
"United States of America,Virgin River Land Preservation Inc." : "United States of America",
"UNITED STATES OF AMERICA; STREETS; UNITED STATES OF AMERICA IN TRUST FOR THE RENO SPARKS INDIAN COLONY; NEVADA-DEPARTMENT OF TRANSPORTATION; SIERRA PACIFIC POWER COMPANY; RENO SPARKS INDIAN COLONY; RENO LODGE #13 F AND A M" : "United States of America",
"United States of American" : "United States of America",
"USA-United States Of America" : "United States of America",
"Utah Reclamation Mitigation and Conservation Commission,United States Of America" : "United States of America",
"WASHOE COUNTY; PRATT, TOM & LANNETTE; UNITED STATES OF AMERICA" : "United States of America",
"WS-BUCKLAND, LLC; UNITED STATES OF AMERICA" : "United States of America"}
 
def assignAllocationOwner(colrowValue):
    if colrowValue == '' or pd.isnull(colrowValue):
        return "Other"
    else:
        String1 = colrowValue.strip()  # remove whitespace chars
        try:
            outList = AllocationOwnerDic[String1]
        except:
            outList = "Other"
    return outList

dfallo['AllocationOwner'] = dfallo.apply(lambda row: assignAllocationOwner(row['AllocationOwner']), axis=1)
#Sort by AllocationAmountID and SiteUUID
dfallo = dfallo.sort_values(by=['AllocationAmountID', 'SiteUUID'], ascending=True).reset_index()
dfallo
## Create Sites Dataframe - with attached Allo and Benuse info ##
########################################
outdfallo = pd.DataFrame()

#The Columns
outdfallo['AA_ID'] = dfallo['AllocationAmountID'].astype(str)
outdfallo['AA_CFS'] = dfallo['AllocationFlow_CFS'].astype(float) 
outdfallo['AA_AF'] = dfallo['AllocationVolume_AF'].astype(float)
outdfallo['AllocationOwner'] = dfallo['AllocationOwner'].astype(str)
outdfallo['SN_ID'] = dfallo['AllocationNativeID'].astype(str)
outdfallo['WBenUse'] = dfallo['WaDENameBenUse'].astype(str)
outdfallo['PD'] = dfallo['Date']

outdfallo['SiteUUID'] = dfallo['SiteUUID'].astype(str)
outdfallo['Lat'] = dfallo['Latitude'].astype(float) 
outdfallo['Long'] = dfallo['Longitude'].astype(float)
outdfallo['State'] = dfallo['State'].astype(str) 
outdfallo['SiteTypeCV'] = dfallo['WaDENameS'].astype(str)

outdfallo['WSN'] = dfallo['WaterSourceName'].astype(str)
outdfallo['WaterSourceTypeCV'] = dfallo['WaterSourceTypeCV'].astype(str)

outdfallo
#Exporting Outbound DataFrame to working csv file.
outdfallo.to_csv('P_AlloLJSite.csv', index=False)  # The output
outdfSite['WBenUse'].drop_duplicates()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11420228019864308, 'fraction_numerical': 0.005525634748548646, 'mean_word_length': 5.454011962532445, 'pattern_counts': {'":': 0, '<': 87, '<?xml version=': 0, '>': 87, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '102021', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 19160, 'n_tokens_neox': 17705, 'n_words': 6999, 'path': 'App 1_WaterRightPointData/MapPODSite_ver2b/bonus code/PreProcessData_SiteLFAllo_B.ipynb', 'repo_name': 'WSWCWaterDataExchange/WaDE2.0Apps', 'size': '57590'}