공공데이터를 DB에 저장하고 전처리하기

데이터 전처리

• 데이터 분석을 위해 수집한 데이터를 분석에 적합한 형태로 가공하는 과정
• 불필요한 데이터를 제거하고 결측치나 이상치를 처리하여 데이터의 질을 향상

💡 정확한 결과를 위해 반드시 필요한 작업

• 결측치 처리 : 빠진값이 있을경우 해당 값을 대체하거나 삭제하여 데이터 일관성 유지
• 이상치 처리 : 데이터에서 비정상 값이 있을 경우 해당값을 대체하거나 삭제하여 분석결과 오류 방지
• 데이터 정규화 : 데이터 값을 일정한 범위로 조정
• 데이터 인코딩 : 텍스트 데이터를 컴퓨터가 이해할 수 있는 형태로 변환 (ex.원-핫 인코딩 등)

원-핫 인코딩? 단어 집합의 크기만큼 벡터 차원을 가지고 해당 단어가 있으면 1 없으면 0으로 표현

---

공공데이터를 원하는 형태로 전처리 하기

공공데이터 중 '전국자동차검사소표준데이터' 를 다운로드 받아 필요한 컬럼들만 정제하여 로컬DB에 적재하고 적재된 데이터를 원하는 형태로 가공해보자

DBMS: MySQL

1. 공공데이터 포털 페이지에서 csv 파일 다운로드

 

2. 저장할 데이터에 대한 테이블 및 컬럼을 생성

CREATE TABLE car_test (
    car_test_no INT AUTO_INCREMENT PRIMARY KEY COMMENT '자동차검사소번호',
    car_test_nm VARCHAR(255) NOT NULL COMMENT '자동차검사소명',
    addr_road VARCHAR(255) NOT NULL COMMENT '소재지도로명주소',
    x DECIMAL(10, 7) NOT NULL COMMENT '위도',
    y DECIMAL(10, 7) NOT NULL COMMENT '경도',
    tel VARCHAR(20) NOT NULL COMMENT '검사소전화번호',
    tester INT NULL COMMENT '검사소인력수',
    work_time VARCHAR(100) NULL COMMENT '운영시간',
    agency_cd VARCHAR(50) NOT NULL COMMENT '제공기관코드',
    agency_nm VARCHAR(255) NOT NULL COMMENT '제공기관명',
    reg_dt DATE NOT NULL COMMENT '데이터기준일'
);

 

3. Table Data Import Wizard

• 테이블 컬럼에 해당하지 않는 불필요한 데이터 삭제

 

데이터가 성공적으로 import 되었다.

 

4. 데이터 가공

• 자동차검사소명 필드의 텍스트 데이터 중 특수문자 제거
• 자동차검사소명 필드의 공백 제거
• 위동, 경도 필드값을 소수점 2자리 까지만 남도록 가공
• 검사소 인력수 값이 없는 경우 결측치 처리

-- 자동차검사소명 필드의 특수문자 제거
UPDATE car_test
SET car_test_nm = REGEXP_REPLACE(car_test_nm, '[^a-zA-Z0-9가-힣]', '');

-- 자동차검사소명 필드의 공백 제거
UPDATE car_test
SET car_test_nm = REPLACE(car_test_nm, ' ', '');

-- 위도, 경도 필드값을 소수점 2 자리 까지만 남도록 가공
UPDATE car_test
SET x = ROUND(x, 2),
y = ROUND(y, 2);

-- 검사소 인력수 값이 없는 경우 결측치 처리
UPDATE car_test
SET tester = NULL
WHERE tester = '';