What you will build
A weekly support export arrives with extra spaces, inconsistent team names, repeated ticket IDs, and missing durations. Fixing the cells manually may solve today’s problem, but next week’s export will need the same attention.
This project turns those decisions into a repeatable Python script. You will produce a clean CSV, a separate review file, and a simple count that accounts for every input record. All seven example records are synthetic.
Prerequisites: Python 3, a text editor, and basic familiarity with variables and loops. The example was executed with Python 3.12.14. It needs no extra packages.
Decide what clean means
For this exercise, one row represents one ticket’s recorded work. The accepted columns are ticket_id, team, and minutes. We deliberately set these rules:
-
IDs are required and case-insensitive. Trim surrounding spaces and convert them to uppercase.
-
The only valid teams are Support and Sales. Normalize their spelling using an explicit lookup.
-
Minutes must be a whole number between zero and 1,440 inclusive.
-
Keep the first valid occurrence of an ID. Send subsequent valid occurrences to review, even when their values match.
These are project assumptions, not universal rules. A real ticket system might allow multiple time entries per ticket. In that case, ticket_id alone would be the wrong deduplication key. Confirm the meaning of a row before removing anything.
Run the complete example
Save the following as clean_csv.py in a new practice folder. Run python clean_csv.py from that folder. If your computer uses python3 or py as its Python command, substitute that command.
import csv
import io
from pathlib import Path
RAW = """ticket_id,team,minutes
T001 , Support ,30
t002,SALES,45
T001,support,30
T003,Support,
T004,Support,forty
T005,Sales,15
T006,Sales,-5
"""
FIELDS = ["ticket_id", "team", "minutes"]
TEAMS = {"support": "Support", "sales": "Sales"}
def clean_csv(text):
reader = csv.DictReader(io.StringIO(text))
if reader.fieldnames != FIELDS:
raise ValueError("Expected headers: ticket_id,team,minutes")
clean, review, seen = [], [], set()
for record, row in enumerate(reader, start=1):
reason = ""
if None in row or any(value is None for value in row.values()):
reason = "wrong number of fields"
else:
ticket = row["ticket_id"].strip().upper()
team = TEAMS.get(row["team"].strip().lower())
try:
minutes = int(row["minutes"].strip())
except ValueError:
minutes = None
if not ticket or team is None:
reason = "missing ID or unknown team"
elif minutes is None or not 0 <= minutes <= 1440:
reason = "minutes must be an integer from 0 to 1440"
elif ticket in seen:
reason = "duplicate ID; review before merging"
if reason:
review.append({"record": record, "reason": reason, "raw": repr(row)})
else:
seen.add(ticket)
clean.append(dict(zip(FIELDS, [ticket, team, minutes])))
return clean, review
def main():
clean, review = clean_csv(RAW)
folder = Path("csv_demo")
folder.mkdir(exist_ok=True)
for name, rows, fields in [
("clean.csv", clean, FIELDS),
("review.csv", review, ["record", "reason", "raw"]),
]:
with (folder / name).open("w", encoding="utf-8", newline="") as file:
writer = csv.DictWriter(file, fieldnames=fields)
writer.writeheader()
writer.writerows(rows)
print(f"Accepted: {len(clean)}; review: {len(review)}")
print(f"Accepted minutes: {sum(row['minutes'] for row in clean)}")
print((folder / "clean.csv").read_text(encoding="utf-8"), end="")
if __name__ == "__main__":
main()
The script creates csv_demo/clean.csv and csv_demo/review.csv in the current folder. Running it again replaces those two demonstration outputs. The original example remains in the script’s RAW string.
Understand the important decisions
DictReader associates each value with its column name. It handles CSV quoting, so use it rather than splitting every line at commas. DictWriter creates the output headers and rows. The explicit encoding and newline setting make file handling predictable; the official CSV documentation explains these interfaces and newline requirements.
The header check stops the run when the expected structure changes. Rows with too many or too few fields go to review before value conversion begins.
The seen set records IDs only after a row passes validation. If the first occurrence is invalid, a later valid occurrence can still be accepted. Review records retain their original parsed values and a reason, giving you something concrete to investigate. The record number counts data records, not physical lines; quoted CSV fields can span lines.
Path creates the demonstration directory and locates the two files. These operations are covered in Python’s pathlib documentation.
Check the result
You should see:
Accepted: 3; review: 4
Accepted minutes: 90
ticket_id,team,minutes
T001,Support,30
T002,Sales,45
T005,Sales,15
There are seven input records: three accepted and four needing review. The repeated T001, missing duration, word “forty,” and negative duration account for the four review entries. The accepted minutes total 90. That is a subtotal of accepted data; it is not proof that the full export’s work has been reconciled.
Open review.csv as text and inspect the reasons. The script makes no attempt to guess a missing duration or combine conflicting duplicates. Investigate with the data owner.
Know the limits and try one change
This is a small, well-formed CSV exercise. It assumes comma separators and stores results in memory. It does not repair damaged quoting, resolve different encodings, validate every possible ID format, or make arbitrary files safe to open in spreadsheet software. Keep real source exports unchanged and adapt the rules before using the script at work.
Exercise: append T003,Support,20 as another line inside RAW. T003 already appears earlier with a missing duration. Before running the script, decide whether the later record should be accepted or rejected as a duplicate. Explain when an ID enters seen. Then run the script and inspect both output files.
Answer: The later T003 is accepted because the earlier invalid record never entered seen. There are four accepted records, four review records, and 110 accepted minutes. The original missing-duration record remains in review. If seen.add(ticket) ran before validation, the later valid record could be rejected incorrectly. Keeping the first valid row is a deliberate rule, not the same as keeping the first row.