[엑셀] 서울시 CCTV 현황 데이터 분석-데이터 전처리

서울시 CCTV 현황 엑셀 파일을 분석하기 위해서 분석항목을 위한 전처리가 선행되어야 한다. 

전처리를 선행하는 이유는, 업무 또는 역할의 분리가 크다.

분석 작업을 하다가 필요한 항목이 생기거나, 데이터 정제가 필요한 경우 분석작업을 멈추고 전처리를 재 수행해야 한다. 

이런 과정의 반복이 데이터 분석의 과정이지만, 이런 수고를 줄이는 것은 필요한다.

 

이를 위해서, 분석항목을 정한 이후 필요한 데이터를 정하고, 

수집된 데이터에서 없으면 추가로 발굴하거나 생성하고, 기존 데이터에서 로직으로 구성이 가능하면 로직을 반영하여 새로운 칼럼을 만들어 두어야 한다.

 

서울시 CCTV 현황 데이터 분석에는 아래 4가지 전처리가 필요했다.

엑셀 기반으로 각 항목별 전처리를 진행하였다.

 

전처리 항목

 

1) 관리기관명에서 "구청" 분리
2) "담당부서" 분리
3) 구청명에서 "구" 분리
4) 도로명주소에서 "길" or "로" 분리 - 도로명 주소의 도로 기준이 "길, 로"로 명시되었음

 

 

1) 관리기관명에서 "구청" 분리

- 데이터 분석
   구청명이 앞에 나온다
   서울특별시가 먼저나온다
   예외) 
     - 서울특별시 강북경찰서 
     - 서울특별시동작구청 : 글자가 붙어 있음

- 규칙
1) 예외 항목인 "강북경찰서"는 "강북구"로 설정하고, "서울특별시동작구청"는 "동작구청"으로 설정한다.
2) "구청" 글자 위치를 파악하여(FIND 함스) 결과값 + 1을 위치값에 대입한다. "구청"글자 길이를 반영하기 위해서다.
3) 위치값이 6이하이면 구청명이 앞에 나오는 경우이므로 "관리기관명"에서 좌측기준 "구청"위치 글자까지 추출한다.
   6보다 크면 "서울특별시"글자가 있으니" "관리기관명"에서 6~구청 글자 위치까지 값을 가져온다

 

 

• "L" 칼럼 
  • 예외 사항 적용
  • SWICH 함수 사용 - 2가지 경우별 처리할 값을 배정하기 위함
  • =SWITCH(B2,"서울특별시동작구청","동작구청","서울특별시 강북경찰서","강북구청",FALSE)
  • B2가 "서울특별시동작구청"  이면  "동작구청"
           "서울특별시 강북경찰서" 이면 "강북구청" 을 배정한다.

 

• "M"칼럼
  • "구청" 글자의 위치값 찾기
  • FIND 함수 사용 - 찾을 글자가 해당위치의 어디에 있는지를 확인
  • =FIND("구청",B2)+1
  • B2 에서 "구청"글자 위치를 찾는다. 여기서 "구청"글자는 2자 이기에 "+1"을 해서 "구청" 글자까지를 위치값을 설정했다.

 

 

 

 

• "N"칼럼
  • 구청명을 추출한다
  • IF 함수, LEFT, MID 함수를 사용
  • =IF(L2=FALSE,IF(M2<7,LEFT(B2,M2),MID(B2,7,M2-6)),L2)
    
    • L2=FALSE는 예외처리 항목이 아니라는 의미이므로 이 때는 구청 글자 위치가 "7"자리 이전에 있으며 "서울특별시"글자가 없는 경우이다.
    • "LEFT(B2,M2)"는 "서울특별시"글자가 없는 경우이므로 좌측부터 잘라왔다.
    • MID(B2,7,M2-6)는 "서울특별시"글자가 있기에 중간 7자리 부터 잘라왔다.
    • 마지막 "L2"는 미리 예외처리가 된 값을 가져온다는 의미이다.

 

2) "담당부서" 분리

- 데이터 분석
  첫문장이 동작구청에만 담당부서가 있다.
  서울시 동작구청이라고 표시된 동작구청은 담당부서가 없다.
  나머지 구청은 부서명은 "구청내부" 라고 명명한다

- 규칙
1) 첫문장이 "동작구청"이지면 다음문장을 추출한다.
2) 그 중에서 첫문장이 "동작구청"이면 "동작구청"이라고 명시해 준다.
3) "동작구청"이면 "동작구청"을, 아니면 "구청내부"라고 명시한다.

 

 

• "O" 칼럼
  • "관리기관명"에 "동작구청"이 포함여부 적용
  • =NOT(ISERROR((FIND("동작구청",B2))))
  • B2에서 "동작구청"을 찾는다. ISERROR 함수로 인해서 찾으면 FALSE, 못 찾으면  TRUE 가 된다. 여기에 NOT함수를 붙여 반대로 만들었다. 이유는 질문인 "동작구성 포함여부"이기 때문이다.

 

• "P" 칼럼
  • "동작구청"은 2가지 방식으로 나온다. 첫문장에 구청이름이 나오는 경우와 "서울특별시" 다음에 나오는 경우다.
    우리는 첫문장에 나오는 "동작구청"만이 부서명이 있는 것을 파악했다.
    그래서 첫문장 여부를 파악하는 것이다.
  • =IF(O2, IF( FIND("동작구청",B2)=1,TRUE, FALSE),FALSE)
    
    • "=IF(O2, ..." 만 한것은 O2의 값이  Boolean 값인  TRUE, FALSE가 있기 때문이다.
    • FIND("동작구청",B2)=1 에서 위치값이 1 이면 첫문장이 "동작구청"이 되므로 "TRUE"를 반환하게 했다.

 

 

• "Q" 칼럼
  • 부서명을 추출한다. 첫문장이 "동작구청"인 경우만 처리하고, 아니면 "구청내부"라고 명시한다.
  • =IF(P2,MID(B2,6,10),"구청내부")
    
    • 첫문장이 "동작구청"인 경우는 "P2" 가 TRUE 이므로
    • "MID(B2,6,10)" 에서 관리기관명의 "동작구청 " 글자를 감안한 6자리부터 10자를 가져온다.
      (10자를 정한 것은 10자 정도면 충분하겠지 생각한 것이다.)

 

 

 

 

3) 구청명에서 "구" 분리

 

- 규칙

1) 분리된 구청명에서 "청"을 제거한다.

 

•  "R"칼럼
  • 추출한 구청명에서 "청" 글자를 제거하여 추출한다.
  • =REPLACE(N2,FIND("청",N2),1,"")
  • "N2"에서 "청"의 위치를 찾아 공백으로 replace 하여 추출한다.

 

 

4) 도로명주소에서 "길" or "로" 분리

 

- 데이터 분석
   도로명 주소에서 도로명은 "길", "로"로 되어 있다.
   "길", "로"가 없는 경우 "동", "공원"을 기준으로 분리한다.
   도로명 부여 순서는 1) "로", 2) "길", 3) "동" 4) "공원" 5) 그외 이다.
   "로" 보다 "길"이 먼저 나오는 경우도 있다.
   도로명 주소 앞에 공백이 있는 경우가 있다.
   예외)
      한신1차아파트 가로공원  --> "로" 글자가 중간에 있다
　      자양4동 199-4 --> 앞에 공백
     서울특별시 관악구 난향7길 60(안전한산책로#1)  --> "길"이 맞는 위치
     "서울특별시 중랑구" --> 도로명 파악이 어렵다

- 규칙
1) "로" 위치를 추출한다.
2) "길" 위치를 추출한다.
3) "동" 위치를 추출한다.
4) "공원" 위치를 추출한다.
5) 1)~4)를 이용하여 도로명 추출 위치를 찾는다.
6) "도로명"을 추출하고 공백과 "서울특별시" 글자를 제거한다.
7) "구"명이 같이 있으므로 "구"명을 제거한다.
8) "예외" 기표 된 것은 "구"명을 넣는다

"Find"함수는 찾는 글자가 없으면 "#VALUE!"를 출력한다.
도로명 앞뒤로 공백이 있을 수 있으므로 "TRIM"함수를 적용하여 공백을 제거한다.

 

 

 

• "S" 칼럼 ==> "로"위치 값 산정, =FIND("로",C2)
• "T" 칼럼 ==> "길"위치 값 산정, =FIND("길",C2)
• "U" 칼럼 ==> "동"위치 값 산정, =FIND("동",C2)
• "V" 칼럼 ==> "공원"위치 값 산정. =FIND("공원",C2)

  ==> Find 함수 사용하여 위치값 산정,

         찾는 글자가 없을 경우 "#VALUE!" 표시가 나타남 

 

• "W" 칼럼
  • 도로명 위치값을 산정하는 데, "로, 길, 동, 공원" 순서로 산정함
  • 엑셀 공식에 위치별로 값을 배정하였음

=
IF(ISERROR(S2),
IF(ISERROR(T2),
 IF(ISERROR(U2),
  IF(ISERROR(V2),"예외",V2),
U2),
T2),
S2)

 

 

• "X" 칼럼
  • 도로명을 추출하는데 "서울특별시"글자를 지우고 가져온다. 이 대 공백은 제거한다.
  • 코드

     =IF(W2<>"예외",
         IF(ISERROR(FIND("서울특별시",C2)),TRIM(LEFT(C2,W2)),TRIM(MID(C2,6,W2-5))),
       W2)

 

 

• "Y" 칼럼
  • 구 명칭이 같이 나오므로, 구 명칭을 제거한다.
  • 이것은 앞에 추출해 둔 "구" 명칭과 비교하여 처리한다

    =IF(R2=LEFT(X2,LEN(R2)),
         TRIM(MID(X2,LEN(R2)+1, LEN(X2)-LEN(R2)+1)),
        X2)

       -> "IF(R2=LEFT(X2,LEN(R2)) "  --> 구 명칭과 X  칼럼의 같은 길이로 비교하여 같은지  check 한다.

 

 

 

 

• "Z" 칼럼
  • "에외"의 경우 "구" 명칭으로 추출 한다
  • =IF(Y92="예외", R92,Y92)
    
    • "예외"일 때만 "R" 칼럼의 구명칭을 가져온다.

 

 

 

 

 

 

 

 

이렇게 필요한 전처리 작업을 했다.

엑셀에서는 한 칼럼에 모든 로직을 다 넣을 수도 있지만, 나중에 다시 보면 해독하기 어렵다.

그래서 위 처럼 단계별로 해 두는 것을 추천한다.

해 보면 이 부분이 편하다.

 

전처리 결과 파일을 첨부 하였다.

 

12_04_08_E_CCTV정보_전처리.xlsx

12.07MB

(끝)