import numpy as np
import pandas as pd

police_station = pd.read_csv(
    './data_science/02. crime_in_Seoul.csv', 
    thousands=',',
    encoding='euc-kr'
                            )
police_station.head()

import googlemaps

gmaps = googlemaps.Client(key="AIzaSyC58AWrye6Z-2vAdoEVv_Xsf6sk6Vsg_90")

gmaps.geocode('서울중부경찰서', language='ko')

# 자료 형태 파악 : 리스트 [ 딕셔너리 { key : value } ] 
tmp = gmaps.geocode('서울종로경찰서', language='ko')[0].get('formatted_address')
tmp.split()

# 구 이름 얻기 
[ name for name in tmp.split() if name[-1] == '구'  ]

station_name=[]
for name in police_station['관서명']:
    station_name.append( '서울' + str(name[:-1]) + '경찰서' )
station_name[0:5]    

station_address = []
station_lat = []
station_lng = []

for name in station_name:
    tmp = gmaps.geocode(name, language='ko')
    # 경찰서 주소 얻기 
    station_address.append(tmp[0].get('formatted_address'))
    tmp_loc = tmp[0].get("geometry")
    # 경찰서 위치 얻기
    station_lat.append(tmp_loc['location']['lat'])
    station_lng.append(tmp_loc['location']['lng'])    

station_address[0:5]

station_lat[0:5]

station_lng[0:5]

# 구 이름 얻기
gu_name = []
for name in station_address:
    tmp = name.split()
    tmp_gu = [ gu for gu in tmp if gu[-1] == '구'][0]
    gu_name.append(tmp_gu)
    
gu_name[20:-1]    

# 구 이름을 붙이기
police_station['구별'] = gu_name
police_station.head()

# 중간 데이터 저장
police_station.to_csv(
    './data_ouput/02.crime_in_Seoul_including_gu_name.csv',
    sep=',',
    encoding='utf-8'
)

gu_criminal_raw = pd.read_csv(
    './data_ouput/02.crime_in_Seoul_including_gu_name.csv',
    encoding='utf-8',
    index_col=0
)
gu_criminal_raw.head()

gu_criminal = pd.pivot_table(
    gu_criminal_raw,
    index='구별',
    aggfunc=np.sum
)
gu_criminal.head()

gu_criminal['강간검거율'] = gu_criminal['강간 검거']/gu_criminal['강간 발생'] * 100
gu_criminal['강도검거율'] = gu_criminal['강도 검거']/gu_criminal['강도 발생'] * 100
gu_criminal['살인검거율'] = gu_criminal['살인 검거']/gu_criminal['살인 발생'] * 100
gu_criminal['절도검거율'] = gu_criminal['절도 검거']/gu_criminal['절도 발생'] * 100
gu_criminal['폭력검거율'] = gu_criminal['폭력 검거']/gu_criminal['폭력 발생'] * 100

gu_criminal.head()

gu_criminal[ gu_criminal[[ '강간검거율', '강도검거율', '살인검거율', '절도검거율', '폭력검거율' ]] > 100 ] = 100
gu_criminal.head()

del gu_criminal['강간 검거']
del gu_criminal['강도 검거']
del gu_criminal['살인 검거']
del gu_criminal['절도 검거']
del gu_criminal['폭력 검거'] 

gu_criminal.head()

gu_criminal.rename( columns={'강간 발생' : '강간',
                             '강도 발생' : '강도',
                             '살인 발생' : '살인',
                             '절도 발생' : '절도',
                             '폭력 발생' : '폭력' },
                   inplace=True
                  )
gu_criminal.head()

gu_criminal_norm = gu_criminal[['강간', '강도', '살인', '절도', '폭력' ]]/gu_criminal[['강간', '강도', '살인', '절도', '폭력' ]].max()
gu_criminal_norm[[ '강간검거율', '강도검거율', '살인검거율', '절도검거율', '폭력검거율'  ]]  = \
                                                            gu_criminal[[ '강간검거율', '강도검거율', '살인검거율', '절도검거율', '폭력검거율'  ]] 
gu_criminal_norm.head()    

# 인구 및 cctv 현황 데이터 읽기 
result_CCTV = pd.read_csv(
                         './data_ouput/01.CCTV_result.csv', 
                          encoding='utf-8',
                          index_col='구별',
                         )
result_CCTV.head()

# 구별 범죄 데이터에 인구 및 CCTV 현황 데이터 중 '인구수'/'소계' 붙이기
gu_criminal_norm[['인구수', 'CCTV']] = result_CCTV[['인구수', '소계']]
gu_criminal_norm.head()

gu_criminal_norm.sort_values(
    by='살인',
    ascending=False
).head()

gu_criminal_norm.sort_values(
    by='강도',
    ascending=False
).head()

gu_criminal_norm['범죄'] = np.sum( gu_criminal_norm[['강간', '강도', '살인', '절도', '폭력'  ]], axis=1 )
gu_criminal_norm.sort_values(
    by='범죄',
    ascending=False
).head()

gu_criminal_norm['검거'] = np.sum( gu_criminal_norm[['강간검거율', '강도검거율', '살인검거율', '절도검거율', '폭력검거율'  ]], axis=1 )
gu_criminal_norm.sort_values(
    by='검거',
    ascending=False
).head()

np.corrcoef( gu_criminal_norm['범죄'], gu_criminal_norm['인구수'] )

np.corrcoef( gu_criminal_norm['검거'], gu_criminal_norm['인구수'] )

np.corrcoef( gu_criminal_norm['살인'], gu_criminal_norm['폭력'] )

import matplotlib.pyplot as plt
import seaborn as sns

%matplotlib inline

# 한글 문제 
import matplotlib.font_manager as fm
# 폰트 적용
font_location = '/usr/share/fonts/truetype/nanum/NanumBarunGothic.ttf'
font_name = fm.FontProperties(fname=font_location).get_name()

from matplotlib import rc
rc('font', family=font_name)

sns.pairplot(
    gu_criminal_norm,
    vars=['강도', '살인', '폭력' ],
    kind='reg',
    size=3
)
plt.show()

sns.pairplot(
    gu_criminal_norm,
    x_vars=[ '인구수', 'CCTV' ],
    y_vars=[ '살인', '강도', '살인검거율', '폭력검거율', '절도검거율' ],
    kind='reg',
    size=3
)
plt.show()

gu_criminal_norm['검거'] = gu_criminal_norm['검거']/gu_criminal_norm['검거'].max() * 100
gu_criminal_norm_sort = gu_criminal_norm.sort_values(
    by='검거',
    ascending=False
)
gu_criminal_norm_sort.head()

target_col = [ '강간검거율', '강도검거율', '살인검거율', '절도검거율', '폭력검거율', '검거'  ]

plt.figure( figsize=(10, 10) )
sns.heatmap( gu_criminal_norm_sort[target_col], annot=True, fmt='f', linewidths=.5  )
plt.title( '범죄비율 (정규화된 검거율로 정렬)')
plt.show()

gu_criminal_norm['범죄'] = gu_criminal_norm['범죄'] / 5

target_col = [ '강간', '강도', '살인', '절도', '폭력', '범죄' ]
gu_criminal_norm_sort = gu_criminal_norm.sort_values(by='범죄', ascending=False)

plt.figure( figsize=(10, 10) )
sns.heatmap( gu_criminal_norm_sort[target_col], annot=True, fmt='f', linewidths=.5  )
plt.title( '범죄비율 (정규화된 발생 건수로 정렬)')
plt.show()

gu_criminal_norm.to_csv('./data_ouput/02.crime_in_Seoul_final.csv', sep=',', encoding='utf-8')

import folium
import json

# 행정구역 경계정보
geo_path = './data_science/02. skorea_municipalities_geo_simple.json'
geo_str = json.load( open(geo_path, encoding='utf-8' ) )

# 행정구역도
fmap = folium.Map( location=[37.5502, 126.982 ], zoom_start=11, tiles='Stamen Toner' )

fmap.choropleth(
    geo_data = geo_str,
    data = gu_criminal_norm['살인'],
    columns = [ gu_criminal_norm.index, gu_criminal_norm['살인'] ],
    fill_color = 'PuRd', 
    key_on = 'feature.id'
)

fmap

fmap.choropleth(
    geo_data = geo_str,
    data = gu_criminal_norm['범죄'],
    columns = [ gu_criminal_norm.index, gu_criminal_norm['범죄'] ],
    fill_color = 'PuRd', 
    key_on = 'feature.id'
)

fmap

tmp_criminal = gu_criminal_norm['범죄']/gu_criminal_norm['인구수'] * 1000000

fmap.choropleth(
    geo_data = geo_str,
    data = tmp_criminal,
    columns = [ gu_criminal_norm.index, tmp_criminal ],
    fill_color = 'YlGnBu', 
    key_on = 'feature.id'
)

fmap

gu_criminal_raw['lat'] = station_lat
gu_criminal_raw['lng'] = station_lng

tmp = gu_criminal_raw[ [ '살인 검거', '강도 검거', '강간 검거', '절도 검거', '폭력 검거'] ] / \
                       gu_criminal_raw[ [ '살인 검거', '강도 검거', '강간 검거', '절도 검거', '폭력 검거'] ].max()

gu_criminal_raw['검거']   = np.sum(tmp, axis=1)
gu_criminal_raw.head()

fmap = folium.Map( location=[37.5502, 126.982 ], zoom_start=11 )

for n in gu_criminal_raw.index:
    folium.CircleMarker(
        [ gu_criminal_raw['lat'][n],  gu_criminal_raw['lng'][n]  ],
        radius=gu_criminal_raw['검거'][n] * 10,
        color='#3186cc',
        fill_color='#3186cc'
    ).add_to(fmap)

fmap

fmap = folium.Map( location=[37.5502, 126.982 ], zoom_start=11 )

fmap.choropleth(
    geo_data = geo_str,
    data = gu_criminal_norm['범죄'],
    columns = [ gu_criminal_norm.index, gu_criminal_norm['범죄'] ],
    fill_color = 'PuRd', 
    key_on = 'feature.id'
)


for n in gu_criminal_raw.index:
    folium.CircleMarker(
        [ gu_criminal_raw['lat'][n],  gu_criminal_raw['lng'][n]  ],
        radius=gu_criminal_raw['검거'][n] * 10,
        color='#3186cc',
        fill_color='#3186cc'
    ).add_to(fmap)

fmap