2022 OOO 실태조사
조사 대상자를 정확하게 구분 짓기 위한 명부를 정리했던 프로젝트
0. 개요
조사 목적 | OOO 자격증 소지자의 재직 현황 및 처우, 필요 지원책 등 실태 전반에 대해 조사함으로써 정책적인 근거를 마련하는 것이 목적 |
조사 진행 기관 | 과학기술정보통신부 / 사단법인 OOOOOO |
기간 | 2022년 9월 ~ 2022년 12월 (약 4개월) |
참여 인원 | 3명 |
내 역할 | 조사 대상자 명부 정리 / 보고서 일부 작성 |
1. 내 역할
•
조사 대상자 명부(표집틀) 정리
◦
DB 유효성 검증
▪
테이블 내 & 테이블 간 중복값 제거
▪
결측치 제거
▪
이메일 & 전화번호 & 이름 오타 확인, 이상치 수정 및 제거
◦
DB 정규화
▪
추후 UPDATE(갱신), INSERT(삽입) 과정에서 이상현상 없도록 원자화
▪
테이블 내 & 테이블 간 중복값에 대한 처리 기준 마련
•
보고서 일부 작성
2. 문제
표집틀의 오류로 인한 조사 과정에서 난항 예상
•
조사 대상자 A, B, C 집단에 대한 목록 3개를 각각의 테이블로 보유
•
전화조사 또는 온라인 조사를 희망하나 테이블 내 전화번호, 이메일 등 중복되는 값이 많음
•
각 집단의 목록끼리 비교해도 이메일, 전화번호 등 중복이 다수 발견
•
각 집단마다 설문 문항이 다르기 때문에 조사 전 목록을 정리하여 중복되는 값들에 대해 집단 구분을 명확하게 할 필요
3. 해결
3.1. 이메일 중복값
•
테이블 내 중복
◦
A 집단의 목록 확인 결과, 49,229명 중 235명이 중복된 이메일을 사용하고 있고, 중복되는 이메일은 78개의 고유한 값으로 이루어져 있었음.
◦
중복된 78개의 이메일 확인 결과, ABC@notion.com → 20명 / 가나다@samsung.com → 35명과 같이 도메인명이 특정 기업명이었고 ID 역시 동일하여 해당 기업의 대표 이메일로 작성한 것으로 확인
◦
같은 기업에 속한 직원 1명이 고객사에 OOO 자격증 소지자들을 일괄 등록하며 발생한 문제로 추정
중복, 결측치 등 제외하고 최종 37,303명을 조사 대상자로 선정•
테이블 간 중복
◦
A와 B 집단 간 중복되는 이메일 1,314개
◦
A집단이면서 동시에 B집단일 수 있는 경우 → OOO 자격증 소지자이면서 사업체를 운영하는 경우
고객사와 협의를 통해 각각의 다른 성격의 조사를 2번 참여하는 것으로 결정하고 사전에 미리 전화하여 상황 설명
3.2. 유효성 검증
이메일과 전화 발신 시 생길 수 있는 문제
•
이메일
isValidEmail <- function(x) {
grepl("\\<[A-Z0-9._%+-]+@[A-Z0-9.-]+\\.[A-Z]{2,}\\>", as.character(x), ignore.case=TRUE)
} #정규식을 이용한 이메일 오타 점검
R
복사
◦
\\< : 문자열의 시작 위치
◦
[A-Z0-9._%+-]+ : 이메일 주소의 로컬 파트(local part)를 검색합니다. 알파벳 대문자, 숫자, 밑줄(_), 마침표(.), 퍼센트 기호(%), 플러스 기호(+) 또는 하이픈(-)으로 이루어진 문자열이 하나 이상 나타나는 패턴을 검색
◦
@ : 이메일 주소의 로컬 파트와 도메인 파트(domain part)를 구분하는 기호
◦
[A-Z0-9.-]+ : 이메일 주소의 도메인 파트를 검색합니다. 알파벳 대문자, 숫자, 마침표(.) 또는 하이픈(-)으로 이루어진 문자열이 하나 이상 나타나는 패턴을 검색
◦
\\. : 도메인 파트 내의 마침표를 검색
◦
[A-Z]{2,} : 도메인의 최상위 도메인(TLD)을 검색합니다. 알파벳 대문자로 이루어진 두 글자 이상의 문자열을 검색
◦
\\> : 문자열의 끝 위치
◦
이메일의 주소가 유효한 형식인지 확인하여 grepl() 함수를 사용하여 해당 패턴이 x 변수에 포함되어 있는지 검사
◦
ignore.case=TRUE 대소문자를 구분하지 않고 검사
•
전화번호
◦
A집단 테이블 내 중복된 전화번호는 총 6개, 이 중복된 전화번호를 가진 인원은 12명
▪
A집단 테이블 내 총 6개의 중복되는 전화번호
▪
A집단 테이블 내 중복되는 전화번호를 갖고 있는 인원은 12명
#중복되는 전화번호와 개수
hp_person_dupl_cnt <- sqldf("SELECT ID, NAME, COUNT(PHONE) AS cnt FROM df_person_email
WHERE PHON_YN = 'O'
AND PHONE != '000-0000-000'
GROUP BY PHONE
HAVING COUNT(PHONE) > 1;")
hp_person_dupl_cnt
R
복사
hp_person_dupl <- df_person_email %>%
group_by(PHONE) %>%
filter(n()>1) %>%
subset(PHON_YN == "O")
hp_person_dupl
R
복사
◦
B집단 테이블 내 중복되는 전화번호
▪
B집단 테이블 내 중복되는 전화번호는 총 13개, 이 중복된 전화번호를 가진 업체는 총 29개
#중복되는 전화번호와 개수
hp_office_dupl_cnt <- sqldf("SELECT 전화번호, COUNT(전화번호) AS cnt FROM df_office_email
WHERE 전화번호 != '000-0000-000'
GROUP BY 전화번호
HAVING COUNT(전화번호) > 1;")
hp_office_dupl_cnt
R
복사
hp_office_dupl <- df_office_email %>%
group_by(전화번호) %>%
filter(n()>1) %>%
subset(전화번호유무 == "O")
hp_office_dupl
R
복사
◦
A와 B 집단 테이블 간 중복되는 전화번호 총 000개
inter_hp <- as.data.frame(intersect(df_person_email$PHONE, df_office_email$전화번호)) #두 테이블의 전화번호 열의 교집합
inter_hp
R
복사
•
생년월일
◦
생년월일 대부분의 값은 연도/월/일 6자리
▪
ex) 19901215, 19740506 …
◦
조사 진행 자체에는 영향 없으나 고객사의 회원 관리 관점에서는 생년월일에 대한 유효성 검증 필요
◦
6자리가 아닌 30명의 데이터 확인하여 오류의 패턴 확인 후 아래 ‘구체적인 해결 순서’와 같이 해결함
sqldf("select *, length(BIRTH) as Birth_len from df_person
where Birth_len != 6; -- 몇개 있긴하다") #생년월일이 6자리가 아닌 경우
R
복사
◦
6개가 아닌 경우 중 5개인 경우를 보면 연도와 월 사이에 숫자 0 하나가 빠진 것으로 추정
sqldf("SELECT ID, BIRTH
FROM df_person
WHERE ID = 'A2116';") #생년월일이 6자리가 아닌 경우 1개 조회해보기
R
복사
◦
구체적인 해결 순서
1.
nchar 함수통해 BIRTH(생년월일) 변수에서 5자리인 벡터 추출
2.
해당 벡터들을 substr 함수를 통해 1~2자리 & 3~5자리까지 별도로 나눠 추출
3.
1~2자리 & 3~5 자리 사이에 paste 함수를 통해 0을 삽입
4.
해당 값들을 우너래 데이터 프레임의 BIRTH 변수에 반영
5.
5자리도 아닌 0으로된 사실상의 결측치들은 일괄 NA 처리
6.
데이터타입을 날짜로 변경
df_person$BIRTH[nchar(df_person$BIRTH) == 5] <- paste(substr(df_person$BIRTH[nchar(df_person$BIRTH) == 5], 1, 2),0, substr(df_person$BIRTH[nchar(df_person$BIRTH) == 5], 3, 5), sep = "")
df_person$BIRTH[df_person$BIRTH == 0] <- 000000 #생년월일이 0으로 되어 있는 사람을 위해 자리수 맞추기
df_person$BIRTH <- format(as.Date(df_person$BIRTH, "%y%m%d"), "19%y%m%d")
df_person$BIRTH <- as.Date(df_person$BIRTH, "%Y%m%d")
str(df_person)
R
복사
4. 사용 라이브러리
•
R 라이브러리
◦
sqldf
▪
중복값을 찾기 위해 SQL 쿼리 활용
▪
예시
email_person_dupl_list <- as.data.frame(sqldf("SELECT EMAIL , -- 중복되는 데이터
COUNT(EMAIL) AS count-- 중복 갯수
FROM df_person -- 중복조사를 할 테이블 이름
WHERE EMAIL_YN = 'O'
GROUP BY EMAIL -- 중복되는 항목 조사를 할 컬럼
HAVING COUNT(EMAIL) > 1 ; -- 1개 이상 (갯수)"))
R
복사
▪
테이블 간 중복값을 찾고 목록을 추출하는 과정이 고객사에게는 다소 복잡하게 느껴질 수 있기 때문에 과정에 대한 설명 목적으로 최대한 효율적으로 코드를 작성할 수 있는 SQL문을 일부 활용함
◦
readxl
▪
tidyverse 패키지에 속한 패키지
▪
엑셀 파일을 불러오기 위한 패키지
▪
read_xlsx 함수 통해 엑셀 파일을 불러옴
◦
dplyr
▪
데이터 전처리를 위한 패키지로 tidyverse 패키지에 속한 패키지
◦
5. 배운점과 인사이트
•
조사 대상자 명부를 갖고 있는 고객사가 꽤나 많다. 그 DB의 유효성을 빠르게 검증할 수 있는 코드들이 필요하다.
◦
이번 프로젝트에 적용한 이메일, 전화번호, 생년월일 등 정규식을 통해 검증할 수 있는 코드
◦
해당 코드를 타 부서에서도 쉽게 활용할 수 있는 프로그램 개발까지 되었다면 더 좋았을 것이다.
•
고객사에 회원 DB를 관리할 수 있는 방법까지 제시해줬다면 좋았을 것이다.
◦
이렇게 처리하여 다시 고객사에게 준다고 해도 고객사가 다시 DB 관리에 소홀히 한다면 같은 문제 발생할 것이다.
◦
만약 다른 프로젝트에서 비슷한 미래가 예견된다면 고객사 측에 꼭 지켜야 하는 점을 글을 통해서라도 전달하는 것이 좋을 것 같다.