How to Clean Messy CSV Data (A Practical Checklist)

지난 화요일, 저는 한 주니어 분석가가 CSV 파일을 수동으로 수정하는 데 4시간을 소비하는 모습을 보았습니다. 이 파일은 청소하는 데 20분이면 충분했어야 했습니다. 그녀는 서로 다른 세 개의 시스템에서 내보낸 날짜 형식을 수정하려고 셀을 하나씩 복사하고 붙여넣기 하고 있었습니다. 마침내 작업을 끝냈을 때, 그녀의 눈은 흐릿해졌고, 파일에는 여전히 그녀가 발견하지 못한 불일치가 있었습니다. 저는 데이터 엔지니어로 12년 경력을 가지고 있으며, 이런 상황이 끊임없이 발생하는 것을 봅니다—뛰어난 사람들이 체계적인 접근 방식이 없어서 혼란스러운 CSV 파일에 온 하루를 낭비하고 있는 것입니다.

진실은 CSV 파일이 가장 보편적이면서도 문제를 일으키는 데이터 형식이라는 것입니다. 모든 시스템이 내보낼 수 있고, 모든 도구가 가져올 수 있지만, 인코딩 문제, 구분자 혼란, 구조적 불일치를 암초처럼 숨기고 있습니다. 2023년 데이터 품질 연구소의 조사에 따르면 데이터 전문가들은 데이터 정리 작업에 평균 19.2시간을 소비하며, 그 중 약 40%는 CSV 파일 조작에 해당합니다. 매주 거의 8시간, 즉 하루의 업무 시간을 예방 가능한 문제를 해결하는 데 잃고 있는 것입니다.

저는 마커스 첸이며, 지난 10년 동안 불확실한 스타트업부터 포춘 500 기업에 이르기까지 다양한 회사에 데이터 파이프라인을 구축해왔습니다. 저는 수십억 행의 CSV 데이터를 처리했으며, 이 파일이 깨지는 모든 상상을 초월하는 방법을 보았습니다. 제가 배운 것은 혼란스러운 CSV 데이터를 정리하는 것이 영리해지는 것이 아니라 체계적이 되는 것이라는 것입니다. 체크리스트가 필요하며, 분석이나 데이터베이스에 문제가 발생하기 전에 문제를 발견하는 반복 가능한 프로세스가 필요합니다. 이 기사는 수천 시간의 실제 데이터 조작에서 distilled된 체크리스트입니다.

CSV 파일이 문제를 일으키는 이유 이해하기

청소 프로세스에 들어가기 전에 CSV 파일이 왜 이렇게 많은 문제를 일으키는지 이해해야 합니다. 이 형식은 겉보기에는 간단해 보입니다: 값은 쉼표로 구분되고, 각 행은 한 줄로 기록됩니다. 그러나 그 단순함이 바로 문제입니다. JSON이나 XML 같은 구조화된 형식과 달리 CSV는 모두가 따르는 공식 사양이 없습니다. RFC 4180 표준이 존재하지만, 이는 규정이라기보다 제안에 가깝고 대부분의 시스템은 이를 무시합니다.

한 번은 고객의 레거시 회계 시스템에서 세미콜론을 구분자로 사용하는 CSV 파일을 받았고, 숫자 값(예: "1,234.56")에 쉼표가 포함되어 있으며, 텍스트 자격을 위해 단일 및 이중 인용부호를 혼용하였고, 같은 파일에 Windows와 Unix 줄 바꿈을 혼합했습니다. 이를 올바르게 구문 분석하는 데만 3시간이 걸렸고, 전문 도구를 사용했는데도 그랬습니다. 이 파일은 고객의 시스템에서 수년 동안 "잘 작동"했는데, 그들의 소프트웨어가 이러한 모든 특이점을 처리하기 위한 맞춤 논리를 가지고 있었기 때문입니다.

CSV 파일의 핵심 문제는 여러 범주로 나눌 수 있습니다. 첫째, 구분자 문제—데이터에 쉼표가 일반적으로 사용되므로 시스템은 탭, 파이프, 세미콜론 또는 기타 문자를 사용하지만 항상 어떤 것을 사용하는지 알려주지는 않습니다. 둘째, 텍스트 자격 문제—값 주위에 인용 부호가 필요할 때는 언제인지, 데이터에 인용 문자 가 포함될 때는 무엇이 문제인지? 셋째, 인코딩—이것이 UTF-8인가, Latin-1인가, Windows-1252인가 아니면 완전히 다른 것인가? 잘못되면 악센트가 있는 문자가 있어야 할 곳에 쓰레기 문자가 보일 것입니다.

그 다음에는 구조적 문제도 있습니다. CSV 파일은 데이터 유형을 나타내기 위한 내장된 방법이 없으므로 모든 것이 파싱될 때까지는 텍스트로 처리됩니다. 날짜는 "2024-01-15", "01/15/2024", "15-Jan-24" 또는 "January 15, 2024"일 수 있으며, 때로는 서로 다른 사용자가 데이터를 다르게 입력했기 때문에 같은 열에 이 네 가지 형식이 모두 나타날 수 있습니다. 숫자에는 통화 기호, 백분율 기호 또는 천 단위 구분 기호가 있을 수 있습니다. 부울 값은 "true/false", "yes/no", "1/0", "Y/N" 또는 그 조합일 수 있습니다.

가장 큰 문제는 많은 CSV 문제들이 잠재적으로 발생한다는 것입니다. 가져오기 도구는 오류 없이 성공할 수 있지만, 데이터는 미묘하게 손상됩니다. 제품 코드에서 선행 0이 제거되어 "00123"이 "123"으로 변하는 사례를 보았고 이는 재고 시스템을 망가뜨립니다. 시스템이 MM/DD/YYYY로 가정했기 때문에 날짜가 잘못 해석되어 주문이 불가능한 날짜인 2월 31일로 예정된 경우도 보았습니다. 이러한 오류는 스스로 알리지 않으며, 숫자가 맞지 않는 것을 누군가 알아차릴 때까지 데이터에 숨어 있습니다.

1단계: 가져오기 전에 검사하기

사람들이 CSV 파일과 관련하여 가장 크게 저지르는 실수는 이를 Excel에서 직접 열거나 데이터베이스에 바로 가져오는 것입니다. 이렇게 하지 마세요. 첫 번째 단계는 항상 텍스트 편집기나 명령줄 도구로 검사를 하는 것입니다. 저는 Unix 시스템에서 head, tail, wc -l 조합을 사용하거나 VS Code나 Sublime Text와 같은 프로그래머 텍스트 편집기를 사용하여 큰 파일도 여유롭게 처리할 수 있습니다.

"CSV 파일은 데이터 형식의 바퀴벌레입니다—모든 것을 견디고, 어디서나 작동하며, 예상치 못한 문제를 일으킵니다."

먼저 파일의 처음 20-30줄을 살펴보세요. 이렇게 하면 구분자, 헤더 행이 있는지 여부 및 일반적인 구조가 어떻게 되는지 알 수 있습니다. 하지만 거기서 멈추지 말고 마지막 20-30줄도 확인하세요. 파일이 처음에는 깨끗하지만 마지막에는 불량 데이터가 포함된 경우를 몇 번이나 발견했는지 셀 수 없습니다: 부분 행, 내보내기 프로세스의 오류 메시지, 또는 누군가가 데이터에 대해 추가한 요약 통계 등이 있습니다. 한 고객의 ERP 시스템은 모든 내보내기 끝에 "총 레코드: 45,892"라는 바닥글을 추가하여, 아무런 준비없이 가져오는 프로세스를 중단시켰습니다.

wc -l filename.csv를 사용하여 행 수를 확인하고 예상하는 것과 비교하세요. 파일에 10,000개의 레코드와 헤더가 있어야 한다면, 10,001줄이 보여야 합니다. 10,247줄이 보인다면 뭔가 잘못되었습니다—아마도 텍스트 필드에 포함된 줄 바꿈이 원인입니다. 사용자 생성 콘텐츠가 포함된 CSV 파일에서는 이러한 문제가 매우 일반적입니다. 누군가가 "이 제품은 아주 좋습니다\n강력 추천합니다"라고 작성하면 갑자기 행 중간에 줄 바꿈이 생깁니다.

처음 몇 줄을 살펴보면서 구분자를 찾아보세요. 각 행에 있는 쉼표, 탭, 파이프 또는 세미콜론을 세어보세요. 그것들은 일관성이 있어야 합니다. 첫 번째 행에 12개가 있고 두 번째 행에 15개가 있다면 문제가 있는 것입니다—구분자가 잘못되었거나, 구분자 문자를 포함하는 비자격 텍스트 필드가 있는 것입니다. 저는 한 번 파이프 문자(|)를 구분자로 사용하는 파일을 디버깅하는 데 한 시간을 보낸 적이 있는데, 이는 제 터미널 글꼴에서 거의 보이지 않았습니다.

비 ASCII 문자로 인코딩을 확인하세요. 아포스트로피 대신 ’와 같은 이상한 기호가 보이거나, é 대신 é가 보인다면 인코딩 불일치가 있는 것입니다. 파일은 아마도 UTF-8이지만 Latin-1로 읽히고 있거나, 반대일 수 있습니다. Unix에서 file -i filename.csv와 같은 도구를 사용하여 인코딩을 감지하거나, 텍스트 편집기의 인코딩 감지 기능을 이용하세요. 초기에 이를 잘못 설정하면 데이터의 모든 텍스트 필드가 손상됩니다.

2단계: 구조 확인하기

파일을 수동으로 검사한 후의 다음 단계는 구조적 검증입니다. 모든 행은 동일한 수의 필드를 가져야 하며, 그 숫자는 헤더 행과 일치해야 합니다. 이는 명백하게 들리지만, 현실 데이터에서는 항상 위반됩니다. 私は、Python와 csv 모듈 또는 pandas를 사용하여 프로그래밍적으로 이 확인을 합니다. 왜냐하면 눈으로 수천 행을 체크하는 것은 불가능하기 때문입니다.

제가 받은 모든 CSV 파일에서 실행하는 간단한 Python 스크립트가 있습니다: 각 행의 필드 수를 세고 헤더와 일치하지 않는 것을 보고합니다. 최근 프로젝트에서 이 스크립트는 50,000개 행 중 347개 행이 추가 필드를 가지고 있음을 밝혔습니다. 내보내기 프로세스에 버그가 있었기 때문인데, 특정 조건에서 마지막 열이 중복되었습니다. 이 검사 없이는 해당 행들이 가져오기 과정에서 조용히 손상되었을 것이며, 추가 데이터가 잘리거나 잘못된 열로 밀려났을 것입니다.

인용된 필드에 특별히 주의하세요. CSV는 구분자와 줄 바꿈을 허용하기 위해 인용 부호를 사용하지만, 인용 규칙은 복잡하고 종종 잘못 구현됩니다. 필드 "Smith, John"은 적절히 인용되지만, "그가 나에게 '안녕하세요'라고 말했다"는 무엇입니까? 올바른 형식은 "그가 나에게 ""안녕하세요""라고 말했다"인데, 많은 시스템들이 이를 잘못해 "그가 나에게 '안녕하세요'라고 말했다"라는 형식을 생성하여 구문 분석을 망칩니다.

빈 행을 확인하세요. 이들은 종종 파일의 끝이나 데이터 섹션 사이에 나타납니다. 이러한 행들은 처리해야 합니다.