goodthings4me.tistory.com
엑셀 함수는 데이터 정리를 위한 아주 유용한 도구. 공공데이터 포털에서 아파트 관련 정보를 추출하여 의미 있는 데이터를 만들어보기 위해 도로명 주소와 지번 주소 구조에서 엑셀 함수로 아파트 주소를 분리해보았다.
아파트 주소에서 엑셀 함수로 지역 명칭과 아파트명 분리해보기
아파트 도로명 주소의 구조는 일정하지만 약간의 변수가 있었다. "동" 이름이 있는 경우와 없는 경우, 아파트명이 있는 경우와 없는 경우
그래서, 주소 분리작업은 지번 주소를 기준으로 할 수 밖에 없었는데, 그 방법은 다음과 같다.
주소 형태를 살펴 보면,
- 광역시도, 시군구, 동읍(면), 리, 지번, 아파트명 순으로 되어있는데, 여기서 광역시도, 시군구, 동읍면, 아파트명을 분리하려고 한다.
- 사용 함수는 find(), left(), right(), mid(), if(), and(), or(), int(), isnumber(), len(), trim()을 사용한다.
광역시도 분리
- FIND(" ",C2)를 하면 첫 번째 공란 위치가 숫자로 나온다. 강원도의 경우 4, 충청남도의 경우 5
=TRIM(LEFT(C2,FIND(" ",C2)-1))
FIND() 함수는 찾는 문자와 찾을 대상(셀)을 지정하고 마지막에 시작 위치를 주는데, 생략하면 해당 셀의 처음부터 찾음을 의미한다.
- LEFT(C2,FIND(" ",C2)-1)는 주소에서 왼쪽부터 지정 위치(FIND(" ",C2) 결과)만큼 문자를 추출하는데, FIND(" ",C2)-1을 해줘야 ~도까지 추출한다.
- TRIM()은 좌우 공백을 없애는 함수로, 주소 데이터가 엄청 많기 때문에 혹시나 있을 공백을 처리해주기 위해서 사용했다.
시군구 분리
- 시군구 추출도 위 광역시도 추출과 같은 방법으로 FIND() 함수를 이용했다. 조금은 복잡하지만 하나씩 뜯어서 보면 이해하는데 큰 어려움은 없다.
=TRIM(MID(C2,FIND(" ",C2)+1,FIND(" ",C2,FIND(" ",C2)+1)-FIND(" ",C2)-1))
- 먼저, 시군구의 명칭이 주소의 중간에 있기 때문에 문자열 내 중간 부분 추출하는 MID() 함수를 사용했고, 공백 위치는 FIND() 함수로 잡았다.
- MID() 함수는 문자열을 지정하고 시작 위치와 추출할 문자열의 개수를 주면 해당 문자열이 추출되는데, 예로, 시작 위치는 "원주시"의 "원"이 되고 "그 위치부터 셈하여 3을 주면 "원주시"가 추출된다. 시작 위치(5)는 FIND(" ",C2)+1 하면 되는데, 문제는 시군구 문자 개수를 계산하는 방법이다. 즉, 시군구 명칭이 2글자에서 많게는 7~8자까지 있기 때문에 시군구 명칭이 끝나는 부분까지 공백 위치를 확보해야 정확한 명칭이 추출될 수 있다.
- 여기서, FIND() 함수로 공백 위치를 연속적으로 잡아야 하는데, 생각해야 할 것은 주소의 공백이 왼쪽에서 오른쪽으로 가면서 위치가 증가하는데 반하여, FIND() 함수는 바깥쪽 즉, 오른쪽이 첫 번째 공백 위치가 되고 왼쪽으로 오면서 찾는 공백 위치가 증가한다는 것에 주의해야 한다.
- 시군구 문자열만을 추출하기 위해서는 시군구 명칭이 끝나는 공백의 위치를 찾고, 시군구 명칭이 시작하는 공백 위치를 찾아 빼주면 된다. 즉, "원주시"를 예로, FIND(" ",C2,FIND(" ",C2)+1)는 공백 문자를 C2셀에서 찾는데 시작 위치를 FIND(" ",C2)+1인 5부터 찾으라는 의미로 결과는 8이 되고, 여기서 다시 -FIND(" ",C2)-1인 -(4+1)를 빼기 때문에 결과는 3이 되어 "원주시"가 추출된다.
자치구 아닌 구(일반구)가 있는 시
- 먼저, "자치구가 아닌 구"의 의미를 알아야 한다.
지방자치법 3조에 의한 지방자치단체(이하, "지자체")의 관할을 살펴보면,
- "특별시, 광역시, 특별자치시, 도, 특별자치도"(이하 "광역시/도")는 정부의 직할 아래 두고
- "시"는 도의 관할 구역, - "군"은 광역시/도의 관할 구역.
- "자치구"는 특별/광역시의 관할 구역에 둔다고 되어있다.
여기서, 자치구가 아닌 "구"(일명, "일반구" 또는 "행정구"라 한다)가 있는 "시"가 12개 있다.
아래의 "시"에는 2~5개의 "구"가 있는데,
자치구는 자치권(구청장 선거)이 있지만
일반구는 자치권이 없어서 구청장을 선거로 뽑지 않고 일반직 공무원이 임명된다.
고양, 부천, 성남, 수원, 안산, 안양, 용인, 전주, 창원, 천안, 청주, 포항
- IF 문으로 일반구인 경우 T, 아닌 경우 F로 하여 구분해놓는다.
=IF(OR(F3="천안시",F3="포항시",F3="창원시",F3="청주시",F3="전주시",F3="고양시",F3="수원시",F3="용인시",F3="안산시",F3="안양시",F3="성남시",F3="부천시"),"T","F")
일반구 분리
- 앞의 시군구 분리할 때처럼 MID()와 FIND() 함수를 사용하되 IF 문으로 일반구인 경우와 아닌 경우를 판단하게 한다.
=TRIM(IF(G2="T",MID(C2,FIND(" ",C2)+1,FIND(" ",C2,FIND(" ",C2,FIND(" ",C2)+1)+1)-FIND(" ",C2)-1),F2))
- 일반구("T")인 경우는 "시 구"명칭까지 추출하고, 아닌 경우는 기존 자치구(F2)를 참조한다.
동읍면 분리
- 동읍면 추출도 자치구인 경우와 일반구인 경우, 공백 위치가 서로 다르다. 즉, 자치구인 경우는 3번째까지 공백을 구해고 동읍면 시작 위치를 빼는 반면, 일반구는 공백 위치가 1개 더 있어서 그만큼 더 위치를 잡고 명칭 시작 위치를 빼줘야 한다.
=TRIM(IF(G2="T",MID(C2,FIND(" ",C2,FIND(" ",C2,FIND(" ",C2)+1)+1)+1,FIND(" ",C2,FIND(" ",C2,FIND(" ",C2,FIND(" ",C2)+1)+1)+1)-FIND(" ",C2,FIND(" ",C2,FIND(" ",C2)+1)+1)-1), MID(C2,FIND(" ",C2,FIND(" ",C2)+1)+1,FIND(" ",C2,FIND(" ",C2,FIND(" ",C2)+1)+1)-FIND(" ",C2,FIND(" ",C2)+1)-1)))
- 좀 복잡하기는 하지만, 하나씩 대입해보면 위 시군구에 한 방식으로 되어있기 때문에 응용을 하면 쉽게 해결할 수 있다.
아파트 명칭 추출을 위한 공란 위치
- 이 위치를 잡은 이유는,
- 동과 읍면의 포맷이 다르고(읍면인 경우 "리"까지 있기 때문),
- 아파트 명칭을 공백을 기준으로 추출할 경우 띄어쓰기된 아파트 명칭이 분리가 되는 경우가 다수이고,
- 지번 주소의 특성상 자치구든 일반구든 지번이 나온 후 아파트 명칭이 나오는 점을 감안하여,
- 자치구 포함한 시군구는 4번째 공백을 기준으로 바로 앞 문자가 숫자인지 여부를 판단하여 아파트 명칭을 확보하고,
- 일반구 또한 5번째 공백을 기준으로 할 경우 자치구처럼 추출이 가능하다는 점을 이용한 것임
=IF(G2="T",FIND(" ",C2,FIND(" ",C2,FIND(" ",C2,FIND(" ",C2,FIND(" ",C2)+1)+1)+1)+1),FIND(" ",C2,FIND(" ",C2,FIND(" ",C2,FIND(" ",C2)+1)+1)+1))
- 이 위치 또한 위의 사례와 같이 일반구인지 여부를 판단하여 4, 5번째 공란을 기준으로 FIND() 함수를 이용하였다.
공란 위치 바로 앞 글자 기준 정하기
- 바로 위에서 설명했듯이 "공란 위치 - 1"의 한 문자를 추출하여 정수(INT)화 하였다.
=INT(MID(C2,J2-1,1))
- 숫자이면 정수, 아니면 값 에러남 (에러 자체가 판단의 근거가 됨)
아파트명 분리
- 마지막 과정으로, 아파트 명칭을 추출하는 함수이다.
=IF(AND(G2="T",ISNUMBER(K2)), RIGHT(C2,LEN(C2)-FIND(" ",C2,FIND(" ",C2,FIND(" ",C2,FIND(" ",C2,FIND(" ",C2)+1)+1)+1)+1)), IF(AND(G2="T",ISERROR(K2)), RIGHT(C2,LEN(C2)-FIND(" ",C2,FIND(" ",C2,FIND(" ",C2,FIND(" ",C2,FIND(" ",C2,FIND(" ",C2)+1)+1)+1)+1)+1)), IF(AND(G2="F",ISNUMBER(K2)), RIGHT(C2,LEN(C2)-FIND(" ",C2,FIND(" ",C2,FIND(" ",C2,FIND(" ",C2)+1)+1)+1)), IF(AND(G2="F",ISERROR(K2)), RIGHT(C2,LEN(C2)-FIND(" ",C2,FIND(" ",C2,FIND(" ",C2,FIND(" ",C2,FIND(" ",C2)+1)+1)+1)+1)),""))))
- IF 조건문을 통해, 4가지 조건을 기준으로 아파트 명칭을 추출하는데,
- 일반구이면서 공란 앞 한 문자가 숫자인 경우와 값 에러인 경우에 대해 공란 위치를 달리하여 추출 범위를 정하고,
- 자치구이면서 공란 앞 한 문자가 숫자인 경우와 값 에러인 경우에 대해 공란 위치를 달리하여 추출 범위를 정하는 방법으로 아파트 명칭을 최종 추출한 것이다.
위와 같은 방법으로 아파트 명칭이 있는 600만 개 데이터에 대해 각 지역과 아파트 명칭을 추출하였다.
사실 복잡해 보이지만, 엑셀의 내장 함수 만을 이용해서 특정 규칙을 찾아낸 후 처리한 것뿐이다.
아파트 관련 정보를 취합하기 위한 1단계 작업인 아파트 주소 분리 작업을 해봤다.
이제, 아파트 단지에 대한 코드를 공공데이터 포털에서 API로 추출한 후, 아파트 표준 정보 데이터와 아파트 실거래가 OPEN API를 활용하여 각 아파트 단지 정보 데이터를 만들어보는 것이 최종 목표다.
'IT(Tip)' 카테고리의 다른 글
| 공공데이터 포털 오픈 api 아파트 기본정보 추출 데이터의 문제 (0) | 2022.01.28 |
|---|---|
| 유튜브 영상 다운로드와 음원 추출해주는 클립다운 어떤가... (1) | 2022.01.25 |
| 엑셀 파워쿼리 작업 예시 - 용량이 큰 단일 텍스트 파일 불러오기 (0) | 2022.01.16 |
| VS Code 터미널 출력 라인수 늘리기 (0) | 2022.01.02 |
| 데스크탑 조립PC 추천 사양 (0) | 2021.12.27 |
댓글