Before you average a CSV: check missing values and duplicate rows with pandas
Your Python script has printed an average. The arithmetic can be correct even when the rows going into it answer the wrong question. A repeated record, an unfinished item or an unknown value can change what that number means.
A CSV is a text file that stores a table, usually with commas separating its columns. pandas is a Python library for working with tables. Its DataFrame is the table object you will use below. Before calculating a mean, the sum of values divided by their count, check what counts as one observation.
This exercise uses seven rows and a short script. You need basic Python and pandas installed; the official pandas installation guide explains the setup. Put the CSV and script in the same folder and run the code from that folder.
A useful next step after learning to analyse data
Eva J Patel’s 26 October 2026 freeCodeCamp tutorial on building a pandas data analyser introduces loading, inspecting and summarising a dataset. Here, the focus is narrower: deciding which rows support one average, then stating its coverage. This is a practice exercise inspired by recent coverage, not a new pandas feature.
Start with a small file you can inspect
Illustrative example: a developer wants the mean recorded duration of completed build runs in one export. A build run is one execution of a software build job. The export should have one row per run. Its specification says durations are rounded down to whole seconds, so zero is a valid recorded value.
| Run | State | Seconds |
|---|---|---|
| R101 | complete | 30 |
| R102 | complete | 0 |
| R102 | complete | 0 |
| R103 | complete | (blank) |
| R104 | running | 25 |
| R105 | complete | tbc |
| R106 | complete | 90 |
Save this as builds.csv. The export contains a repeated row, an unfinished run, one blank and the text tbc. Do not assume these all mean the same thing.
run_id,state,seconds
R101,complete,30
R102,complete,0
R102,complete,0
R103,complete,
R104,running,25
R105,complete,tbc
R106,complete,90Check the records before calculating
1. Keep the original values and inspect repetition
Purpose: Find records that need an explanation before removing anything.
import pandas as pd
df = pd.read_csv('builds.csv', dtype='string', keep_default_na=False)
print(df[df.duplicated('run_id', keep=False)])Save the Python snippets in order as check_builds.py. The import gives pandas the short name pd. Reading columns as strings preserves the supplied text. With keep_default_na=False and no custom missing-value list, the blank remains an empty string at this stage.
The duplicate check prints both R102 rows. pandas documents how duplicated works: specifying run_id checks that column, while keep=False marks every row in each repeated group.
A repeated identifier is a question, not a deletion instruction. In another export, it could represent separate events for one run. Inspect the fields and confirm what a row represents. In this example, the export owner confirms that the identical R102 record was accidentally included twice.
2. Separate the records and the unknown values
Purpose: Make selection and conversion decisions visible.
# Only after confirming R102 is a repeated export row.
selected = df.drop_duplicates().copy()
closed = selected[selected['state'] == 'complete'].copy()
raw = closed['seconds'].str.strip()
closed['seconds_number'] = pd.to_numeric(raw, errors='coerce')
print('Blank:', raw.eq('').sum())
print('Invalid:', (raw.ne('') & closed['seconds_number'].isna()).sum())drop_duplicates() removes identical rows here, keeping one copy. It is appropriate only after the example’s confirmation. The original df is retained. The next line selects completed runs, excluding R104 because its elapsed time is not a completed duration.
The raw values are kept alongside the converted numeric column. to_numeric with errors=”coerce” converts unparseable entries to missing numeric values. It does not tell you why an entry is unusable or repair it.
The two printed counts are Blank: 1 and Invalid: 1. R103 has no supplied duration; R105 contains tbc. Both lack a usable number, but keeping their original text preserves that distinction. Neither becomes zero. This small example does not validate every possible duration or status; real exports need rules for unexpected values too.
3. Report the mean with its coverage
Purpose: Explain exactly which records support the result.
measured = closed['seconds_number'].dropna()
print('Mean:', measured.mean())
print('Measured:', len(measured), 'of', len(closed))The output is Mean: 40.0 and Measured: 3 of 5. The three included durations are 30, 0 and 90 seconds: their sum is 120, divided by three. Five unique completed runs remain after removing the confirmed repeated row, but two have no usable duration.
Follow the rows into the calculation
- 7exported rowsThe starting file.
- 6unique runsOne confirmed repeat removed.
- 5completed runsThe running job is excluded.
- 3measured durationsTwo completed runs have unknown durations.
| Run | Seconds | In the mean? |
|---|---|---|
| R101 | 30 | Included |
| R102 | 0 | Included |
| R103 | (blank) | Unknown |
| R105 | tbc | Unknown |
| R106 | 90 | Included |
(30 + 0 + 90) ÷ 3 = 40 seconds
Measured: 3 of 5 completed runs. R102’s zero is included once. R103 and R105 remain in the completed-run count, but cannot contribute a numeric duration.
The explicit dropna() makes the measured subset visible. The pandas mean documentation also states that missing values are excluded by default. Getting a number back does not establish that every relevant record was measured.
Write a sentence the data can support
A suitable handover is: “The mean recorded duration is 40 seconds across three of five unique completed runs in this export. One blank and one unparseable duration remain unresolved. One confirmed repeated export row and one running job were excluded.”
This is a description of the observed subset. It does not establish the average for all five completed runs, show a trend or explain why builds take that long. No time period is provided in this practice file, so do not attach a reporting window or compare it with another period.
If the raw converted column were averaged immediately, the result would be 29 seconds. That calculation includes the repeated zero and the running job while ignoring the unusable entries. The difference comes from the selection rules, not from a better averaging formula.
Keep a short calculation note
Try one change: replace R106’s 90 with a blank and rerun the script. Expect a mean of 15 seconds from two of five completed runs. The lower number reflects less information; it does not demonstrate faster builds. Before sharing an average from your own export, make the same connection between the number, the selected rows and what remains unknown.
You May Also Like
Mastering Agile Daily Standup Meetings: A Concise Guide with Practical Examples
Hiding a button is not access control: write a permission rule you can test
