async def fetch_rental_prices(city: str, statecode: str):
"""
City-level historic violent crime and Property crime rate
`city`: The name of a U.S. city; e.g. `New York` or `Los Angeles`
`statecode`: e.g `NY` The [USPS 2 letter abbreviation](https://en.wikipedia.org/wiki/List_of_U.S._state_and_territory_abbreviations#Table) 📈
## Response
JSON string of walkscore for 5031 US cities
U.S. cities.
"""
query = """
SELECT *
FROM
crime
"""
columns = ['city', 'State', 'Violent_crime2018', 'Property_crime2018']
df = pd.read_json(fetch_query(query, columns))
# Input sanitization
city = city.title()
statecode = statecode.lower().upper()
# Handle Edge Cases:
# saint
if city[0:5] == "Saint":
city = city.replace("Saint", "St.")
elif city[0:3] == "St ":
city = city.replace("St", "St.")
# fort
elif city[0:3] == "Ft ":
city = city.replace("Ft", "Fort")
elif city[0:3] == "Ft.":
city = city.replace("Ft.", "Fort")
# multiple caps
elif city[0:2] == 'Mc':
city = city[:2] + city[2:].capitalize()
# Find matching metro-area in database
match = df.loc[(df.city.str.startswith(city))
& (df.State.str.contains(statecode))].head(1)
# Raise HTTPException for unknown inputs
if len(match) < 1:
raise HTTPException(status_code=404,
detail=f'{city}, {statecode} not found!')
# DF to dictionary
pairs = match.to_json(orient='records')
return pairs
return fetch_query(query, columns)
async def fetch_rental_prices(city: str, statecode: str):
"""
City-level historic rental prices for 1 bedroom apartments from
[Apartment List](https://www.apartmentlist.com/research/category/data-rent-estimates)
`city`: The name of a U.S. city; e.g. `New York` or `Los Angeles`
`statecode`: e.g `NY` The [USPS 2 letter abbreviation](https://en.wikipedia.org/wiki/List_of_U.S._state_and_territory_abbreviations#Table) 📈
## Response
JSON string of current rental price estimates 429
U.S. cities.
"""
query = """
SELECT *
FROM rental
"""
columns = ["city", "State", "Bedroom_Size", "Price_2020_08"]
df = pd.read_json(fetch_query(query, columns))
# Input sanitization
city = city.title()
statecode = statecode.lower().upper()
# Handle Edge Cases:
# saint
if city[0:5] == "Saint":
city = city.replace("Saint", "St.")
elif city[0:3] == "St ":
city = city.replace("St", "St.")
# fort
elif city[0:3] == "Ft ":
city = city.replace("Ft", "Fort")
elif city[0:3] == "Ft.":
city = city.replace("Ft.", "Fort")
# multiple caps
elif city[0:2] == 'Mc':
city = city[:2] + city[2:].capitalize()
# Find matching metro-area in database
match = df.loc[(df.city.str.startswith(city))
& (df.State.str.contains(statecode))].head(1)
# Raise HTTPException for unknown inputs
if len(match) < 1:
raise HTTPException(status_code=404,
detail=f'{city}, {statecode} not found!')
# DF to dictionary
pairs = match.to_json(orient='records')
return pairs
return fetch_query(query, columns)
async def cities_and_states_for_frontend():
"""
City-level historic rental prices for 1 bedroom apartments from
[Apartment List](https://www.apartmentlist.com/research/category/data-rent-estimates) 📈
## Response
JSON string of city names and statecodes found in the rental price data.
"""
query = """
SELECT
city,
"state"
FROM rp_clean1
WHERE bedroom_size = 'Studio'
"""
columns = ["city", "state"]
return fetch_query(query, columns)
async def fetch_rental_prices():
"""
City-level historic rental prices for 1 bedroom apartments from
[Apartment List](https://www.apartmentlist.com/research/category/data-rent-estimates) 📈
## Response
JSON string of current rental price estimates for more than 400
U.S. cities.
"""
query = """
SELECT
city,
"state",
bedroom_size,
price_2020_08
FROM rp_clean1
WHERE bedroom_size = '1br' OR bedroom_size = 'Studio'
"""
columns = ["city", "state", "bedroom_size", "price_2020_08"]
return fetch_query(query, columns)
async def fetch_static_data(city: str, statecode: str):
"""
Static city-level data for 135 US cities. Dataset compiled of rental price estimates,
walkscores, population, and most prevelant job industry for each city. 📈
## Path Parameters
`city`: The name of a U.S. city; e.g. `Atlanta` or `Los Angeles`
`statecode`: The [USPS 2 letter abbreviation](https://en.wikipedia.org/wiki/List_of_U.S._state_and_territory_abbreviations#Table)
(case insensitive) for any of the 50 states or the District of Columbia.
## Response
JSON string of various city statistics for 135 US Cities.
"""
query = """
SELECT *
FROM static
"""
columns = [
"city", "state", "studio", "onebr", "twobr", "threebr", "fourbr",
"walkscore", "population", "occ_title", "hourly_wage", "annual_wage",
"climate_zone", "simple_climate"
]
df = pd.read_json(fetch_query(query, columns))
# Input sanitization
city = city.title()
statecode = statecode.lower().upper()
# Handle Edge Cases:
# saint
if city[0:5] == "Saint":
city = city.replace("Saint", "St.")
elif city[0:3] == "St ":
city = city.replace("St", "St.")
# fort
elif city[0:3] == "Ft ":
city = city.replace("Ft", "Fort")
elif city[0:3] == "Ft.":
city = city.replace("Ft.", "Fort")
# multiple caps
elif city[0:2] == 'Mc':
city = city[:2] + city[2:].capitalize()
# Find matching metro-area in database
match = df.loc[(df.city.str.contains(city))
& (df.state.str.contains(statecode))]
# Raise HTTPException for unknown inputs
if len(match) < 1:
raise HTTPException(
status_code=404,
detail=
f'{city}, {statecode} not found or lacked enough data to be included here!'
)
# DF to dictionary
pairs = match.to_json(orient='records')
return pairs
async def viz(city: str, statecode: str,
city2: Optional[str] = None, statecode2: Optional[str] = None,
city3: Optional[str] = None, statecode3: Optional[str] = None):
"""
Visualize city-level rental price estimates from
[Apartment List](https://www.apartmentlist.com/research/category/data-rent-estimates) 📈
## Path Parameters
`city`: The name of a U.S. city; e.g. `Atlanta` or `Los Angeles`
`statecode`: The [USPS 2 letter abbreviation](https://en.wikipedia.org/wiki/List_of_U.S._state_and_territory_abbreviations#Table)
(case insensitive) for any of the 50 states or the District of Columbia.
## Query Parameters (Optional)
`city2`: The name of a second US city to make rental comparison.
`statecode2`: The statecode for second US city to make rental comparison.
`city3`: The name of a third US city to make rental comparison.
`statecode3`: The statecode for third US city to make rental comparison.
## Response
- Plotly bar chart of `city`'s rental price estimates
"""
# Get the city's rental price from database
query = """
SELECT
city,
"state",
bedroom_size,
price_2020_08
FROM rp_clean1
"""
columns = ["city", "state", "bedroom_size", "price_2020_08"]
# Re-formatting bedroom values. Data type/graph population issue.
bedrooms = {'Studio': 'Studio',
'1br': 'One',
'2br': 'Two',
'3br': 'Three',
'4br': 'Four'}
df = pd.read_json(fetch_query(query, columns))
df['bedroom_size'] = df['bedroom_size'].replace(bedrooms)
# Make a set of cities in the rental price data
citynames = set(df.city.to_list())
statecodes = set(df.state.to_list())
# Input sanitization
city = city.title()
statecode = statecode.lower().upper()
# Handle Edge Cases:
# saint
if city[0:5] == "Saint":
city = city.replace("Saint", "St.")
elif city[0:3] == "St ":
city = city.replace("St", "St.")
# fort
elif city[0:3] == "Ft ":
city = city.replace("Ft", "Fort")
elif city[0:3] == "Ft.":
city = city.replace("Ft.", "Fort")
# multiple caps
elif city[0:2] == 'Mc':
city = city[:2] + city[2:].capitalize()
if city2 and statecode2:
city2 = city2.title()
statecode2 = statecode2.lower().upper()
# saint
if city2[0:5] == "Saint":
city = city.replace("Saint", "St.")
elif city2[0:3] == "St ":
city2 = city2.replace("St", "St.")
# fort
elif city2[0:3] == "Ft ":
city2 = city2.replace("Ft", "Fort")
elif city2[0:3] == "Ft.":
city2 = city2.replace("Ft.", "Fort")
# multiple caps
elif city2[0:2] == 'Mc':
city2 = city2[:2] + city2[2:].capitalize()
if city3 and statecode3:
city3 = city3.title()
statecode3 = statecode3.lower().upper()
# saint
if city3[0:5] == "Saint":
city = city.replace("Saint", "St.")
elif city3[0:3] == "St ":
city3 = city3.replace("St", "St.")
# fort
elif city3[0:3] == "Ft ":
city3 = city3.replace("Ft", "Fort")
elif city3[0:3] == "Ft.":
city3 = city3.replace("Ft.", "Fort")
# multiple caps
elif city3[0:2] == 'Mc':
city3 = city3[:2] + city3[2:].capitalize()
# Raise HTTPException for unknown inputs
if city not in citynames:
raise HTTPException(
status_code=404,
detail=f'City name "{city}" not found!'
)
if city2 not in citynames and city2:
raise HTTPException(
status_code=404,
detail=f'City name "{city2}" not found!'
)
if city3 not in citynames and city3:
raise HTTPException(
status_code=404,
detail=f'City name "{city3}" not found!'
)
if statecode not in statecodes:
raise HTTPException(
status_code=404,
detail=f'Statecode "{statecode}" not found!'
)
if statecode2 not in statecodes and statecode2:
raise HTTPException(
status_code=404,
detail=f'Statecode "{statecode2}" not found!'
)
if statecode3 not in statecodes and statecode3:
raise HTTPException(
status_code=404,
detail=f'Statecode "{statecode3}" not found!'
)
city1_df = df[df.city == city]
# CASE: All are the same.
if ((city == city2 == city3) and
(statecode == statecode2 == statecode3)):
city2, statecode2 = None, None
city3, statecode3 = None, None
# CASE: 2nd and 3rd city, state are the same.
if city3 == city2 and statecode3 == statecode2:
city3, statecode3 = None, None
# CASE: 1st and 3rd city, state are the same.
if city == city3 and statecode == statecode3:
city3, statecode3 = None, None
# CASE: 1st and 2nd city, state are the same.
if city2 == city and statecode2 == statecode:
if city3 and statecode3:
city2, statecode2 = city3, statecode3
city3, statecode3 = None, None
elif not city3 and not statecode3:
city2, statecode2 = None, None
if city and not city2 and not city3:
return single(city1_df, city, statecode)
if city and city2 and not city3:
city2_df = df[df.city == city2]
return two(city1_df, city2_df, city, statecode, city2, statecode2)
if city and city2 and city3:
city2_df = df[df.city == city2]
city3_df = df[df.city == city3]
return three(city1_df, city2_df, city3_df,
city, statecode, city2, statecode2, city3, statecode3)
async def most_prevalent_job_industry(city: str, statecode: str):
"""
Most prevalent job industry (city-level) per "Location Quotient" from
[Burea of Labor Statistics](https://www.bls.gov/oes/tables.htm) 📈
## Path Parameters
`city`: The name of a U.S. city; e.g. `Atlanta` or `Los Angeles`
`statecode`: The [USPS 2 letter abbreviation](https://en.wikipedia.org/wiki/List_of_U.S._state_and_territory_abbreviations#Table)
(case insensitive) for any of the 50 states or the District of Columbia.
## Response
JSON string of top ten most prevalent job industries for specified
U.S. city. (More than 300 searchable)
"""
query = """
SELECT *
FROM bls_jobs
WHERE annual_wage > 0
"""
columns = [
"city", "state", "occ_title", "jobs_1000", "loc_quotient",
"hourly_wage", "annual_wage"
]
df = pd.read_json(fetch_query(query, columns))
# Input sanitization
city = city.title()
statecode = statecode.lower().upper()
# Handle Edge Cases:
# saint
if city[0:5] == "Saint":
city = city.replace("Saint", "St.")
elif city[0:3] == "St ":
city = city.replace("St", "St.")
# fort
elif city[0:3] == "Ft ":
city = city.replace("Ft", "Fort")
elif city[0:3] == "Ft.":
city = city.replace("Ft.", "Fort")
# multiple caps
elif city[0:2] == 'Mc':
city = city[:2] + city[2:].capitalize()
# Find matching metro-area in database
match = df.loc[(df.city.str.contains(city))
& (df.state.str.contains(statecode))]
# Raise HTTPException for unknown inputs
if len(match) < 1:
raise HTTPException(status_code=404,
detail=f'{city}, {statecode} not found!')
# Subset of top jobs for matching city
sub = match.sort_values(['city', 'loc_quotient'],
ascending=False).groupby('city').head(10)
sub = sub.reset_index()
sub = sub.drop("index", axis=1)
# DF to dictionary
pairs = sub.to_json(orient='records')
return pairs
async def most_prevalent_industry_visualization(city: str,
statecode: str,
view=False):
"""
Most prevalent job industry (city-level) per "Location Quotient" from
[Burea of Labor Statistics](https://www.bls.gov/oes/tables.htm) 📈
## Path Parameters
`city`: The name of a U.S. city; e.g. `Atlanta` or `Los Angeles`
`statecode`: The [USPS 2 letter abbreviation](https://en.wikipedia.org/wiki/List_of_U.S._state_and_territory_abbreviations#Table)
(case insensitive) for any of the 50 states or the District of Columbia.
`view`: If 'True' (string), returns a PNG instead of JSON
## Response
Visualization of most prevelant job industries for more than 350
U.S. cities.
"""
# Query for all jobs that have associated annual wage data
query = """
SELECT *
FROM bls_jobs
WHERE annual_wage > 0
"""
columns = [
"city", "state", "occ_title", "jobs_1000", "loc_quotient",
"hourly_wage", "annual_wage"
]
df = pd.read_json(fetch_query(query, columns))
# Input sanitization
city = city.title()
statecode = statecode.lower().upper()
# Handle Edge Cases:
# saint
if city[0:5] == "Saint":
city = city.replace("Saint", "St.")
elif city[0:3] == "St ":
city = city.replace("St", "St.")
# fort
elif city[0:3] == "Ft ":
city = city.replace("Ft", "Fort")
elif city[0:3] == "Ft.":
city = city.replace("Ft.", "Fort")
# multiple caps
elif city[0:2] == 'Mc':
city = city[:2] + city[2:].capitalize()
# Find matching metro-area in database
match = df.loc[(df.city.str.contains(city))
& (df.state.str.contains(statecode))]
# Raise HTTPException for unknown inputs
if len(match) < 1:
raise HTTPException(status_code=404,
detail=f'{city}, {statecode} not found!')
# Subset of top jobs for matching city
sub = match.sort_values(['city', 'loc_quotient'],
ascending=False).groupby('city').head(10)
sub = sub.reset_index()
sub = sub.drop("index", axis=1)
### Begin Visualization
styling = dict()
styling['city1color'] = '#CC0000' # red
# Set background to be transparent.
layout = go.Layout(paper_bgcolor='rgba(0,0,0,0)',
plot_bgcolor='rgba(0,0,0,0)')
# Set Title
styling["title"] = "Hover over bars for Job Industry"
x = sub["occ_title"]
y = sub["annual_wage"]
color_scale = "tealgrn"
fig = go.Figure(data=go.Bar(name=f'{city}, {statecode}',
x=x,
y=y,
marker=dict(color=y, colorscale=color_scale)),
layout=layout)
fig.update_layout(
barmode='group',
title_text=styling.get('title'),
xaxis_title='10 Most Prevelant Jobs (left to right, descending)',
yaxis_title='Average Annual Salary',
font=dict(family='Open Sans, extra bold', size=10),
height=412,
width=640)
fig.update_xaxes(showticklabels=True) # hide all the xticks
if view and view.title() == "True":
img = fig.to_image(format="png")
return StreamingResponse(io.BytesIO(img), media_type="image/png")
return fig.to_json()
async def advanced_search(popmin: int,
br_size: int,
max_rent: int,
climate: str,
popmax=50_000_000):
"""
Advanced search function which locates matching cities based on specified
criteria.
## Path Parameters
`popmin`: The minimum desired population size; e.g. `0` or `75000`
`popmax`: (Optional) The maximum desired population size; e.g. `699999`
`br_size`: The number of bedrooms for which `max_rent` will be applied; e.g. `0` for studio,
`4` for four bedrooms
`max_rent`: The maximum rent amount for corresponding `br_size`; e.g. `1500` or `2500`
`climate`: The preferred climate; e.g. `cold`, `mild`, or `hot`
## Response
JSON string of all matching cities per specified criteria.
"""
if br_size == 0:
br_size = "studio"
elif br_size == 1:
br_size = "onebr"
elif br_size == 2:
br_size = "twobr"
elif br_size == 3:
br_size = "threebr"
elif br_size == 4:
br_size = "fourbr"
query = f"""
SELECT *
FROM "static"
WHERE population >= {popmin} AND population <= {popmax}
AND {br_size} <= {max_rent}
AND simple_climate='{climate}'
"""
columns = [
"city", "state", "studio", "onebr", "twobr", "threebr", "fourbr",
"walkscore", "population", "occ_title", "hourly_wage", "annual_wage",
"climate_zone", "simple_climate"
]
df = pd.read_json(fetch_query(query, columns))
# Input sanitization - will need different sanitization logic
# city = city.title()
# statecode = statecode.lower().upper()
# Raise HTTPException if no matches found
if len(df) < 1:
raise HTTPException(
status_code=404,
detail=
f'No cities found that match your criteria ... please try again!')
# DF to dictionary
pairs = df.to_json(orient='records')
print("Number of Cities:", len(df))
return pairs
async def fetch_census_population_data(city: str, statecode: str):
"""
Population data (city-level) from
[US Census Bureau](https://www.census.gov/data/tables/time-series/demo/popest/2010s-total-cities-and-towns.html) 📈
## Path Parameters
`city`: The name of a U.S. city; e.g. `Atlanta` or `Los Angeles`
`statecode`: The [USPS 2 letter abbreviation](https://en.wikipedia.org/wiki/List_of_U.S._state_and_territory_abbreviations#Table)
(case insensitive) for any of the 50 states or the District of Columbia.
## Response
JSON string of city population stats for more than 500
U.S. cities.
"""
query = """
SELECT
city,
state,
popestimate2019
FROM census
"""
columns = ["city", "state", "popestimate2019"]
df = pd.read_json(fetch_query(query, columns))
# Input sanitization
city = city.title()
statecode = statecode.lower().upper()
# Handle Edge Cases:
# saint
if city[0:5] == "Saint":
city = city.replace("Saint", "St.")
elif city[0:3] == "St ":
city = city.replace("St", "St.")
# fort
elif city[0:3] == "Ft ":
city = city.replace("Ft", "Fort")
elif city[0:3] == "Ft.":
city = city.replace("Ft.", "Fort")
# multiple caps
elif city[0:2] == 'Mc':
city = city[:2] + city[2:].capitalize()
# Find matching metro-area in database
match = df.loc[(df.city.str.startswith(city))
& (df.state.str.contains(statecode))].head(1)
# Raise HTTPException for unknown inputs
if len(match) < 1:
raise HTTPException(status_code=404,
detail=f'{city}, {statecode} not found!')
# DF to dictionary
pairs = match.to_json(orient='records')
return pairs
