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