헤더를 찾았다고 데이터가 깨끗해지는 것은 아닙니다. 사람이 오래 관리해 온 엑셀에는 눈으로는 안 보이지만 프로그램은 걸려 넘어지는 것들이 곳곳에 박혀 있습니다. 천 단위 콤마가 붙은 "1,234"는 사람에게는 숫자지만 코드에게는 문자열이고 같은 뜻의 열이 시트마다 다른 이름으로 적혀 있으며 어떤 시트는 백만달러로 어떤 시트는 억달러로 값을 담습니다. 분기마다 갱신되는 무역통계 엑셀을 자동으로 가공하는 프로젝트에서, 이 제각각인 원본을 하나의 통합 데이터로 모으는 정규화가 파이프라인의 허리였습니다. 그 설계를 정리합니다.
정규화가 없으면 뒷단이 전부 조건문이 된다
정규화의 목적은 단순합니다. 뒤에 오는 모든 단계가 "데이터는 이런 모양이다"를 하나만 믿게 만드는 데 있습니다. 만약 통합 없이 각 시트를 그대로 들고 다니면, 교차표를 만드는 코드가 "이 시트는 콤마가 있고 저 시트는 없고, 여기는 기간이라 쓰고 저기는 분기라 쓰고" 같은 예외를 전부 떠안게 됩니다. 예외가 뒷단으로 번지면 코드는 조건문 덩어리가 되고 유지보수가 무너집니다.
그래서 원칙을 세웠습니다. 지저분한 것은 전부 입구에서 흡수한다. 정규화를 통과한 뒤의 데이터는 언제나 아홉 개 열, 정해진 자료형, 하나의 단위 기준을 갖습니다. 이 통합 데이터프레임 하나만 바라보면 되도록 뒷단을 단순하게 유지하는 것이 정규화 단계가 존재하는 이유입니다.
이름이 다른 같은 열을 맞추기
첫 번째 일은 열 이름 정렬입니다. 표준 스키마는 년도, 기간, 유형, ICT산업, 기관형태, 지역과 수출, 수입, 수지입니다. 그런데 원본에서는 "기간"이 "분기"로 적혀 있기도 합니다. 사람은 둘이 같은 뜻인 줄 알지만 프로그램은 모릅니다. 그래서 표준 이름마다 별칭 목록을 두고 원본 열 이름을 정규화한 뒤 별칭까지 훑어 표준 이름에 대응시킵니다. 대응표를 만든 다음 필요한 아홉 개 열만 골라 새 데이터프레임을 구성하고 표준에 없는 여분의 열은 버립니다.
여기서 중요한 판단은 "필수 열이 하나라도 없으면 멈춘다"입니다. 아홉 개 중 하나라도 매핑되지 않으면 조용히 빈 값으로 채우는 대신, 어떤 열이 없는지를 명확히 알리고 실패로 처리합니다. 데이터가 부족한 채로 진행해 그럴듯한 틀린 결과를 내놓는 것보다, 무엇이 빠졌는지 알고 멈추는 편이 훨씬 안전합니다.
콤마 숫자와 단위, 텍스트 공백
열을 맞춘 다음은 값을 다듬습니다. 텍스트 열은 앞뒤 공백을 정리합니다. 눈에 안 보이는 공백 하나가 "유형A"와 "유형A "를 다른 값으로 만들어 나중에 교차표에서 같은 항목을 둘로 쪼개기 때문입니다. 숫자 열은 엑셀이 콤마를 섞어 내보낸 문자열을 숫자로 되돌립니다. 이 변환을 건너뛰면 합계가 문자열 이어붙이기가 되어 값이 완전히 어긋납니다.
단위는 조금 더 신경 쓴 부분입니다. 원본의 기본 단위는 백만달러지만 보고서는 억달러와 비중(%)도 함께 요구합니다. 이 변환을 사람이 시트마다 다시 계산하던 것이 기존 작업의 큰 부담이었습니다. 정규화 단계에서 기준 단위를 백만달러로 통일해 두면, 뒤에서 억달러 시트와 비중 시트를 파생시키는 계산이 한 곳에서 규칙대로 일어납니다. 단위 변환이 데이터 곳곳에 흩어지지 않고 한 규칙으로 모이는 것이 정규화의 실익입니다.
여러 워크북을 하나로 쌓기
마지막은 통합입니다. 6년 추이를 보려면 연도별 워크북 여섯 개를 모두 열어야 했는데, 정규화를 거친 각 시트는 동일한 아홉 개 열 구조를 가지므로 그냥 위아래로 쌓으면 됩니다. 서로 다른 워크북, 서로 다른 시트가 하나의 커다란 통합 데이터프레임이 되고 이 시점부터는 어느 값이 원래 어느 파일 어느 시트에서 왔는지를 신경 쓰지 않아도 됩니다. 사람이 파일을 오가며 수치를 모으던 일이 이 통합 한 번으로 대체됩니다.
정규화를 한 줄로 요약하면 "예외는 입구에서 끝낸다"입니다. 이름의 차이, 자료형의 차이, 단위의 차이, 공백의 차이를 전부 이 단계에서 흡수하고 나면, 교차표를 만들고 검증하는 뒷단은 깨끗한 데이터 하나만 상대하면 됩니다. 자동화가 오래 사는지 아닌지는 대개 이 입구를 얼마나 관대하게 설계했는지에서 갈립니다.
이 정규화의 입구인 헤더 감지는 엑셀 헤더 자동 감지 설계에서, 정규화된 데이터로 교차표를 만드는 과정은 교차분석표 자동 피벗에서 다룹니다. 전체 사례는 무역 통계 엑셀 교차분석 자동화에서 볼 수 있습니다.
자주 묻는 질문
콤마가 섞인 숫자는 왜 따로 처리해야 하나요?
엑셀이 천 단위 콤마를 붙여 내보낸 "1,234"는 겉보기에 숫자지만 프로그램에는 문자열입니다. 이대로 합계를 내면 덧셈이 아니라 문자열 이어붙이기가 되어 값이 완전히 틀어집니다. 그래서 정규화 단계에서 콤마를 걷어내고 숫자로 되돌린 뒤에야 계산에 넣습니다.
필수 열이 없으면 왜 멈추게 했나요?
빠진 열을 조용히 빈 값으로 채우면 그럴듯하지만 틀린 결과가 나오고 그 오류는 발견하기가 매우 어렵습니다. 아홉 개 표준 열 중 하나라도 대응되지 않으면 어떤 열이 없는지 알리고 실패로 처리하는 편이, 잘못된 데이터로 계속 진행하는 것보다 안전하다고 봤습니다.
단위 변환을 정규화에서 통일하면 뭐가 좋나요?
기준 단위를 한곳에서 백만달러로 통일해 두면, 억달러와 비중처럼 파생되는 시트의 계산이 규칙 하나로 모입니다. 단위 변환 로직이 데이터 곳곳에 흩어지지 않으므로 계산 실수가 줄고 사람이 시트마다 다시 환산하던 반복 작업도 사라집니다.