[Excel] 데이터 전처리 : 특정 문자 개수 세기, 특정 문자 삭제, LEN(), SUBSTITUTE(), IFS()
공공데이터포털에 업로드할 주기성 데이터를 전처리하면서 사용했던
엑셀 함수와 파이썬 작업을 메모해두는 글. :-]
지난글(파이썬 작업) https://hjryu09.tistory.com/38
앞서 파이썬으로 불필요한 물음표를 제거했지만, 파일을 열어보면 제거해야 할 물음표가 아직 남아있다.
오류 값으로 입력된 물음표인지, 맥락상 기재된 물음표인지 최대한 구분해보자...!!
아이디어
1) 문자열에 포함된 물음표의 총 개수를 파악한다.
2) 맥락상 물음표보다 오류로 입력되는 물음표가 훨씬 많다.
그렇다면 맥락상 물음표를 가려낼 수 있는 조건을 설정하고, 이 조건(IF)을 충족하지 않을 때(False) 물음표를 제거한다.
3) 1번의 물음표 개수와 2번 결과값이 가지고 있는 물음표 개수를 비교한다.
만약 두 값이 같다면 = 물음표가 제거되지 않았다 = 내가 설정한 조건에 True = 맥락상 물음표
두 값이 같지 않다면 = 물음표가 제거되었다 = 내가 설정한 조건에 False = 눈으로 체크!
1) 문자열에 포함된 물음표의 총 개수를 파악한다. = F열
물음표 개수 = 문자열의 문자 수 - 물음표를 제거한 문자 수 = LEN(E2)-LEN(SUBSTITUTE(E2,"?",))
LEN(문자 수를 알고 싶은 문자열)
SUBSTITUTE(문자열, 기존 문자, 바꿀 문자)
2) 맥락상 쓰인 물음표만 그냥 두고, 나머지는 제거한다. = H열
제목의 맨 끝에 붙은 물음표는 맥락상 물음표일 것이다 = IF( RIGHT(E2)="?", E2, (SUBSTITUTE(E2,"?",))
제목에 (?) 가 있다면 맥락상 물음표일 것이다 =IF( (LEN(E2)-LEN(SUBSTITUTE(E2,"(?)",)))>0, E2, (SUBSTITUTE(E2,"?",) )
두 가지 조건을 합치면 = IFS( RIGHT(E2)="?", E2, (LEN(E2)-LEN(SUBSTITUTE(E2,"(?)",)))>0, E2, TRUE, SUBSTITUTE(E2,"?",))
해석하면 아래와 같음.
조건1. 맨 오른쪽 문자가 물음표인가?
> True : 그대로 반환
False : 조건2. (?)가 들어가는가? > True : 그대로 반환
False : 맥락상 물음표가 아니라고 판단되므로 물음표 삭제 SUBSTITUTE(E2,"?",)
이런 식으로 맥락상 물음표라고 판단할 수 있는 조건들(ex. 까? 은? 는? 왜?)을 주고 여러 번 필터링한다.
3) 1번에서 반환한 F열 값과 2번에서 반환한 H열의 물음표 개수를 비교한다. = G열
= IF( (LEN(H2)-LEN(SUBSTITUTE(H2,"?",)))=F2, "" , "체크")
만약 두 값이 같다면 = 물음표가 제거되지 않았다 = 내가 설정한 조건에 True = 맥락상 물음표
두 값이 같지 않다면 = 물음표가 제거되었다 = 내가 설정한 조건에 False = 눈으로 체크!
눈으로 체크했을 때 맥락상 물음표라면 해당 케이스를 2번의 조건에 추가한다.
맥락상 물음표를 찾아낼 수 있는 필터를 계속해서 업데이트하는 셈.
머신러닝이 아니라 내가 러닝하면서 계속 수식의 정확도를 높여감...
사용한 엑셀 수식은 단순한데, 이런 과정을 짜는 게 복잡하고 어려웠다.
마치 코딩 전에 분석을 기획하고 단계를 설정하는 게 더 어렵듯이.
하지만 엑셀이나 스프레드시트 파고드는건 역시 재밌다고 느낌.. 변태같지만 사실이다... 난 역시 엑셀 좋아하네^_^
아무튼 데이터를 전처리하는 게 왜 생각보다 쉽지 않은지,
엑셀과 스프레드시트를 동원하는 일이 왜 비일비재한지 이해하게 된 작업. :)