SyntaxPilot
Courses/Data Analytics with Python/Lesson 20

Data Analytics with Python · Lesson 20 of 90 · 12 min

Cleaning 2: duplicates and data types

duplicateddrop_duplicateskeepsubsetnear-duplicatesastypeto_numeric (errors='coerce')category dtypenullable dtypes (Int64, boolean, string)memory_usagedowncastingconvert_dtypes

These are the notes for Data Analytics with Python, episode 20. Two more problems from the quality scorecard: rows that appear twice and columns stored as the wrong type. Both give wrong answers without an error. We cover duplicated, drop_duplicates, keep, subset, near-duplicates, astype, pd.to_numeric(errors="coerce"), nullable dtypes (Int64, boolean, string), the category dtype, memory_usage, downcasting and convert_dtypes. Every term is defined the first time it appears and collected in the Glossary. Every program below was run for real (Python 3.13, pandas 3.0.5, NumPy 2.5.3); outputs are unedited.

The data

Ramesh's billing app exported eight orders. Some problems are deliberate copies of what real exports look like:

import sys; sys.tracebacklimit = 0
import pandas as pd, numpy as np, io
raw = """order_id,item,qty,price,pay,member
101,Masala Chai,2,20,UPI,Yes
102,Samosa,3,15,Cash,No
103,Coffee,1,Rs 40,UPI,
103,Coffee,1,Rs 40,UPI,
104,Masala Chai,,20,Card,Yes
105,Samosa,2,15,upi,No
105,Samosa,2,15,UPI ,No
106,Coffee,two,40,Cash,Yes
"""
orders = pd.read_csv(io.StringIO(raw))

Exact duplicates: duplicated and drop_duplicates

The printer jams, the cashier presses print again, and bill 103 exists twice. A duplicate is a row that is an exact copy of an earlier row. Counted twice, it inflates revenue.

duplicated() returns True for each row that repeats an earlier row:

print(orders)
print(orders.duplicated().tolist())
   order_id         item  qty  price   pay member
0       101  Masala Chai    2     20   UPI    Yes
1       102       Samosa    3     15  Cash     No
2       103       Coffee    1  Rs 40   UPI    NaN
3       103       Coffee    1  Rs 40   UPI    NaN
4       104  Masala Chai  NaN     20  Card    Yes
5       105       Samosa    2     15   upi     No
6       105       Samosa    2     15  UPI      No
7       106       Coffee  two     40  Cash    Yes
[False, False, False, True, False, False, False, False]

Only the second 103 row is True; the first copy counts as the original. The keep setting decides which copy is "the original":

keep= Marks as duplicate drop_duplicates keeps
"first" (default) every copy except the first the first copy
"last" every copy except the last the last copy
False every copy none of the repeated rows

keep=False is the way to see all copies; drop_duplicates() removes the extra ones:

print(orders[orders.duplicated(keep=False)])
print(len(orders.drop_duplicates()), "rows left")
   order_id    item qty  price  pay member
2       103  Coffee   1  Rs 40  UPI    NaN
3       103  Coffee   1  Rs 40  UPI    NaN
7 rows left

Two missing values in the same column count as equal for duplicated (both 103 rows have a blank member).

Near-duplicates: subset, and tidying first

Order 105 is still there twice: one row says upi, the other UPI (with a trailing space). They are near-duplicates: the same real thing written slightly differently, so duplicated() misses them.

Fix 1: compare only the column(s) that identify a record, with subset:

print(orders.duplicated(subset=["order_id"]).sum(), "repeated ids")
print(orders.drop_duplicates(subset=["order_id"])["pay"].tolist())
print(orders.drop_duplicates(subset=["order_id"], keep="last")["pay"].tolist())
2 repeated ids
['UPI', 'Cash', 'UPI', 'Card', 'upi', 'Cash']
['UPI', 'Cash', 'UPI', 'Card', 'UPI ', 'Cash']

With subset, rows count as duplicates when those columns match, even if others differ. keep decides which version survives, so choose the one you trust (often the latest, keep="last", after sorting by a timestamp).

Fix 2: tidy the text, then check again. str.strip() removes spaces at both ends and str.upper() makes capitals (the .str accessor is episode 21):

o = orders.drop_duplicates()
o["pay"] = o["pay"].str.strip().str.upper()
print(o.duplicated().sum(), "duplicates after tidying text")
o = o.drop_duplicates()
print(len(orders), "->", len(o), "rows")
1 duplicates after tidying text
8 -> 6 rows

Tidy first, then de-duplicate. Before dropping by subset, check that the "duplicates" really are the same thing: two different customers can share a name, and an order id reused by a buggy app is a problem to report, not to hide.

Numbers stored as text

Every column has a data type (dtype): the kind of values it holds. Text is str; whole numbers are int64; decimals are float64. A price tag that says "Rs 40" is a label: you can read it, but a calculator can't add it. And one bad cell is enough for pandas to read the whole column as text.

print(orders.dtypes)
print(orders["qty"] * orders["price"])
order_id    int64
item          str
qty           str
price         str
pay           str
member        str
dtype: object
TypeError: can't multiply sequence by non-int of type 'str'

qty is text because someone typed two; price is text because of Rs 40. Worse, summing a text column gives no error:

print(orders["qty"].sum())
231122two

sum() on text joins the strings ("2" + "3" + "1" + ...). Always check dtypes before doing maths.

Converting: astype and pd.to_numeric

Converting values to another type is called casting. astype(type) casts a whole column, and it is all-or-nothing:

print(orders["price"].astype(int))
ValueError: invalid literal for int() with base 10: 'Rs 40'

pd.to_numeric(col, errors="coerce") converts what it can and turns anything unreadable into NaN instead of crashing ("coerce" means force). It is a safety net, not a fix: better to clean the text first.

p = pd.to_numeric(orders["price"], errors="coerce")
print(p.tolist())
p2 = pd.to_numeric(orders["price"].str.replace("Rs ", ""))
print(p2.tolist(), p2.dtype)
[20.0, 15.0, nan, nan, 20.0, 15.0, 15.0, 40.0]
[20, 15, 40, 40, 20, 15, 15, 40] int64

With coerce we silently lost two real prices. After removing "Rs ", everything converts and the dtype is int64. After any errors="coerce", count the new NaNs and look at the original values behind them. (errors="raise", the default, stops with an error.)

Nullable dtypes: Int64, boolean, string

Quantities are whole numbers, so cast to int:

q = pd.to_numeric(orders["qty"], errors="coerce")
print(q.tolist(), q.dtype)
print(q.astype(int))
[2.0, 3.0, 1.0, 1.0, nan, 2.0, 2.0, nan] float64
pandas.errors.IntCastingNaNError: Cannot convert non-finite values (NA or inf) to integer.Replace or remove non-finite values or cast to an integer typethat supports these values (e.g. 'Int64')

The classic NumPy int64 cannot store a blank, so pandas used float64 (2.0, 3.0), and casting to int fails. (inf is infinity, another non-finite value.) The error suggests the fix: a nullable dtype, a type that can hold real values and a missing value, pd.NA, shown as <NA>. Note the capital letter:

q = pd.to_numeric(orders["qty"], errors="coerce").astype("Int64")
print(q.tolist(), q.dtype)
print(q.sum())
[2, 3, 1, 1, <NA>, 2, 2, <NA>] Int64
11

The same idea for True/False. member is Yes, No or blank:

m = orders["member"].map({"Yes": True, "No": False})
print(m.tolist(), m.dtype)
print(m.astype("boolean").tolist(), m.astype("boolean").dtype)
[True, False, nan, nan, True, False, False, True] object
[True, False, <NA>, <NA>, True, False, False, True] boolean

object is pandas' "mixed bag" dtype: any Python objects. boolean (lowercase, a pandas dtype) is the nullable True/False type. The third nullable type is string, a dedicated text type with <NA> as its missing value.

Classic dtype Can hold a blank? Nullable version
int64 no (pandas switches to float64) Int64
bool no (pandas switches to object) boolean
str / object yes, as NaN string (blank = <NA>)

The category dtype

Payment type has three possible values, but a text column stores the full word in every row. A form with a dropdown prints the options once and you just pick one: that's the category dtype. pandas stores the list of categories once and each row keeps a small integer code pointing to its category. memory_usage(deep=True) reports the real bytes a column uses (deep=True includes the text itself):

import sys; sys.tracebacklimit = 0
import pandas as pd, numpy as np
big = pd.DataFrame({"pay": np.random.default_rng(1).choice(
    ["UPI", "Cash", "Card"], 100_000)})
print(big["pay"].memory_usage(deep=True), "bytes as text")
big["pay"] = big["pay"].astype("category")
print(big["pay"].memory_usage(deep=True), "bytes as category")
print(big["pay"].cat.categories.tolist())
5266737 bytes as text
100290 bytes as category
['Card', 'Cash', 'UPI']

About 50 times smaller. (rng.choice picks random values from a list, episode 10; .cat is the accessor for category columns.) A category only accepts its listed values:

big.loc[0, "pay"] = "Wallet"
TypeError: Cannot setitem on a Categorical with a new category (Wallet), set the categories first

Add it first with big["pay"] = big["pay"].cat.add_categories(["Wallet"]). That strictness is a free validity check. Use category for columns with few distinct values repeated many times (city, payment type, grade), not for unique text like names.

Downcasting

int64 uses 8 bytes per value and can store numbers up to 9,223,372,036,854,775,807. Quantities from 1 to 19 don't need that. Choosing a smaller type is downcasting:

import pandas as pd, numpy as np
qty = pd.Series(np.random.default_rng(1).integers(1, 20, 100_000))
print(qty.dtype, qty.memory_usage(index=False), "bytes")
small = pd.to_numeric(qty, downcast="integer")
print(small.dtype, small.memory_usage(index=False), "bytes")
int64 800000 bytes
int8 100000 bytes

index=False leaves out the index's own memory. The tempting shortcut, astype("int8"), does not check the range:

import pandas as pd
s = pd.Series([100, 300])
print(s.astype("int8").tolist())
print(pd.to_numeric(s, downcast="integer").dtype)
[100, 44]
int16

int8 holds only −128 to 127, so 300 wrapped around to 44 (300 − 256), silently. This is called integer overflow. to_numeric(downcast="integer") looks at the values and picks the smallest safe type (int16: −32,768 to 32,767).

Type Bytes Range
int8 1 −128 to 127
int16 2 −32,768 to 32,767
int32 4 about ±2.1 billion
int64 8 about ±9.2 × 10¹⁸

convert_dtypes()

After the basic cleaning, convert_dtypes() picks the best nullable type for every column:

o = orders.drop_duplicates(subset=["order_id"]).copy()
o["qty"] = pd.to_numeric(o["qty"], errors="coerce")
o["price"] = pd.to_numeric(o["price"].str.replace("Rs ", ""))
o["member"] = o["member"].map({"Yes": True, "No": False})
print(o.dtypes.astype(str).to_dict())
print(o.convert_dtypes().dtypes.astype(str).to_dict())
{'order_id': 'int64', 'item': 'str', 'qty': 'float64', 'price': 'int64', 'pay': 'str', 'member': 'object'}
{'order_id': 'Int64', 'item': 'string', 'qty': 'Int64', 'price': 'Int64', 'pay': 'string', 'member': 'boolean'}

It can't fix "Rs 40" or "two" (on the raw table it would leave those columns as string): clean first, then convert.

Mini project: tidy()

def tidy(df):
    df = df.copy()
    df["pay"] = df["pay"].str.strip().str.upper()
    df = df.drop_duplicates(subset=["order_id"], keep="first")
    q = df["qty"].replace({"two": "2"})
    df["qty"] = pd.to_numeric(q, errors="coerce").astype("Int64")
    df["price"] = pd.to_numeric(df["price"].str.replace("Rs ", ""))
    df["member"] = df["member"].map({"Yes": True, "No": False}).astype("boolean")
    df["pay"] = df["pay"].astype("category")
    return df
t = tidy(orders)
print(t)
print("revenue:", (t["qty"] * t["price"]).sum())
   order_id         item   qty  price   pay  member
0       101  Masala Chai     2     20   UPI    True
1       102       Samosa     3     15  CASH   False
2       103       Coffee     1     40   UPI    <NA>
4       104  Masala Chai  <NA>     20  CARD    True
5       105       Samosa     2     15   UPI   False
7       106       Coffee     2     40  CASH    True
revenue: 235

The word two is fixed by hand with replace (a known value), before to_numeric, so it isn't lost to coerce. Order 104 has no quantity, so it is left out of the sum: write that in the cleaning log ("revenue excludes order 104: quantity missing").

Common mistakes

Code / habit Real error / symptom Why Fix
orders["qty"].sum() on text no error; 231122two text is joined, not added pd.to_numeric(...) first
orders["qty"] * orders["price"] TypeError: can't multiply sequence by non-int of type 'str' both columns are text convert both
orders["price"].astype(int) ValueError: invalid literal for int() with base 10: 'Rs 40' one bad value stops astype clean text, then to_numeric
q.astype(int) with blanks IntCastingNaNError: Cannot convert non-finite values (NA or inf) to integer int64 can't hold a blank astype("Int64")
to_numeric(errors="coerce") and move on no error; real values become NaN coerce hides problems count and inspect new NaNs
s.astype("int8") with 300 no error; 300 → 44 integer overflow pd.to_numeric(s, downcast="integer")
big.loc[0, "pay"] = "Wallet" on a category TypeError: Cannot setitem on a Categorical with a new category (Wallet)... categories are fixed cat.add_categories([...]) first
drop_duplicates() on untidy text near-duplicates survive upi ≠ UPI tidy text, or subset=[id]

Practice

All practice questions use this school fee sheet:

import pandas as pd, numpy as np, io
raw = """roll,name,fee,paid,house
1,Asha,"1,200",Yes,Red
2,Ravi,1500,No,blue
2,Ravi,1500,No,blue
3,Meena,Rs 900,yes,Blue
4,Kabir,,No,Red
4,kabir ,,No,Red
"""
fees = pd.read_csv(io.StringIO(raw))

("1,200" is in quotes so the comma inside is part of the value, not a separator.)

1. How many exact duplicate rows are there, and how many repeated roll numbers?

Solution
print(fees.duplicated().sum(), fees.duplicated(subset=["roll"]).sum())
1 2

Ravi's rows are identical; Kabir's differ only in kabir vs Kabir, so only subset catches them.

2. Tidy the names (strip spaces, title case) and drop duplicates. How many rows are left?

Solution
f = fees.copy()
f["name"] = f["name"].str.strip().str.title()
f = f.drop_duplicates()
print(len(f))
4

3. Convert fee to a nullable whole-number column and print its total.

Solution
fee = f["fee"].str.replace("Rs ", "").str.replace(",", "")
f["fee"] = pd.to_numeric(fee).astype("Int64")
print(f["fee"].tolist(), f["fee"].dtype, f["fee"].sum())
[1200, 1500, 900, <NA>] Int64 3600

4. Make paid a nullable boolean (careful: Yes and yes) and house a category with consistent spelling.

Solution
f["paid"] = f["paid"].str.lower().map({"yes": True, "no": False}).astype("boolean")
f["house"] = f["house"].str.title().astype("category")
print(f.dtypes.astype(str).to_dict())
print(f)
{'roll': 'int64', 'name': 'str', 'fee': 'Int64', 'paid': 'boolean', 'house': 'category'}
   roll   name   fee   paid house
0     1   Asha  1200   True   Red
1     2   Ravi  1500  False  Blue
3     3  Meena   900   True  Blue
4     4  Kabir  <NA>  False   Red

5. Which dtype does pd.to_numeric(pd.Series([5, 200, 70000]), downcast="integer") choose, and why not int16?

Solution
print(pd.to_numeric(pd.Series([5, 200, 70000]), downcast="integer").dtype)
int32

70,000 is above int16's maximum of 32,767.

Quick quiz

s = pd.Series(["5", "10"])
print(s.sum())

A. 15 B. 510 C. TypeError D. 5.10

Answer

B. 510. The values are text, so sum() joins them. pd.to_numeric(s).sum() gives 15.

Interview questions

  1. How do you find and remove duplicate rows in pandas? df.duplicated() (count with .sum(), see all copies with keep=False), df.drop_duplicates(), with subset= for key columns and keep="first"/"last"/False.
  2. What are near-duplicates and how do you handle them? Records of the same thing that differ in formatting (case, spaces, abbreviations). Standardise text first, de-duplicate on key columns with subset, and use fuzzy matching for typos (episode 21).
  3. Why might a numeric column have dtype object or str? At least one value isn't a valid number: a currency symbol, thousands separator, a word, a stray character, or a placeholder like "-".
  4. astype vs pd.to_numeric? astype casts everything and fails on the first bad value; to_numeric parses numbers and, with errors="coerce", turns unparseable values into NaN; it also supports downcast.
  5. Why does an integer column with missing values become float? NumPy's int64 has no missing value, so pandas uses float64 with NaN. Use the nullable Int64 dtype to keep integers with <NA>.
  6. What is pd.NA? pandas' missing-value marker for nullable dtypes (Int64, boolean, string), shown as <NA>.
  7. When would you use the category dtype? Low-cardinality columns (few distinct values, many rows): it saves memory, speeds up groupby, enforces allowed values, and can store an order (ordered categoricals).
  8. How do you reduce a DataFrame's memory? Check memory_usage(deep=True); convert repeated text to category; downcast numbers with pd.to_numeric(downcast=...); load only needed columns (usecols).
  9. What's the danger of astype("int8")? Silent integer overflow when values are outside −128 to 127.
  10. What does convert_dtypes() do? Converts each column to the best nullable dtype (Int64, boolean, string, Float64) that fits its current values.

Glossary

Term Meaning Example
Duplicate A row that is an exact copy of an earlier row bill 103 twice
duplicated() True for each row that repeats an earlier one orders.duplicated()
drop_duplicates() Remove repeated rows, keeping one copy 8 → 7 rows
keep Which copy counts as the original: "first", "last" or False (none) keep=False shows all
subset Columns used to decide whether rows are duplicates subset=["order_id"]
Near-duplicate The same record written slightly differently upi vs UPI
dtype (data type) The kind of values a column holds int64, str
str Text dtype (pandas 3 default for text) "Rs 40"
int64 Whole numbers, 8 bytes each, no blanks 320
float64 Decimal numbers, 8 bytes each; blank = NaN 2.0
object Mixed-bag dtype holding any Python objects True, False, nan
Casting Converting values to another type text → number
astype(type) Cast a whole column; fails on any bad value astype("Int64")
pd.to_numeric Parse a column as numbers to_numeric(col)
errors="coerce" Turn values that can't be converted into NaN Rs 40 → NaN
Nullable dtype A dtype that can also hold pd.NA Int64
Int64 Nullable whole numbers (capital I) [2, <NA>]
boolean Nullable True/False [True, <NA>]
string Nullable text dtype convert_dtypes() result
pd.NA / <NA> pandas' missing marker for nullable dtypes blank member
category Dtype storing each distinct value once plus small codes payment type
Category One allowed value of a categorical column "UPI"
.cat Accessor for category columns .cat.categories
memory_usage(deep=True) Real bytes used by a column, including text 5,266,737
Downcasting Storing numbers in a smaller type int64 → int8
downcast="integer" Let to_numeric pick the smallest safe integer type int16
Integer overflow A value too big for its type wraps around 300 → 44 in int8
convert_dtypes() Convert every column to the best nullable dtype qty → Int64
IntCastingNaNError pandas' error when casting blanks to int q.astype(int)

Summary

  • Duplicates: duplicated() finds them, drop_duplicates() removes them; keep picks the survivor, subset compares only key columns. Tidy text first to reveal near-duplicates.
  • Check dtypes before maths: a text column joins instead of adding (231122two).
  • astype is all-or-nothing; pd.to_numeric(errors="coerce") is a safety net: clean first, then count new NaNs.
  • Nullable dtypes Int64, boolean, string hold real values plus <NA>.
  • category saves memory and enforces allowed values; downcast with to_numeric(downcast=...), never a blind astype("int8"); convert_dtypes() finishes the job.
  • Next episode: cleaning 3, text data and regular expressions.