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
- How do you find and remove duplicate rows in pandas?
df.duplicated()(count with.sum(), see all copies withkeep=False),df.drop_duplicates(), withsubset=for key columns andkeep="first"/"last"/False. - 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). - 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 "-".
astypevspd.to_numeric?astypecasts everything and fails on the first bad value;to_numericparses numbers and, witherrors="coerce", turns unparseable values into NaN; it also supportsdowncast.- Why does an integer column with missing values become float? NumPy's
int64has no missing value, so pandas usesfloat64with NaN. Use the nullableInt64dtype to keep integers with<NA>. - What is
pd.NA? pandas' missing-value marker for nullable dtypes (Int64,boolean,string), shown as<NA>. - 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).
- How do you reduce a DataFrame's memory? Check
memory_usage(deep=True); convert repeated text tocategory; downcast numbers withpd.to_numeric(downcast=...); load only needed columns (usecols). - What's the danger of
astype("int8")? Silent integer overflow when values are outside −128 to 127. - 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;keeppicks the survivor,subsetcompares only key columns. Tidy text first to reveal near-duplicates. - Check
dtypesbefore maths: a text column joins instead of adding (231122two). astypeis all-or-nothing;pd.to_numeric(errors="coerce")is a safety net: clean first, then count new NaNs.- Nullable dtypes
Int64,boolean,stringhold real values plus<NA>. categorysaves memory and enforces allowed values; downcast withto_numeric(downcast=...), never a blindastype("int8");convert_dtypes()finishes the job.- Next episode: cleaning 3, text data and regular expressions.