<jupyter_start><jupyter_text>2017-2018 school vaccination data- Numbers in the original document have been surpressed for =95%,>=98%,>=99%. Original file has errors with =, and --* symbols when importing directly from .xlsx- The 3 raw datafiles for childcare, kindergarden, and 7th grade has been compiled manually into a csv called `raw17_18_combined.csv` with = removed and replaced with the corresponding numbers<jupyter_code># Import raw combined 2017-2018 school vaccination data
pertusis_1718 = pd.read_csv("/Users/42/desktop/capstone/mids_capstone/vaxxfacts/raw_data/raw17_18_combined.csv",encoding = "ISO-8859-1")
pertusis_1718.head()
#Clean up missing values and set to Nan
pertusis_1718.n=pertusis_1718.n.replace('.', np.nan)
pertusis_1718.pct=pertusis_1718.pct.replace('.',np.nan)

# uppercase all counties and cities
pertusis_1718.COUNTY=pertusis_1718['COUNTY'].str.upper()
pertusis_1718.CITY=pertusis_1718['CITY'].str.upper()
pertusis_1718.CITY
pertusis_1718.dtypes
# Change n and percent to floats
pertusis_1718[['n', 'pct']] = pertusis_1718[['n', 'pct']].astype('float64')
# Check conversion
pertusis_1718.dtypes
pertusis_1718.head()
sum(pertusis_1718.REPORTED=='N')
sum(pertusis_1718.REPORTED=='Y')<jupyter_output><empty_output><jupyter_text>Subset dataset to those that reported<jupyter_code># Take subset that have numbers
vac_1718=pertusis_1718[pertusis_1718.REPORTED=='Y']
vac_1718.head()<jupyter_output><empty_output><jupyter_text>Numbers in the original document have been surpressed for =95%,>=98%,>=99% so will impute values by assuming that the percentage is equal to whatever value listed and multiply by the number of students enrolled at the school.<jupyter_code># Calculate n from enrollment and approximate percentage
vac_1718.loc[:,('n')]=vac_1718.n.fillna(vac_1718.ENROLLMENT*vac_1718.pct/100)
vac_1718.head()
# Change to integers 
vac_1718.loc[:,('ENROLLMENT','n','pct')]=vac_1718.loc[:,('ENROLLMENT','n','pct')].astype('int64')
vac_1718.head()
# Change print settings to see all
#np.set_printoptions(threshold=np.nan)
#change back to default printing length
np.set_printoptions(threshold=5)
# Check County names
sorted(list(vac_1718.COUNTY.unique()))
#Check city names
sorted(list(vac_1718.CITY.unique()))
# Fix City Typos
vac_1718.loc[vac_1718.CITY=='ACTON, CA', 'CITY']='ACTON'
vac_1718.loc[vac_1718.CITY=='AGOURA', 'CITY']='AGOURA HILLS'
vac_1718.loc[vac_1718.CITY=='ANAHEIM,', 'CITY']='ANAHEIM'
vac_1718.loc[vac_1718.CITY=='ANANHEIM,', 'CITY']='ANAHEIM'
vac_1718.loc[vac_1718.CITY=='CA', 'CITY']='NAPA'
vac_1718.loc[vac_1718.CITY=='BEALE AIR FORCE BASE', 'CITY']='BEALE AFB' # to match census name
vac_1718.loc[vac_1718.CITY=='BENICA', 'CITY']='BENICIA'
vac_1718.loc[vac_1718.CITY=='CARDIFF', 'CITY']='CARDIFF BY THE SEA'
vac_1718.loc[vac_1718.CITY=='CARMEL', 'CITY']='<NAME>'
vac_1718.loc[vac_1718.CITY=='CHINO,', 'CITY']='CHINO'
vac_1718.loc[vac_1718.CITY=='CHULAR', 'CITY']='CHUALAR'
vac_1718.loc[vac_1718.CITY=='CITY OF COMMERCE', 'CITY']='COMMERCE'
vac_1718.loc[vac_1718.CITY=='CITY OF INDUSTRY', 'CITY']='INDUSTRY'
vac_1718.loc[vac_1718.CITY=='CUDAHAY', 'CITY']='CUDAHY'
vac_1718.loc[vac_1718.CITY=='E. NICOLAUS', 'CITY']='EAST NICOLAUS'
vac_1718.loc[vac_1718.CITY=='<NAME>', 'CITY']='EAST RANCHO DOMINGUEZ'
vac_1718.loc[vac_1718.CITY=='E <NAME>UEZ', 'CITY']='EAST RANCHO DOMINGUEZ'
vac_1718.loc[vac_1718.CITY=='E. WHITTIER', 'CITY']='EAST WHITTIER'
vac_1718.loc[vac_1718.CITY=='EL SEGUNDO,', 'CITY']='EL SEGUNDO'
vac_1718.loc[vac_1718.CITY=='FAIRIFELD', 'CITY']='FAIRFIELD'
vac_1718.loc[vac_1718.CITY=='FREMOTN', 'CITY']='FREMONT'
vac_1718.loc[vac_1718.CITY=='<NAME>', 'CITY']='FORT IRWIN'
vac_1718.loc[vac_1718.CITY=='GREENFILED', 'CITY']='GREENFIELD'
vac_1718.loc[vac_1718.CITY=='HUNTINGTON', 'CITY']='HUNTINGTON PARK'
vac_1718.loc[vac_1718.CITY=='JAMUAL', 'CITY']='JAMUL'
vac_1718.loc[vac_1718.CITY=='LA', 'CITY']='LOS ANGELES'
vac_1718.loc[vac_1718.CITY=='LA CANADA', 'CITY']='LA CANADA FLINTRIDGE'
vac_1718.loc[vac_1718.CITY=='LA CAÃ\x91ADA', 'CITY']='LA CANADA FLINTRIDGE'
vac_1718.loc[vac_1718.CITY=='LA CAÃ\x83â\x80\x98ADA', 'CITY']='LA CANADA FLINTRIDGE'
vac_1718.loc[vac_1718.CITY=='LA CRESCENTA', 'CITY']='LA CRESCENTA-MONTROSE'
vac_1718.loc[vac_1718.CITY=='LAKE VIEW TERRANCE', 'CITY']='LAKE VIEW TERRACE'
vac_1718.loc[vac_1718.CITY=='LAKEVIEW TERRACE', 'CITY']='LAKE VIEW TERRACE'
vac_1718.loc[vac_1718.CITY=='LANCASTER,', 'CITY']='LANCASTER'
vac_1718.loc[vac_1718.CITY=='LAVERNE', 'CITY']='LA VERNE'
vac_1718.loc[vac_1718.CITY=='MC KINLEYVILLE', 'CITY']='MCKINLEYVILLE'
vac_1718.loc[vac_1718.CITY=='MONTROSE', 'CITY']='LA CRESCENTA-MONTROSE'
vac_1718.loc[vac_1718.CITY=='MT. SHASTA', 'CITY']='MOUNT SHASTA'
vac_1718.loc[vac_1718.CITY=='N. HOLLYWOOD', 'CITY']='NORTH HOLLYWOOD'
vac_1718.loc[vac_1718.CITY=='N.A.S. LEMOORE', 'CITY']='LEMOORE STATION' # match census
vac_1718.loc[vac_1718.CITY=='NAS LEMOORE', 'CITY']='LEMOORE STATION'
vac_1718.loc[vac_1718.CITY=='NEWPORT BEACH,', 'CITY']='NEWPORT BEACH'
vac_1718.loc[vac_1718.CITY=='PACOMIA', 'CITY']='PACOIMA'
vac_1718.loc[vac_1718.CITY=='PANORAM ', 'CITY']='PANORAMA CITY'
vac_1718.loc[vac_1718.CITY=='PANORAM CITY', 'CITY']='PANORAMA CITY'
vac_1718.loc[vac_1718.CITY=='PT. REYES STATION', 'CITY']='POINT REYES STATION'
vac_1718.loc[vac_1718.CITY=='RANCHO SANTA MARGARI', 'CITY']='RANCHO SANTA MARGARITA'
vac_1718.loc[vac_1718.CITY=='RANCHO SAN MARGARITA', 'CITY']='RANCHO SANTA MARGARITA'
vac_1718.loc[vac_1718.CITY=='RANCHO STA MARGAITA', 'CITY']='RANCHO SANTA MARGARITA'
vac_1718.loc[vac_1718.CITY=='RANCHO STA MARGARITA', 'CITY']='RANCHO SANTA MARGARITA'
vac_1718.loc[vac_1718.CITY=='RCHO STA MARG', 'CITY']='RANCHO SANTA MARGARITA'
vac_1718.loc[vac_1718.CITY=='RANCHOS PALOS VERDES', 'CITY']='RANCHO PALOS VERDES'
vac_1718.loc[vac_1718.CITY=='RIVERSIDE,', 'CITY']='RIVERSIDE'
vac_1718.loc[vac_1718.CITY=='ROLLING HILLS ESTATE', 'CITY']='ROLLING HILLS ESTATES'
vac_1718.loc[vac_1718.CITY=='S. EL MONTE', 'CITY']='SOUTH EL MONTE'
vac_1718.loc[vac_1718.CITY=='S. LAKE TAHOE', 'CITY']='SOUTH LAKE TAHOE'
vac_1718.loc[vac_1718.CITY=='S PASADENA', 'CITY']='SOUTH PASADENA'
vac_1718.loc[vac_1718.CITY=='SAN BERARDINO', 'CITY']='SAN BERNARDINO'
vac_1718.loc[vac_1718.CITY=='SAN FRNCISCO', 'CITY']='SAN FRANCISCO'
vac_1718.loc[vac_1718.CITY=='SAN JOSE,', 'CITY']='SAN JOSE'
vac_1718.loc[vac_1718.CITY=='SANTA BARARA', 'CITY']='SANTA BARBARA'
vac_1718.loc[vac_1718.CITY=='SHASTA LAKE CITY', 'CITY']='SHASTA LAKE'
vac_1718.loc[vac_1718.CITY=='SILVERADO CANYON', 'CITY']='SILVERADO'
vac_1718.loc[vac_1718.CITY=='SOUTH LATE TAHOE', 'CITY']='SOUTH LAKE TAHOE'
vac_1718.loc[vac_1718.CITY=='SPRECKLES', 'CITY']='SPRECKELS'
vac_1718.loc[vac_1718.CITY=='STEVENSONS RANCH', 'CITY']='STEVENSON RANCH'
vac_1718.loc[vac_1718.CITY=='SUISUN', 'CITY']='SUISUN CITY'
vac_1718.loc[vac_1718.CITY=='SUNNYALE', 'CITY']='SUNNYVALE'
vac_1718.loc[vac_1718.CITY=='SUPELVEDA', 'CITY']='SEPULVEDA'
vac_1718.loc[vac_1718.CITY=='TURLOCK,', 'CITY']='TURLOCK'
vac_1718.loc[vac_1718.CITY=='UPPERLAKE', 'CITY']='UPPER LAKE'
vac_1718.loc[vac_1718.CITY=='VANDENBERG AIR FORCE BASE', 'CITY']='VANDENBERG AFB' # match census
vac_1718.loc[vac_1718.CITY=='W. SACRAMENTO', 'CITY']='WEST SACRAMENTO'
vac_1718.loc[vac_1718.CITY=='WEST LOS ANGELES', 'CITY']='LOS ANGELES'

vac_1718.loc[vac_1718.COUNTY=='BERKELEY CITY', 'COUNTY']='ALAMEDA'
vac_1718.loc[vac_1718.COUNTY=='LONG BEACH', 'COUNTY']='LOS ANGELES'
# Change case to title case
vac_1718.COUNTY=vac_1718['COUNTY'].str.title()
vac_1718.CITY=vac_1718['CITY'].str.title()
# RECODE NEIGHBORHOODS TO CITIES to match Census
vac_1718.loc[vac_1718.CITY=='Alta Loma', 'CITY']='Rancho Cucamonga'
vac_1718.loc[vac_1718.CITY=='Alviso', 'CITY']='San Jose'
vac_1718.loc[vac_1718.CITY=='Anaheim Hills', 'CITY']='Anaheim'
vac_1718.loc[vac_1718.CITY=='Angels Camp', 'CITY']='Angels'
vac_1718.loc[vac_1718.CITY=='Arleta', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Canoga Park', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Canyon Country', 'CITY']='Santa Clarita'
vac_1718.loc[vac_1718.CITY=='Capistrano Beach', 'CITY']='Dana Point'
vac_1718.loc[vac_1718.CITY=='Cardiff By The Sea', 'CITY']='Encinitas'
vac_1718.loc[vac_1718.CITY=='Carmel Valley', 'CITY']='Carmel Valley Village'
vac_1718.loc[vac_1718.CITY=='Chatsworth', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Corona Del Mar', 'CITY']='Newport Beach'
vac_1718.loc[vac_1718.CITY=='Edwards', 'CITY']='Edwards AFB'
vac_1718.loc[vac_1718.CITY=='Emerald Hills', 'CITY']='San Diego'
vac_1718.loc[vac_1718.CITY=='Encino', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Etiwanda', 'CITY']='Rancho Cucamonga'
vac_1718.loc[vac_1718.CITY=='Foothill Ranch', 'CITY']='Lake Forest'
vac_1718.loc[vac_1718.CITY=='Granada Hills', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Harbor City', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Highland Park', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Hollywood', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Idyllwild', 'CITY']='Idyllwild-Pine Cove'
vac_1718.loc[vac_1718.CITY=='La Jolla', 'CITY']='San Diego'
vac_1718.loc[vac_1718.CITY=='Lake Balboa', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Lake View Terrace', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Leucadia', 'CITY']='Encinitas'
vac_1718.loc[vac_1718.CITY=='Mar Vista', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Mcclellan', 'CITY']='North Highlands'
vac_1718.loc[vac_1718.CITY=='Mira Loma', 'CITY']='Jurupa Valley'
vac_1718.loc[vac_1718.CITY=='Murrietta', 'CITY']='Murrieta'
vac_1718.loc[vac_1718.CITY=='Newbury Park', 'CITY']='Thousand Oaks'
vac_1718.loc[vac_1718.CITY=='Newhall', 'CITY']='Santa Clarita'
vac_1718.loc[vac_1718.CITY=='North Hills', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='North Hollywood', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Northridge', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Olympic Valley', 'CITY']='Squaw Valley'
vac_1718.loc[vac_1718.CITY=='Pacific Palisades', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Pacoima', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Palos Verdes', 'CITY']='Palos Verdes Estates'
vac_1718.loc[vac_1718.CITY=='Palos Verdes Peninsula', 'CITY']='Palos Verdes Estates'
vac_1718.loc[vac_1718.CITY=='Panorama City', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Playa Del Rey', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Playa Vista', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Porter Ranch', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Quail Valley', 'CITY']='Menifee'
vac_1718.loc[vac_1718.CITY=='Reseda', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='San Pedro', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='San Ysidro', 'CITY']='San Diego'
vac_1718.loc[vac_1718.CITY=='Santa Catalina', 'CITY']='Avalon'
vac_1718.loc[vac_1718.CITY=='Saugus', 'CITY']='Santa Clarita'
vac_1718.loc[vac_1718.CITY=='Sepulveda', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Sherman Oaks', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Studio City', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Sun City', 'CITY']='Menifee'
vac_1718.loc[vac_1718.CITY=='Sun Valley', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Sunland', 'CITY']='Sunland-Tujunga'
vac_1718.loc[vac_1718.CITY=='Sunset Beach', 'CITY']='Huntington Beach'
vac_1718.loc[vac_1718.CITY=='Sylmar', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Royal Oaks', 'CITY']='Interlaken'
vac_1718.loc[vac_1718.CITY=='Tahoe City', 'CITY']='Sunnyside-Tahoe City'
vac_1718.loc[vac_1718.CITY=='Tarzana', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Tujunga', 'CITY']='Sunland-Tujunga'
vac_1718.loc[vac_1718.CITY=='Tuolumne', 'CITY']='Tuolumne City'
vac_1718.loc[vac_1718.CITY=='Twenty Nine Palms', 'CITY']='Twentynine Palms'
vac_1718.loc[vac_1718.CITY=='Valencia', 'CITY']='Santa Clarita'
vac_1718.loc[vac_1718.CITY=='Valley Glen', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Valley Village', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Van Nuys', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Venice', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Ventura', 'CITY']="San Buenaventura (Ventura)"
vac_1718.loc[vac_1718.CITY=='Walnut Creet', 'CITY']='Walnut Creek'
vac_1718.loc[vac_1718.CITY=='Walnut Valley', 'CITY']='Walnut'
vac_1718.loc[vac_1718.CITY=='West Hills', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Westchester', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Westminister', 'CITY']='Westminster'
vac_1718.loc[vac_1718.CITY=='Wilmington', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Winnetka', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Woodland Hills', 'CITY']='Los Angeles'

vac_1718.loc[vac_1718.CITY=='Angels City', 'CITY']='Angels'
vac_1718.loc[vac_1718.CITY=='Cardiff-By-The-Sea', 'CITY']='Encinitas'
vac_1718.loc[vac_1718.CITY=='Davis,', 'CITY']='Davis'
vac_1718.loc[vac_1718.CITY=='La Cañada', 'CITY']='La Canada Flintridge'
vac_1718.loc[vac_1718.CITY=='La Canada', 'CITY']='La Canada Flintridge'
vac_1718.loc[vac_1718.CITY=='Los Nietos', 'CITY']='West Whittier-Los Nietos'
vac_1718.loc[vac_1718.CITY=='Montclair,', 'CITY']='Montclair'
vac_1718.loc[vac_1718.CITY=='Newport Coast', 'CITY']='Newport Coast'
vac_1718.loc[vac_1718.CITY=='South San Francisoc', 'CITY']='South San Francisco'
vac_1718.loc[vac_1718.CITY=='Sunland-Tujunga', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Arcata Ca', 'CITY']='Arcata'
vac_1718.loc[vac_1718.CITY=='Berkeley Ave', 'CITY']='Berkeley'
vac_1718.loc[vac_1718.CITY=='Camp Pendelton', 'CITY']='Camp Pendleton North'
vac_1718.loc[vac_1718.CITY=='Carmicheal', 'CITY']='Carmichael'
vac_1718.loc[vac_1718.CITY=='Laguan Niguel', 'CITY']='Laguna Niguel'
vac_1718.loc[vac_1718.CITY=='Laguna Niguel Ste 3409', 'CITY']='Laguna Niguel'
vac_1718.loc[vac_1718.CITY=='Lapuente', 'CITY']='La Puente'
vac_1718.loc[vac_1718.CITY=='Las Flores', 'CITY']='Las Flores'
vac_1718.loc[vac_1718.CITY=='Mt. View', 'CITY']='Mountain View'
vac_1718.loc[vac_1718.CITY=='Mision Viejo', 'CITY']='Mission Viejo'
vac_1718.loc[vac_1718.CITY=='S Lake Tahoe', 'CITY']='South Lake Tahoe'
vac_1718.loc[vac_1718.CITY=='Saint Helena', 'CITY']='St. Helena'
vac_1718.loc[vac_1718.CITY=='Santa Clara Ca', 'CITY']='Santa Clara'
vac_1718.loc[vac_1718.CITY=='St Helena', 'CITY']='St. Helena'
vac_1718.loc[vac_1718.CITY=='St Helena', 'CITY']='St. Helena'
vac_1718.loc[vac_1718.CITY=='Stantan', 'CITY']='Stanton'
vac_1718.loc[vac_1718.CITY=='Stinson', 'CITY']='Stinson Beach'

vac_1718.loc[vac_1718.CITY=='Vandenberg Afb', 'CITY']='Vandenberg AFB'
vac_1718.loc[vac_1718.CITY=='Travis Afb', 'CITY']='Travis AFB'
vac_1718.loc[vac_1718.CITY=='Beale Afb', 'CITY']='Beale AFB'

vac_1718.loc[vac_1718.CITY=='"Avalon, Catalina Isl"', 'CITY']='Avalon'
vac_1718.loc[vac_1718.CITY=='"Beale Afb, Ca"', 'CITY']='Beale AFB'
vac_1718.loc[vac_1718.CITY=='"Menifee, Ca"', 'CITY']='Menifee'
vac_1718.loc[vac_1718.CITY=='"Palm Springs, Ca"', 'CITY']='Palm Springs'
vac_1718.loc[vac_1718.CITY=='Aliso Viejo, Ca.', 'CITY']='Aliso Viejo'
vac_1718.loc[vac_1718.CITY=='Avalon, Catalina Isl', 'CITY']='Avalon'
vac_1718.loc[vac_1718.CITY=='Beale Afb, Ca', 'CITY']='Beale AFB'
vac_1718.loc[vac_1718.CITY=='Camarilla', 'CITY']='Camarillo'
vac_1718.loc[vac_1718.CITY=='Camp Pendleton', 'CITY']='Camp Pendleton North'
vac_1718.loc[vac_1718.CITY=='Capinteria', 'CITY']='Carpinteria'
vac_1718.loc[vac_1718.CITY=='Carpenteria', 'CITY']='Carpinteria'
vac_1718.loc[vac_1718.CITY=='Castic', 'CITY']='Castaic'
vac_1718.loc[vac_1718.CITY=='Clear Lake', 'CITY']='Clearlake'
vac_1718.loc[vac_1718.CITY=='Coto De Caza', 'CITY']='Coto de Caza'
vac_1718.loc[vac_1718.CITY=='E. Palo Alto', 'CITY']='East Palo Alto'
vac_1718.loc[vac_1718.CITY=='El Sobrante', 'CITY']='El Sobrante (Contra Costa County)'
vac_1718.loc[vac_1718.CITY=='Fairfield Ca', 'CITY']='Fairfield'
vac_1718.loc[vac_1718.CITY=='Firebaugh Ca', 'CITY']='Firebaugh'
vac_1718.loc[vac_1718.CITY=='Fort Dick', 'CITY']='Fort Dick'
vac_1718.loc[vac_1718.CITY=='Ft Irwin', 'CITY']='Fort Irwin'
vac_1718.loc[vac_1718.CITY=='Grover City', 'CITY']='Grover Beach'
vac_1718.loc[vac_1718.CITY=='Hoopa', 'CITY']='Hoopa'
vac_1718.loc[vac_1718.CITY=='Hunington Beach', 'CITY']='Huntington Beach'
vac_1718.loc[vac_1718.CITY=='Huntington Beach', 'CITY']='Huntington Beach'
vac_1718.loc[vac_1718.CITY=="King'S Beach", 'CITY']='Kings Beach'
vac_1718.loc[vac_1718.CITY=='Ladrea Ranch', 'CITY']='Ladera Ranch'
vac_1718.loc[vac_1718.CITY=='Lancaster, Ca', 'CITY']='Lancaster'
vac_1718.loc[vac_1718.CITY=='Las Flores', 'CITY']='Las Flores'
vac_1718.loc[vac_1718.CITY=='Lemore Nas', 'CITY']='Lemoore'
vac_1718.loc[vac_1718.CITY=='Long Beach', 'CITY']='Long Beach'
vac_1718.loc[vac_1718.CITY=='Los Alto Hills', 'CITY']='Los Altos Hills'
vac_1718.loc[vac_1718.CITY=='Los Angeles, Ca', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Los Flores', 'CITY']='Las Flores (Orange County)'
vac_1718.loc[vac_1718.CITY=='Marina Del Rey', 'CITY']='Marina del Rey'
vac_1718.loc[vac_1718.CITY=='Mcarthur', 'CITY']='McArthur'
vac_1718.loc[vac_1718.CITY=='Mccloud', 'CITY']='McCloud'
vac_1718.loc[vac_1718.CITY=='Mcfarland', 'CITY']='McFarland'
vac_1718.loc[vac_1718.CITY=='Mckinleyville', 'CITY']='McKinleyville'
vac_1718.loc[vac_1718.CITY=='Mckittrick', 'CITY']='McKittrick'
vac_1718.loc[vac_1718.CITY=='Menifee, Ca', 'CITY']='Menifee'
vac_1718.loc[vac_1718.CITY=='Mountain Veiw', 'CITY']='Mountain View'
vac_1718.loc[vac_1718.CITY=='Mountian View', 'CITY']='Mountain View'
vac_1718.loc[vac_1718.CITY=='Mt Shasta', 'CITY']='Mount Shasta'
vac_1718.loc[vac_1718.CITY=='Newport Coast', 'CITY']='Newport Coast'
vac_1718.loc[vac_1718.CITY=='No. Hollywood', 'CITY']='North Hollywood'
vac_1718.loc[vac_1718.CITY=='Northrige', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Onatrio', 'CITY']='Ontario'
vac_1718.loc[vac_1718.CITY=='Pala', 'CITY']='Pala'
vac_1718.loc[vac_1718.CITY=='Palm Springs, Ca', 'CITY']='Palm Springs'
vac_1718.loc[vac_1718.CITY=='Palmdale,', 'CITY']='Palmdale'
vac_1718.loc[vac_1718.CITY=='Palos Verdes Peninsu', 'CITY']='Palos Verdes Estates'
vac_1718.loc[vac_1718.CITY=='Poplar', 'CITY']='Poplar-Cotton Center'
vac_1718.loc[vac_1718.CITY=='Presidio Of Sf', 'CITY']='San Francisco'
vac_1718.loc[vac_1718.CITY=='<NAME>', 'CITY']='San Diego'
vac_1718.loc[vac_1718.CITY=='Rolling Hills Est.', 'CITY']='Rolling Hills Estates'
vac_1718.loc[vac_1718.CITY=='Sacramento, Ca', 'CITY']='Sacramento'
vac_1718.loc[vac_1718.CITY=='San Bernadino', 'CITY']='San Bernadino'
vac_1718.loc[vac_1718.CITY=='San Miguel', 'CITY']='San Miguel'
vac_1718.loc[vac_1718.CITY=='Santa Barbra', 'CITY']='Santa Barbara'
vac_1718.loc[vac_1718.CITY=='Simi Valley', 'CITY']='Simi Valley'
vac_1718.loc[vac_1718.CITY=='So. Lake Tahoe', 'CITY']='South Lake Tahoe'
vac_1718.loc[vac_1718.CITY=='So. San Francisco', 'CITY']='South San Francisco'
vac_1718.loc[vac_1718.CITY=='Spring Valley', 'CITY']='Spring Valley'
vac_1718.loc[vac_1718.CITY=='Stevenson', 'CITY']='Stevenson Ranch'
vac_1718.loc[vac_1718.CITY=='Thousand Oak', 'CITY']='Thousand Oaks'
vac_1718.loc[vac_1718.CITY=='Tranquility', 'CITY']='Tranquillity'
vac_1718.loc[vac_1718.CITY=='Travis AFB', 'CITY']='Fairfield'
vac_1718.loc[vac_1718.CITY=='W. Hollywood', 'CITY']='West Hollywood'
vac_1718.loc[vac_1718.CITY=='West Los Angeles', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Westlake', 'CITY']='Westlake Village'
vac_1718.loc[vac_1718.CITY=='Westminter', 'CITY']='Westminster'
vac_1718.loc[vac_1718.CITY=='Yermo', 'CITY']='Yermo'

vac_1718.loc[vac_1718.CITY=='El Sobrante', 'CITY']='El Sobrante (Contra Costa County)'
vac_1718.loc[vac_1718.CITY=='Las Flores', 'CITY']='Las Flores (Orange County)'
vac_1718.loc[vac_1718.CITY=='Los Nietos', 'CITY']='West Whittier-Los Nietos'
vac_1718.loc[vac_1718.CITY=='West Whittier', 'CITY']='West Whittier-Los Nietos'
vac_1718.loc[vac_1718.CITY=='Paso Robles', 'CITY']='El Paso de Robles (Paso Robles)'
vac_1718.loc[vac_1718.CITY=='El Paso de Robles', 'CITY']='El Paso de Robles (Paso Robles)'
vac_1718.loc[vac_1718.CITY=='Piñon Hills', 'CITY']='Pinon Hills'
vac_1718.loc[vac_1718.CITY=='San Miguel', 'CITY']='San Miguel (San Luis Obispo County)'
vac_1718.loc[vac_1718.CITY=='Spring Valley', 'CITY']='Spring Valley (San Diego County)'

# Fix more typos
vac_1718.loc[vac_1718.CITY=='Ananheim', 'CITY']='Anaheim'
vac_1718.loc[vac_1718.CITY=='Costa Mesa,', 'CITY']='Costa Mesa'
vac_1718.loc[vac_1718.CITY=='Paso Robles', 'CITY']='El Paso De Robles (Paso Robles)'
vac_1718.loc[vac_1718.CITY=='El Paso de Robles', 'CITY']='El Paso De Robles (Paso Robles)'
vac_1718.loc[vac_1718.CITY=='Chasworth', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Huntington  Beach', 'CITY']='Huntington Beach'
vac_1718.loc[vac_1718.CITY=='Long  Beach', 'CITY']='Long Beach'
vac_1718.loc[vac_1718.CITY=='Mt Baldy', 'CITY']='Mount Baldy'
vac_1718.loc[vac_1718.CITY=='<NAME>', 'CITY']='Mount Baldy'
vac_1718.loc[vac_1718.CITY=='North Hollywood', 'CITY']='Los Angeles'
vac_1718.loc[vac_1718.CITY=='Oneals', 'CITY']="O'Neals"
vac_1718.loc[vac_1718.CITY=='Canyon County', 'CITY']='Santa Clarita'
vac_1718.loc[vac_1718.CITY=='Coaresgold', 'CITY']='Coarsegold'
vac_1718.loc[vac_1718.CITY=='El Toro', 'CITY']='Lake Forest'
vac_1718.loc[vac_1718.CITY=='Herber', 'CITY']='Heber'
vac_1718.loc[vac_1718.CITY=='Hespera', 'CITY']='Hesperia'
vac_1718.loc[vac_1718.CITY=='Los Oso', 'CITY']='Los Osos'
vac_1718.loc[vac_1718.CITY=='Newport Coast', 'CITY']='Newport Beach'
vac_1718.loc[vac_1718.CITY=='Pinedale', 'CITY']='Fresno'
vac_1718.loc[vac_1718.CITY=='Rancho Tehema', 'CITY']='Rancho Tehama Reserve'
vac_1718.loc[vac_1718.CITY=='Redding ', 'CITY']='Redding'
vac_1718.loc[vac_1718.CITY=='Riversidty', 'CITY']='Riverside'
vac_1718.loc[vac_1718.CITY=='San Bernadino', 'CITY']='San Bernardino'
vac_1718.loc[vac_1718.CITY=='Simi  Valley', 'CITY']='Simi Valley'
vac_1718.loc[vac_1718.CITY=='Hilmar', 'CITY']='Hilmar-Irwin'
vac_1718.rename(columns={'COUNTY': 'county',
                         'CITY': 'city',
                         'FACILITY_NUMBER': 'facility_num',
                         'FACILITY_NAME':'facility_name',
                         'pub_priv_headstart':'is_public',
                         'ENROLLMENT': 'enrollment',
                         'REPORTED': 'reported'
                        }, inplace=True)
vac_1718.loc[vac_1718.vac_info_type=='7th grade', 'vac_info_type']='7thGradeData'
vac_1718.loc[vac_1718.vac_info_type=='childcare', 'vac_info_type']='ChildCareData'
vac_1718.loc[vac_1718.vac_info_type=='kindergarten', 'vac_info_type']='KindergartenData'
#Get count of students vaccinated by county,city and grade
vac_n=vac_1718.groupby(by=['county','city','vac_info_type'], as_index=False)['n'].sum()
vac_n
#Export count of students vaccinated by county,city and grade
vac_n.to_csv("vac_n_table1718.CSV")
#Get average percentage of students vaccinated by county,city and grade
vac_pct=vac_1718.groupby(by=['county','city','vac_info_type'], as_index=False)['pct'].mean()
vac_pct
#Export average percentage of students vaccinated by county,city and grade
vac_pct.to_csv("vac_pct_table1718.CSV")
# create grouping by county for comparison to outbreak incidence rates
#Get average percentage of students vaccinated by county and grade
vac_county_pct=vac_1718.groupby(by=['county','vac_info_type'], as_index=False)['pct'].mean()
vac_county_pct
#Export average percentage of students vaccinated by county,and grade
vac_county_pct.to_csv("vac_pct_county_table1718.CSV")
vac_1718.head()
#Export full cleaned data
vac_1718.to_csv("clean1718.csv", index=False)<jupyter_output><empty_output><jupyter_text>Census Data Cleaning  Demographics<jupyter_code># Import and clean census data for basic demographics age gender race
census16 = pd.read_csv("/Users/42/desktop/capstone/mids_capstone/vaxxfacts/raw_data/Census/ACS_16_demographic.csv",encoding = "ISO-8859-1",header=[0],na_values=['-'],)
census16.head()
demographic16 = census16[['GEO.id','GEO.id2','GEO.display-label',
                     'HC01_VC03',
                     'HC01_VC04','HC03_VC04',
                     'HC01_VC05','HC03_VC05',
                     'HC01_VC08','HC03_VC08',
                     'HC01_VC09','HC03_VC09',
                     'HC01_VC10','HC03_VC10',
                     'HC01_VC11','HC03_VC11',
                     'HC01_VC12','HC03_VC12',
                     'HC01_VC13','HC03_VC13',
                     'HC01_VC14','HC03_VC14',
                     'HC01_VC15','HC03_VC15',
                     'HC01_VC16','HC03_VC16',
                     'HC01_VC17','HC03_VC17',
                     'HC01_VC18','HC03_VC18',
                     'HC01_VC19','HC03_VC19',
                     'HC01_VC20','HC03_VC20',
                     'HC01_VC23','HC03_VC23',
                     'HC01_VC88','HC03_VC88',
                     'HC01_VC94','HC03_VC94',
                     'HC01_VC95','HC03_VC95',
                     'HC01_VC96','HC03_VC96',
                     'HC01_VC97','HC03_VC97',
                     'HC01_VC98','HC03_VC98',
                     'HC01_VC99','HC03_VC99',
                     'HC01_VC100','HC03_VC100',
                     'HC01_VC101','HC03_VC101',
                     'HC01_VC102','HC03_VC102'                 
        ]]
demographic16.dtypes
# demographic16[['HC01_VC03','HC01_VC04','HC01_VC05','HC01_VC08','HC01_VC09','HC01_VC10','HC01_VC11','HC01_VC12','HC01_VC13',
#           'HC01_VC14','HC01_VC15','HC01_VC16','HC01_VC17','HC01_VC18','HC01_VC19','HC01_VC20','HC01_VC23','HC01_VC88',
#           'HC01_VC94','HC01_VC95','HC01_VC96','HC01_VC97','HC01_VC98',
#           'HC01_VC99','HC01_VC100','HC01_VC101','HC01_VC102']] = demographic16[['HC01_VC03','HC01_VC04','HC01_VC05','HC01_VC08','HC01_VC09','HC01_VC10','HC01_VC11','HC01_VC12','HC01_VC13',
#           'HC01_VC14','HC01_VC15','HC01_VC16','HC01_VC17','HC01_VC18','HC01_VC19','HC01_VC20','HC01_VC23','HC01_VC88',
#           'HC01_VC94','HC01_VC95','HC01_VC96','HC01_VC97','HC01_VC98',
#           'HC01_VC99','HC01_VC100','HC01_VC101','HC01_VC102']].astype('float64')
#combine other race, two or more races (HC01_VC99, HC01_VC100, HC01_VC101, HC01_VC102)
demographic16['other']=pd.Series(census16['HC01_VC99']+census16['HC01_VC100']+census16['HC01_VC101']+census16['HC01_VC102'])
demographic16['other_pct']=pd.Series(census16['HC03_VC99']+census16['HC03_VC100']+census16['HC03_VC101']+census16['HC03_VC102'])
demographic16.rename(columns={'GEO.display-label': 'city',
                         'HC01_VC03': 'tot_pop', 
                         'HC01_VC04': 'male',
                         'HC01_VC05': 'female',
                         'HC01_VC08':'under_5',
                         'HC01_VC09':'5_9',
                         'HC01_VC10':'10_14',
                         'HC01_VC11':'15_19',
                         'HC01_VC12':'20_24',
                         'HC01_VC13':'25_34',
                         'HC01_VC14':'35_44',
                         'HC01_VC15':'45_54',
                         'HC01_VC16':'55_59',
                         'HC01_VC17':'60_64',
                         'HC01_VC18':'65_74',
                         'HC01_VC19':'75_84',
                         'HC01_VC20':'85_over',
                         'HC01_VC23':'median_age',
                         'HC01_VC88':'hispanic_latino',
                         'HC01_VC89':'mexican',
                         'HC01_VC94':'white',
                         'HC01_VC95':'black',
                         'HC01_VC96':'aian',
                         'HC01_VC97':'asian',
                         'HC01_VC98':'nhopi',

                         'HC03_VC04': 'male_pct',
                         'HC03_VC05': 'female_pct',
                         'HC03_VC08':'under_5_pct',
                         'HC03_VC09':'5_9_pct',
                         'HC03_VC10':'10_14_pct',
                         'HC03_VC11':'15_19_pct',
                         'HC03_VC12':'20_24_pct',
                         'HC03_VC13':'25_34_pct',
                         'HC03_VC14':'35_44_pct',
                         'HC03_VC15':'45_54_pct',
                         'HC03_VC16':'55_59_pct',
                         'HC03_VC17':'60_64_pct',
                         'HC03_VC18':'65_74_pct',
                         'HC03_VC19':'75_84_pct',
                         'HC03_VC20':'85_over_pct',
                         'HC03_VC23':'median_age_pct',
                         'HC03_VC88':'hispanic_latino_pct',
                         'HC03_VC89':'mexican_pct',
                         'HC03_VC94':'white_pct',
                         'HC03_VC95':'black_pct',
                         'HC03_VC96':'aian_pct',
                         'HC03_VC97':'asian_pct',
                         'HC03_VC98':'nhopi_pct'
                        }, 
                inplace=True)
# Other race categories
#                          'HC01_VC99':'other_only',
#                          'HC01_VC100':'more_two_races',
#                          'HC01_VC101':'more_two_races_other',
#                          'HC01_VC102':'more_two_races_other_exclude',

demographic16.dtypes
demographic16['city']=demographic16.city.replace(' city, California', '', regex=True)
demographic16['city']=demographic16.city.replace(' CDP, California', '', regex=True)
demographic16['city']=demographic16.city.replace(' town, California', '', regex=True)
## Uppercase cities
demographic16['city']=demographic16['city'].str.title()
demographic16=demographic16.drop(columns=['HC01_VC99','HC03_VC99','HC01_VC100','HC03_VC100','HC01_VC101','HC03_VC101','HC01_VC102','HC03_VC102'])
demographic16.head()<jupyter_output><empty_output><jupyter_text>Median Household Income and Health insurance Status Median Household Income has categories 2,500-, 250,000+ which was changed to 2500 and 250000.<jupyter_code># Import and clean acs data for income and insurance status
acs16 = pd.read_csv("/Users/42/desktop/capstone/mids_capstone/vaxxfacts/raw_data/Census/ACS_16_economic.csv", encoding = "ISO-8859-1", header=[0], na_values=['-']) 
acs16.head()
acs16=acs16[['GEO.id','GEO.id2','GEO.display-label','HC01_VC85','HC01_VC131','HC03_VC131',
                'HC01_VC132','HC03_VC132','HC01_VC133','HC03_VC133','HC01_VC134','HC03_VC134']]
income16=acs16
income16.head()
income16.dtypes
income16.rename(columns={'GEO.display-label': 'city',
                         'HC01_VC85': 'median_income',
                         'HC01_VC131': 'insurance',
                         'HC01_VC132': 'private_insure',
                         'HC01_VC133': 'public_insure',
                         'HC01_VC134': 'no_insurance',

                         'HC03_VC131': 'insurance_pct',
                         'HC03_VC132': 'private_insure_pct',
                         'HC03_VC133': 'public_insure_pct',
                         'HC03_VC134': 'no_insurance_pct'  
                        }, 
                inplace=True)
income16.dtypes
income16['city']=income16.city.replace(' CDP, California', '', regex=True)
income16['city']=income16.city.replace(' city, California', '', regex=True)
income16['city']=income16.city.replace(' town, California', '', regex=True)
income16.head()<jupyter_output><empty_output><jupyter_text>Join Demographics and income data<jupyter_code>pop_data=demographic16.merge(income16, left_on='GEO.id', right_on='GEO.id', how='outer')
pop_data.head()
list(pop_data.columns.values)
pop_data=pop_data.drop(columns=['GEO.id2_y','city_y'])
pop_data=pop_data.rename(columns={'GEO.id': 'geoid',
                         'GEO.id2_x': 'geoid2',
                         'city_x': 'city'})
# Fix City naming to be similar for special characters
pop_data.loc[pop_data.city=='La Cañada Flintridge', 'city']='La Canada Flintridge'
pop_data.loc[pop_data.city=='Piñon Hills', 'city']='Pinon Hills'

# Change census cities names that are present in multiple places to match vaccine data city
pop_data.loc[pop_data.city=='El Sobrante CDP (Contra Costa County), California', 'city']='El Sobrante (Contra Costa County)'
pop_data.loc[pop_data.city=='Edwards Afb', 'city']='Edwards AFB'
pop_data.loc[pop_data.city=='Las Flores CDP (Orange County), California', 'city']='Las Flores (Orange County)'

pop_data.head()
# Exported merged census data
pop_data.to_csv("pop_data16.csv", index=False)<jupyter_output><empty_output><jupyter_text>Join Vaccine data to Census data Join needs unique cities in the vaccine data to use as a key. Will need to figure out how to weight the different grade levels based on the census population numbers for age.<jupyter_code>vac_pop_1718=vac_1718.merge(pop_data, left_on='city', right_on='city', how='left')
vac_pop_1718
#Export full cleaned data
vac_pop_1718.to_csv("cleanjoin1718.csv", index=False)
vac_pop_1718.dtypes<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1831701346389229, 'fraction_numerical': 0.11254589963280294, 'mean_word_length': 5.355698171917542, 'pattern_counts': {'":': 0, '<': 35, '<?xml version=': 0, '>': 39, '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': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '77271', 'license': 'MIT', 'n_tokens_mistral': 16706, 'n_tokens_neox': 13482, 'n_words': 1779, 'path': 'vaxxfacts/cleaning_etl/Cleaning_File_Drafts/Data_Cleaning_2017-2018.ipynb', 'repo_name': 'jeffrey-hsu/mids_capstone', 'size': '32722'}