create cities dictionary

city_dict.py

from state_abbr import us_state_abbrev as abbr

# create city state list
cities = pd.read_excel('notebooks/datasets/data/schools/csv/List of Cities.xlsx')

# just get the second and third colun
cities = cities[['Unnamed: 1','Unnamed: 2']]

# create new dictionary with reversed key, value pairs
full = dict(map(reversed, abbr.items()))

# map state abbreviations to full name
cities['states'] = cities['Unnamed: 2'].map(full)

# making sure state/city combo conform to url format of "-" for " "
cities['states'] = cities['states'].str.strip()
cities['states'] = cities['states'].str.replace(" ", "-")
cities['Unnamed: 1'] = cities['Unnamed: 1'].str.replace(" ", "-")

# remove extraneous header rows
cities = cities.iloc[2:]
cities['city'] = (cities['states'] + '/'+ cities['Unnamed: 1']).str.lower()
print(cities.head())

# persist by creating new csv
cities.to_csv('notebooks/datasets/data/schools/csv/cities.csv')