라돈 - 03 공간 분석용 데이터로 재구축

[Spatial Analysis]

#GIS #QGIS #Spatial Data Analysisv #Big Data #Data #Map #Radon #School

#공간데이터 분석 #빅데이터 분석 #라돈 #발암물질 #학교 #실내공기질

#데이터 #지도 #공공데이터포털 #학교알리미

 

[ 03 분석용 데이터로 재구축 ]

   앞선 장에서 ‘02 활용데이터 정의 및 수집 구축’을 통해 가장 기본이 되는 ‘2017년 전국 학교 실내공기질 이미지 데이터’를 오픈소스 OCR 웹페이지를 통해 Excel 형태로 구축했다. 하지만 우리는 분석에 앞서 가장 기본이 되는 Raw 형태의 데이터를 구축한 것이지 실질적인 공간데이터 분석을 하기 위해서는 아직도 갈 길이 멀다.

   기 구축한 Raw 데이터를 가지고 우리는 앞으로 공간 분석을 진행하려고 한다. 분석을 위해서는 기구축한 라돈 검출 학교 데이터를 좌표를 가진 형태의 공간 데이터로 만들어야 하는데 주소를 좌표로 변환 시켜주는 Geocoding 방법(좌표를 주소로 변환 시켜주는 경우 Reverse Geocoding이라고함)을 이용해 만들 수 있다.

   하지만, Geocoding 작업을 진행하기 위해서는 우선 학교 주소가 필요하다. 데이터가 적은 경우 검색을 통해 수작업으로 학교 주소를 검색해 데이터를 일일이 구축해도 무방하나, 데이터 양이 많은 경우 학교 주소록 데이터와 라돈 검출 학교 데이터를 Key 값(학교명)으로 결합하여 하나의 데이터 형태로 만들어 주는 것이 편리하다.

A. 재구축용 추가 데이터 수집

   다행히 공공데이터포털(http://www.data.go.kr) 혹은 한국교육학술정보원의 학교알리미(http://www.schoolinfo.go.kr) 에서 전국초등학교 위치 표준 데이터(WGS 84)를 무료로 제공하고 있다.

   학교알리미 웹페이지 제공 데이터를 활용해 데이터를 재구축해 보자. 학교알리미 웹페이지 우측 하단의 ‘공개용 데이터’를 클릭하면 전국 초/중/고등학교 정보에 대해 공개된 자료를 XLSX, CSV 형태의 파일로 다운로드 받을 수 있다. 여기서 필요한 항목을 클릭한 후 유형 및 사용목적을 선택하면 다운로드 받을 수 있다.

   '공개용 데이터'에서 제공하는 데이터 중 ① 2017년 학교기본정보 Excel 자료와 ② 2017년 학교 현황(학생수) Excel 자료를 다운로드 받아 살펴보자. 2017년 자료를 다운로드 받는 이유는 앞서 구축한 라돈 검출 학교 자료가 2017년 자료이기 때문이다. 우선, ① 2017년 학교기본정보 Excel 자료의 경우 총 25개의 컬럼(시도교육청/지역교육청/지역/정보공시 학교코드/학교명/학교급코드/설립구분/학교특성/분교여부/설립유형/주야구분/개교기념일/설립일/주소코드ID/주소내역/상세주소내역/학교도로명우편번호/학교도로명주소/학교도로명상세주소/위도/경도/전화번호/팩스번호/홈페이지주소/남녀공학구분) 12,139개 전국 초/중/고등학교로 구성되어 있으며, 좌표값(위도, 경도)를 포함하고 있어 주소를 좌표로 변환하는 Geocoding 과정을 거치지 않아도 된다. 

② 2017년 학교 현황(학생수) Excel 자료의 경우 총 18개의 컬럼(시도교육청/지역교육청/지역/정보공시 학교코드/학교명/학교급코드/설립구분/제외여부/제외사유/1학년/2학년/3학년/4학년/5학년/6학년/특수학급/순회학급/계)로 구성되어 '정보공시 학교코드' 컬럼을 KEY 값으로 두 데이터를 결합하여 하나의 데이터 셋으로 구축 가능 할 것으로 생각된다.

B. 데이터 결합

   데이터 결합의 경우 데이터 양이 많은 경우 Access나 ETL(Extract Transform Loading) Tool을 이용해 핸들링해야 하지만 데이터 양이 적은 경우 Excel로도 충분하다. 2017년 전국 초/중/고등학교의 수가 대략 약 12,000개 정도 밖에 되지 않으니 Excel에서 데이터를 재구축해 보겠다.

우선 ① 2017년 학교기본정보 Excel 자료와 ② 2017년 학교 현황(학생수) Excel 자료를 Excel의 Vlookup 기능을 이용해 하나의 데이터 파일로 결합해 보겠다. Vlookup 기능을 쓰기 위해서는 반드시 공통적으로 일치하는 KEY 값(KEY 컬럼)이 있어야 하며 여기서는 ‘정보공시 학교코드’를 중심으로 결합할 예정이다. 

① ‘2017년 학교기본정보 Excel 파일’을 열고, 좌측 하단의 플러스 기호를 눌러 새로운 sheet를 만든다. 새로만든 sheet 명을 ‘학생수’라 기입하고 다운로드 받아둔 ‘2017년 학교 현황(학생수) Excel 자료’에서 ‘정보공시 학교코드’와 ‘학생수 총계’ 컬럼만 전부 복사해 하나의 데이터 셋으로 만든다. 이때 반드시 기준이되는 Key 값(컬럼)을 맨 처음 컬럼에 위치시킨다.

② 첫 번째 기준 sheet의 맨우측 첫 줄에 필드명(학생수)를 기입하고 커서를 두번째 공란에 위치 시킨 후 '=vlookup(첫번째 sheet에서 Key 컬럼, 두번째 sheet 데이터 전체 컬럼, 결합하고자 하는 컬럼의 순서, 값 일치 여부)'의 내용으로 작성한다. 즉, 공란에 '=VLOOKUP(A:A,학생수!A:B,2,TRUE)' 이라고 작성 한 후 실행시키면 해당하는 값을 결합해 준다.

 

③ 방금 작성한 셀을 선택하고 커서를 선택한 셀 테두리 우측하단 위로 옮기면 커서가 십자 모양으로 변한다. 이때 마우스를 더블 클릭하면 해당 컬럼에 동일한 기능(Function)을 적용해 값을 불러 온다.

 

④ 동일한 방법으로 기 구축한 '라돈 학교 데이터'도 Excel의 Vlookup 기능을 이용해 결합한다. (다만, Key 값(컬럼)은 ‘학교명’으로 해야하며 두 데이터의 학교명 부여 방식이 한쪽은 ‘강남초등학교’ 다른 한쪽은 ‘강남초’ 형태로 서로 다르므로 한쪽의 형태로 일치시켜준 뒤 결합해야 한다.)

   최종 데이터 셋 결과는 아래와 같다.

 

- 끝 -