Categorical, String and Datetime Data

Context

Three kinds of column get their own tools in pandas. A text column takes Python's string methods, applied to every value at once, a column of repeated labels can be stored as a category, a short list of distinct labels plus one small code per row, and a column of date strings can be parsed into real timestamps whose parts and differences come back as numbers.

This is the work between loading a partner's export and counting anything in it. Neighborhood names typed by different agents have to agree before a count by neighborhood means anything, a condition grade has to rank from poor to excellent rather than alphabetically before a model can use its order, and a listing date has to become a month, a weekday or a number of days on the market before a model can read it at all.

Skip it and the failures are quiet. Three spellings of one neighborhood become three neighborhoods in every count by neighborhood, a grade stored as text names poor the best condition because p comes after e, and a date like 9/1/2023 compared as text puts September after October because the character 9 beats the character 1. A cast to a category list that misses one spelling turns that value into a missing one with no more than a warning, and a days-on-market feature measured from the day you ran the script changes every day you run it. The previous lesson gave every numeric column the right type, this lesson does the same for text, labels and dates.

Goal

• Normalize a text column with .str.strip(), .str.lower() and .str.replace() before counting it, and filter it with .str.contains() without losing track of its missing values.
• Convert a repeated-label column to category, read its categories and codes, and measure the memory it saves.
• Build an ordered category for a grade scale, compare and sort it by meaning, and prove that a cast lost no values.
• Parse date strings with pd.to_datetime, using errors="coerce" and format= for messy and day-first input, or parse_dates at load time.
• Build calendar and days-on-market features with the .dt accessor, measured from a fixed reference date.

Prerequisites

Optional, and the lesson stands without either. They are here if you want to see a text column cleaned, or a date column parsed, before you do it yourself.

• YouTube - "How do I use string methods in pandas?" by Data School. How a string method is called on a whole column through .str, with a few examples.
• Official docs - pandas User Guide, "Time series / date functionality", the "Converting to timestamps" section. How to_datetime reads strings, and why a parsed column prints datetime64[us] in pandas 3.

Table of Contents

• The str Accessor
• The category dtype
• Parsing Dates with to_datetime
• The dt Accessor and Time Deltas

Theory

Nothing to install. Each subtitle starts a fresh script whose first block imports pandas and builds or loads what that subtitle uses, and housing.csv is the download you saved into ~/dev/bootcamp in Lesson 2.4.1.

The str Accessor

What. .str gives a text column the string methods of Lesson 1.5.1, .str.strip(), .str.lower(), .str.replace() and the rest, and runs each one on every value at once, returning a new Series, or a new DataFrame when .str.split() is given expand=True. Why. People type labels inconsistently, and value_counts, nunique and every grouping treat each spelling as a label of its own. Fixing a column value by value would be a Module 1 loop, and .str does it in one call that also steps around missing values. When. Before you count, group, deduplicate or filter a text column, and before you convert text that holds a number, such as a price written with a currency sign.

Most methods under .str are Python string methods of the same name, applied to each value in turn. Six agents typing two neighborhoods is enough to show the problem:

import pandas as pd

# Six agents typed two neighborhoods five ways and left one blank.
typed = pd.Series([" downtown", "Downtown ", "DOWNTOWN", "Riverside", None, "riverside"])
print(typed.value_counts(dropna=False))

 downtown    1
Downtown     1
DOWNTOWN     1
Riverside    1
NaN          1
riverside    1
Name: count, dtype: int64

Five spellings and a gap, one listing each, where the city has two neighborhoods. Two chained methods fix it, because each returns a new Series and the next method runs on that:

# trim both ends, then lowercase, every value in one call
clean = typed.str.strip().str.lower()
print(clean.value_counts(dropna=False))

downtown     3
riverside    2
NaN          1
Name: count, dtype: int64

The missing value came through as NaN instead of raising, which is what every method that transforms text does with it. .str.title() (it capitalizes the first letter of each word and lowercases the rest) turns the clean labels into the form a report shows:

print(clean.str.title().tolist())  # ['Downtown', 'Downtown', 'Downtown', 'Riverside', nan, 'Riverside']

Two methods answer a question about each value instead of changing it. .str.len() counts characters and .str.contains(text) reports whether a piece of text appears, and the two treat the missing value differently:

lengths = clean.str.len()
print(lengths.tolist())   # [8.0, 8.0, 8.0, 9.0, nan, 9.0]
print(lengths.dtype)      # float64
has_town = clean.str.contains("town")
print(has_town.tolist())  # [True, True, True, False, False, False]
print((~has_town).sum())  # 3

.str.len() gives the missing value NaN, which makes the whole result float64 for the reason Lesson 2.5.2 gave a gapped integer column. .str.contains() on a str column reports the missing value as False, so ~has_town counts it alongside the two Riverside listings, 3 where only 2 labels lack "town".

.str.replace(old, new) swaps every occurrence of a piece of text, and in pandas 3.0.5 it reads old as plain text, so "$" means the dollar sign. .str.contains does not. It reads its pattern as a regular expression (a small pattern language in which some characters are commands, $ meaning the end of the text and . meaning any character) unless you pass regex=False. Replacing is the cleaning step that comes before Lesson 2.5.2's pd.to_numeric, and the two contains calls show the difference:

asking = pd.Series(["$310,000", "245000", "$1,250,000", None])
print(asking.str.contains("$").tolist())  # [True, True, True, False]
has_sign = asking.str.contains("$", regex=False)
print(has_sign.tolist())                  # [True, False, True, False]
digits = asking.str.replace("$", "").str.replace(",", "")
print(digits.tolist())                    # ['310000', '245000', '1250000', nan]
price = pd.to_numeric(digits)
print(price.tolist())                     # [310000.0, 245000.0, 1250000.0, nan]

.str.split(sep) cuts each value at a separator and returns a list per value. With expand=True it returns the pieces as the columns of a new DataFrame instead, which is how one column holding two facts becomes two columns:

zone = pd.Series(["Downtown-North", "Riverside-East", None])
print(zone.str.split("-").tolist())
parts = zone.str.split("-", expand=True)  # one column per piece
print(parts)

[['Downtown', 'North'], ['Riverside', 'East'], nan]
           0      1
0   Downtown  North
1  Riverside   East
2        NaN    NaN

On housing.csv the check costs one line. Its neighborhood labels were typed consistently, so normalizing them changes nothing:

df = pd.read_csv("housing.csv")
print(df["neighborhood"].nunique())  # 7
tidy = df["neighborhood"].str.strip().str.title()
print(tidy.nunique())                # 7

DON'T:

• DON'T count, group or deduplicate a text column before normalizing it. " downtown", "Downtown " and "DOWNTOWN" are three labels to value_counts until .str.strip().str.lower() makes them one.
• DON'T read ~s.str.contains(text) as every other label. On a str column a missing value is False in .str.contains, so the complement counts it too, and s.notna() & ~s.str.contains(text) marks only the named labels without the text.
• DON'T search for a symbol with .str.contains and its default pattern rules. asking.str.contains("$") is True for "245000" too, because $ means the end of the text, so pass regex=False.
• DON'T call .str on a numeric column. df["rooms"].str.len() raises AttributeError: Can only use .str accessor with string values, not integer. Did you mean: 'std'?.

The category dtype

What. A category column stores its distinct labels once, as its categories, and one small integer code per row that points into them. astype("category") converts a column, and .cat reaches the categories and the codes. Why. neighborhood repeats 7 labels over 209 rows, and as text memory_usage(deep=True) counts the word once for every row. As a category it stores 7 words and 209 one-byte codes. A category can also carry an order, which a grade scale needs and plain text cannot express. When. For a column whose distinct values are few next to its rows, a neighborhood, a property type, a condition grade, and for any scale whose labels rank, from poor to excellent.

memory_usage(deep=True) from Lesson 2.5.2 measures the difference, and on a Series it counts the index too, 132 bytes here:

import pandas as pd

df = pd.read_csv("housing.csv")
nb = df["neighborhood"]
print(nb.memory_usage(deep=True))      # 12143
nb_cat = nb.astype("category")
print(nb_cat.memory_usage(deep=True))  # 743
print(nb_cat.dtype)                    # category

Every value is still the same label, and only the storage changed. The categories and the codes sit under .cat:

print(nb_cat.cat.categories)
print(nb_cat.cat.codes.head(3).tolist())
print(nb_cat.cat.codes.dtype)

Index(['Downtown', 'Eastgate', 'Hillcrest', 'Lakeview', 'Northend',
       'Riverside', 'Southgate'],
      dtype='str')
[3, 0, 6]
int8

The categories come out sorted, and each code is a position in that list, so the first listing, in Lakeview, carries 3. The codes are int8, one byte per row, and a missing value gets the code -1:

cond_cat = df["condition"].astype("category")
print(cond_cat.cat.codes.loc[55])  # -1, row 55 has no condition

A code is a position in the category list, not a measurement, and turning categories into numbers a model can use is Module 3's encoding lesson, 3.2.2.

An ordered category ranks its labels in the order you list them. pd.CategoricalDtype(labels, ordered=True) builds that type once, and astype applies it:

grade = pd.CategoricalDtype(["poor", "fair", "good", "excellent"], ordered=True)
graded = df["condition"].astype(grade)
print((df["condition"] > "fair").sum())  # 118
print((graded > "fair").sum())           # 128
print(df["condition"].max(), graded.max())
print(graded.value_counts(sort=False))

poor excellent
condition
poor         24
fair         56
good         94
excellent    34
Name: count, dtype: int64

As text, "good" and "poor" both sort after "fair", so the comparison counts the 94 good and the 24 poor listings, and max() names poor the best condition. The ordered grade counts the 94 good and the 34 excellent ones, and value_counts(sort=False) lists the grades in their own order.

A value that is not in the category list does not survive the cast. Save this as grade_cast.py and run it with python3 grade_cast.py:

import pandas as pd

grade = pd.CategoricalDtype(["poor", "fair", "good", "excellent"], ordered=True)
typed = pd.Series(["good", "fair", "Good", "excelent", None])
print(typed.isna().sum())
graded = typed.astype(grade)
print(graded.isna().sum())
print(graded.tolist())

1
/home/goku/dev/bootcamp/grade_cast.py:6: Pandas4Warning: Constructing a Categorical with a dtype and values containing non-null entries not in that dtype's categories is deprecated and will raise in a future version.
  graded = typed.astype(grade)
3
['good', 'fair', nan, nan, nan]

"Good" and "excelent" are not among the four categories, so both became missing values and the count went from 1 to 3. The label "unknown" that Lesson 2.5.1 filled into condition's gap meets the same fate, since no grade on the scale means unknown. pandas 3.0.5 warns that this will raise an error in a future version, and today it converts and carries on. The warning is a deprecation warning, a kind Python shows for code in the script you run and hides by default in a module you import, so, run with python3, the same cast inside a cleaning function you import from a module of your own prints nothing at all. The missing count before and after the cast is the check that always works.

DON'T:

• DON'T cast to a category list you have not checked against the data. Every value outside it becomes missing, so compare the missing count before and after the cast, or find the strays first with isin.
• DON'T compare or sort a grade stored as text. > and max() work alphabetically, and an unordered category refuses > with TypeError: Unordered Categoricals can only compare equality or not.
• DON'T convert a column whose values are nearly all distinct, such as a listing ID. The category list then holds every value, the codes come on top, and the column can take more memory than the text it replaced.

Parsing Dates with to_datetime

What. pd.to_datetime turns date strings into timestamps, a dtype pandas 3.0.5 prints as datetime64[us], a point in time stored to the microsecond. A missing date becomes NaT, Lesson 2.5.1's date marker, which isna() counts like NaN. Why. A date read from a CSV is text. Text cannot be subtracted, has no month to read, and compares character by character, so durations, months and the order of any date not written year first wait until the column is parsed. When. Right after loading, before any filter, sort or feature that uses the date, or during the load itself with parse_dates.

import pandas as pd

df = pd.read_csv("housing.csv")
print(df["listing_date"].dtype)  # str
listed = pd.to_datetime(df["listing_date"])
print(listed.dtype)              # datetime64[us]
print(listed[listed.isna()])

6     NaT
57    NaT
115   NaT
Name: listing_date, dtype: datetime64[us]

Tutorials written for pandas 2 show datetime64[ns], nanoseconds, because pandas 2 parsed every string to that resolution, and pandas 3 parses to microseconds unless a string carries more precision. The three rows whose date was empty in the file are NaT, and an empty string inside a Series becomes NaT the same way:

raw = pd.Series(["2024-03-15", "", "2023-11-02", None])
print(pd.to_datetime(raw))

0   2024-03-15
1          NaT
2   2023-11-02
3          NaT
dtype: datetime64[us]

A value that is not a date at all stops the parse. pandas works out the layout from the first value that is not missing or empty, here %Y-%m-%d, a four-digit year, a month and a day joined by hyphens, and refuses any value that does not match it, naming that value:

messy = pd.Series(["2024-03-15", "soon", "2023-11-02"])
try:
    pd.to_datetime(messy)
except ValueError as err:
    print(err)

time data "soon" doesn't match format "%Y-%m-%d". You might want to try:
    - passing `format` if your strings have a consistent format;
    - passing `format='ISO8601'` if your strings are all ISO8601 but not necessarily in exactly the same format;
    - passing `format='mixed'`, and the format will be inferred for each element individually. You might want to use `dayfirst` alongside this.

errors="coerce" turns every value that cannot be parsed into NaT and says nothing, the same silent trade Lesson 2.5.2's to_numeric makes, so count before and after:

before = messy.isna().sum()
coerced = pd.to_datetime(messy, errors="coerce")
print(before, coerced.isna().sum())  # 0 1

A layout pandas cannot tell apart is worse than one it refuses. "05/01/2024" is the fifth of January to a writer who puts the day first, and the first of May to pandas, which reads the month first by default. format= states the layout, %d for the day, %m for the month and %Y for the four-digit year:

day_first = pd.Series(["05/01/2024", "06/01/2024"])
print(pd.to_datetime(day_first))
print(pd.to_datetime(day_first, format="%d/%m/%Y"))

0   2024-05-01
1   2024-06-01
dtype: datetime64[us]
0   2024-01-05
1   2024-01-06
dtype: datetime64[us]

Without format= both dates parsed without a word, and both are wrong. Only a value whose day is above 12 gives the guess away, because there is no month 13. read_csv parses at load time when you name the columns in parse_dates:

df = pd.read_csv("housing.csv", parse_dates=["listing_date"])
print(df["listing_date"].dtype)  # datetime64[us]

DON'T:

• DON'T compare or sort dates while they are still text. Characters are compared one at a time, so "9/1/2023" > "10/1/2023" is True because "9" beats "1", and only year-first, zero-padded text such as YYYY-MM-DD happens to sort in date order.
• DON'T let pandas guess a day-first layout. "05/01/2024" parses as the first of May without a word, and only a later value such as "13/01/2024" stops the guess, with time data "13/01/2024" doesn't match format "%m/%d/%Y". Pass format=.
• DON'T pass errors="coerce" and move on. Count the missing values before and after, and print the raw values that failed, before you decide they were junk.

The dt Accessor and Time Deltas

What. .dt is to a datetime column what .str is to a text column: .dt.year, .dt.month, .dt.day_name() and .dt.dayofweek read one part of every timestamp at once. Subtracting one timestamp from another gives a Timedelta, a length of time, and .dt.days turns a column of them into numbers. Why. A model reads numbers, not timestamps. The month a listing went up, whether it went up on a weekend and how long it has been on the market are features, and each is one .dt call away once the column is parsed. When. After parsing, whenever a date has to become a feature, a filter or a duration.

import pandas as pd

df = pd.read_csv("housing.csv", parse_dates=["listing_date"])
listed = df["listing_date"]
print(listed.dt.year.head(3).tolist())       # [2023.0, 2024.0, 2023.0]
print(listed.dt.month.head(3).tolist())      # [4.0, 10.0, 6.0]
names = listed.dt.day_name()
print(names.head(3).tolist())                # ['Saturday', 'Sunday', 'Friday']
print(listed.dt.dayofweek.head(3).tolist())  # [5.0, 6.0, 4.0]

The numbers are floats because three listing dates are NaT, and their missing years make the column float64 for the same reason a gapped integer column becomes one. Without the missing dates the same call gives int32:

print(listed.dt.year.dtype)           # float64
print(listed.dropna().dt.year.dtype)  # int32

.dt.dayofweek numbers the days from Monday as 0 to Sunday as 6, which makes a weekend flag one comparison. Comparing a datetime column with a string compares dates, because pandas parses the string into a timestamp first, and between from Lesson 2.4.2 keeps both ends:

weekend = listed.dt.dayofweek >= 5  # Saturday 5, Sunday 6
print(weekend.sum())                # 62
recent = df[listed >= "2024-07-01"]
print(len(recent))                  # 60
first_quarter = listed.between("2024-01-01", "2024-03-31")
print(first_quarter.sum())          # 29

The three undated listings are False in the weekend flag and fall out of both sides of a date filter, the rule Lesson 2.4.2 showed for a missing price. max() - min() measures how long the file covers:

span = listed.max() - listed.min()
print(span)       # 725 days 00:00:00
print(span.days)  # 725

Subtracting a whole column from one fixed timestamp gives a Timedelta per row, and .dt.days turns them into a feature a model can read:

snapshot = pd.Timestamp("2025-01-01")  # fixed, after the last listing
on_market = snapshot - listed
print(on_market.head(3))

0   627 days
1    66 days
2   572 days
Name: listing_date, dtype: timedelta64[us]

days = on_market.dt.days
print(days.head(3).tolist())  # [627.0, 66.0, 572.0]
print(days.dtype)             # float64

The reference date is a value written into the code. pd.Timestamp.today() would be shorter and wrong, because it moves every time the script runs, so the same listing in the same file gets a different number each day. Two reference dates half a year apart show what a model would be fed:

for today in ["2025-01-01", "2025-07-01"]:
    moved = (pd.Timestamp(today) - listed).dt.days
    print(today, moved.iloc[0])

2025-01-01 627.0
2025-07-01 808.0

Same listing, same file, 181 more days. A model trained on one run's numbers and fed a later run's sees every listing 181 days older than the same listing it learned from, though nothing in the data changed.

DON'T:

• DON'T build a feature on today's date. pd.Timestamp.today() moves every time the script runs, so the rows a model trains on and the rows it scores later are measured from different days.
• DON'T expect whole numbers from .dt.year or .dt.days on a column that holds NaT. They come back as float64, 2023.0, until the missing dates are handled.
• DON'T call .dt on a column that is still text. It raises AttributeError: Can only use .dt accessor with datetimelike values. Did you mean: 'at'?, so parse first.

Lab Exercises

Lab Exercise 1: Clean a Partner's Labels Into Categories

A partner agency typed its labels by hand. Start from this frame:

import pandas as pd

partner = pd.DataFrame({
    "neighborhood": ["Lakeview", " lakeview", "EASTGATE", "Eastgate ", "Hillcrest", None],
    "condition": ["good", "Excellent", "fair", " good", "poor", "GOOD"],
})

(a) Print the counts of neighborhood with the missing value shown, normalize the column to title case with no spaces at either end, assign it back, and print the counts again.

(b) Count the listings whose neighborhood contains "gate", the named listings whose neighborhood does not, and the listings with no neighborhood, and print the three counts, which must add up to 6.

(c) Normalize condition to lowercase with no spaces at either end, cast it to the ordered grade poor < fair < good < excellent, print the missing count before and after the cast, and print how many listings are in better than fair condition.

(d) On housing.csv, print the memory of the condition column as text and as a category.

Lab Exercise 2: Days on Market From Day-First Dates

The same partner writes its listing dates day first, DD/MM/YYYY, and two of them are not dates:

import pandas as pd

dates = pd.Series(["03/02/2024", "17/02/2024", "", "TBD", "28/12/2023", "05/01/2024"])

(a) Print how many values are missing before parsing. Parse the dates in their real layout, turning anything that cannot be parsed into NaT, print how many are missing after, and print the raw values that failed.

(b) Print the day name of every parsed date.

(c) Measure days on market from the fixed date 2024-03-01, print the numbers, and print how many listings went up in 2024 and how many have been on the market for more than 30 days.

import pandas as pd

partner = pd.DataFrame({
    "neighborhood": ["Lakeview", " lakeview", "EASTGATE", "Eastgate ", "Hillcrest", None],
    "condition": ["good", "Excellent", "fair", " good", "poor", "GOOD"],
})

# (a) five spellings and a gap, then three names and a gap
print(partner["neighborhood"].value_counts(dropna=False))
partner["neighborhood"] = partner["neighborhood"].str.strip().str.title()
print(partner["neighborhood"].value_counts(dropna=False))

neighborhood
Lakeview     1
 lakeview    1
EASTGATE     1
Eastgate     1
Hillcrest    1
NaN          1
Name: count, dtype: int64
neighborhood
Lakeview     2
Eastgate     2
Hillcrest    1
NaN          1
Name: count, dtype: int64

# (b) contains() is False for the missing name, so exclude it on purpose
nb = partner["neighborhood"]
has_gate = nb.str.contains("gate")
print(has_gate.sum())                  # 2
print((nb.notna() & ~has_gate).sum())  # 3
print(nb.isna().sum())                 # 1

# (c) normalize first, then cast, then prove nothing was lost
grade = pd.CategoricalDtype(["poor", "fair", "good", "excellent"], ordered=True)
cond = partner["condition"].str.strip().str.lower()
print(cond.isna().sum())               # 0
graded = cond.astype(grade)
print(graded.isna().sum())             # 0
print((graded > "fair").sum())         # 4

# (d) four labels over 209 rows
housing = pd.read_csv("housing.csv")
text_bytes = housing["condition"].memory_usage(deep=True)
print(text_bytes)                      # 11358
as_cat = housing["condition"].astype("category")
print(as_cat.memory_usage(deep=True))  # 558

import pandas as pd

dates = pd.Series(["03/02/2024", "17/02/2024", "", "TBD", "28/12/2023", "05/01/2024"])

# (a) "" is text, so nothing is missing yet
print(dates.isna().sum())             # 0
listed = pd.to_datetime(dates, format="%d/%m/%Y", errors="coerce")
print(listed.isna().sum())            # 2
print(dates[listed.isna()].tolist())  # ['', 'TBD']

# (b) a missing date has no day name
print(listed.dt.day_name().tolist())

['Saturday', 'Saturday', nan, nan, 'Thursday', 'Friday']

# (c) a fixed reference date, never today's
snapshot = pd.Timestamp("2024-03-01")
days = (snapshot - listed).dt.days
print(days.tolist())                   # [27.0, 13.0, nan, nan, 64.0, 56.0]
print((listed.dt.year == 2024).sum())  # 3
print((days > 30).sum())               # 2

Theory Assessment

Q1: Eleven Neighborhoods in a City of Seven

A teammate loads a partner's export and runs value_counts() on its neighborhood column. It lists 11 neighborhoods, where the city has 7. Name the most likely cause and the fix, and say why the fix has to come before any count. Then the teammate counts "every listing outside the two gate neighborhoods" with (~df["neighborhood"].str.contains("gate")).sum(), and the number is one higher than the listings they can find by name. Explain the extra row.

Hint: For the first part, ask what makes two strings unequal that a reader would never notice. For the second, ask what .str.contains answers for a missing value on a str column.

Q2: What a Category Stores, and Which Way It Sorts

Explain what a category column stores, why neighborhood takes 743 bytes as a category against 12143 as text, and what .cat.codes holds for a missing value. Then explain why max() on the text condition column returns "poor" while max() on the ordered grade returns "excellent".

Hint: Count how many times memory_usage(deep=True) counts each neighborhood name in each form. For the second part, ask what order each version of the column compares in.

Q3: Three Date Columns, Three Outcomes

Say what pd.to_datetime(s), with no other argument, does with each of these, and which option fixes each: ["2024-03-15", "soon"], then ["05/01/2024", "06/01/2024"] written day first, then ["05/01/2024", "13/01/2024"] written day first. Then say why a days-on-market feature is measured from a date written into the code.

Hint: pandas works out one layout from the first value that is not missing and applies it to all of them. Only one of the three fails without a word.

Q4: Five Numbers From One Short Script

Read this script, saved as audit.py, without running it:

import pandas as pd

grade = pd.CategoricalDtype(["poor", "fair", "good", "excellent"], ordered=True)
listings = pd.DataFrame({
    "condition": ["good", "Good ", "excellent", "fair", None],
    "listing_date": ["2024-12-30", "2024-12-02", "2025-01-15", "9/1/2024", ""],
})
listings["grade"] = listings["condition"].astype(grade)
listings["listed"] = pd.to_datetime(listings["listing_date"], errors="coerce")
print(listings["grade"].isna().sum())
print((listings["grade"] >= "good").sum())
print(listings["listed"].isna().sum())
print((listings["listing_date"] > "2024-12-01").sum())
print((listings["listed"] > "2024-12-01").sum())

Nothing raises. State what each print shows and what else appears on the screen, then explain why the last two lines disagree, and say which of the five numbers a report about this partner's data can trust as written.

Hint: Walk each value through each line. Ask which values the category list does not contain, which date the inferred layout cannot read, and how "9/1/2024" compares with "2024-12-01" character by character.

/home/goku/dev/bootcamp/audit.py:8: Pandas4Warning: Constructing a Categorical with a dtype and values containing non-null entries not in that dtype's categories is deprecated and will raise in a future version.
  listings["grade"] = listings["condition"].astype(grade)
2
2
2
4
3

Assignment

Assignment: The Partner Export, End to End

A partner agency sends its listings as the file below, and the next model run needs every column clean and typed. Save this bootstrap as make_partner.py in ~/dev/bootcamp and run it once, to create partner.csv:

csv_text = """neighborhood,asking,condition,listed
 lakeview,"$437,000",Fair,15/04/2024
Downtown ,"$268,000",good,27/04/2024
EASTGATE,"$1,250,000",Excellent,02/05/2024
eastgate,"$409,000", excellent ,02/05/2024
Hillcrest,,poor,TBD
riverside,"$310,000",GOOD,30/05/2024
Southgate ,"$227,000",fiar,04/06/2024
"""
with open("partner.csv", "w", encoding="utf-8") as f:
    f.write(csv_text)

Then write clean_partner.py, which reads partner.csv with no options and does the following, printing what each step asks for.

Store neighborhood as a category whose categories are the six neighborhoods in title case, and print them. Turn asking into a numeric price column and print the missing count before and after the conversion, which must agree. Turn condition into the ordered grade poor < fair < good < excellent with no real grade lost: before casting, print every value the cast would turn into a missing one, repair it, and print the missing count after the cast. Parse listed, written day first, print how many dates could not be parsed and the raw values behind them, and add days_on_market measured from 2024-07-01, the day the partner sent the file.

Finally, count the listings per calendar month. This needs two more calls, .dt.to_period("M") and sort_index(): the first turns each timestamp into the month it falls in, printed as 2024-05, so value_counts() on its result counts listings per month, and the second puts the months in calendar order. Print the monthly counts.

Hints:

• isin from Lesson 2.4.2, with the four grade labels, finds every value the cast would turn into a missing one, before the cast does it.
• Strip the ends of neighborhood and condition before anything else touches them, since " excellent " and "Southgate " will not match their clean forms until you do.

import pandas as pd

df = pd.read_csv("partner.csv")

# asking: strip the dollar sign and the commas, then convert
print(df["asking"].isna().sum())  # 1
digits = df["asking"].str.replace("$", "").str.replace(",", "")
df["price"] = pd.to_numeric(digits)
print(df["price"].isna().sum())   # 1

# neighborhood: one spelling per place, then a category
nb = df["neighborhood"].str.strip().str.title()
df["neighborhood"] = nb.astype("category")
print(df["neighborhood"].cat.categories.tolist())

['Downtown', 'Eastgate', 'Hillcrest', 'Lakeview', 'Riverside', 'Southgate']

# condition: find what the cast would lose BEFORE casting
labels = ["poor", "fair", "good", "excellent"]
grade = pd.CategoricalDtype(labels, ordered=True)
cond = df["condition"].str.strip().str.lower()
print(cond[~cond.isin(labels)].tolist())  # ['fiar']
cond = cond.str.replace("fiar", "fair")
df["condition"] = cond.astype(grade)
print(df["condition"].isna().sum())       # 0

# listed: day first, junk to NaT, and say which raw values failed
listed = pd.to_datetime(df["listed"], format="%d/%m/%Y", errors="coerce")
print(listed.isna().sum())  # 1
failed = df.loc[listed.isna(), "listed"]
print(failed.tolist())      # ['TBD']
df["listed"] = listed
snapshot = pd.Timestamp("2024-07-01")
df["days_on_market"] = (snapshot - listed).dt.days
print(df["days_on_market"].tolist())

# listings per calendar month, in calendar order
per_month = listed.dt.to_period("M").value_counts().sort_index()
print(per_month)

[77.0, 65.0, 60.0, 60.0, nan, 32.0, 27.0]
listed
2024-04    2
2024-05    3
2024-06    1
Freq: M, Name: count, dtype: int64

Additional Resources

Optional, and nothing later depends on either. Worth opening if you want every date code format= accepts, or the tools for changing a category list after the cast.

• Official docs - Python 3.12, datetime, "strftime() and strptime() Format Codes". The full table of codes a format= string is built from, month names and hours included.
• Official docs - pandas User Guide, "Categorical data", "Working with categories". Renaming, adding and removing the categories of a column that is already a category.

Summary

Every column in this lesson arrived as text, and the work was deciding what each one really was. A neighborhood is a label from a short list, a condition is a rank on a scale, and a listing date is a point in time, yet the file stored all three the same way, as characters. The tools differ, .str, category, to_datetime and .dt, but the move is one move made three times: say what the column means, convert it to the type that means that, and only then ask it questions. Counting, sorting and comparing all answer correctly once the type is right, and each can answer something else, without complaint, while it is still text.

The quiet failures are the thread worth keeping. Nothing in this lesson raises when a neighborhood is typed two ways, when a grade sorts alphabetically, when a day-first date is read month first, or when a value outside the category list becomes a gap. A date pandas cannot parse fails loudly, and that is the friendly failure, because it names the value. Everything else has to be caught by a habit: normalize before counting, check a category list against the data before casting, count missing values before and after every conversion, and state the layout of a date instead of letting pandas guess it.

The last idea reaches furthest. A feature measured from today's date is a feature that changes while the data does not, so a model trained on one day's numbers and fed another day's is comparing two different measurements of the same listings. Fixing the reference date in the code is a small line with a large consequence, and the same instinct, make every number come out the same on every run, is what lets a result be checked by someone else at all. The columns are now the right types, and what remains is the table itself, the rows that repeat, the values that cannot be right and the shape of the whole.

Cheat Sheet

Part A - Reference Table

Part B - Compact Code Block

import pandas as pd
df = pd.read_csv("housing.csv", parse_dates=["listing_date"])

# normalize first, then store labels once
nb = df["neighborhood"].str.strip().str.title()
df["neighborhood"] = nb.astype("category")

labels = ["poor", "fair", "good", "excellent"]
grade = pd.CategoricalDtype(labels, ordered=True)
cond = df["condition"]
strays = cond.notna() & ~cond.isin(labels)  # check first
df["condition"] = cond.astype(grade)        # then cast

listed = df["listing_date"]
weekend = listed.dt.dayofweek >= 5          # Sat 5, Sun 6
snapshot = pd.Timestamp("2025-01-01")       # never today
days = (snapshot - listed).dt.days          # Timedelta to days
monthly = listed.dt.to_period("M").value_counts()