12.1.2 데이터 정제하기
두 테이블에 데이터를 넣었으니 이제 데이터 정제 작업을 해야 합니다. 데이터 정제는 데이터 클렌징(cleansing)이라고도 하는데, 데이터를 분석하기 쉽도록 데이터 수집과 입력 과정에서 발생한 오류로 생긴, 정확하지 않은 데이터를 걸러서 분석 결과에 왜곡 발생을 줄이는 작업을 말합니다.
숫자형 칼럼에 NULL이 들어간 경우 이를 0으로 치환하거나 일관성이 없는 데이터를 보정하는 작업 등이 이에 속합니다. 예를 들어 광역시와 도 정보를 저장한 칼럼에 어떤 건은 서울특별시, 어떤 건은 서울시 또는 서울로 데이터를 저장했습니다. 이럴 때 해당 칼럼 값을 서울특별시로 통일하는 것이 일종의 정제 작업입니다.
우리가 사용할 코로나 데이터는 이미 어느 정도 정제된 데이터라서 작업할 내용은 많지 않고 몇 가지만 처리하면 됩니다.
불필요한 데이터 삭제하기
covid19_country와 covid19_data 테이블에는 국가 코드가 OWID로 시작하는 데이터가 있습니다. 일단 해당 건을 조회해 보죠.
코드 12-5
SELECT countrycode, countryname
FROM covid19_country
WHERE countrycode LIKE 'OWID%'
ORDER BY 1;
실행결과
국가 코드가 OWID로 시작하는 데이터는 국가별 데이터를 각 국가가 속한 대륙별로 다시 집계한 데이터입니다. 예를 들어 OWID_ASI는 아시아에 속한 국가들의 집계 데이터로 OWID에서 별도로 집계한 건이죠. 우리는 원천 데이터를 대상으로 분석하므로 추가로 집계된 데이터는 필요 없습니다. 게다가 특정 일자의 전체 국가 확진자 수를 구할 때 해당 데이터 때문에 확진자 수가 2배로 집계될 수 있습니다. 따라서 covid19_country와 covid19_data 테이블에서 해당 건들을 삭제하겠습니다.
코드 12-6
DELETE FROM covid19_country
WHERE countrycode LIKE 'OWID%';
DELETE FROM covid19_data
WHERE countrycode LIKE 'OWID%';
실행결과
DELETE 문으로 국가 코드가 OWID로 시작되는 건을 삭제합니다. covid19_country 테이블에서는 11건, covid19_data 테이블에서는 3923건이 삭제됐습니다.
숫자형 칼럼 값 NULL 처리하기
두 테이블의 숫자형 칼럼에 NULL이 있는 건이 있습니다. NULL과 사칙연산을 하면 결과가 NULL이 나오므로 이 건들은 0으로 변경하는 것이 좋습니다. 물론 연산할 때 SQL 문에서 IFNULL() 함수로 NULL을 0으로 대체해 반환하게 할 수도 있지만, 쿼리를 작성할 때마다 IFNULL() 함수를 사용하는 것도 번거로우니 아예 데이터에서 NULL을 0으로 변경하겠습니다.
covid19_country 테이블에는 숫자형 칼럼이 6개가 있고, covid19_data 테이블에는 9개가 있습니다. 이 칼럼 값을 다음과 같이 UPDATE 문을 사용해 0으로 변경합니다.
코드 12-7
UPDATE covid19_country
SET population = IFNULL(population, 0),
population_density = IFNULL(population_density, 0),
median_age = IFNULL(median_age, 0),
aged_65_older = IFNULL(aged_65_older, 0),
aged_70_older = IFNULL(aged_70_older, 0),
hospital_beds_per_thousand = IFNULL(hospital_beds_per_thousand, 0);
UPDATE covid19_data
SET cases = IFNULL(cases, 0),
new_cases_per_million = IFNULL(new_cases_per_million, 0),
deaths = IFNULL(deaths, 0),
icu_patients = IFNULL(icu_patients, 0),
hosp_patients = IFNULL(hosp_patients, 0),
tests = IFNULL(tests, 0),
reproduction_rate = IFNULL(reproduction_rate, 0),
new_vaccinations = IFNULL(new_vaccinations, 0),
stringency_index = IFNULL(stringency_index, 0);
실행결과
NULL을 특정 값으로 변경할 때 해당 칼럼에 NULL이 들어간 건을 찾아 0으로 수정하는 방법을 떠올릴 수 있죠. 예를 들어 covid19_country 테이블의 population 칼럼을 대상으로 한다면 UPDATE 문에서 SET population = 0을 기술하고 WHERE 절에서 population IS NULL이란 조건을 주면 됩니다.
또 다른 방법으로는 코드 12-7처럼 대상 칼럼들을 자기 자신의 값으로 변경하는데, IFNULL() 함수로 NULL을 0으로 수정할 수도 있습니다. 이 경우에는 WHERE 절이 필요 없지만, NULL이 아닌 칼럼 값도 다시 자신의 값으로 변경되는 불필요한 작업이 수반됩니다.
두 가지 방법 중 어느 것이 좋은지는 상황에 따라 다릅니다. 예를 들어 테이블에 데이터가 아주 많다면 UPDATE 문장은 많지만 NULL인 건만 골라 수정하는 첫 번째 방법이 성능상 좋을 수 있습니다. 여기에서는 데이터가 상대적으로 많지 않아 두 번째 방법으로 처리했습니다.
그럼 NULL이 0으로 제대로 수정됐는지 확인해 보죠. 어떤 쿼리를 사용하면 NULL이 있는지 없는지 알 수 있을까요? 첫 번째는 SUM() 함수로 해당 칼럼에 있는 값을 모두 더하는 방법입니다. 하지만 SUM() 함수는 NULL이 있는 건을 무시하고 모두 더한 결과를 반환하므로 NULL의 존재 여부를 알 수 없습니다. 그럼 어떻게 확인하면 좋을까요? 다음 코드를 봅시다.
코드 12-8
WITH null_check1 AS
(SELECT population + population_density + median_age + aged_65_older +
aged_70_older + hospital_beds_per_thousand AS plus_col
FROM covid19_country
),
null_check2 AS
(SELECT CASE WHEN plus_col IS NULL THEN 'NULL'
ELSE 'NOT NULL'
END chk
FROM null_check1
)
SELECT chk, COUNT(*)
FROM null_check2
GROUP BY chk;
실행결과
코드를 보면, 첫 번째로 null_check1 CTE에서는 covid19_country 테이블에서 NULL을 0으로 수정한 모든 칼럼을 더합니다. SUM() 함수를 사용하면 NULL을 확인하지 못하지만, 사칙연산을 하면 NULL 값이 포함된 경우 NULL이 반환되므로 해당 숫자 칼럼들을 모두 더합니다. 두 번째로 null_check2에서는 CASE 연산자로 숫자 칼럼을 모두 더한 값인 plus_col 값이 NULL이면 'NULL'이란 문자를, NULL이 아니면 'NOT NULL' 문자를 chk라는 가상 칼럼으로 반환하게 합니다. 마지막으로 메인쿼리에서는 null_check2 서브쿼리를 조회해 chk 칼럼별로 집계합니다.
결과를 보면 계산한 값이 NOT NULL인 건이 204개입니다. 따라서 모두 NULL이 아님을 확인할 수 있습니다.
이처럼 데이터 정제 작업을 하고 나면 제대로 처리됐는지 반드시 확인해야 합니다. covid19_data 테이블에서도 같은 방식으로 확인할 수 있습니다.