PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Outdated 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 matchTo clean a messy sales CSV with pandas, inspect how the file was read, make source-specific rules for missing values, amounts and dates, review possible duplicates, validate the result, and export only after you can account for changes. The examples below use pandas APIs documented for versions 3.0.5 and 3.0.6; check the documentation for the version installed in your environment.
1. Read the CSV and inspect what pandas found
Begin with the default import, then check its dimensions, sample rows, column names, inferred types and missing-value counts before changing anything:
As an Amazon Associate I earn from qualifying purchases.
import pandas as pd
source_path = "sales.csv"
df = pd.read_csv(source_path)
print("Rows, columns:", df.shape)
print(df.head())
print(df.columns.tolist())
print(df.dtypes)
print(df.isna().sum())How the file is parsed affects every later step. If the export uses a delimiter other than a comma, a non-UTF-8 encoding, special missing-value markers or columns that must keep a specific type, provide the relevant options to read_csv. Its documented options include sep, encoding, dtype, na_values, keep_default_na, thousands, decimal, parse_dates and chunksize. See the pandas read_csv reference.
Recommended Free Tools
For example, if the source uses semicolons and stores a decimal comma with a period as the thousands separator, those conventions can be declared at import:
#1 Best Overall
df = pd.read_csv(
source_path,
sep=";",
decimal=",",
thousands="."
)Use settings that match the actual export rather than copying this example unchanged. If a column contains identifiers such as customer or product codes, specify a text dtype when leading zeros or non-arithmetic meaning matter; an inferred integer can lose those zeros.
Do not silently skip malformed lines to get a DataFrame that appears clean. The on_bad_lines option can error, warn or skip. Skipping may discard sales records, so investigate the affected rows and determine whether they are corrupt or merely unusual. See the documented parsing options.
2. Normalize column names and text carefully
Display the original headers first. Stripping surrounding whitespace and standardizing case can make later references easier, but check that the change will not make two distinct names identical.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →original_columns = df.columns.tolist()
print("Original columns:", original_columns)
normalized_columns = df.columns.str.strip().str.lower()
if normalized_columns.duplicated().any():
raise ValueError("Header normalization would create duplicate column names")
df.columns = normalized_columnsTrim cell values only in columns where surrounding whitespace is accidental. Avoid blanket conversion of the entire DataFrame to strings or broad text replacement: either can alter meaningful values and types.
text_columns = ["customer_name", "product_name"]
for column in text_columns:
if column in df.columns:
df[column] = df[column].str.strip()Adjust the column names to match your file. Keep business identifiers as text when their formatting carries meaning.
3. Decide what counts as missing
Pandas recognizes common missing-value tokens by default. Add source-specific markers with na_values; use keep_default_na to control whether the default markers remain enabled. A dash, for instance, might mean “not recorded” in one export but be a valid value in another, so verify its meaning before treating it as missing. See pandas’ missing-value and I/O guide.
If you discover that a particular source uses "-" to mean missing, you can declare it when reading the file:
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minutedf = pd.read_csv(source_path, na_values=["-"])Do not fill every blank sales amount with zero. Zero is a value; a missing amount means the value is unknown or absent. Count affected records and decide, based on the column’s business meaning, whether they should remain missing, be corrected from the source, or be excluded under a documented rule.
print(df.isna().sum())
amount_column = "sales_amount" # Change to the column in your export
if amount_column in df.columns:
print("Rows with missing amount:", df[amount_column].isna().sum())4. Convert sales amounts stored as text
First inspect the raw values so you know which currency marks and separators actually occur. Currency symbols and grouping marks can be removed only when they are formatting in this source. Decimal and thousands separators vary by locale: a comma may group thousands in one file and mark the decimal fraction in another.
amount_column = "sales_amount" # Change to your actual column name
print(df[amount_column].head(20).to_list())
print(df[amount_column].dropna().astype(str).unique()[:20])If this source consistently uses a dollar sign and commas only as thousands separators, remove just those known formatting characters, then convert. Do not use this cleanup on a file with different conventions.
raw_amount = df[amount_column]
amount_text = raw_amount.astype("string").str.strip()
# Valid only if this source uses $ for currency and commas for grouping.
amount_text = amount_text.str.replace("$", "", regex=False)
amount_text = amount_text.str.replace(",", "", regex=False)
converted_amount = pd.to_numeric(amount_text, errors="coerce")
failed_amount = raw_amount.notna() & converted_amount.isna()
print("Nonmissing amount values that failed conversion:", failed_amount.sum())
print(df.loc[failed_amount, [amount_column]])pd.to_numeric with errors="coerce" turns invalid inputs into missing values; it diagnoses parse failures, but does not repair them. Correct known formatting issues and inspect what remains before replacing the original column. Alternatively, errors="raise" raises an error when a value cannot be parsed. See the pandas to_numeric reference.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →# Assign only after reviewing conversion failures.
df[amount_column] = converted_amountFor a comma-decimal source, removing commas would destroy the intended values. Configure the import using the source’s decimal and thousands conventions, or apply a conversion appropriate to those conventions. If a value cannot be interpreted reliably, keep it visible for correction or document why the affected row is retained or excluded.
Rank #4
5. Parse sale dates using the source convention
If the format is known, specify it instead of relying on an ambiguous interpretation. For example, 03/04/2025 could mean March 4 or April 3. Confirm the source’s convention; do not infer it from a guess about geography or from the date itself.
For a known month/day/year format:
date_column = "sale_date" # Change to your actual column name
raw_date = df[date_column]
parsed_date = pd.to_datetime(
raw_date,
format="%m/%d/%Y",
errors="coerce"
)
failed_date = raw_date.notna() & parsed_date.isna()
print("Nonmissing dates that failed conversion:", failed_date.sum())
print(df.loc[failed_date, [date_column]])Use the format that matches the export. If the source has a known day-first convention, pandas also provides the dayfirst option; do not apply it without confirming that convention. When CSV date parsing is non-standard, the pandas documentation says: “For non-standard datetime parsing, use to_datetime() after read_csv().” See the read_csv reference and to_datetime reference.
If you do not yet know the format, read the date column as text, inspect representative values and parse only once the convention is established. Unparsable strings or mixed time zones can prevent pandas from producing a datetime array during CSV import; inspect the result rather than assuming every date was converted. See pandas’ CSV parsing documentation.
# Assign only after reviewing the failed-date rows.
df[date_column] = parsed_date6. Review candidate duplicates before removing rows
duplicated() can identify repeated-looking records. Start by reviewing exact full-row matches:
candidate_duplicates = df.duplicated(keep=False)
print("Rows in exact-match groups:", candidate_duplicates.sum())
print(df.loc[candidate_duplicates].sort_values(by=df.columns.tolist()))A repeated order ID is not necessarily a duplicate sale: one order may have several legitimate product lines, and identical-looking lines may be valid repeated items. Define duplicates using the transaction’s business meaning and the columns that identify one record. Then inspect candidate rows and compare row counts and sales totals before and after any removal.
# Example only: choose a key that uniquely identifies a sale line in your system.
duplicate_key = ["order_id", "line_id"]
# Review duplicates on that key before deciding whether to remove any.
key_duplicates = df.duplicated(subset=duplicate_key, keep=False)
print(df.loc[key_duplicates].sort_values(duplicate_key))Do not run drop_duplicates until the chosen key and removal rule are justified. The appropriate definition is a business-data decision, not a universal pandas rule.
7. Validate changes before export
Keep the original row count and record how many rows each deliberate filtering or deduplication rule changes. Check required fields, conversion failures, the date range, and totals where the raw and cleaned values are both interpretable.
Free tools Windows power users keep installed
One-click scans. No signup required.
original_row_count = len(df)
# After all planned transformations:
final_row_count = len(df)
print("Rows before cleaning:", original_row_count)
print("Rows after cleaning:", final_row_count)
print("Row-count difference:", original_row_count - final_row_count)
required_columns = ["order_id", "sale_date", "sales_amount"]
for column in required_columns:
if column in df.columns:
print(f"Missing {column}:", df[column].isna().sum())
if date_column in df.columns and pd.api.types.is_datetime64_any_dtype(df[date_column]):
print("Date range:", df[date_column].min(), "to", df[date_column].max())
if amount_column in df.columns:
print("Cleaned sales total:", df[amount_column].sum(min_count=1))Use your dataset’s actual rules for sensible amount limits and expected date ranges. Nonnegative sales amounts, for example, are not universal: refunds, corrections or other transaction types may be represented with negative values. Explain any differences in row counts or totals and retain an audit of conversion failures and excluded records.
8. Export the cleaned CSV and check the saved file
Write the DataFrame to a new file so the original export remains available. Set index=False when the DataFrame index is not a meaningful CSV column; otherwise pandas may write an unintended extra column.
output_path = "sales_cleaned.csv"
df.to_csv(output_path, index=False)The pandas to_csv reference documents controls for index output and missing-data representation. Reopen the result and verify its headers, row count and expected formatting:
Quick Recap
check = pd.read_csv(output_path)
print("Saved rows, columns:", check.shape)
print("Saved columns:", check.columns.tolist())
print(check.head())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.
