Manage a reference list in CSV and check duplicate or missing DOIs

01Decide what the DOI check can and cannot tell you

This workflow checks two practical problems: a reference has no DOI value, or two rows contain the same DOI after simple text normalization. It does not contact a DOI registry and therefore does not prove that a DOI exists, resolves correctly, or belongs to the cited article.

The script removes common textual prefixes such as https://doi.org/ and doi:, trims whitespace, and converts the remaining DOI text to lowercase for comparison. The original DOI text is preserved in the output beside the normalized value.

02Create a synthetic reference list

The following reference list is synthetic and was written specifically for this article. The DOI-like strings are demonstration data and are not presented as real or resolvable publications. Save the file as references.csv.

ref_id,title,year,doi
R001,Synthetic bracket study,2024,10.0000/demo.alpha
R002,Synthetic vibration study,2025,10.0000/demo.beta
R003,Synthetic bracket study copy,2024,https://doi.org/10.0000/demo.alpha
R004,Synthetic fatigue note,2023,
R005,Synthetic surrogate study,2026,doi:10.0000/DEMO.GAMMA
R006,Synthetic optimization study,2026,10.0000/demo.delta

There are 6 reference rows. Five contain DOI text and one, R004, is missing a DOI. R001 and R003 use different textual forms but normalize to the same value. R005 demonstrates that capitalization and a doi: prefix can also be normalized for comparison.

03Work out the expected issues by hand

The expected counts are 6 references, 5 rows with DOI text, 1 row with a missing DOI, and 1 duplicate DOI group containing 2 rows. After normalization, the five nonblank DOI entries contain 4 distinct DOI values.

04Normalize DOI text and write review files

Save the following script as reference_list_check.py. It writes a normalized copy of the entire reference list and a separate issues.csv containing only rows that need review. The original references.csv is read only.

import csv
from pathlib import Path

SOURCE = Path("references.csv")
OUTPUT_DIR = Path("outputs") / "reference_list_result"
NORMALIZED = OUTPUT_DIR / "normalized_references.csv"
ISSUES = OUTPUT_DIR / "issues.csv"
REQUIRED = {"ref_id", "title", "year", "doi"}


def normalize_doi(value: str) -> str:
    text = value.strip()
    lowered = text.lower()
    prefixes = (
        "https://doi.org/",
        "http://doi.org/",
        "doi:",
    )
    for prefix in prefixes:
        if lowered.startswith(prefix):
            text = text[len(prefix):].strip()
            break
    return text.lower()


def main() -> None:
    if not SOURCE.is_file():
        raise FileNotFoundError(f"Source CSV not found: {SOURCE}")
    if OUTPUT_DIR.exists():
        raise FileExistsError(f"Output folder already exists: {OUTPUT_DIR}")

    rows = []
    with SOURCE.open("r", encoding="utf-8-sig", newline="") as stream:
        reader = csv.DictReader(stream)
        if reader.fieldnames is None or not REQUIRED.issubset(reader.fieldnames):
            raise ValueError("CSV is missing a required column.")

        seen_ids = set()
        for row in reader:
            ref_id = row["ref_id"].strip()
            if not ref_id:
                raise ValueError("ref_id must not be blank.")
            if ref_id in seen_ids:
                raise ValueError(f"Duplicate ref_id: {ref_id}")
            seen_ids.add(ref_id)

            rows.append({
                "ref_id": ref_id,
                "title": row["title"],
                "year": row["year"],
                "doi": row["doi"],
                "normalized_doi": normalize_doi(row["doi"]),
            })

    by_doi = {}
    for row in rows:
        doi = row["normalized_doi"]
        if doi:
            by_doi.setdefault(doi, []).append(row["ref_id"])

    duplicate_dois = {
        doi: ids for doi, ids in by_doi.items() if len(ids) > 1
    }

    issue_rows = []
    for row in rows:
        doi = row["normalized_doi"]
        if not doi:
            issue_rows.append({
                "ref_id": row["ref_id"],
                "issue": "MISSING_DOI",
                "normalized_doi": "",
                "related_ref_ids": "",
            })
        elif doi in duplicate_dois:
            issue_rows.append({
                "ref_id": row["ref_id"],
                "issue": "DUPLICATE_DOI",
                "normalized_doi": doi,
                "related_ref_ids": ";".join(duplicate_dois[doi]),
            })

    OUTPUT_DIR.parent.mkdir(parents=True, exist_ok=True)
    OUTPUT_DIR.mkdir()

    normalized_fields = ["ref_id", "title", "year", "doi", "normalized_doi"]
    with NORMALIZED.open("x", encoding="utf-8", newline="") as stream:
        writer = csv.DictWriter(stream, fieldnames=normalized_fields)
        writer.writeheader()
        writer.writerows(rows)

    issue_fields = ["ref_id", "issue", "normalized_doi", "related_ref_ids"]
    with ISSUES.open("x", encoding="utf-8", newline="") as stream:
        writer = csv.DictWriter(stream, fieldnames=issue_fields)
        writer.writeheader()
        writer.writerows(issue_rows)

    missing_count = sum(row["issue"] == "MISSING_DOI" for row in issue_rows)
    print(f"References checked: {len(rows)}.")
    print(f"Missing DOI rows: {missing_count}.")
    print(f"Duplicate DOI groups: {len(duplicate_dois)}.")
    print(f"Issue rows: {len(issue_rows)}.")
    print(f"Output folder: {OUTPUT_DIR.as_posix()}")


if __name__ == "__main__":
    main()

05Compare the expected review report

issues.csv should contain three data rows: R001 and R003 for the duplicated normalized DOI, plus R004 for the missing DOI.

ref_id,issue,normalized_doi,related_ref_ids
R001,DUPLICATE_DOI,10.0000/demo.alpha,R001;R003
R003,DUPLICATE_DOI,10.0000/demo.alpha,R001;R003
R004,MISSING_DOI,,

The expected console output below was derived by hand from the synthetic list and script. It is not a captured execution log.

References checked: 6.
Missing DOI rows: 1.
Duplicate DOI groups: 1.
Issue rows: 3.
Output folder: outputs/reference_list_result

06Review the flagged rows instead of deleting automatically

• Confirm that R001 and R003 normalize to the same DOI text.
• Confirm that R004 is retained in the normalized reference list even though its DOI is missing.
• Check the paper title, authors, year, and publication details before deciding whether duplicate-DOI rows should be merged.
• For missing DOI rows, verify whether the publication actually has a DOI before adding one.
• Run the script again without changing OUTPUT_DIR. It should stop with FileExistsError instead of replacing the previous review.

07Understand the limits of DOI-based deduplication

DOI matching is useful when DOI metadata is present, but a missing DOI does not mean a reference is invalid or incomplete. Some publications or document types may not have a DOI, and older records may require other identifiers or manual bibliographic checks.

This script performs text normalization only. It does not resolve DOI links, query Crossref or another registration agency, verify titles or authors, detect retractions, or determine whether two different DOI values refer to related versions of a work.

For a real research database, keep fields such as authors, title, year, journal, volume, pages, DOI, and another local reference ID. Treat automatic duplicate detection as a review aid rather than an instruction to delete bibliographic records.

Execution and verification record

ChatGPT Python tool · Python 3.x · standard library: csv, pathlib

• Execution completed successfully for 6 references.
• Confirmed 1 missing DOI row: R004.
• Confirmed 1 duplicate DOI group: normalized DOI 10.0000/demo.alpha shared by R001 and R003.
• Confirmed issues.csv contains exactly 3 data rows: R001 DUPLICATE_DOI, R003 DUPLICATE_DOI, and R004 MISSING_DOI.
• Confirmed R005 normalized from doi:10.0000/DEMO.GAMMA to 10.0000/demo.gamma.
• Confirmed there are 4 distinct nonblank normalized DOI values among the 5 DOI-containing rows.
• Console output matched the article exactly: 6 references, 1 missing DOI row, 1 duplicate DOI group, 3 issue rows, and the expected output folder.
• Generated issues.csv matched the article's expected CSV exactly.
• Second execution raised FileExistsError for outputs/reference_list_result, confirming overwrite protection.
• SHA-256 comparison confirmed references.csv was unchanged.

References

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

• Official Python 3.12 documentation · csv ↗ (New tab)
• Official Python 3.12 documentation · pathlib ↗ (New tab)
• Crossref documentation · Display guidelines ↗ (New tab)