ESG Data Quality and Cleaning
Info
Learning objective: Identify and handle common data-quality issues without "fixing" uncertain data just so it looks normal.
This lesson uses DS101_ESG_Company_Data_Practice.csv, an illustrative teaching dataset that deliberately keeps some of the data-quality problems you meet in real work. Resist the urge to calculate straight away. Review the file the way you would review a report someone handed you: Are there blanks? Are there repeated rows? Are the numeric columns really numbers? Are the units consistent? Are there values outside a sensible range, or values that stand out? Is each company name written the same way every time?
import pandas as pd
df = pd.read_csv("DS101_ESG_Company_Data_Practice.csv")
# How many missing values each column has
df.isna().sum()
# How many rows are exact copies of an earlier row
df.duplicated().sum()
# Which data type pandas inferred for each column
df.dtypes- A missing value is not zero. A blank means "we do not know"; a 0 means "we know it was none". They say very different things.
- A repeated row is not always a true duplicate. Check the key (company_id and year) first, then decide whether it is a double entry or two different records.
- A numeric column can be read as text. A thousands separator such as a comma is enough to turn the whole column into object, and then it cannot be used in calculations.
- Inconsistent units break comparison. If one column mixes units, the sizes of its numbers no longer mean the same thing.
- An outlier is a signal to investigate, not a value to delete automatically. It may be an error, or it may be a real change.
- A plausible value can still be wrong. Falling inside the normal range does not mean it has been checked against the source.
Warning
Required example: if scope2_tco2e is blank, do not fill it with 0 automatically unless the source documentation establishes that zero is the correct value. Treating a blank as 0 understates emissions, and every Scope 1+2 total and intensity value built on it will be wrong too.
# Does any company_id and year combination appear more than once?
print(df[df.duplicated(subset=["company_id", "year"], keep=False)])
# Are company names written consistently (for example, extra spaces)?
print(df["company_name"].unique())
# Which values cannot be read as numbers? (stored in a new variable, original column untouched)
revenue_num = pd.to_numeric(df["revenue_m_twd"], errors="coerce")
print(df.loc[revenue_num.isna() & df["revenue_m_twd"].notna(), ["company_id", "year", "revenue_m_twd"]])
# Are percentage values outside 0 to 100?
print(df[(df["renewable_energy_pct"] < 0) | (df["renewable_energy_pct"] > 100)])
# Summary statistics help spot values that stand out; then look at the same company's other years
df.describe()| Issue | Evidence | Impact if left untreated | Treatment decision | Justification | What must remain unknown |
|---|---|---|---|---|---|
| One year of waste_tonnes for a company is about 1000 times its other years | describe() shows a maximum that stands out; the source note gives the unit as kg, while the data dictionary specifies tonnes | Left untreated, that year's waste figure is overstated by about 1000x, distorting any total or comparison that includes it | After confirming with the data provider, convert to tonnes in a new column and keep the original column | One column must use one unit, or years cannot be compared; the conversion was checked against the source, not guessed | Whether other years also mistakenly used kg has not yet been checked one by one, and stays unknown |
| employees is blank for one company in one year | isna().sum() shows 1 missing value in that column | Imputing 0 or an average would push that year's per-employee metric artificially down or up | Leave it missing, do not impute; exclude that year from per-employee metrics and state this as a limitation | No source confirms the headcount, so imputing it would mean inventing data | The true headcount for that year stays unknown; the per-employee trend has a gap that is not estimated |
| A company name is written slightly differently across rows (for example, a trailing space) | df["company_name"].unique() lists values that look identical but compare as unequal strings | Left untreated, groupby("company_name") splits the same company into two groups, understating any aggregate | Strip leading and trailing spaces and standardize the spelling into a new column, then group on that column; keep the original column | Grouping and aggregation depend on exact string matches; confirm the rows really are the same company before standardizing, rather than rewriting blindly | Whether other spelling differences remain undiscovered still needs attention in later checks |
A good cleaning habit: keep the original columns and put cleaned results in new columns or a new DataFrame; write every change into the decision record; and where something cannot be confirmed, leave it missing and say so in the limitations of your conclusion.
- Start with the overall shape: df.shape and df.dtypes, to confirm size and types are as expected
- Then check missing values: df.isna().sum(), to see which columns have blanks
- Then check duplicates: df.duplicated(subset=["company_id","year"]).sum(), to confirm the key is not repeated
- Finally check range and outliers: df.describe() and column-level range checks, to find numbers that need verification
- Following this order helps avoid missing an earlier problem just because a later one caught your eye first
Check yourself
You notice that scope2_tco2e is blank in one row. What should your first step be?