Data Cleaning Tips: Fix Messy Spreadsheets Fast — csv-x.com

지난 화요일, 저는 한 주니어 분석가가 50,000행의 고객 데이터베이스를 수작업으로 수정하는 데 4시간을 소비하는 모습을 보았습니다. 그녀는 개별 셀을 복사하고 붙여넣고, 일관성 없는 날짜 형식에 눈을 가늘게 뜨며, "이 혼란을 수출한 사람은 누구야"라고 중얼거렸습니다. 저는 데이터 운영 컨설턴트로 12년을 일해왔으며, 제가 일하는 포춘 500대 기업에서 이 시나리오가 주 2회 이상 발생하는 것을 봅니다. 진실은? 대부분의 전문가들은 올바른 데이터 정리 기술을 배우지 않으며, 이는 더러운 데이터에 기반한 잘못된 결정과 생산성 손실로 인해 기업에 연간 약 3.1 조 달러의 비용을 초래하고 있습니다.

저는 사라 첸이며, 저는 scrappy 스타트업부터 다국적 기업에 이르기까지 여러 회사의 데이터 재난을 정리하는 데 10년 넘게 시간을 보냈습니다. 제 전문 분야는 혼란스러운 스프레드시트를 깨끗하고 분석 준비가 된 데이터셋으로 변환하는 것—빠르게 말이죠. 제가 배운 것은 데이터 정리가 완벽함과 관련된 것이 아니라 효율성과 어떤 전투를 싸워야 하는지를 아는 것이란 점입니다. 오늘, 저는 많은 사람들이 어려움을 겪는 데 소모하는 시간의 일부로 지저분한 CSV 파일을 깨끗한 데이터로 변환할 때 사용하는 정확한 프레임워크를 공유합니다.

지저분한 데이터의 실제 비용 (그리고 속도가 중요한 이유)

기술로 들어가기 전에, 왜 이게 중요한지 이야기해 봅시다. 2019년, 저는 중간 규모의 전자상거래 회사와 작업했습니다. 이 회사는 중복 항목이 있는 판매 스프레드시트를 기반으로 재고 결정을 내리고 있었습니다. 그들은 "최고 판매" 제품이 약간 다른 SKU 형식으로 데이터에 세 번 나타나는 것을 인식하지 못했습니다. 결과는? 그들은 340% 과잉 재고를 보유하였고, 120만 달러의 자본을 팔리지 않은 제품에 묶어두게 되었습니다.

이는 고립된 사건이 아닙니다. IBM의 연구에 따르면, 열악한 데이터 품질은 미국 경제에 매년 약 3.1조 달러의 비용을 초래하고 있습니다. 그러나 가장 많은 기사를 읽었을 때 말하지 않는 것은: 가장 큰 비용은 잘못된 결정이 아니라 시간 낭비입니다. 기업의 데이터 작업 흐름을 감사할 때, 저는 분석가들이 실제 분석보다 데이터 준비에 60-80%의 시간을 소비하고 있다는 것을 일반적으로 발견합니다. 이는 데이터가 복잡하기 때문이 아니며, 비효율적인 방법을 사용하기 때문입니다.

속도는 중요합니다. 데이터는 유효 기간이 있습니다. 50,000행을 수동으로 수정할 때쯤, 비즈니스 맥락이 변경되었을 수 있습니다. 분석 중이던 마케팅 캠페인은 종료되었을 수도 있고, 분기 보고서 마감일이 지나갔을 수도 있습니다. 빠른 데이터 정리는 모서리를 자르는 것이 아니라, 관련성을 유지하고 실제로 결정을 내리는 인사이트에 도달하는 것입니다.

체계적이고 도구를 이용한 데이터 정리 접근법을 채택한 기업들은 일상적인 데이터 준비 작업에서 70-85%의 시간 절약을 보고합니다. 더 중요하게는, 그들은 여전히 수동 정리 모드에 갇힌 경쟁사보다 3-4주 더 빠르게 결정을 내린다고 보고합니다. 빠르게 변화하는 산업에서 이 시간 이점은 직접적으로 시장 점유율로 이어집니다.

치료 전 진단: 데이터 환경 스캔하기

제가 사람들이 가장 흔히 하는 실수는, 처리할 내용을 이해하지도 않고 곧바로 정리를 시작하는 것입니다. 이는 의사가 환자를 검사하지 않고 약을 처방하는 것과 같습니다. 저는 컨설턴트로 일한 지 3년 만에 이 교훈을 어렵게 배웠습니다. 데이터를 정리하는 데 6시간을 소비하고 나서야, 문제는 날짜 형식이 아니라 제 날짜 정리가 완전히 무의미하게 만든 중복 기록이라는 사실을 발견했습니다.

"데이터 정리는 완벽함이 아니라 효율성과 어떤 전투를 싸울지를 아는 것에 관한 것입니다. 목표는 분석 준비된 데이터이지 결점 없는 데이터가 아닙니다."

이제 저는 항상 체계적인 스캔으로 시작합니다. CSV 파일을 열고 정확히 5분간 구조화된 평가를 수행합니다. 첫 번째로, 행 수를 확인합니다—이것이 500행인가, 500,000행인가? 접근 방식은 극적으로 다릅니다. 다음으로, 열 헤더를 검사합니다. 설명적입니까? 일관성이 있습니까? 명백한 문제(예: "Column1"이나 병합된 헤더 행)가 보입니까?

다음으로 데이터 유형을 살펴봅니다. 스크롤하면서 어떤 열이 텍스트여야 하고, 어떤 열이 숫자여야 하며, 어떤 열이 날짜여야 하는지를 파악합니다. 형식이 섞여 보이는 열(예: 일부 항목은 숫자이고, 다른 항목은 서식 있는 텍스트인 "전화번호" 열)에 대한 정신 노트를 작성합니다. 이러한 혼합 유형 열은 하류에서 문제를 일으킬 수 있는 위험 신호입니다.

저는 또한 "엣지 스캔"이라고 부르는 작업을 수행합니다—첫 10행, 마지막 10행, 중간의 임의 샘플을 봅니다. 왜냐하면 데이터 품질 문제는 종종 군집화되기 때문입니다. 한 번에는 첫 5,000행이 완벽한 데이터였지만, 5,001행 이후에는 전혀 다른 소스 시스템에서 온 데이터로 인해 완전히 다른 형식이 적용된 데이터셋을 발견한 적이 있습니다. 맨 위만 확인했다면, 나는 거대한 문제를 놓쳤을 것입니다.

마지막으로, "중요 경로" 열—분석이 작동하기 위해 반드시 깨끗해야 하는 3-5개의 열을 식별합니다. 고객 데이터베이스의 경우, 고객 ID, 이메일 및 구매 날짜일 수 있습니다. 제품 카탈로그의 경우, SKU, 가격 및 카테고리일 수 있습니다. 우선 이러한 열에서 정리 작업을 집중합니다. 모든 열이 완벽할 필요는 없고, 올바른 열이 완벽할 필요가 있습니다.

실제로 작동하는 중복 탐지 프레임워크

중복은 데이터 분석의 침묵하는 살인자입니다. 중복은 수치를 부풀리고 평균을 왜곡하며 유령 트렌드를 만듭니다. 저는 마케팅 팀이 시스템 마이그레이션으로 인한 중복 항목으로 인해 "신규 고객 40% 증가"를 축하하는 것을 본 적이 있습니다. 도전 과제는 중복 항목이 스스로를 알리기보다는 미세한 변동 속에 숨는다는 것입니다.

진정한 중복은 쉽습니다: 100% 동일한 두 행입니다. 대부분의 스프레드시트 도구는 이를 자동으로 찾을 수 있습니다. 그러나 12년간 저는 중복이 그렇게 명백한 데이터셋을 거의 접하지 못했습니다. 대신, 저는 "퍼지 중복"이라고 부르는 것과 다루게 됩니다—같은 개체를 나타내지만 약간의 차이가 있는 기록이죠.

고객 기록을 고려하십시오. "John Smith"와 "John Smith"는 같은 사람일 수 있지만, "John Smith"와 "J. Smith" 그리고 "Smith, John" 및 "john smith" (소문자)도 같은 사람일 수 있습니다. 단순한 중복 검사는 이러한 모든 항목을 놓칠 것입니다. 제 프레임워크는 "일치 키"를 생성하는 것과 관련되어 있습니다—비교를 위해 순수하게 사용되는 데이터의 표준화된 버전입니다.

이름의 경우, 저는 소문자로 변환하고 모든 구두점을 제거하고 여백을 삭제한 후, 단어를 알파벳 순으로 정렬하여 일치 키를 생성합니다. 따라서 "Smith, John"은 "john smith"가 되고 "John Smith"도 "john smith"가 되며 이제 서로 일치합니다. 주소의 경우, 아파트 번호를 제거하고 거리 약어를 전체 단어로 변환한 후 구두점을 제거합니다. 전화번호의 경우, 모든 형식을 제거하고 숫자만 남깁니다.

주요 통찰은 원래 데이터를 수정하지 않는다는 것입니다—임시 일치 열을 생성하고 해당 열을 기반으로 중복을 찾은 후, 중복을 식별하고 처리한 후 일치 열을 삭제합니다. 이는 원래 데이터를 보존하면서 숨겨진 중복을 찾을 수 있는 능력을 부여합니다.

저는 또한 대규모 데이터셋에 대해 제가 "확률적 일치"라고 부르는 것을 사용합니다. 두 개의 기록이 정확히 일치할 때만 중복으로 선언하는 대신 유사성 점수를 부여합니다. 두 고객 기록이 이메일과 전화번호에서 일치하지만 이름에서 약간 다르다면, 90% 유사성 점수를 받습니다. 그런 다음 80% 이상 유사성을 가진 항목을 수동으로 검토합니다—이는 일반적으로 95%의 중복을 포착하며 수동으로 검토해야 하는 기록은 단 2-3%입니다.

날짜 형식 괴물 길들이기

제가 날짜 형식을 수정하는 데 소모한 시간마다 1달러를 받았다면, 은퇴할 수 있었을 것입니다. 날짜는 단순해 보이지만 속임수가 은근히 가득합니다. "01/02/2023"은 미국에서는 1월 2일을, 유럽에서는 2월 1일을 의미합니다. "2023-01-02"는 모호하지 않지만 텍스트로 저장될 수 있습니다. 그리고 Excel의 날짜를 일련 번호로 변환하는 습관에 대해서는 논할 필요도 없습니다.

"열악한 데이터 품질은 미국 경제에 매년 3.1 조 달러의 비용을 초래하지만, 진짜 치명적인 것은 숨겨진 세금입니다: 분석가들이 분석하는 대신 60-80%의 시간을 정리에 소비하게 만드는 것입니다."

날짜 정리의 첫 번째 규칙은 어떤 형식으로 시작하는지를 식별하는 것입니다. 저는 작은 샘플을 작성합니다—아마도 20행 정도—그리고 패턴을 찾습니다. 모든 날짜가 같은 형식에 있습니까? "N/A" 또는 "TBD"와 같이 섞여 있는 명백한 이례가 있습니까? “01/01/1900”(종종 기본값) 또는 역사적이어야 하는 날짜가 미래 날짜로 되어 있는 경우 같은 잘못된 날짜를 보나요?