Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problemsiTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
To clean a sales CSV with pandas, inspect how the file was read, make source-aware decisions about headers, missing values, amounts, dates, and duplicates, then validate the result before exporting it. The examples below take one file from import to a cleaned CSV. They use pandas 3.0.5/3.0.6 documentation as the version context; check the documentation for the version installed in your environment because behavior and available options can change.
1. Load the CSV and inspect what pandas read
Begin with a plain import and an inventory before changing data:
import pandas as pd
path = "sales.csv"
df = pd.read_csv(path)
original_rows = len(df)
print("Shape:", df.shape)
print(df.head())
print("Columns:", df.columns.tolist())
print("Types:n", df.dtypes)
print("Missing values:n", df.isna().sum())These checks reveal the row and column counts, sample values, inferred types, and values pandas already considers missing. They do not establish whether the data is correct: for example, an identifier with leading zeros may have been inferred as a number. Keep the original file unchanged so you can compare it with the cleaned output.
Recommended Free Tools
pandas 3.0.6 read_csv documentation describes options for input details such as sep for the delimiter, encoding, dtype, na_values, keep_default_na, thousands, decimal, parse_dates, and chunksize. Set these when you know the source requires them. For example, preserve a text identifier at import if its zeros or non-arithmetic meaning matter:
#1 Best Overall
df = pd.read_csv(path, dtype={"order_id": "string", "product_code": "string"})Use the actual names in your file. A different delimiter, character encoding, or number convention should be handled according to the export specification, not guessed from a few rows.
Do not silently skip malformed lines
The on_bad_lines option can make pandas raise an error, warn, or skip a line. Skipping may remove real sales records. Keep errors visible while diagnosing the file, and investigate a problematic row to determine whether it is corrupt or simply unusual before choosing a recovery rule. See the read_csv parameter reference.
2. Clean column names and text conservatively
First inspect the original header strings. Stripping whitespace and standardizing case can make later references easier, but normalization can also cause two distinct original names to collide. Check before applying it:
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
original_columns = df.columns.tolist()
normalized_columns = [str(col).strip().lower() for col in original_columns]
if len(set(normalized_columns)) != len(normalized_columns):
raise ValueError("Header normalization would create duplicate column names")
df.columns = normalized_columnsTrim surrounding whitespace from text values only when it is accidental. Do not strip or otherwise alter meaningful identifiers, names, or free-text notes without a defined rule. If a column such as an order number is an identifier rather than a quantity, preserve it as text.
for col in ["customer_name", "region"]:
if col in df.columns:
df[col] = df[col].str.strip()This assumes those columns are string-like. If a column has mixed types, inspect it and choose an explicit conversion policy rather than applying a text operation blindly.
3. Define missing values before filling or dropping rows
By default, pandas recognizes common missing-value markers; na_values can add source-specific markers, and keep_default_na controls whether the defaults remain enabled. A dash (-) is only an example of a possible sentinel: verify that the source uses it to mean missing and not a real value. See the pandas CSV import documentation.
If the export specification confirms that a dash means missing, you can declare that at import:
df = pd.read_csv(path, na_values=["-"])Do not fill every blank with zero or delete every row containing a blank. An unknown sales amount is not the same as zero sales, and missing notes may be immaterial while missing order dates may make a record unusable for a particular analysis. Decide field by field whether to retain, correct, exclude, or impute values. Record the rule and count affected rows.
4. Convert text sales amounts into numbers safely
Inspect raw values first to learn whether amounts contain currency symbols, spaces, grouping separators, or decimal marks. Commas and periods have different roles in different locales: a comma can separate thousands in one export and mark the decimal in another. Remove only formatting known to belong to this source.
For a source confirmed to use dollar signs and commas as thousands separators with periods as decimal points, a diagnostic conversion can look like this:
raw_amount = df["sales_amount"].copy()
amount_text = raw_amount.astype("string").str.strip()
# Apply only if these are the confirmed conventions for this export.
amount_text = amount_text.str.replace("$", "", regex=False)
amount_text = amount_text.str.replace(",", "", regex=False)
amount = pd.to_numeric(amount_text, errors="coerce")
failed_amount = raw_amount.notna() & amount.isna()
print("Nonmissing amounts that failed conversion:", int(failed_amount.sum()))
print(df.loc[failed_amount, ["sales_amount"]].head(20))pd.to_numeric(..., errors="coerce") turns values it cannot parse into missing values; it diagnoses conversion failures but does not correct their causes. pandas also supports errors="raise", which raises an error instead of coercing invalid values. Review every failed value, correct known formatting issues, and decide explicitly what should happen to irrecoverable records. Do not treat coerced missing values as successfully cleaned. See the pandas 3.0.5 to_numeric documentation.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallOutdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchOnce you have resolved the failures according to the source rules, assign the numeric series:
Rank #4
df["sales_amount"] = amountIf the file uses a different decimal or thousands convention, adapt the transformation to that convention—or configure the applicable import options—rather than applying the dollar example as a general rule.
5. Parse sale dates using the source convention
A date such as 03/04/2025 is ambiguous: it could mean March 4 or April 3. Confirm the convention from the export definition or another authoritative source before parsing. Do not infer it from geography or from the apparent values alone.
When the date format is known, specify it deliberately. For example, for dates confirmed to use year-month-day:
raw_date = df["sale_date"].copy()
df["sale_date"] = pd.to_datetime(
raw_date,
format="%Y-%m-%d",
errors="coerce"
)
failed_date = raw_date.notna() & df["sale_date"].isna()
print("Nonmissing dates that failed parsing:", int(failed_date.sum()))
print(df.loc[failed_date, ["sale_date"]].head(20))Use the actual source format, not this example, and review failed values before deciding whether to correct or exclude them. If the dates follow a known day-first convention, set dayfirst=True as appropriate; an explicit format is preferable when it accurately describes the data. For non-standard date parsing, use to_datetime() after read_csv(). The read_csv documentation also notes that mixed time zones or unparsable values may result in object data rather than a datetime array; the to_datetime reference documents date parsing options.
6. Review duplicate candidates using business meaning
Use duplicated() to find possible repeats, not to decide automatically that they are erroneous:
candidate_duplicates = df[df.duplicated(keep=False)]
print(candidate_duplicates.sort_values("order_id").head(30))Identical-looking lines may represent legitimate repeated products or line items. A repeated order ID alone may be normal when an order contains multiple items. Choose the columns that define a duplicate in this dataset, review candidate records, and compare row counts and totals before and after any removal. If the business rule establishes that a particular combination should occur only once, make that rule explicit in code:
duplicate_key = ["order_id", "product_code", "sale_date"]
# Only use this key if the business definition confirms it identifies a duplicate.
df = df.drop_duplicates(subset=duplicate_key, keep="first")Change the example key to match the actual business definition. Do not drop rows merely because they share an order number or look similar.
Free tools Windows power users keep installed
One-click scans. No signup required.
7. Validate the cleaned data before export
Validation should make every change explainable. Keep counts from each transformation and check the final data against the rules for this sales dataset. For example:
print("Rows before cleaning:", original_rows)
print("Rows after cleaning:", len(df))
print("Missing required values:")
print(df[["order_id", "sale_date", "sales_amount"]].isna().sum())
print("Date range:", df["sale_date"].min(), "to", df["sale_date"].max())
print("Sales total:", df["sales_amount"].sum(min_count=1))- Explain each row-count change, including any exclusions or duplicate removals.
- Count missing values in fields required for your intended analysis.
- Count nonmissing raw amounts and dates that failed conversion; inspect those rows rather than relying on the converted columns alone.
- Check the date range and sales amount boundaries against the dataset’s actual rules. For instance, nonnegative amounts are not a universal rule: refunds or reversals may be represented as negative values.
- Compare totals before and after changes when both totals have interpretable meanings, and investigate unexpected differences.
- Review candidate duplicates before removal and confirm the chosen key reflects the business definition.
For a repeatable workflow, record the counts and decisions alongside the cleaned file or in a separate processing log. The goal is not to force every field to be complete; it is to know what changed and whether the remaining data suits the analysis.
8. Save the cleaned DataFrame as a CSV
Export with index=False when the DataFrame index is not intended to become a separate CSV column:
output_path = "sales_cleaned.csv"
df.to_csv(output_path, index=False)pandas’ to_csv documentation includes options for index output and missing-data representation. After saving, reopen the file or inspect it in a text editor or spreadsheet application. Confirm the header names, row count, and expected amount and date formatting. If the output will be consumed by a system with a specific encoding, delimiter, or missing-value convention, set those export options deliberately and verify the resulting artifact.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
