<jupyter_start><jupyter_text>This Notebook for cityspire DS API- Datasets  - crime rates  - rental rates  - population  - walkability- JSON Response- JSON dict   Initial Exploratory Data Analysis (EDA)<jupyter_code>import pandas as pd
import numpy as np
# crime_rate dataset
url_cr = 'https://raw.githubusercontent.com/JeffreyAsuncion/LambdaLabs/main/cityspire-a-ds/Datasets_csv/2021_pred_crime.csv'
# rental_rate dataset
url_rr = 'https://raw.githubusercontent.com/JeffreyAsuncion/LambdaLabs/main/cityspire-a-ds/Datasets_csv/Rent_Predictions_Jan21.csv'
# population dataset
url_pop = 'https://raw.githubusercontent.com/JeffreyAsuncion/LambdaLabs/main/cityspire-a-ds/Datasets_csv/pop_2021.csv'
# walk_score dataset
url_walk = 'https://raw.githubusercontent.com/JeffreyAsuncion/LambdaLabs/main/cityspire-a-ds/Datasets_csv/walk.csv'
cr_df = pd.read_csv(url_cr)
rr_df = pd.read_csv(url_rr)
pop_df = pd.read_csv(url_pop)
walk_df = pd.read_csv(url_walk)<jupyter_output><empty_output><jupyter_text>Crime Rate<jupyter_code>cr_df.head()
# create columns to join datasets
cr_df["City_State"] = cr_df["city_state"]
# filter down the columns to those needed for the JOIN
cr_df = cr_df[["City_State", "crime_rate"]]
# number of unique City_State for Crime Rate Dataset
len(cr_df.City_State.unique())<jupyter_output><empty_output><jupyter_text>Rental Rates<jupyter_code>rr_df.head()
# number of unique City_State for Rental Rate Dataset
len(rr_df.City_State.unique())
rr_df.shape<jupyter_output><empty_output><jupyter_text>Walkability Score<jupyter_code>walk_df.head()
# number of unique City_State for Walk Score Dataset
len(walk_df.city_state.unique())
walk_df.shape<jupyter_output><empty_output><jupyter_text>Population<jupyter_code>pop_df.head()
pop_df.shape
pop_df["id_num"] = pop_df.index
# number of unique City_State for Population Dataset
len(pop_df.city_state.unique())<jupyter_output><empty_output><jupyter_text>First Join for Crime RateNaN fill with average<jupyter_code># Join the Population df with the Crime Rate df on the City_State column
join_df = pop_df.set_index('City_State').join(cr_df.set_index('City_State'))
join_df.head()
# Check to see if any rows were lost during the JOIN
join_df.shape
# make another id column
join_df["id"] = join_df["id_num"]
join_df.reset_index(inplace=True)
join_df.head()
# https://stackoverflow.com/questions/20461165/how-to-convert-index-of-a-pandas-dataframe-into-a-column
join_df = join_df.set_index('id')
join_df.head()
# Find any null values after the Population df and Crime Rate df are joined
join_df.isna().values.sum()
join_df.sample(10)
# Split the City_State column into the individual columns 
# to help fill the missing values according to State 
join_df[['CITY','STATE']] = join_df.City_State.str.split(",",expand=True)
join_df.columns
# number of states accounted for
len(join_df['STATE'].unique())
join_df['STATE'].unique()[0]
states = join_df['STATE'].unique()
# 50 US States including D.C.(District of Columbia) and Puerto Rico
states<jupyter_output><empty_output><jupyter_text>Crime rate fill with Average<jupyter_code>import numpy as np
import sys

# create a list for df of states crime rate
states_df = []

for i in range(len(states)):
    # filter df down to each state
    df = join_df[join_df['STATE'] == states[i]]
    # fill the NaN with the average for each state
    df['crime_rate'] = df['crime_rate'].replace(np.NaN, round(df.crime_rate.mean(), 2))
    # add df to list of states for concat below
    states_df.append(df)<jupyter_output>/usr/local/lib/python3.6/dist-packages/ipykernel_launcher.py:10: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  # Remove the CWD from sys.path while we load stuff.<jupyter_text>have a look at the fill and some viualizes<jupyter_code>states_df[11]
import seaborn as sns
import matplotlib.pyplot as plt

sns.lineplot(data=states_df[8], x='POPULATION', y="crime_rate")

# control x and y limits
# sns.plt.ylim(0, 20)
plt.xlim(0, 5000)
 
plt.show()

len(states_df)<jupyter_output><empty_output><jupyter_text>Concat the States and fill Nan with dataset average if state ave not available<jupyter_code>states_df[1].head()
# just need to join the df together to rebuild full dataframe
cr_impute_df = states_df[0]

for i in range(1, len(states_df)):
    cr_impute_df = pd.concat([cr_impute_df, states_df[i]])
cr_impute_df.shape
cr_impute_df.head()
# check for nans
cr_impute_df.isna().values.sum()
# fill nans with ave crime rate for the whole df
cr_impute_df['crime_rate'] = cr_impute_df['crime_rate'].replace(np.NaN, round(cr_impute_df.crime_rate.mean(), 2))
# check for nans
cr_impute_df.isna().values.sum()
print(cr_impute_df.shape)
cr_impute_df.head()<jupyter_output>(29626, 7)<jupyter_text>Second Join for Rental Rates<jupyter_code>rr_df.head()
rr_df["rental_rate"] = rr_df["Price"].copy()
rr_df = rr_df[["City_State", "rental_rate"]]
rr_df.head()

join_df2 = cr_impute_df.set_index('City_State').join(rr_df.set_index('City_State'))
print(join_df2.shape)
join_df2.head()
# make another id column
join_df2["id"] = join_df2["id_num"]
join_df2.reset_index(inplace=True)
# reset index to id
join_df2 = join_df2.set_index('id')
join_df2.head()
join_df2.isna().values.sum()
# plan to fill with state ave 
# if no state ave availbile then national ave
len(join_df2['STATE'].unique())<jupyter_output><empty_output><jupyter_text>Rental Rates fill with State Average<jupyter_code># create a list for df of states rental rate
states_df2 = []

for i in range(len(states)):
    # filter df down to each state
    df2 = join_df2[join_df2['STATE'] == states[i]]
    # fill the NaN with the average for each state
    df2['rental_rate'] = df2['rental_rate'].replace(np.NaN, round(df2.rental_rate.mean(), 2))
    # add df to list of states for concat below
    states_df2.append(df2)

df2.head()
states_df2[0].isna().values.sum()
states_df2[0].head()<jupyter_output><empty_output><jupyter_text>Rental Rates Concat the States and fill Nan with dataset average if state ave not available<jupyter_code># just need to join the df together to rebuild full dataframe
rr_impute_df = states_df2[0]

for i in range(1, len(states_df2)):
    rr_impute_df = pd.concat([rr_impute_df, states_df2[i]])
print(rr_impute_df.shape)
rr_impute_df.isna().values.sum()
# fill nans with ave
rr_impute_df['rental_rate'] = rr_impute_df['rental_rate'].replace(np.NaN, round(rr_impute_df.rental_rate.mean(), 2))
rr_impute_df.isna().values.sum()
print(rr_impute_df.shape)
rr_impute_df.head()<jupyter_output>(29626, 8)<jupyter_text>Third Join for Walk Score<jupyter_code>walk_df.head()
walk_df["City_State"] = walk_df["city_state"].copy()
walk_df["walk_score"] = walk_df["Walk Score"].copy()

walk_df = walk_df[["City_State", "walk_score"]]
print(walk_df.shape)
walk_df.head()
rr_impute_df.head()
join_df3 = rr_impute_df.set_index('City_State').join(walk_df.set_index('City_State'))
join_df3.shape
join_df3.isna().values.sum()
join_df3.head()
# make another id column
join_df3["id"] = join_df3["id_num"]
join_df3 = join_df3.set_index('id')
join_df3.head()<jupyter_output><empty_output><jupyter_text>Walk Score fill with Average<jupyter_code># create a list for df of states walk score
states_df3 = []

for i in range(len(states)):
    # filter df down to each state
    df3 = join_df3[join_df3['STATE'] == states[i]]
    # fill the NaN with the average for each state
    df3['walk_score'] = df3['walk_score'].replace(np.NaN, round(df3.walk_score.mean(), 2))
    # add df to list of states for concat below
    states_df3.append(df3)

# just need to join the df together to rebuild full dataframe
ws_impute_df = states_df3[0]

for i in range(1, len(states_df3)):
    ws_impute_df = pd.concat([ws_impute_df, states_df3[i]])
ws_impute_df.shape
walk_df.reset_index(inplace=True)
ws_impute_df.sample(20)
ws_impute_df.isna().values.sum()<jupyter_output><empty_output><jupyter_text>Walk Score Concat the States and fill Nan with dataset average if state ave not available<jupyter_code># fill nans with ave
ws_impute_df['walk_score'] = ws_impute_df['walk_score'].replace(np.NaN, round(ws_impute_df.walk_score.mean(), 2))
ws_impute_df.isna().values.sum()
ws_impute_df["population"] = ws_impute_df["POPULATION"].copy()
ws_impute_df['city_state'] = ws_impute_df["CITY"] + "," + ws_impute_df['STATE']
ws_impute_df[ws_impute_df["STATE"] == " New Jersey"]<jupyter_output><empty_output><jupyter_text>Save Dataset at csv after merge, impute, save<jupyter_code># filter the dataframe to the necessary columns
ws_impute_df = ws_impute_df[["city_state", "id_num", "population", "crime_rate", "rental_rate", "walk_score"]]
ws_impute_df.head()
ws_impute_df.isna().values.sum()
# Persist / Save the dataframe to csv file for future use
ws_impute_df.to_csv('pop_cc_rr_ws.csv', sep=',', index=False)<jupyter_output><empty_output><jupyter_text>Combine columns to form json response<jupyter_code># dictionary of dictionary

# https://stackoverflow.com/questions/59741934/python-pandas-merge-multiple-columns-into-a-dictionary-column
columns = ["id_num", "population", "crime_rate", "rental_rate", "walk_score"]
ws_impute_df['metadata'] = ws_impute_df[columns].to_dict(orient='records')
ws_impute_df.isna().values.sum()
ws_impute_df.head()
ws_impute_df[ws_impute_df["city_state"] == "Newark, New Jersey"]
ws_impute_df.columns
ws_impute_df.shape
ws_impute_df.describe()<jupyter_output><empty_output><jupyter_text>Create dictionary of City_State : {JSON Response}<jupyter_code># https://intellipaat.com/community/20442/python-pandas-dataframe-columns-convert-to-dict-key-and-value

pop_cr_rr_ws_dict = dict(zip(ws_impute_df.city_state, ws_impute_df.metadata))
pop_cr_rr_ws_dict["Alderpoint, California"]
pop_cr_rr_ws_dict["Newark, New Jersey"]
pop_cr_rr_ws_dict["Hawi, Hawaii"]
# export json to file

import json  
     
# Data to be written  
# pop_cr_dict
with open("predict_json.py", "w") as outfile:  
    json.dump(pop_cr_rr_ws_dict, outfile) 
# https://www.geeksforgeeks.org/how-to-convert-python-dictionary-to-json/


# Serializing json    
# json_object = json.dumps(pop_cr_dict, indent = 4)   
# print(json_object)
ws_impute_df.head()<jupyter_output><empty_output><jupyter_text>request for Ids for Web Team<jupyter_code>id_num__city_state = dict(zip(ws_impute_df.id_num, ws_impute_df.city_state))
id_num__city_state
# Data to be written  
with open("id_num.json", "w") as outfile:  
    json.dump(id_num__city_state, outfile) 
city_state__id_num = dict(zip(ws_impute_df.city_state, ws_impute_df.id_num))
city_state__id_num
# Data to be written  
with open("city_state_json.py", "w") as outfile:  
    json.dump(city_state__id_num, outfile)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1235965312642629, 'fraction_numerical': 0.011684162482884527, 'mean_word_length': 6.146771037181996, 'pattern_counts': {'":': 0, '<': 74, '<?xml version=': 0, '>': 74, 'https://': 9, 'lorem ipsum': 0, 'www.': 1, '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': '19', 'dir': 'jupyter-structured-clean-dedup', 'id': '369246', 'license': 'MIT', 'n_tokens_mistral': 4256, 'n_tokens_neox': 4121, 'n_words': 826, 'path': 'notebooks/CityspireDatasetJoin.ipynb', 'repo_name': 'JeffreyAsuncion/fastapi_ds_api', 'size': '10955'}