Types and missing values

In short: Every column in pandas has a type (dtype) that decides what you can do with it, and missing values (NaN) are found with isna and filled with fillna or dropped with dropna.

Problem

“Which day do people order the most?”

The manager wants to know when to have more people in the warehouse. The file year_orders.csv has 11,116 orders for one year, each with a date and a time. It looks like one command: take the day from the date and count.

Pythondata: the shop tables

import pandas as pd

orders = pd.read_csv('year_orders.csv')
print(orders.dtypes)
orders['ordered_at'].dt.day_name()

import pandas as pd orders = pd.read_csv('year_orders.csv') print(orders.dtypes) orders['ordered_at'].dt.day_name()

▶ RunRestore the code

An error. dtypes says why: ordered_at is str, text. To us “2025-11-01 07:20” is a date, but read_csv sees digits, dashes and a colon, and it does not guess.

The type of a column decides what you can do with it. In this lesson you will see how to change it, which types save memory and give an order, and what happens when a value is missing.

The idea

Every column has a type: astype

Every column of a DataFrame has one type (dtype). You have seen int64 and float64 for numbers and str for text. There is also bool for True and False, datetime64 for dates and category for a few values that repeat.

read_csv guesses the type from the text in the file. When it guesses wrong, or when you need another one, astype makes a column of a new type. In products the category is float64, because the gift card has no category, and NaN is a decimal number. Whole numbers with gaps are kept by the type Int64 (with a capital I):

Pythondata: the shop tables

import pandas as pd

products = pd.read_csv('products.csv')
print(products['category_id'].dtype)
category = products['category_id'].astype('Int64')
print(category.dtype)
print(category.tail(3))

import pandas as pd products = pd.read_csv('products.csv') print(products['category_id'].dtype) category = products['category_id'].astype('Int64') print(category.dtype) print(category.tail(3))

▶ RunRestore the code

The numbers are whole again, and the gap is <NA>: the sign for “nothing” in the newer pandas types. With astype(int) you would get the error IntCastingNaNError, because a plain int has no room for a gap.

1. prices holds the prices of the first four products: 1.2, 1.9, 0.8 and 2.5. What does prices.astype(int) give?

An error: a decimal cannot become an integer1, 2, 1, 2: it rounds to the nearest1, 1, 0, 2: the fraction is cut off

Check

See it

Few values, many rows: category

The column status has 11,116 rows but only five different words. As text, each word is stored in every row. The type category keeps the five words once, and for each row only the number of its word:

Pythondata: the shop tables

import pandas as pd

orders = pd.read_csv('year_orders.csv')
print(orders['status'].memory_usage(deep=True))
status = orders['status'].astype('category')
print(status.memory_usage(deep=True))
print(status.cat.categories)

import pandas as pd orders = pd.read_csv('year_orders.csv') print(orders['status'].memory_usage(deep=True)) status = orders['status'].astype('category') print(status.memory_usage(deep=True)) print(status.cat.categories)

▶ RunRestore the code

377,057 bytes become 11,345, about 33 times less. In the shop's year this is small, but in a table with millions of rows it is the difference between “fits in memory” and “does not fit”.

A category has a second use too: an order. The statuses are not an alphabet but a path: new, paid, shipped, delivered. CategoricalDtype with ordered=True states that order:

Pythondata: the shop tables

import pandas as pd

orders = pd.read_csv('year_orders.csv')
# The path of an order: new, paid, shipped, delivered. Cancelled ones are off the path, at the end.
steps = pd.CategoricalDtype(['new', 'paid', 'shipped', 'delivered', 'cancelled'], ordered=True)
orders['status'] = orders['status'].astype(steps)
print(orders['status'].value_counts(sort=False))
print((orders['status'] < 'delivered').sum())

import pandas as pd orders = pd.read_csv('year_orders.csv') # The path of an order: new, paid, shipped, delivered. Cancelled ones are off the path, at the end. steps = pd.CategoricalDtype(['new', 'paid', 'shipped', 'delivered', 'cancelled'], ordered=True) orders['status'] = orders['status'].astype(steps) print(orders['status'].value_counts(sort=False)) print((orders['status'] < 'delivered').sum())

▶ RunRestore the code

value_counts(sort=False) counts in the order of the path, not by count. The comparison < 'delivered' means “before delivery”: 205 orders are still on their way. The cancelled ones are put at the end, off the path.

Try it

The statuses along the path

Make the column status a category with the order new, paid, shipped, delivered, cancelled, and store in status_counts how many orders there are at each step, in that order.

Pythondata: the shop tables

import pandas as pd

orders = pd.read_csv('year_orders.csv')
steps = ...
orders['status'] = ...
status_counts = ...
status_counts

import pandas as pd orders = pd.read_csv('year_orders.csv') steps = ... orders['status'] = ... status_counts = ... status_counts

Check▶ Run

The idea

Dates: pd.to_datetime and .dt

pd.to_datetime turns the text into dates. The column gets the type datetime64 and now knows the calendar. The parts of a date come from .dt: .dt.year, .dt.month, .dt.day_name() and many more:

Pythondata: the shop tables

import pandas as pd

orders = pd.read_csv('year_orders.csv')
orders['ordered_at'] = pd.to_datetime(orders['ordered_at'])
print(orders['ordered_at'].dtype)
print(orders['ordered_at'].min(), orders['ordered_at'].max())
when = orders['ordered_at']
pd.DataFrame({'ordered_at': when, 'year': when.dt.year, 'month': when.dt.month, 'day': when.dt.day_name()}).head(3)

import pandas as pd orders = pd.read_csv('year_orders.csv') orders['ordered_at'] = pd.to_datetime(orders['ordered_at']) print(orders['ordered_at'].dtype) print(orders['ordered_at'].min(), orders['ordered_at'].max()) when = orders['ordered_at'] pd.DataFrame({'ordered_at': when, 'year': when.dt.year, 'month': when.dt.month, 'day': when.dt.day_name()}).head(3)

▶ RunRestore the code

The year runs from 1 November 2025, 7:20, to 31 October 2026, 23:01. The first order is on a Saturday. .dt works only on dates: on the text at the start it gave the error.

More on dates (by week, by month, a moving average) is in the lesson on time.

1. when is a column of dates. The first order is from 1 November 2025, a Saturday. What does when.dt.day give for it?

Saturday5: Saturday is the sixth day, counting from 01: the day of the month

Check

The idea

Text: .str

What .dt is for dates, .str is for text: it applies a string method to every row at once. You saw str.contains with filters. There is also str.lower() and str.upper(), str.strip() (removes the spaces at the ends), str.len(), str.split() and slicing as with a string: str[:14].

The shelf labels fit 14 characters. Which names do not fit, and how will they look when cut?

Pythondata: the shop tables

import pandas as pd

products = pd.read_csv('products.csv')
names = products['name']
print((names.str.len() > 14).sum())
long = products[names.str.len() > 14]
long[['name']].assign(label=long['name'].str[:14].str.upper())

import pandas as pd products = pd.read_csv('products.csv') names = products['name'] print((names.str.len() > 14).sum()) long = products[names.str.len() > 14] long[['name']].assign(label=long['name'].str[:14].str.upper())

▶ RunRestore the code

4 names are longer than 14 characters. str[:14] cuts them, and str.upper() puts them in capitals for the label. The result is a column again, so assign puts it next to the name.

The idea

Missing values: isna and notna

Almost every real table has something missing: a customer did not give their city, the till did not send a report. pandas writes the gap as NaN (NaT for dates, <NA> for the newer types). isna() gives a mask that is True where a value is missing, and .sum() on it counts the gaps in each column:

Pythondata: the shop tables

import pandas as pd

customers = pd.read_csv('customers.csv')
print(customers.isna().sum())
customers[customers['city'].isna()]

import pandas as pd customers = pd.read_csv('customers.csv') print(customers.isna().sum()) customers[customers['city'].isna()]

▶ RunRestore the code

One customer has no city. notna() is the opposite mask: True where there is a value. You filter with both masks as with any condition.

See it

Why 0 is not “missing”

The banitsa and the cucumbers have stock 0. Is that a gap?

Pythondata: the shop tables

import pandas as pd

products = pd.read_csv('products.csv')
print(products['stock'].isna().sum())
print((products['stock'] == 0).sum())
products.loc[products['stock'] == 0, ['name', 'stock']]

import pandas as pd products = pd.read_csv('products.csv') print(products['stock'].isna().sum()) print((products['stock'] == 0).sum()) products.loc[products['stock'] == 0, ['name', 'stock']]

▶ RunRestore the code

isna() finds nothing in stock: the zeros are real values. 0 means “we know there are none”, and NaN means “we do not know”.

Now the takings by day from the lesson on summaries, but as a pandas Series. One important difference: NumPy gave nan for the mean, and pandas skips the gaps:

Pythondata: the shop tables

import pandas as pd

# Takings by day, Monday to Sunday. The Wednesday report is missing.
revenue = pd.Series([412.5, 388.0, None, 455.2, 501.3, 620.8, 590.4])
print(revenue.count(), len(revenue))
print(round(revenue.mean(), 2))
print(round(revenue.sum(), 2))
print(round(revenue.fillna(0).mean(), 2))

import pandas as pd # Takings by day, Monday to Sunday. The Wednesday report is missing. revenue = pd.Series([412.5, 388.0, None, 455.2, 501.3, 620.8, 590.4]) print(revenue.count(), len(revenue)) print(round(revenue.mean(), 2)) print(round(revenue.sum(), 2)) print(round(revenue.fillna(0).mean(), 2))

▶ RunRestore the code

count() counts 6 known days out of 7. Their mean is €494.70, as with np.nanmean. The sum, €2,968.20, quietly leaves out Wednesday, though: pandas does not warn you. With fillna(0) the mean drops to €424.03, because the zero says “we sold nothing on Wednesday”, and that is not true.

More precisely, if you are curious

The skipping is controlled by the parameter skipna: revenue.mean(skipna=False) gives nan, like NumPy. That is useful when you would rather have the result flag a gap than quietly give a smaller number.

The idea

fillna, dropna or nothing

There are three ways to handle a gap, and the choice depends on the question, not on the code:

• fillna(value), when you know what the gap means. In a report of “customers by city”, a missing city is “unknown”: it is an answer, not an invention.
• dropna(), when the row cannot answer the question. In a report by category, the gift card has nowhere to go. subset=[...] looks only at the given columns.
• Nothing, when you do not know. Wednesday's takings are not 0, and the mean of the known days is more honest.

Pythondata: the shop tables

import pandas as pd

products = pd.read_csv('products.csv')
with_category = products.dropna(subset=['category_id'])
print(len(products), len(with_category))
print(products['category_id'].notna().sum())
customers = pd.read_csv('customers.csv')
customers['city'].fillna('unknown').value_counts().tail(3)

import pandas as pd products = pd.read_csv('products.csv') with_category = products.dropna(subset=['category_id']) print(len(products), len(with_category)) print(products['category_id'].notna().sum()) customers = pd.read_csv('customers.csv') customers['city'].fillna('unknown').value_counts().tail(3)

▶ RunRestore the code

dropna removed the gift card: of 25 products, 24 remain, as many as notna() counts in the category. In a report of “how many products we sell” that would be a mistake. fillna('unknown') makes the missing city a row of its own in value_counts.

Try it

Customers by city

Marketing wants to know where the customers are from. Store in missing_city how many customers have no city, and in by_city how many customers there are in each city, with the missing city counted as “unknown”.

Pythondata: the shop tables

import pandas as pd

customers = pd.read_csv('customers.csv')
missing_city = ...
by_city = ...
by_city

import pandas as pd customers = pd.read_csv('customers.csv') missing_city = ... by_city = ... by_city

Check▶ Run

Exercise

The day with the most orders

Answer the manager. Make ordered_at a date, put the name of the weekday in a new column day, store in by_day how many orders there are on each day, and in best_day the day with the most orders.

Pythondata: the shop tables

import pandas as pd

orders = pd.read_csv('year_orders.csv')
orders['ordered_at'] = ...
orders['day'] = ...
by_day = ...
best_day = ...
by_day

import pandas as pd orders = pd.read_csv('year_orders.csv') orders['ordered_at'] = ... orders['day'] = ... by_day = ... best_day = ... by_day

Check▶ Run

Test

Check yourself

1. The column ordered_at was read from a CSV and has type str. Why does orders['ordered_at'].dt.month give an error?

.dt works only with dates, and the column is text: pd.to_datetime firstBecause the column also has the timeBecause the month comes from .str.month

2. The column status has 11,116 rows but only 5 different values. What do you gain with astype('category')?

Memory drops a lot (about 33 times here), and with ordered=True the statuses have an order tooArithmetic with the column becomes more preciseNothing: the values are the same

3. In the takings by day, one day is NaN. What does revenue.mean() give in pandas?

The mean of the known days: pandas skips NaNAn errornan, as in NumPy

4. Two products have stock 0, and one has category NaN. Which of them is “missing”?

Only the zeros: an empty shelf means missingOnly the category: 0 units is a known valueAll three

5. You are making a report of “how many products we sell”, and the gift card has no category. What do you do with it?

dropna(), so that the table has no NaNfillna(0) in category_idKeep it: having no category does not make it less of a product

Check

Start the lesson over

Related lessons

• Builds on: Series and DataFrame · Selecting: [], loc and iloc · Filters and sorting · New columns
• Continues in: Groups: groupby · Joining: merge · Wide and long: pivot_table and melt · Dates and time series
• Tasks on this topic: Tables with pandas