Check response counts, missing answers, and duplicates before tallying a survey CSV

01Read the ten responses yourself first

If you report the number of rows in a survey file as the number of respondents, duplicate submissions may be included. The percentage for the same option also changes depending on whether blank answers are in the denominator. This example uses one question, “Would you use it again?”, to practice the rules to set before tallying. Before running anything, look for duplicates and blank answers in the original.

1. Open responses.csv in a text editor or a program that reads CSV. The first line has two columns: respondent_id,answer.
2. Excluding the header, count that there are 10 data rows. Check that R005 appears twice and that the answers for R004 and R009 are blank.
3. Run the command below in a terminal in the folder containing example.py.
4. Open summary.json, choices.csv, and audit_rows.csv created in outputs, in that order.

python example.py

The code finds responses.csv relative to where example.py is. Output is written to a separate outputs folder, and the input is not changed. Even if you run it with the script path from another working folder, it reads the same example input.

02Distinguish the duplicate rule and the two denominators

R005 submitted both ‘예’ (Yes) and ‘아니오’ (No). This example does not arbitrarily pick the first or last value; it excludes every row of any ID that appears more than once, because there is no information to judge which response is valid. This policy is stated in the code and in duplicate_policy in the summary file.

The completion rate is 75.00%, the share of 6 valid answers among the 8 remaining unique respondents. The percentage for each option uses the 6 valid answers as the denominator. The 9 unique IDs are not used as the denominator because all of R005's responses were excluded from the analysis.

03Full code with checks and tallying

read_rows checks the columns, IDs, and options. summarize first counts how often each ID appears, then tags each row with one status: duplicate_id, blank_answer, or valid. Because duplicate rows are judged first, the same row is not counted twice as both a duplicate exclusion and a blank-answer exclusion.

"""설문 합성 CSV의 중복 ID와 무응답을 점검한 뒤 유효 응답을 집계합니다."""

import csv
import json
import re
import sys
from collections import Counter
from decimal import Decimal, ROUND_HALF_UP
from pathlib import Path

BASE = Path(__file__).resolve().parent
INPUT = BASE / "responses.csv"
OUTPUT = BASE / "outputs"
CHOICES = ["예", "아니오", "잘 모르겠음"]


def percent(numerator, denominator):
    # 분모가 0이면 0%로 오해하지 않도록 계산 불가를 None으로 남깁니다.
    if denominator == 0:
        return None
    value = Decimal(numerator) * Decimal(100) / Decimal(denominator)
    return str(value.quantize(Decimal("0.01"), rounding=ROUND_HALF_UP))


def read_rows():
    if INPUT.is_symlink() or not INPUT.is_file():
        raise ValueError("responses.csv는 일반 파일이어야 합니다.")
    rows = []
    with INPUT.open("r", encoding="utf-8-sig", newline="") as stream:
        reader = csv.DictReader(stream, strict=True)
        if reader.fieldnames != ["respondent_id", "answer"]:
            raise ValueError("열 이름과 순서는 respondent_id,answer여야 합니다.")
        for number, row in enumerate(reader, start=1):
            if None in row or any(value is None for value in row.values()):
                raise ValueError(f"데이터 {number}행: 열 수를 확인하세요.")
            respondent_id, answer = row["respondent_id"].strip(), row["answer"].strip()
            if not re.fullmatch(r"R[0-9]{3}", respondent_id):
                raise ValueError(f"데이터 {number}행: ID는 R001처럼 R과 숫자 세 자리여야 합니다.")
            if answer and answer not in CHOICES:
                raise ValueError(f"데이터 {number}행: 알 수 없는 응답 {answer!r}")
            rows.append({"data_row": number, "respondent_id": respondent_id, "answer": answer})
    return rows


def summarize(rows):
    frequencies = Counter(row["respondent_id"] for row in rows)
    duplicate_ids = sorted(key for key, count in frequencies.items() if count > 1)
    duplicates = set(duplicate_ids)
    counts = Counter()
    audit, blank_after_duplicates = [], 0
    for row in rows:
        # 중복 그룹에서는 첫 응답이나 마지막 응답을 임의로 고르지 않습니다.
        if row["respondent_id"] in duplicates:
            status = "duplicate_id"
        elif not row["answer"]:
            status = "blank_answer"
            blank_after_duplicates += 1
        else:
            status = "valid"
            counts[row["answer"]] += 1
        audit.append({**row, "status": status})

    duplicate_rows = sum(frequencies[key] for key in duplicate_ids)
    eligible = len(rows) - duplicate_rows  # 여기에는 빈 답변을 한 고유 ID도 포함됩니다.
    valid = sum(counts.values())
    summary = {
        "question": "다시 이용할 의향이 있나요?",
        "duplicate_policy": "exclude_all_rows_with_duplicate_id",
        "raw_rows": len(rows),
        "unique_ids": len(frequencies),
        "duplicate_ids": duplicate_ids,
        "duplicate_id_count": len(duplicate_ids),
        "excluded_duplicate_rows": duplicate_rows,
        "raw_blank_answer_rows": sum(not row["answer"] for row in rows),
        "eligible_unique_respondents": eligible,
        "blank_answer_rows_after_duplicates": blank_after_duplicates,
        "valid_answer_rows": valid,
        "completion_rate_percent": percent(valid, eligible),
        "choices": [
            {"answer": answer, "count": counts[answer], "denominator": valid,
             "percent": percent(counts[answer], valid)} for answer in CHOICES
        ],
    }
    return summary, audit


def main():
    if OUTPUT.exists() or OUTPUT.is_symlink():
        raise FileExistsError("outputs가 이미 있습니다. 기존 결과를 옮긴 뒤 실행하세요.")
    summary, audit = summarize(read_rows())  # 입력 검증과 집계를 마친 뒤 출력합니다.
    OUTPUT.mkdir()
    with (OUTPUT / "summary.json").open("x", encoding="utf-8") as stream:
        json.dump(summary, stream, ensure_ascii=False, indent=2)
        stream.write("\n")
    with (OUTPUT / "choices.csv").open("x", encoding="utf-8-sig", newline="") as stream:
        writer = csv.DictWriter(stream, fieldnames=["answer", "count", "denominator", "percent"])
        writer.writeheader()
        writer.writerows(summary["choices"])
    with (OUTPUT / "audit_rows.csv").open("x", encoding="utf-8-sig", newline="") as stream:
        writer = csv.DictWriter(stream, fieldnames=["data_row", "respondent_id", "answer", "status"])
        writer.writeheader()
        writer.writerows(audit)
    print(f"원시 {summary['raw_rows']}행 / 고유 ID {summary['unique_ids']}개")
    print(f"중복 제외 {summary['excluded_duplicate_rows']}행 / 남은 고유 응답자 {summary['eligible_unique_respondents']}명")
    print(f"빈 응답 {summary['blank_answer_rows_after_duplicates']}행 / 유효 응답 {summary['valid_answer_rows']}행")
    if not summary["valid_answer_rows"]:
        print("유효 응답이 없어 선택지 비율을 계산하지 않았습니다.")
    print(f"완료: {OUTPUT}")
    return 0


if __name__ == "__main__":
    try:
        raise SystemExit(main())
    except (OSError, ValueError, csv.Error) as error:
        print(f"중지: {error}", file=sys.stderr)
        raise SystemExit(2)

It counts occurrences per ID and per option with Counter, and rounds percentages to two decimal places with Decimal. Leading and trailing spaces in IDs and answers are removed. Unknown options are not silently dropped; the script stops. The output folder is created only after all input checks are finished.

04Check counts and percentages per option

‘예’ (Yes) is the three responses from R001, R003, and R007. R005's ‘예’ was excluded, so it is not four. ‘아니오’ (No) is two responses from R002 and R008, and ‘잘 모르겠음’ (Not sure) is one response from R006. The sum of the three option counts, 6, must equal valid_answer_rows.

choices.csv provides four columns: answer, count, denominator, and percent. percent records a numeric string such as 50.00 without the % sign. When moving it into a table, remember that it is already a percentage multiplied by 100. Be careful not to apply a spreadsheet's percentage format directly and turn it into 5000%.

05Trace exclusion reasons back to the original rows

1. In audit_rows.csv, check that data_row 5 and 6 are duplicate_id. Both should be R005.
2. Check that data_row 4 and 10 are blank_answer. The IDs are R004 and R009.
3. Check that the remaining six rows are valid, and that the count matches summary.json.
4. Check that the original responses.csv is unchanged. Running the same command again should stop while keeping the existing outputs.

data_row is the order of data read, excluding the header. Physically blank lines are skipped when reading the CSV, so it does not always match the line numbers in a text editor. To represent no answer, use a row with an ID and only the answer blank, such as R004, rather than a completely empty line.

06A small experiment where the denominator changes

When repeating the practice, move the first result under a different name or extract the ZIP into a new folder. Try running a copy where R004's blank answer is changed to ‘예’ (Yes). After removing duplicates there are still 8 respondents, and 7 valid responses. ‘예’ should be 4/7 = 57.14%, and the completion rate 7/8 = 87.50%.

You can also consider making every unique respondent's answer blank. If respondents remain but there are 0 valid answers, the completion rate is 0.00%. On the other hand, the denominator for option percentages is 0, so they are not calculated. JSON records null and the CSV records a blank to distinguish them from 0%.

07Input errors to fix before tallying

If there is an input error, the script stops before creating the output folder. If you delete the row first to avoid the error, the evidence for the exclusion is lost. Keep the original, record the changes and rules in a copy, and run again. Even if an unknown answer is in a duplicate row, the input check stops first.

08What to decide when expanding to a real survey

In a real survey, first decide what a duplicate submission means. The handling policy depends on whether a later response is a revision, several people on the same device, or a wrongly assigned ID. Do not automatically apply this example's “exclude all duplicate IDs” to every survey; set a basis that fits the survey design. The same ID alone also does not prove it is actually the same person.

This code also handles only single-choice answers to one question. It does not include multiple choice, questions skipped by conditions, weights, classifying free-text answers, sample representativeness, or statistical significance. Distinguish the step of drawing real research conclusions from the step of checking the CSV structure, and state the number analyzed and the exclusion rules together in the report.

All data is read into memory, so performance testing for large surveys is a separate task. If a disk error occurs while writing results, partial output may remain, so check the completion message and the three files. No personal information or connection to a real survey service was used to verify the example.

Execution and verification record

2026-09-19 · Windows 11 · CPython 3.12.14 · standard library only · run on a temporary copy

• Passed 14 independent tests: confirmed 10 raw rows, 9 unique IDs, 2 duplicate rows excluded, 8 remaining, 2 blank, 6 valid
• Confirmed option percentages of 50.00%, 33.33%, and 16.67%, and a completion rate of 75.00%
• Confirmed zero-denominator handling for empty data, all-blank answers, and all-duplicate IDs
• Confirmed that blank answers in a duplicate group are not excluded twice, and whitespace normalization
• Confirmed rejection of input errors, preservation of existing results, and identical SHA-256 of the original before and after running

Site-wide writing and verification principles

References

The explanations and examples were written for this site. See the official sources below for the related behavior and concepts.

• Official Python documentation · csv and DictReader ↗ (New tab)
• Python official documentation · Counting frequencies with collections.Counter ↗ (New tab)
• Python official documentation · Rounding with Decimal.quantize ↗ (New tab)