Lesson 15

Column Types and astype

Read the dtype of every column, convert one or several of them with astype, and handle the text that will not turn into a number.

Intermediate25 min

Column Types and astype hero Module 24 Lesson 15

Every column has one dtype. Data that arrives from a file is often text, and text does not add up, so the first cleaning step is usually converting the columns that should be numbers.

A table of text columns with one column converted to whole numbers

Look before you convert

dtypes lists the type of every column. object almost always means Python strings. Numbers stored as text still print without quotes inside a table, so check the dtype rather than the look of it.

python

import pandas as pd

fees = pd.DataFrame({
    "course": ["Python", "NumPy", "pandas"],
    "fee": ["2400", "1850", "2100"],
    "days": ["30", "45", "35"],
})
print(fees.dtypes)
print(fees["fee"].sum())

Output:

text

course    object
fee       object
days      object
dtype: object
240018502100

sum on a text column joins the strings instead of adding them, which is the clearest sign that a conversion is missing.

Convert one column

Each column is a Series, so Series.astype does the work and you assign the result back. int64 and float64 are the usual targets.

python

import pandas as pd

fees = pd.DataFrame({
    "course": ["Python", "NumPy", "pandas"],
    "fee": ["2400", "1850", "2100"],
})
fees["fee"] = fees["fee"].astype("int64")
print(fees.dtypes)
print(fees["fee"].sum())

Output:

text

course    object
fee        int64
dtype: object
6350

Convert several with a dict

astype on the DataFrame takes a dict of column name to type, so one call fixes a whole table. Passing a single type converts every column, which only works when every column can take it.

python

import pandas as pd

fees = pd.DataFrame({
    "course": ["Python", "NumPy", "pandas"],
    "fee": ["2400", "1850", "2100"],
    "days": ["30", "45", "35"],
})
clean = fees.astype({"fee": "int64", "days": "float64"})
print(clean.dtypes)
print(clean)

Output:

text

course     object
fee         int64
days      float64
dtype: object
   course   fee  days
0  Python  2400  30.0
1   NumPy  1850  45.0
2  pandas  2100  35.0

When the text is not a number

One bad value stops the whole conversion with a ValueError. pd.to_numeric with errors="coerce" turns whatever it cannot read into NaN, so you can find the bad rows and decide what they deserve.

python

import pandas as pd

fees = pd.DataFrame({
    "course": ["Python", "NumPy", "pandas"],
    "fee": ["2400", "n/a", "2100"],
})
fees["fee"] = pd.to_numeric(fees["fee"], errors="coerce")
print(fees)
print(fees["fee"].isna().sum())
print(fees.dropna(subset="fee"))

Output:

text

   course     fee
0  Python  2400.0
1   NumPy     NaN
2  pandas  2100.0
1
   course     fee
0  Python  2400.0
2  pandas  2100.0

The column becomes float64, because NaN is a float. Fill or drop the gaps first if you need whole numbers.

Useful target types

category stores a repeated label once and refers to it, which saves a lot of memory on columns such as a city or a track. string is the modern text type, and bool reads yes and no columns.

python

import pandas as pd

learners = pd.DataFrame({
    "name": ["Avery", "Jordan", "Casey", "Riley", "Morgan", "Quinn"],
    "track": ["Python", "NumPy", "Python", "pandas", "NumPy", "pandas"],
    "city": ["Northgate", "Eastport", "Northgate", "Westfield", "Eastport", "Westfield"],
    "hours": [42, 31, 55, 24, 47, 38],
    "score": [88, 74, 91, 65, 82, 79],
})
learners["track"] = learners["track"].astype("category")
learners["city"] = learners["city"].astype("string")
print(learners.dtypes)
print(learners["track"].cat.categories.tolist())

Output:

text

name             object
track          category
city     string[python]
hours             int64
score             int64
dtype: object
['NumPy', 'Python', 'pandas']

A category column keeps the list of possible labels under .cat, and stores each row as a small number that points into that list.

Key takeaways

dtypes tells you what each column really holds, and object means text
astype on a Series converts one column; a dict converts several
One unreadable value makes astype raise a ValueError
pd.to_numeric with errors="coerce" turns the bad values into NaN
Next: Working with Missing Data
Previous: Sorting Data

Loading playground…

Check your understanding

Column Types and astype Quiz

What does an object dtype usually mean?

1 / 10

← Sorting Data

Working with Missing Data →