Pandas Loading, Cleaning and Summary

A Pandas DataFrame is a labelled two-dimensional table; a selected column is commonly a Series. CSV loading supplies column names and inferred types, but inference is not proof that data satisfy the scenario. Inspect shape, column names, dtypes and missing counts. df.info() provides a structural summary, while describe() gives summaries for suitable columns. A mean over dirty mixed text is not an adequate first step.

This exercise contains sample identifiers, site labels and numeric readings. Its contract requires a non-missing sample ID, one numeric reading per record and no duplicated complete records. One row contains "bad", another is a repeated complete record and one has a missing reading. The identifier is an identifier rather than a quantity to average, even if it happens to contain digits.

pd.to_numeric(..., errors="coerce") converts valid numeric text and marks invalid entries as missing. It does not establish an acceptable physical range; apply a separate range rule if required. The example drops complete duplicate records and then removes rows without a sample ID or numeric value. dropna’s subset makes the required fields explicit. Assign or retain returned DataFrames because these operations do not generally modify the original object by default.

Dropping every row with a missing field is not a universal rule. A missing optional comment may be harmless, and imputing a mean can distort variation or invent an observation. Choose the policy from the task, record how many rows it removes and preserve the source. Sorting by sample identifier makes the export predictable; index=False prevents the incidental DataFrame index becoming an unwanted CSV column.

from io import StringIO
import pandas as pd

source = StringIO('sample,site,value\nS1,north,10\nS2,north,bad\nS3,south,20\nS3,south,20\nS4,south,\n')
raw = pd.read_csv(source)
clean = raw.copy()
clean['value'] = pd.to_numeric(clean['value'], errors='coerce')
clean = clean.drop_duplicates()
clean = clean.dropna(subset=['sample', 'value'])
clean = clean.sort_values('sample')
print(raw.shape, clean.shape)
print(int(clean['value'].count()))
print(float(clean['value'].mean()), float(clean['value'].median()))
print(clean['sample'].tolist())
exported = StringIO()
clean.to_csv(exported, index=False)
exported.seek(0)
restored = pd.read_csv(exported)
print(restored.columns.tolist())
print(restored['value'].tolist())

The clean values are 10 and 20, so their mean and median are both 15. That summary describes the two retained valid records. It does not prove that rejected records would have followed the same distribution or that the two sites have equal real-world conditions. Report retained and removed counts alongside the result so that a reader can judge how much evidence remains.

Series.count counts non-missing values, whereas len counts entries including missing ones. A Series can have more than one mode; do not assume mode always returns a single scalar. For a larger table, distinguish label-based .loc selection from position-based .iloc and state filtering conditions explicitly. Add independent expected results for conversion failures, duplicate records, missing required fields, an empty result and a file containing only a header.

Attempt the task before opening its worked response. Record each change of state, explain the rule used, and check the result against the original requirements. Code examples labelled Python 3 use that language’s conventions; pseudocode states its own assumptions.