Mathew K AnalyticsLesson 18 · Pandas Full Course
Fix Wrong Data Types in Pandas: astype, to_numeric and to_datetime | Pandas Course #18
In this lesson you'll be able to: Read a table's types and spot the ones that are wrong Turn text into real dates and numbers, safely Store whole numbers…
- CoursePandas Full Course
- Lesson18 of 65
- FormatJupyter notebook · 42 code cells
- Data3 datasets
What you'll learn
Datasets used in this lesson
Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.
- salary_survey.csv preview4.9 MB
- mpg.csv preview20.7 KB
- online_retail.csv.gz preview7.1 MB
📓 Full notebook
Download .ipynbPandas 18: Changing Data Types and Converting Units#
In this lesson you'll be able to:
- Read a table's types and spot the ones that are wrong
- Turn text into real dates and numbers, safely
- Store whole numbers with gaps, and save memory with categories
- Convert units, like pounds to kilograms and mpg to litres Data: a year of online retail sales and 398 classic cars
Warm-up: the shop data, minus its copies#
Last lesson we removed 5,268 duplicate rows. Let's do it again.
import pandas as pd
retail = pd.read_csv("data/pandas/online_retail.csv.gz")
retail = retail.drop_duplicates(ignore_index=True)
print(retail.shape)
retail.dtypes
Check the types before you calculate#
The wrong type gives wrong answers, or no answer at all.
Quick check: a column holds the text "6" and "6". What does its sum give?
digits = retail["Quantity"].head(2).astype(str)
print(digits.sum())
print(digits.astype(int).sum())
retail.info()
print(retail.select_dtypes(include="number").columns.tolist())
print(retail.select_dtypes(include="str").columns.tolist())
Common mistake: treating a text column as dates.
retail["InvoiceDate"].dt.month
retail["InvoiceDate"] = pd.to_datetime(retail["InvoiceDate"],
format="%m/%d/%Y %H:%M")
print(retail["InvoiceDate"].dtype)
print(retail["InvoiceDate"].min(), "to", retail["InvoiceDate"].max())
retail["InvoiceDate"].dt.month_name().value_counts().head(3)
Your turn: which day of the week has the most order lines?
retail["InvoiceDate"].dt.day_name().value_counts()
Turn text into numbers safely#
pd.to_numeric converts text, and errors="coerce" marks what won't fit.
Common mistake: assuming every value in a column is a number.
pd.to_numeric(retail["InvoiceNo"])
inv_num = pd.to_numeric(retail["InvoiceNo"], errors="coerce")
bad = inv_num.isna()
print(bad.sum(), "could not be converted")
retail.loc[bad, "InvoiceNo"].str[0].value_counts()
retail["is_cancelled"] = retail["InvoiceNo"].str.startswith("C")
print(retail["is_cancelled"].dtype)
retail.groupby("is_cancelled")["Quantity"].sum()
survey = pd.read_csv("data/pandas/salary_survey.csv")
amounts = survey.loc[[752, 9284, 1001, 11734], "currency_other"]
print(amounts.tolist())
print(pd.to_numeric(amounts, errors="coerce").tolist())
fixed = amounts.str.replace(r"[$,]", "", regex=True)
print(pd.to_numeric(fixed, errors="coerce").tolist())
Your turn: show the 3 invoices that start with
A. What are they?
is_a = retail["InvoiceNo"].str.startswith("A")
retail.loc[is_a, ["InvoiceNo", "Description", "UnitPrice"]]
Quick check: should
StockCodebecome a number too?
codes = pd.to_numeric(retail["StockCode"], errors="coerce")
print(codes.isna().sum(), "codes contain letters")
retail.loc[codes.isna(), "StockCode"].unique()[:6]
Store whole numbers that have gaps#
A single gap turns a whole-number column into decimals. Int64 fixes that.
ids = retail["CustomerID"]
print(ids.dtype, "with", ids.isna().sum(), "missing")
print(ids.astype(str).head(3).tolist())
retail["CustomerID"] = ids.astype("Int64")
print(retail["CustomerID"].dtype)
print(retail["CustomerID"].astype("string").head(3).tolist())
print(retail["CustomerID"].isna().sum(), "still missing")
Your turn: convert
UnitPricefrom pounds to whole pence.
pence = (retail["UnitPrice"] * 100).round().astype(int)
print(pence.dtype)
print(pence.head(3).tolist())
Save memory with categories and smaller numbers#
Repeated labels and oversized number types waste memory.
before = retail.memory_usage(deep=True)
print(round(before.sum() / 1e6, 1), "MB in total")
(before / 1e6).round(1).sort_values(ascending=False).head(3)
print(retail["Country"].nunique(), "countries")
retail["Country"] = retail["Country"].astype("category")
mb = retail["Country"].memory_usage(deep=True) / 1e6
print(round(mb, 1), "MB as a category")
print(retail["Country"].cat.categories[:4].tolist())
print(retail["Country"].cat.codes.head(3).tolist())
slim = pd.read_csv("data/pandas/online_retail.csv.gz",
usecols=["InvoiceNo", "Quantity", "Country"],
dtype={"Country": "category"})
print(slim.dtypes)
print(round(slim.memory_usage(deep=True).sum() / 1e6, 1), "MB")
Quick check: can the tiny
int8type hold every quantity?
print(retail["Quantity"].max(), "largest quantity")
print(retail["Quantity"].astype("int8").max(), "after int8")
qty = pd.to_numeric(retail["Quantity"], downcast="integer")
print(qty.dtype, qty.max())
retail = retail.astype({"StockCode": "category",
"Description": "category",
"InvoiceNo": "category"})
retail["Quantity"] = qty
after = retail.memory_usage(deep=True).sum() / 1e6
print(round(before.sum() / 1e6, 1), "MB ->", round(after, 1), "MB")
Convert units with simple arithmetic#
Multiply by a conversion factor, and name the new column after its unit.
cars = pd.read_csv("data/pandas/mpg.csv")
print(cars.shape)
cars[["name", "mpg", "weight", "displacement"]].head(3)
KG_PER_LB = 0.453592
LITRES_PER_CUBIC_INCH = 0.0163871
cars["weight_kg"] = (cars["weight"] * KG_PER_LB).round()
cars["engine_l"] = (cars["displacement"]
* LITRES_PER_CUBIC_INCH).round(1)
cars[["weight", "weight_kg", "displacement", "engine_l"]].head(3)
cars["l_per_100km"] = (235.215 / cars["mpg"]).round(1)
cars[["name", "mpg", "l_per_100km"]].head(3)
Quick check: does a higher number mean a better car in both units?
cars.nsmallest(3, "l_per_100km")[["name", "mpg", "l_per_100km"]]
cars["year"] = cars["model_year"] + 1900
cars["origin"] = cars["origin"].astype("category")
cars[["model_year", "year", "origin"]].dtypes
auto = pd.read_csv("data/pandas/mpg.csv").convert_dtypes()
auto.dtypes.head(5)
cars.groupby("cylinders")["engine_l"].median()
Common mistake: forcing a column with gaps into plain
int.
cars["horsepower"].astype(int)
KW_PER_HP = 0.7457
cars["hp"] = cars["horsepower"].astype("Int64")
cars["power_kw"] = (cars["horsepower"] * KW_PER_HP).round()
print(cars["hp"].isna().sum(), "cars with no horsepower")
cars[["hp", "power_kw"]].head(3)
Your turn: add the weight in tonnes. Which car is heaviest?
cars["weight_t"] = cars["weight_kg"] / 1000
cars.nlargest(1, "weight_t")[["name", "weight", "weight_t"]]
Mini-project: did cars get thriftier?#
Fuel use and weight in metric, from 1970 to 1982.
per_year = cars.groupby("year")["l_per_100km"].median()
per_year.iloc[[0, 6, -1]]
table = cars.pivot_table(index="year", columns="origin",
values="l_per_100km", aggfunc="median",
observed=True)
table.iloc[[0, -1]]
drop = (1 - table.iloc[-1] / table.iloc[0]) * 100
drop.round()
import matplotlib.pyplot as plt
ax = table.plot(figsize=(8, 4), marker="o")
ax.set_title("Median fuel use by model year and origin")
ax.set_xlabel("Model year")
ax.set_ylabel("Litres per 100 km (lower is better)")
plt.show()
cars.groupby("origin", observed=True)["weight_kg"].median()
cars["kw_per_t"] = cars["power_kw"] / cars["weight_t"]
cars.groupby("origin", observed=True)["kw_per_t"].median().round(1)
import os
os.makedirs("output/pandas", exist_ok=True)
cars.to_csv("output/pandas/l18_cars_metric.csv", index=False)
back = pd.read_csv("output/pandas/l18_cars_metric.csv")
print(back["origin"].dtype)
back = pd.read_csv("output/pandas/l18_cars_metric.csv",
dtype={"origin": "category"})
print(back["origin"].dtype)
Finding: American cars cut fuel use from 15.7 to 8.4 L/100 km. They still used the most fuel, and weighed the most.
Recap#
- Check
dtypesfirst: text that looks like numbers won't add up pd.to_datetime(format=...)andpd.to_numeric(errors="coerce")Int64keeps whole numbers with gaps;stringfor clean textcategoryanddowncastshrink memory; avoid blindint8- Convert units with named constants; put the unit in the name
- CSV forgets types: pass
dtype=when you read it back
Challenges#
- Add a
revenuecolumn to the shop data. Which month earned most? - Convert
acceleration(seconds to 60 mph) into km/h per second. - Find the 5 products with the most cancelled units.
What's next#
Lesson 19: String Operations and Text Cleaning in Pandas. Splitting, searching and extracting from messy text.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.
Related lessons
26 minLesson 4
Data Mining with Python: pandas & scikit-learn Crash Course
21 minLesson 14
How to Change Data Types and Convert Units in Pandas for Accurate Data Analysis
23 minLesson 11
Clean Missing & Dirty Operations Data in pandas
Lesson 11