Your CSV Is a Mess. Here's How to Fix It in 10 Minutes.

지난 화요일, 저는 포춘 500대 기업의 수석 데이터 분석가가 10분이면 끝날 CSV 파일을 수동으로 정리하는 데 4시간을 소요하는 모습을 보았습니다. 그녀는 셀을 복사하고 붙여넣고, 보이지 않는 문자를 찾으며, 화면에 점점 더 창의적인 욕설을 중얼거리고 있었습니다. 제가 마침내 걸어가 세 가지 간단한 기술을 보여주었을 때, 그녀는 제가 마법을 부린 것처럼 저를 쳐다보았습니다.

저는 사라 첸이며, 지난 12년간 데이터 운영 컨설턴트로 활동하며 기발한 스타트업부터 다국적 기업까지 다양한 고객과 작업해왔습니다. 그동안 저는 같은 CSV 악몽이 수천 번 반복되는 것을 보았습니다. 가져오기에서 실패하게 만드는 보이지 않는 유니코드 문자들. 분석을 손상시키는 불일치 날짜 형식. 이름을 알아볼 수 없는 말로 변질시키는 인코딩 문제. 그걸 보면 너무 짜증이 나는데, 이 문제들 중 거의 모든 문제가 빠르고 반복 가능한 해결책이 있다는 것을 대부분의 사람들이 모른다는 것입니다.

CSV 파일은 데이터 세계의 바퀴벌레입니다. 보기 흉하고, 어디에나 있으며, 죽지 않으려 합니다. 1970년대 초에 발명되었음에도 불구하고, CSV는 여전히 비즈니스에서 가장 일반적인 데이터 교환 형식입니다. 데이터 관리 협회의 2023년 조사에 따르면, 87%의 조직이 여전히 시스템 간 데이터 공유의 주요 방법으로 CSV 파일을 사용하고 있습니다. 그러나 잔인한 진실은 이렇습니다: 데이터 과학 프로젝트 시간의 약 60%가 데이터 정리에 소요되며, CSV 문제는 그 낭비의 불비례한 부분을 차지합니다.

이 글은 가장 일반적인 CSV 재앙을 신속하고 영구적으로 수정하는 방법에 대한 현장 가이드입니다. 복잡한 프로그래밍이나 비싼 도구를 가르치지 않겠습니다. 대신, 지저분한 CSV를 10분 이내에 깨끗하고 사용 가능한 데이터로 변환하는 데 매일 사용하는 검증된 기술을 공유하겠습니다. 시작해 봅시다.

보이지 않는 적: 문자 인코딩 문제

문자 인코딩 문제는 제가 만나는 가장 큰 CSV 문제이며, 이는 문자 그대로 보이지 않기 때문에 가장 답답합니다. CSV 파일을 열면 갑자기 "José García"가 "José García" 또는 더 나쁜 경우 "Jos� Garc�a"로 변합니다. 악센트가 있는 이름, 통화 기호, 심지어 간단한 따옴표까지 이해할 수 없는 쓰레기로 변할 수 있습니다.

무슨 일이 일어나고 있는가 하면, 귀하의 CSV는 아마도 UTF-8 인코딩으로 생성되었을 것이며(국제 문자를 지원하는 현대 표준), 그러나 소프트웨어는 이를 Windows-1252 또는 ASCII(확장 문자를 지원하지 않는 이전 인코딩)로 읽으려 하고 있습니다. 이는 영어 사전만을 이용해 스페인어 책을 읽으려는 것과 같아—단어는 번역되지 않습니다.

제가 찾은 가장 빠른 해결책은 Windows에서 Notepad++, Mac에서 TextEdit라는 무료 도구를 사용하는 것입니다. Notepad++에서 CSV를 열고 메뉴에서 인코딩으로 가서 "UTF-8-BOM으로 변환"을 선택합니다. 그 "BOM"(Byte Order Mark)은 중요합니다—파일 시작 부분에 있는 특별한 보이지 않는 문자로, 다른 프로그램에게 "안녕, 이 파일은 UTF-8이다, 그에 맞게 취급해라."라고 알립니다. 파일을 저장하면 문자 문제의 90%가 사라집니다.

Mac 사용자라면 TextEdit에서 파일을 열고, 형식 > 일반 텍스트로 만들기로 가서, 파일 > 저장을 클릭한 후 드롭다운에서 UTF-8 인코딩을 선택하세요. 중요한 것은 단순히 보기를 변경하는 것이 아니라 변환하고 저장하는 것입니다.

이 문제를 자주 다룬다면, 모든 CSV에서 실행할 수 있는 간단한 Python 스크립트를 만드는 것을 추천합니다. 제가 사용하는 방법은 이렇습니다: UTF-8 인코딩으로 파일을 읽고, 문제가 있는 문자를 대체하여 처리한 다음 UTF-8-BOM 인코딩으로 다시 저장합니다. 지난 3년 동안 이렇게 50,000개 이상의 CSV 파일을 처리했으며, 수동 수정에 약 200시간을 절약했습니다.

전문 팁: 국제 소스에서 CSV를 받을 때, 항상 UTF-8 및 BOM으로 내보내달라고 요청하세요. 대부분의 내보내기 대화 상자에서 간단한 체크박스 하나이지만, 수 시간의 하류 두통을 방지합니다. 저는 제가 작성하는 모든 데이터 공유 계약에 이 요구 사항을 추가했으며, 이로 인해 제 프로젝트의 인코딩 문제를 약 85% 줄였습니다.

줄 바꿈 혼란: 행이 실제로 행이 아닐 때

이런 모습을 상상해 보세요: 1,000개의 행이 있는 CSV를 가져왔는데, 데이터베이스에는 1,247개의 행이 표시됩니다. 또는 더 심각하게는, 1,000개의 행이 있지만 어떤 행은 신비롭게도 여러 줄에 나뉘어 있으며, 한 줄에는 고객 주소의 반이 있고 다음 줄에 나머지 반이 있습니다. 불일치하는 줄 바꿈의 악몽에 오신 것을 환영합니다.

"CSV 파일은 표면적으로는 속이기 쉬운 단순한 구조이지만 데이터 파이프라인을 조용히 손상시킬 수 있는 edge case의 지뢰밭입니다. 10분 만에 고칠 수 있는 문제와 4시간의 악몽의 차이는 어느 세 버튼을 눌러야 하는지를 아는 것입니다."

이런 일이 발생하는 것은 서로 다른 운영 체제가 줄의 끝을 표시하기 위해 서로 다른 문자를 사용하기 때문입니다. Windows는 CRLF(캐리지 리턴 + 줄 바꿈)를 사용하고, Unix와 Mac은 LF(줄 바꿈만), 오래된 Mac은 CR(캐리지 리턴만)을 사용합니다. 이러한 것들이 단일 파일에서 혼합될 때—다양한 시스템을 통해 데이터가 전달될 때 더 자주 발생합니다—혼란이 발생합니다.

문제는 실제 줄 바꿈을 포함하는 텍스트 필드가 있을 때 기하급수적으로 악화됩니다. 예를 들어 고객의 코멘트가 "멋진 제품<줄 바꿈>빠른 배송<줄 바꿈>다시 구매할 것"이라고 한다면, 그 내부 줄 바꿈이 행의 끝줄 바꿈과 일치하지 않으면 CSV 파서가 행이 실제로 어디서 끝나는지를 혼란스러워합니다.

제가 사용하는 기본 솔루션은 dos2unix라는 무료 명령줄 도구입니다(역순을 위해 unix2dos도 있습니다). Windows에서는 Chocolatey를 통해 설치하거나 직접 다운로드할 수 있습니다. Mac이나 Linux에서는 보통 사전 설치되어 있거나 패키지 관리자를 통해 사용할 수 있습니다. "dos2unix yourfile.csv"를 실행하면 모든 줄 바꿈을 Unix 형식(LF)으로 표준화합니다. 이는 가장 호환성이 좋습니다.

텍스트 필드에 내포된 줄 바꿈이 있는 파일의 경우 보다 정교한 접근이 필요합니다. CSV 표준은 줄 바꿈이 포함된 텍스트 필드는 따옴표로 묶어야 한다고 명시하고 있지만 모든 시스템이 이 규칙을 따르지는 않습니다. 저는 csvkit이라는 Python 라이브러리를 사용하며, 특히 csvclean 명령을 사용해 이러한 경우를 지능적으로 처리합니다. 고객 피드백 데이터나 여러 줄에 걸친 제품 설명을 다룰 때 수없이 많은 시간을 절약해 주었습니다.

지난 달의 실제 사례를 소개합니다: 한 소매 고객이 3,200개의 제품이 포함된 제품 카탈로그 CSV를 보냈습니다. 가져왔을 때, 4,100개의 행이 되었고, 이는 제품 설명에서 인용되지 않은 줄 바꿈이 포함되어 있었기 때문입니다. csvclean을 실행하자 15초 만에 해결되었습니다. 만약 그 대안이라면 수천 개의 행을 수동으로 검토하고 수정해야 했으며, 며칠이 걸렸을 것입니다.

날짜 형식 재앙

누군가의 분석이 불일치 날짜 형식 때문에 깨질 때마다 1달러를 받았다면, 저는 내일 퇴직할 수 있을 것입니다. 날짜는 "01/02/2024"가 미국에서는 1월 2일을 의미하지만 유럽에서는 2월 1일을 의미하며, "2024-01-02"만이 진정으로 모호하지 않은 형식이라는 것을 깨닫기 전까지는 속이기 쉬운 것입니다.

제가 만난 최악의 CSV는 동일한 열에 다섯 개의 서로 다른 날짜 형식이 있었던 것입니다: "MM/DD/YYYY", "DD/MM/YYYY", "YYYY-MM-DD", "Month DD, YYYY", 그리고 Unix 타임스탬프였습니다. 이는 여러 지역 사무소에서 데이터를 병합하여 생성되었으며, 아무도 형식을 먼저 표준화하지 않았습니다. 그 결과? 그들의 분기별 판매 분석은 수백만 달러의 차이가 발생했습니다, 날짜가 잘못 해석되고 있었기 때문입니다.

날짜 정리에 대한 저의 표준 운영 절차는 다음과 같습니다: 먼저, 파일에 있는 모든 날짜 형식을 식별합니다. 날짜 열을 정렬하여 이 작업을 빠르게 수행할 수 있습니다.