<jupyter_start><jupyter_text>위치정보요약 데이터의 좌표 정보로 각 도로명 주소별 평균 위도, 경도 table 만들기<jupyter_code># pyproj로 주소정보 데이터 위도, 경도로 바꿔보기

import pyproj
from pyproj import Proj
from pyproj import transform
#연세대학교 위치. X과표 : 동서, Y좌표 : 남북, GRS80 UTM-K
# +proj=tmerc, +lat_0=38, +lon_0=127.5, +k=0.9996, +x_0=1000000, +y_0=2000000, +ellps=GRS80, +units=m, +no_defs

lx=950254.198668
ly=1951390.210053

# 구글 지도 epsg 3857 or epsg 900913
# +proj=merc, +a=6378137, +b=6378137, +lat_ts=0.0, +lon_0=0.0, +x_0=0.0, +y_0=0, +k=1.0, +units=m, +nadgrids=@null, +no_defs ->이건 구글 map 좌표.
# 우리가 필요한 건 경도, 위도
# https://www.osgeo.kr/17

# transfom(p1, p2, x,y, z=None, radians =False) : p1에서 정의된 x1,y1,z1 점을 p2에서 정의된 x2,y2로 바꾸는 것. z, radian은 default가 none,false인 듯 
GRS80={'proj':'tmerc', 'lat_0':'38', 'lon_0':'127.5', 'k':0.9996, 'x_0':1000000, 'y_0':2000000, 'ellps':'GRS80', 'units':'m',}
p1=Proj(**GRS80)
#EPSG={'proj':'merc', 'a':6378137, 'b':6378137, 'lat_ts':'0.0', 'lon_0':'0.0', 'x_0':0.0, 'y_0':0, 'k':1.0, 'units':'m'}
p2=Proj(init='epsg:4019')
nlx,nly = transform(p1,p2,lx,ly)
map_pos=list((nly,nlx))
#m=folium.Map(location=map_pos,min_zoom=11, max_zoom=20)

map_pos
import pandas as pd
import numpy as np
import os
import glob #파일 한꺼번에 부르기 위해서 사용
import re
glob.glob(os.path.join("gh-data/loc-data","*.*"))
filenames = glob.glob(os.path.join("gh-data/loc-data","entrc*.xlsx"))
filenames
df0=pd.read_excel('gh-data/loc-data/entrc_busan.xlsx',header = 0,encoding='utf-8')
df0.head()
df=pd.read_excel('gh-data/entrc_seoul_head.xlsx',header = 0,encoding='utf-8')
df_refine=df[['시도','시군구','도로명','x좌표','y좌표']]
df_refine.dtypes
df_refine.keys()
df_refine['x좌표'][0], df_refine['y좌표'][0]
a,b= transform(p1,p2,df_refine['x좌표'][0], df_refine['y좌표'][0])

a,b
len(df_refine)
df_refine.describe()
lx=df_refine['x좌표'][67]
ly=df_refine['y좌표'][67]

lx>0
gx=[]
gy=[]
null=[]

#
#67번 데이터,

for k in list(range(len(df_refine))) :
    lx=df_refine['x좌표'][k]
    ly=df_refine['y좌표'][k]
    if lx>0 and ly>0 :
        a,b= transform(p1,p2,lx,ly)
        gx.append(a)
        gy.append(b)
    else :
        gx.append(0)
        gy.append(0)
        null.append(k)


len(null)
df_refine['위도']=gy
df_refine['경도']=gx

df_refine
type(null)
df_refine_final=df_refine.drop(null,0) #가로일때 0, 세로일때 1
df_refine_final.describe()
a1=df_refine_final['시도'][0]
a2=df_refine_final['시군구'][0]
a3=df_refine_final['도로명'][0]
ab=[a1,a2,a3]
new=' '.join(ab) #이렇게 하는게 처리 속도가 더 빠르다고 함

new
df_refine_final.reset_index(inplace=True,drop=True)

address=[]

#list(range(len(df_refine_final)))

for i in list(range(len(df_refine_final))) :
    a1=df_refine_final['시도'][i]
    a2=df_refine_final['시군구'][i]
    a3=df_refine_final['도로명'][i]
    ab=[a1,a2,a3]
    new=' '.join(ab)
    address.append(new)

address
df_refine_final["도로주소"]=address

AveSeoul=pd.pivot_table(df_refine_final, index=["도로주소"])
AveSeoul
AveSeoul.to_excel('gh-data/2018_loc_for_add_in_seoul.xlsx',encoding='utf-8')
AA=pd.read_excel('gh-data/2018_loc_for_add_in_seoul.xlsx',index_col=None,encoding='utf-8')

AA<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.20408163265306123, 'fraction_numerical': 0.07603686635944701, 'mean_word_length': 5.9862068965517246, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 9, 'https://': 1, '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': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '506958', 'license': 'MIT', 'n_tokens_mistral': 1769, 'n_tokens_neox': 1836, 'n_words': 232, 'path': '.ipynb_checkpoints/nims-academy-190228-yonsei-oh-report02-00-checkpoint.ipynb', 'repo_name': 'neulbo-187/1902-NIMS-report', 'size': '3038'}