Python Tutorial / Clean and Analyze Tabular Data with pandas
pandas represents tabular data as a DataFrame. Separate reading, validation, aggregation and saving, and report missing or invalid values so readers can interpret what the final totals include.
Choose column types before aggregation
A DataFrame is a table-like structure and a Series represents one column. Inferred CSV types may differ from your data contract. Read identifiers as strings when leading zeros matter, then convert only numeric columns explicitly.
Install pandas in the project environment:
python -m pip install pandas
pandas is a third-party package. Choose a release compatible with your Python and operating system.
For the relevant APIs and platform requirements, see Clean and Analyze Tabular Data with pandas documentation.
Run a complete validation and summary
Save the complete code below as pandas_demo.py in a separate practice folder, using UTF-8. Run python pandas_demo.py in a terminal with your project virtual environment selected. Outside a virtual environment on macOS or Ubuntu, use python3 when that is your interpreter command.
from io import StringIO
import pandas as pd
raw = "id,category,amount\ne1,food,3500\ne2,food,8000\ne3,transport,2500\ne4,food,bad\n"
df = pd.read_csv(StringIO(raw), dtype="string", keep_default_na=False)
amounts = pd.to_numeric(df["amount"], errors="coerce")
valid = amounts.notna() & (amounts >= 0) & (amounts % 1 == 0)
clean = df.loc[valid].copy()
clean["amount"] = amounts.loc[valid].astype("int64")
report = clean.groupby("category", as_index=False, sort=True)["amount"].sum()
print("accepted:", len(clean), "rejected:", int((~valid).sum()))
for row in report.itertuples(index=False):
print(row.category, row.amount)
Expected output:
accepted: 3 rejected: 1 food 11500 transport 2500
Interpret coercion, masks and copies
errors="coerce" marks conversion failures as missing values. That is a way to identify rejected data, not permission to ignore it. Inspect the valid mask and rejected count. The example uses loc followed by copy before assigning a converted column, keeping the selected table independent for that operation.
The small amounts fit in int64. Very large integers or exact decimal money require separate checks on conversion precision and range. Add missing, blank and duplicate-ID validation according to the actual data contract; the numeric mask alone does not establish full record validity.
Save and reconcile the report
After creating report, append this line at the end of the file:
report.to_csv("category-report.csv", index=False, encoding="utf-8")
Use a new output path because to_csv replaces an existing file at that name. index=False omits the DataFrame index column. Check that accepted input and report totals both equal 14000.
- Replace bad with 0 and compare accepted and rejected counts.
- Replace it with -1 and confirm rejection.
- Restore the original input and reconcile accepted plus rejected counts with all source rows.
The standard-library CSV project provides an explicit row-by-row alternative when pandas is unnecessary.
