완전히 똑같은 줄만, 헤더를 유지하고 원래 순서도 그대로 두고 지우려면 다음과 같이 합니다.
awk 'NR==1 || !seen[$0]++' input.csv > deduped.csv
이것은 쉬운 경우를 처리하는 방법인데, 쉬운 경우는 보기보다 드뭅니다. 실제 작업 파일의 "중복"은 대부분 똑같은 줄이 아니라 같은 사람에 대한 두 레코드입니다. 대소문자가 다르거나, 끝에 공백이 붙었거나, 전화번호가 갱신된 경우가 많습니다. 잘못된 쪽을 지우면 데이터를 잃기 때문에 작업 순서가 중요합니다. 두 행을 같은 것으로 볼 기준을 정하고, 무엇이 다른지 살펴본 다음, 그때 지웁니다.
1단계: 키 정하기
중복은 같은 대상을 나타내는 두 행입니다. 어떤 열로 그것을 판단할지는 파일의 속성이 아니라 데이터에 대한 결정입니다.
- 행 전체 중복은 모든 필드가 같은 경우로, 거의 항상 실수입니다. 이중 가져오기, 다시 실행된 작업, 두 번 실행된 추가 작업이 원인입니다. 안전하게 지워도 됩니다.
- 키 중복은
email이나order_id는 같지만 나머지가 전부 다른 경우로, 데이터 문제입니다. 한쪽이 더 최신이거나 더 완전할 가능성이 크고, 어느 쪽을 남길지 직접 골라야 합니다.
이 둘을 거꾸로 하는 것이 레코드를 잃는 전형적인 방법입니다. 고객 목록을 name 기준으로 중복 제거하면, 이름이 같은 서로 다른 두 사람을 아무렇지 않게 지워 버립니다.
2단계: 지우기 전에 세기
중복 제거는 확인 없이 실행하지 마세요. 지우려는 것이 무엇인지 먼저 봅니다.
duckdb -c "SELECT email, count(*) n FROM read_csv('input.csv') GROUP BY email HAVING n > 1 ORDER BY n DESC LIMIT 20"
키가 맞는지는 숫자 두 개로 알 수 있습니다. 중복이 몇 건뿐이라면 진짜 이중 입력일 가능성이 큽니다. 파일의 3분의 1이 "중복"이라면 키가 잘못된 것입니다. 원래 고유하지 않았던 값으로 비교하고 있는 것입니다.
그다음 특정 사례 하나를 전체 열로 확인합니다. 많은 사람이 건너뛰는 단계입니다.
duckdb -c "SELECT * FROM read_csv('input.csv') WHERE email = '[email protected]'"
두 행이 신경 쓰던 바로 그 열에서 다르다는 사실을 여기서 알게 됩니다.
3단계: 남길 행 고르기
행이 서로 다르다는 것을 알면 "중복 제거"는 "올바른 행 남기기"가 됩니다. 규칙은 세 가지이고, 맞는 경우가 많은 순서대로 나열했습니다.
가장 최근 것을 남깁니다. 타임스탬프가 있다면 보통 이것이 답입니다.
duckdb -c "COPY (SELECT * FROM (SELECT *, row_number() OVER (PARTITION BY email ORDER BY updated_at DESC) rn FROM read_csv('input.csv')) WHERE rn = 1) TO 'deduped.csv' (HEADER)"
가장 완전한 것을 남깁니다. 타임스탬프가 없다면 비어 있지 않은 필드의 개수로 정렬하는 것이 괜찮은 대용입니다. 더 많이 채워진 레코드가 보통 나중 것이기 때문입니다. (col_a IS NOT NULL)::INT + (col_b IS NOT NULL)::INT + … 로 정렬하고 맨 위를 가져옵니다.
둘을 합칩니다. 각 행에 상대에게 없는 정보가 있을 때도 있습니다. 흔한 일이고 지름길은 없습니다. 열마다 coalesce를 하는 작업입니다. 건수가 적다면 큰 데이터를 어설프게 자동화하는 것보다 손으로 하는 편이 낫습니다.
어느 쪽을 고르든, 지우기 전에 버릴 행을 두 번째 파일에 저장해 두세요. 되돌릴 수 없는 중복 제거는 앞으로 2주 안에 반드시 후회하게 됩니다.
비슷한 중복, 사실상 대부분
정확히 일치하는 것만 찾으면 실제 중복의 대다수를 놓칩니다. 먼저 정규화하고, 그다음 비교하고, 원래 값은 그대로 보관하세요.
duckdb -c "SELECT lower(trim(email)) k, count(*) n FROM read_csv('input.csv') GROUP BY k HAVING n > 1"
효과가 큰 순서대로 적용할 정규화는 다음과 같습니다.
- 대소문자.
[email protected]과[email protected]은 같은 메일함입니다. - 공백. 복사해 붙여넣을 때 생기는 끝 공백은 눈에 보이지 않고 매우 흔합니다.
trim()은 앞뒤 공백을 지우고, 웹 폼에서 들어오는 줄바꿈 없는 공백(non-breaking space)은replace로 처리합니다. - 식별자 안의 기호. 전화번호
+44 20 7946 0958,02079460958,(020) 7946 0958은 하나의 번호입니다. 비교하기 전에 숫자가 아닌 것을 모두 지웁니다. - Gmail의 점과 플러스 주소.
[email protected]은[email protected]과 같은 받은편지함에 도착합니다. 이것을 중복으로 볼지는 사람을 중복 제거하는지, 계정을 중복 제거하는지에 따라 달라집니다.
자동으로 하면 안 되는 것은 이름의 유사 일치(fuzzy matching)입니다. "Jon Smith"와 "John Smith"는 같은 사람일 수도, 동료 두 명일 수도 있습니다. 한 파일에 맞는 유사도 기준은 다음 파일에서는 틀립니다. 후보 쌍을 뽑고, 직접 검토한 뒤에 처리하세요.
SQL 편집기와 시각적 필터 편집기가 이 작업의 두 축입니다. GROUP BY로 중복 키를 찾고, 필터로 그 키를 공유하는 모든 행을 읽을 수 있으며, 먼저 데이터베이스에 불러올 필요가 없습니다.
기능스프레드시트에서 하기
Excel과 Google 스프레드시트에는 모두 중복 항목 제거(Remove Duplicates) 명령이 있는데, 쓰기 전에 알아야 할 특성이 하나 있습니다. 첫 번째로 나온 행을 남기고 나머지를 지우며, 어느 것이 첫 번째인지 정할 방법이 없습니다. 시트가 우연히 어떤 순서였느냐에 따라 어느 레코드가 살아남을지 결정됩니다.
그러니 실행하기 전에 의도적으로 정렬하세요. 타임스탬프 내림차순으로 정렬하면 "첫 번째 유지"가 "최신 유지"가 되고, 보통 그것이 원래 의도입니다.
스프레드시트의 다른 방법인 COUNTIF 보조 열은 더 느리지만 데이터를 파괴하지 않고, 중복을 지우는 대신 보여 줍니다. 큰 파일에서는 Excel이 멈추는 원인이 되기도 합니다. 50만 행에 걸친 COUNTIF는 계산량이 제곱으로 늘기 때문입니다.
휴대폰에서
휴대폰에서 현실적으로 할 수 있는 것은 완전한 중복 제거가 아닙니다. 의심되는 열로 정렬하고 스크롤하면서 반복되는 행이 나란히 있는 것을 보는 정도입니다. 파일에 중복 문제가 있는지 알아보는 가장 빠른 방법입니다.
중복이 보이면, 키 하나로 필터링해서 그 키를 공유하는 몇 행을 읽어 보세요. 정말 중요한 질문에 답이 나옵니다. 이것은 같은 레코드인가, 우연히 필드 하나를 공유하는 두 레코드인가?
Excel처럼 원하는 열로 정렬한 뒤, 필터를 적용한 채로 안전하게 행을 삭제할 수 있습니다. 숨겨진 행은 보호되므로 삭제가 필터 밖의 데이터를 건드리지 않습니다. 실행 취소가 모든 것을 되돌려 주기 때문에 휴대폰에서도 안심하고 이 작업을 할 수 있습니다.
기능검증하기
숫자 세 개가 서로 맞아야 합니다.
wc -l input.csv deduped.csv
duckdb -c "SELECT count(*) rows, count(DISTINCT email) keys FROM read_csv('deduped.csv')"
이제 rows와 keys가 같아야 합니다. 같지 않다면 중복 제거가 모든 그룹에 적용되지 않은 것입니다. 또 input − deduped는 2단계에서 센, 지우려고 했던 행의 수와 같아야 합니다. 그보다 많이 지워졌다면 키가 생각보다 넓거나 좁았다는 뜻이며, 따로 저장해 둔 버린 행 파일이 이제 그날 오후를 구해 줍니다.