A Data Tour Through Los Angeles Neighborhoods

# Download LA crime data
# !wget -O 'crime_data.csv' 'https://data.lacity.org/api/views/y8tr-7khq/rows.csv?accessType=DOWNLOAD' # uncomment if file hasn't been downloaded before
# print('LA crime data downloaded!')

# import required libraries
import pandas as pd
import requests
import numpy as np
import pprint
from pandas.io.json import json_normalize
# !conda install -q -c conda-forge geopy --yes # uncomment if library not installed
from geopy.geocoders import Nominatim # module to convert an address into latitude and longitude values
# !conda install -q -c conda-forge folium=0.5.0 --yes # uncomment if library not installed
import folium
# !pip install -q python-zillow # uncomment if library not installed
# import zillow
# !pip install -q shapely # uncomment if library not installed
from shapely.geometry import shape, Point
import json
from bs4 import BeautifulSoup
from matplotlib import cm
import matplotlib.colors

print('Libraries loaded')

# set up credentials
# FOURSQUARE API credentials
CLIENT_ID = '' # your Foursquare ID
CLIENT_SECRET = '' # your Foursquare Secret
VERSION = '20180604'
LIMIT = 30

# scrap LA neighborhood list from LA Times's website.
url = requests.get('http://maps.latimes.com/neighborhoods/population/total/neighborhood/list/').text
soup = BeautifulSoup(url, 'lxml')

# print(soup.prettify())

# extract the list of cities from LA
my_table = soup.find('table', {'class': 'datagrid'}) # returns a bs4 object
neighborhoods = []
populations = []

for row in my_table.find_all('a', href=True):
    neighborhoods.append(row.find(text=True))
    
for row in my_table.find_all('td', text=True):
    populations.append(row.find(text=True))
populations = populations[1::2] # skip rankings

neigh_pop_df = pd.DataFrame({'Neighborhood':neighborhoods, 'Population':populations})

# clean up neighborhood names and remove ones that don't return coordinates 
drop_neighborhoods = ['Unincorporated Santa Monica Mountains', 
                      'Castaic Canyons', 
                      'Tujunga Canyons',
                     'Unincorporated Santa Susana Mountains',
                     'Unincorporated Catalina Island']
neigh_pop_df = neigh_pop_df[~neigh_pop_df['Neighborhood'].isin(drop_neighborhoods)]
neigh_pop_df['Neighborhood'] = neigh_pop_df['Neighborhood'].str.split('/').str[0].str.split(',').str[0].str.split('e-L').str[0]
neigh_pop_df = neigh_pop_df.sort_values('Neighborhood', ascending=False)
neigh_pop_df['Population'] = pd.to_numeric(neigh_pop_df['Population'].str.replace(',', ''))

print(neigh_pop_df.shape)
neigh_pop_df.head()
# neigh_pop_df.shape
# neigh_pop_df.info()

# define function to get venue information based on latitude and longitude
def getCityVenues(names, radius=1000, limit=100):
    venues_list = []
    c = 1
    for name in names:
        # get latitude and longitudes of the city
        address = name + ', CA'
#         if c 
        print(str(c) + ': ' + address)
        geolocator = Nominatim(user_agent="la_explorer", timeout=3)
        location = geolocator.geocode(address)
        latitude = location.latitude
        longitude = location.longitude

        # make the API call
        url = 'https://api.foursquare.com/v2/venues/explore?client_id={}&client_secret={}&v={}&ll={},{}&radius={}&limit={}'.format(
            CLIENT_ID, 
            CLIENT_SECRET, 
            VERSION, 
            latitude, 
            longitude, 
            radius,
            limit)
        results = requests.get(url).json()["response"]['groups'][0]['items']
        
        # add venue name, location, category, and city
        venues_list.append([(
            name,
            latitude,
            longitude,
            v['venue']['name'],
            v['venue']['location']['lat'],
            v['venue']['location']['lng'],
            v['venue']['categories'][0]['name']) for v in results])
        
        c += 1

    la_venues = pd.DataFrame([item for venue_list in venues_list for item in venue_list])
    la_venues.columns = ['City',
                        'City latitude', 
                        'City longitude',
                        'Venue',
                        'Venue latitude',
                        'Venue longitude',
                        'Venue category']
    return la_venues

# get venue information for all neighborhoods
# venues_df = getCityVenues(names=neigh_pop_df['Neighborhood']) # takes a few minutes
# print('Venue information done')
# venues_df.to_pickle('venues.pkl')

# load from saved pickle
venues_df = pd.read_pickle('venues.pkl')
print('Venue information loaded')

# check the dataframe
print(venues_df.shape)
venues_df.head()

# check the # of venues returned for each city
venues_df.groupby('City').count();

# clean up data
# one hot encoding
la_venue_onehot = pd.get_dummies(venues_df[['Venue category']], prefix="", prefix_sep="")

# add neighborhood column back to dataframe
la_venue_onehot['City'] = venues_df['City'] 

# move neighborhood column to the first column
city_idx = int(np.where(la_venue_onehot.columns == 'City')[0])
other_idx = np.where(la_venue_onehot.columns != 'City')
fixed_columns = [la_venue_onehot.columns[city_idx]] + list(la_venue_onehot.columns[other_idx])
la_venue_onehot = la_venue_onehot[fixed_columns]

# group venues by city and calculate frequency of occurrence
la_venue_onehot_grouped = la_venue_onehot.groupby('City').mean().reset_index()
la_venue_onehot_grouped

# fixed_columns
# la_venue_onehot.columns[:100]
print(la_venue_onehot_grouped.shape)
la_venue_onehot_grouped.head()

# uncomment if file hasn't been downloaded before
# !wget -q -O 'la_rental_prices.csv' 'https://usc.data.socrata.com/api/views/4a97-v5tx/rows.csv?accessType=DOWNLOAD'
# print('LA rental prices data downloaded!')

# Clean up rental price data
prices_df = pd.read_csv('la_rental_prices.csv')
prices_df.head()
# prices_df.drop(['Policy Area', 'Dataset', 'Variable', 'Tract', 'GEOID', 'Row ID', 'Date'], axis=1, inplace=True)
prices_df.drop(['Policy Area', 'Dataset', 'Variable', 'Tract', 'GEOID', 'Row ID', 'Date',
       'Neighborhoods_from_gw34_aa8i', 'Neighborhoods_from_5jp5_kw3x',
       'New Neighborhoods_from_s3dq_ztay', 'layer_0_from_mgnu_zr5s',
       'layer_0_from_yr99_sgmi', 'LA Neighborhoods_from_5ikr_59ci',
       'City Boundaries_from_22pk_kkuz', 'Los Angeles Incorporated Cities',
       'la_county_tracts_from_jczq_z5ui', 'DPW_CITY_BOUNDARIES_from_dmu2_xaer',
       ':@computed_region_yak3_5aqv', ':@computed_region_izm9_hpec',
       ':@computed_region_aysi_wk4g', ':@computed_region_an3c_5a9u'], axis=1, inplace=True)

# select data for 2016 (most recent)
prices_df = prices_df[prices_df['Year'] == 2016]

# split location into latitude and longitude
coordinates = prices_df['Location'].str.split(',', expand=True)
coordinates.columns = ['Latitude', 'Longitude']
coordinates['Latitude'] = pd.to_numeric(coordinates['Latitude'].str.strip('('))
coordinates['Longitude'] = pd.to_numeric(coordinates['Longitude'].str.strip(')'))
prices_df = pd.concat([prices_df, coordinates], axis=1)
prices_df.drop(['Location'], axis=1, inplace=True)

# # drop rows without rent 
prices_df.dropna(inplace=True)

# keep only entries per neighborhood with largest tract number (assumed to be most recent)
prices_df = prices_df.sort_values('Tract Number', ascending=False).drop_duplicates('Neighborhood')
prices_df.reset_index(inplace=True)
prices_df.drop('index', axis=1, inplace=True)

# rename columns
col_names = ['Year', 'Median Rent', 'Tract Number', 'Neighborhood', 'Latitude', 'Longitude']
prices_df.columns = col_names

# # final clean up
prices_df.drop(['Year', 'Tract Number'], axis=1, inplace=True)

# prices_df.shape
prices_df.head()

# combine neighborhood, population, median rent, and location
neigh_rent_df = pd.merge(left=neigh_pop_df, right=prices_df, left_on='Neighborhood', right_on='Neighborhood')
print(neigh_rent_df.shape)
neigh_rent_df.head()

# read data
crime_df = pd.read_csv('crime_data.csv') # takes ~10 s to read

# regex to find rows from years 2014-2019
crime_df = crime_df[crime_df['Date Occurred'].str.contains('.*(201[4-9])', regex=True)]

# keep the columns we want
crime_df = crime_df.loc[:, ['Area ID', 'Area Name', 'Location ']].reset_index()

# separate Location into latitude and longitude
coordinates = crime_df['Location '].str.split(',', expand=True)
coordinates.columns = ['Latitude', 'Longitude']
coordinates['Latitude'] = pd.to_numeric(coordinates['Latitude'].str.strip('('))
coordinates['Longitude'] = pd.to_numeric(coordinates['Longitude'].str.strip(')'))
crime_df = pd.concat([crime_df, coordinates], axis=1)
crime_df.drop(['index', 'Location '], axis=1, inplace=True)

# group into neighborhoods
crime_df.tail()

# rank neighorhood safety based on the total crimes (i.e. lowest ranking means highest crime counts)
crime_counts = pd.DataFrame(crime_df['Area Name'].value_counts().sort_values()).reset_index()
# safety_rankings['Safety Ranking'] = list(pd.to_numeric(safety_rankings.index)+1)
crime_counts.columns = ['Area Name', 'Crime Counts']

# set average coordinates per area as location
neigh_coordinates = crime_df.groupby('Area Name')[['Latitude', 'Longitude']].agg([np.mean]).reset_index() # average coordinate of area
neigh_coordinates.columns = ['Area Name', 'Latitude', 'Longitude']

# combine both df together
safety_df = pd.merge(left=crime_counts, right=neigh_coordinates, left_on='Area Name', right_on='Area Name')
safety_df.head()

# Find the closest neighborhood to each police area by comparing Euclidean distances
closest_area = []
area_list = []
for lat_neigh, lng_neigh in zip(neigh_rent_df['Latitude'], neigh_rent_df['Longitude']):
    best_distance = 1e6
    for area, lat_area, lng_area in zip(safety_df['Area Name'], safety_df['Latitude'], safety_df['Longitude']):
        distance = np.sqrt((lat_neigh - lat_area)**2 + (lng_neigh - lng_area)**2)
        if distance < best_distance:
            best_distance = distance
            closest_area = area
    area_list.append(closest_area)
            
neigh_rent_df['Area Name'] = area_list
neigh_rent_df.head()

neigh_rent_crime_df = pd.merge(left=neigh_rent_df, 
                               right=safety_df, 
                               left_on='Area Name', 
                               right_on='Area Name',
                               suffixes=('_neigh', '_area'))
neigh_rent_crime_df.head()

# find crime rate per capita
area_population = neigh_rent_crime_df.groupby('Area Name')[['Population']].sum()
area_population.reset_index(inplace=True)
area_population.columns = ['Area Name', 'Area Population']

safety_df2 = safety_df.set_index('Area Name').join(area_population.set_index('Area Name'), lsuffix='_area', rsuffix='_neigh')
safety_df2['Crime Rate'] = safety_df2['Crime Counts']/safety_df2['Area Population']
safety_df2.reset_index(inplace=True)
safety_df2.sort_values('Crime Rate', ascending=True, inplace=True)
safety_df2['Safety Ranking'] = list(np.arange(safety_df2.shape[0], 0, -1) + 1)

neigh_rent_safety_df = pd.merge(left=neigh_rent_crime_df, right=safety_df2, on='Area Name')

# remove redundant columns
neigh_rent_safety_df.drop(['Latitude', 'Longitude', 'Crime Counts_y'], axis=1, inplace=True)

neigh_df_cleaned = neigh_rent_safety_df[['Neighborhood', 'Median Rent', 'Population', 'Safety Ranking']]

# normalize numerical columns
from sklearn import preprocessing
scaled_neigh_df = neigh_df_cleaned.copy()
scaler = preprocessing.MinMaxScaler()
cols = ['Median Rent', 'Population', 'Safety Ranking']
x_scaled = scaler.fit_transform(scaled_neigh_df[cols].values)
scaled_neigh_df.loc[:,cols] = x_scaled

print(scaled_neigh_df.shape)
scaled_neigh_df.head()

# finally, get the total combined df
df = pd.merge(scaled_neigh_df, la_venue_onehot_grouped, left_on='Neighborhood', right_on='City')

print(df.shape)
df.head()

# create map of New York using latitude and longitude values
la_lat = 34.0522
la_lng = -118.2437
map_la = folium.Map(location=[la_lat, la_lng], zoom_start=10)

# add markers to map
# show areas
for lat, lng, area in zip(safety_df['Latitude'], 
                          safety_df['Longitude'], 
                          safety_df['Area Name']):
    label = '{}'.format(area)
    label = folium.Popup(label, parse_html=True)
    folium.CircleMarker(
        [lat, lng],
        radius=5,
        popup=label,
        color='blue',
        fill=True,
        fill_color='#3186cc',
        fill_opacity=0.7,
        parse_html=False).add_to(map_la)  
    
# show neighborhoods
for lat, lng, city, area in zip(neigh_rent_safety_df['Latitude_neigh'], 
                                neigh_rent_safety_df['Longitude_neigh'], 
                                neigh_rent_safety_df['Neighborhood'], 
                                neigh_rent_safety_df['Area Name']):
    label = '{}, {}'.format(city, area)
    label = folium.Popup(label, parse_html=True)
    folium.CircleMarker(
        [lat, lng],
        radius=5,
        popup=label,
        color='green',
        fill=True,
        fill_color='#3186cc',
        fill_opacity=0.7,
        parse_html=False).add_to(map_la)  
    
map_la

from sklearn.cluster import KMeans

n_clusters = 10
df_cluster = df.drop(['Neighborhood_x', 'City'], axis=1)
kmeans = KMeans(n_clusters=n_clusters, random_state=0).fit(df_cluster)

kmeans.labels_[:10]

def return_most_common_venues(row, num_top_venues):
    row_categories = row.iloc[1:]
    row_categories_sorted = row_categories.sort_values(ascending=False)
    return row_categories_sorted.index.values[0:num_top_venues]

# create columns according to number of top venues
# columns = ['Neighborhood']
num_top_venues = 5
columns = ['Neighborhood']
for ind in np.arange(num_top_venues):
    try:
        columns.append('{}{} Most Common Venue'.format(ind+1, indicators[ind]))
    except:
        columns.append('{}th Most Common Venue'.format(ind+1))
        
final_df = pd.DataFrame(columns=columns)
final_df['Neighborhood'] = df['Neighborhood_x']


# get top venues
c = 1
for neighborhood in final_df['Neighborhood']:
    c += 1
    final_df.iloc[np.where(final_df['Neighborhood'] == neighborhood)[0], 1:] = return_most_common_venues(
        la_venue_onehot_grouped.iloc[np.where(la_venue_onehot_grouped['City'] ==  neighborhood)[0][0], 1:], 
        num_top_venues)
    
# put in safety ranking, median rent, and population
final_df = final_df.merge(neigh_df_cleaned, on='Neighborhood')
final_df = final_df.merge(neigh_rent_df[['Neighborhood', 'Latitude', 'Longitude']], on='Neighborhood')
final_df['Cluster'] = kmeans.labels_ + 1


print(final_df.shape)
final_df.head()
# final_df.head()

# create map of New York using latitude and longitude values
colors = []
cmap = cm.get_cmap('tab10', n_clusters)    # PiYG
for i in range(cmap.N):
    rgb = cmap(i)[:3] # will return rgba, we take only first 3 so we get rgb
    colors.append(matplotlib.colors.rgb2hex(rgb))

la_lat = 34.0522
la_lng = -118.2437
map_la = folium.Map(location=[la_lat, la_lng], zoom_start=10)
# colors = ['#ff00ff', '#ff0000', '#00ff00', '#00ffff', '#0000ff']

# show neighborhoods
for lat, lng, neighborhood, cluster in zip(final_df['Latitude'], 
                                           final_df['Longitude'], 
                                           final_df['Neighborhood'],
                                           final_df['Cluster']):
    label = '{} ({})'.format(neighborhood, cluster)
    label = folium.Popup(label, parse_html=True)
    folium.CircleMarker(
        [lat, lng],
        radius=5,
        popup=label,
        color=colors[cluster-1],
        fill=True,
        fill_color=colors[cluster-1],
        fill_opacity=0.7,
        parse_html=False).add_to(map_la)  

map_la

averages = final_df.groupby('Cluster')[['Median Rent', 'Safety Ranking', 'Population']].mean()
averages

# plot average median rent vs average safety ranking
averages.plot(x='Median Rent', y='Safety Ranking', kind='scatter');

# plot average median rent vs average safety ranking
averages.plot(x='Median Rent', y='Population', kind='scatter');

# plot average median rent vs average safety ranking
averages.plot(x='Safety Ranking', y='Population', kind='scatter');

final_df.groupby('Cluster')[['1th Most Common Venue', 
                              '2th Most Common Venue', 
                              '3th Most Common Venue',
                              '4th Most Common Venue',
                              '5th Most Common Venue']].agg(lambda x: x.value_counts().index[0])

# create columns according to number of top venues
# columns = ['Neighborhood']
num_top_venues = 10
indicators = ['st', 'nd', 'rd']
columns = ['Neighborhood']
for ind in np.arange(num_top_venues):
    try:
        columns.append('{}{} Most Common Venue'.format(ind+1, indicators[ind]))
    except:
        columns.append('{}th Most Common Venue'.format(ind+1))
        
top_columns = columns;
venues_df2 = pd.DataFrame(columns=columns)
venues_df2['Neighborhood'] = df['Neighborhood_x']


# get top venues
c = 1
for neighborhood in venues_df2['Neighborhood']:
    c += 1
    venues_df2.iloc[np.where(venues_df2['Neighborhood'] == neighborhood)[0], 1:] = return_most_common_venues(
        la_venue_onehot_grouped.iloc[np.where(la_venue_onehot_grouped['City'] ==  neighborhood)[0][0], 1:], 
        num_top_venues)

    
# put in safety ranking, median rent, and population
venues_df2 = venues_df2.merge(neigh_df_cleaned, on='Neighborhood')
venues_df2 = venues_df2.merge(neigh_rent_df[['Neighborhood', 'Latitude', 'Longitude']], on='Neighborhood')
venues_df2['Cluster'] = kmeans.labels_ + 1

# print(venues_df2.shape)
# venues_df2.head()

venues_df2.groupby('Cluster')[top_columns[1:]].agg(lambda x: x.value_counts().index[0])