Transform with Python

What you will be able to do

Load the extract in pandas so that every column has the type that you chose, not a type that pandas guessed. Then join and change the data in steps that check themselves and count what they change.

NotebookDo this lesson in a notebookColab · Kaggle · GitHub

The notebook has every script, task and check of this lesson, with the instructions, and shows the same pandas 3 behaviour. Its setup cells install pandas, build the database and make the extract, so it needs no local setup. Read the explanations here, and run the code there.

Open in ColabOpen in KaggleView on GitHub

Colab and Kaggle open your own copy. They need a free Google or Kaggle account. Stuck? See the solution notebook.

Before you startWhat to know and to have readyAbout 50 minutes

What you need to know

• The problems in the extract and their counts, from Profile before changing.
• That a CSV file has no types: every value is text. You saw this with csv.DictReader in Reading and writing files: CSV and JSON.
• Inner and left joins, and how a repeated key repeats rows, from Join tables.

What you need on your computer (only for the local route; the notebook needs none of it)

• The folder ~/projects/ticket-data, with the raw files in data/raw/ from Set up a practice database: Build the database. If they are missing, run python get_data.py again.
• The extract data/extract/tickets.csv and data/extract/outcomes.csv, from Reproducible extraction: Guided practice. If the script is missing or broken, copy scripts/extract.py and the two files in queries/ from the course’s finished scripts, then run python scripts/extract.py.
• The environment .venv with pandas 3.0.6, from Profile before changing: The same profile in pandas. Activate it in a terminal in the project folder (source .venv/bin/activate; on Windows .venv\Scripts\Activate.ps1). The prompt starts with (.venv). If the environment is broken, delete the folder .venv and follow that section again. Isolate dependencies explains why this is safe.
• reports/profile.md, from Profile before changing: Guided practice. This lesson adds one line to it. If it is missing, make the file with a “Categories” section.

Tested on macOS with Python 3.14.6 and pandas 3.0.6. pandas 3 changed some behaviour from pandas 2. This lesson shows the pandas 3 behaviour. The notebook was tested in Jupyter.

pandas guesses types

A CSV file is text. When pd.read_csv reads it, pandas looks at the values in each column and guesses a type. Most guesses are good. Some are wrong, and they do not cause an error. Look at three made-up tickets. Ticket T-2 has no priority, and its region is NA, a new code for North America:

tiny_types.py

import io

import pandas as pd

TINY = """ticket_id,priority,region,created_at
T-1,1,north,2026-03-04 09:15:00
T-2,,NA,2026-03-04 10:02:00
T-3,3,,2026-03-05 16:40:00
"""

guessed = pd.read_csv(io.StringIO(TINY))
print(guessed)
print(guessed.dtypes)

io.StringIO(TINY) lets read_csv read the text as if it were a file. Predict first: what type does priority get?

Output

  ticket_id  priority region           created_at
0       T-1       1.0  north  2026-03-04 09:15:00
1       T-2       NaN    NaN  2026-03-04 10:02:00
2       T-3       3.0    NaN  2026-03-05 16:40:00
ticket_id         str
priority      float64
region            str
created_at        str
dtype: object

Three quiet changes:

1. priority became a decimal number, float64: 1.0 and 3.0. The standard integer type cannot hold a missing value, so pandas chose a type that can (NaN).
2. The region NA became missing. pandas treats some texts as missing by default: NA, N/A, null, None, nan and others. The real code NA is lost, and now T-2 looks the same as T-3, which has no region.
3. created_at is text (str), not a date. A comparison such as “before 1 February” compares letters, not times.

Load with types that you choose

The fix is to say the type of each column. Add this to the same file:

tiny_types.py (continued)

typed = pd.read_csv(
    io.StringIO(TINY),
    dtype={"ticket_id": "string", "priority": "Int64", "region": "string"},
    keep_default_na=False,
    na_values=[""],
)
typed["created_at"] = pd.to_datetime(typed["created_at"], format="%Y-%m-%d %H:%M:%S")
print(typed)
print(typed.dtypes)

Output

  ticket_id  priority region          created_at
0       T-1         1  north 2026-03-04 09:15:00
1       T-2      <NA>     NA 2026-03-04 10:02:00
2       T-3         3   <NA> 2026-03-05 16:40:00
ticket_id             string
priority               Int64
region                string
created_at    datetime64[us]
dtype: object

Setting	What it does
dtype={...}	Gives the type of each column that you name. pandas does not guess for those columns.
"Int64" (capital I)	An integer type that can hold a missing value. It shows the missing value as <NA>. Lowercase int64 cannot.
"Float64" (capital F)	The same idea for decimal numbers.
"string"	A text type whose missing value is <NA>, as for Int64.
keep_default_na=False	Turns off the default list of missing-value texts. NA stays the text NA.
na_values=[""]	Only an empty cell is missing. In this export, an empty cell is how SQL’s NULL arrives in a CSV file.
pd.to_datetime(..., format=...)	Turns text into a date and time, with the format that you give. The type is datetime64[us] (microsecond precision).

The Larkfield export has no region NA today. You set these options anyway, because the next export can have one, and then the guess fails without an error.

pandas 3: str and string

In pandas 3, a text column that pandas guesses gets the type str, and its missing value is NaN. The type "string", which you ask for by name, marks a missing value as <NA>. Both hold text. They behave differently in comparisons, as you see later in this lesson. Mei’s script asks for "string" for every text column.

Dates: always give the format

The format says where each part of a date is. %Y is the 4-digit year, %m the month, %d the day, and %H:%M:%S the hours, minutes and seconds. For 2026-03-05 16:40:00, the format is %Y-%m-%d %H:%M:%S.

What happens if the format is wrong? Mei swaps the day and the month by mistake:

dates.py

import pandas as pd

times = pd.Series(["2026-03-04 09:15:00", "2026-03-05 16:40:00", "2026-03-13 08:05:00"])

print(pd.to_datetime(times, format="%Y-%m-%d %H:%M:%S"))
print(pd.to_datetime(times[:2], format="%Y-%d-%m %H:%M:%S"))
print(pd.to_datetime(times, format="%Y-%d-%m %H:%M:%S"))

Output

0   2026-03-04 09:15:00
1   2026-03-05 16:40:00
2   2026-03-13 08:05:00
dtype: datetime64[us]
0   2026-04-03 09:15:00
1   2026-05-03 16:40:00
dtype: datetime64[us]
Traceback (most recent call last):
  ...
ValueError: time data "2026-03-13 08:05:00" doesn't match format "%Y-%d-%m %H:%M:%S". You might want to try:
    - passing `format` if your strings have a consistent format;
    ...

• With the correct format, every value is correct.
• With the wrong format and only the first two values, pandas reads 4 March as 3 April, with no error. Every day is 12 or less, so every value is a possible date.
• With the third value, day 13 cannot be a month, so pandas stops with an error. That error is good news: it shows the mistake.

Without format, pandas guesses the format from the first value. For the Larkfield timestamps, the guess is correct. For short dates such as 04/03/2026, it is not safe: pandas 3.0.6 reads 04/03/2026 as 3 April, the month-first order. In the United Kingdom, the same text means 4 March. Only the source of the data can tell you which one is correct. Write that answer in the code as a format.

errors="coerce" hides bad values

pd.to_datetime(..., errors="coerce") does not stop at a bad value. It turns the value into NaT (“not a time”), the missing value for dates. If you use it, count the NaT values before and after, and report the new ones. Otherwise a wrong value becomes a missing value, and nobody sees it.

Worked example: Mei loads the extract

Mei starts scripts/clean.py. In lesson 3 it gets the cleaning steps. Today it loads the extract with a type for every column:

scripts/clean.py

"""Clean the extracted tickets and outcomes. Every input row ends in exactly
one place: the clean table, the quarantine table, or the count of removed
exact duplicates. The original values stay next to the corrected ones."""

import json
from pathlib import Path

import pandas as pd

IN = Path("data/extract")
OUT = Path("data/clean")
REPORTS = Path("reports")

TICKET_TYPES = {
    "ticket_id": "string", "customer_id": "string", "channel": "string", "team": "string",
    "priority": "Int64", "order_value": "Float64", "word_count": "Int64",
    "first_reply_minutes": "Int64", "priority_now": "Int64",
    "segment": "string", "region": "string",
}
TIME_FORMAT = "%Y-%m-%d %H:%M:%S"


def load_tickets():
    df = pd.read_csv(IN / "tickets.csv", dtype=TICKET_TYPES, keep_default_na=False, na_values=[""])
    df["created_at"] = pd.to_datetime(df["created_at"], format=TIME_FORMAT)
    df["closed_at"] = pd.to_datetime(df["closed_at"], format=TIME_FORMAT)
    df["joined_on"] = pd.to_datetime(df["joined_on"], format="%Y-%m-%d")
    return df


def main():
    tickets = load_tickets()
    print(f"Loaded {len(tickets)} ticket rows")
    print(tickets.dtypes)
    print("created_at:", tickets["created_at"].min(), "to", tickets["created_at"].max())
    print("missing closed_at (open tickets):", tickets["closed_at"].isna().sum())


if __name__ == "__main__":
    main()

json and OUT and REPORTS are not used yet. Lesson 3 uses them. main() is a temporary check: lesson 3 replaces it.

Terminal

python scripts/clean.py

Output

Loaded 1416 ticket rows
ticket_id                      string
customer_id                    string
created_at             datetime64[us]
channel                        string
team                           string
priority                        Int64
order_value                   Float64
word_count                      Int64
first_reply_minutes             Int64
priority_now                    Int64
closed_at              datetime64[us]
segment                        string
region                         string
joined_on              datetime64[us]
dtype: object
created_at: 2026-01-05 07:30:24 to 2026-06-30 20:04:36
missing closed_at (open tickets): 12

Mei checks three things against the profile. There are 1,416 rows, the same as the extract. The created_at range stays inside the extract’s selection, 1 January to 30 June. And 12 closed_at values are missing: the same 12 open tickets. So pd.to_datetime did not create a single new missing value.

Join tables in pandas, with a key rule

In SQL, a join combines the rows of two tables on a key. In pandas, the method is merge. The extract already joined tickets to customers in SQL. Mei repeats that join in pandas from the raw files, to see two settings that SQL does not have:

scripts/check_join.py

"""Repeat the extract's customer join in pandas, and count every row."""

import pandas as pd

READ = {"keep_default_na": False, "na_values": [""]}
tickets = pd.read_csv("data/raw/tickets.csv", dtype={"customer_id": "string"}, **READ)
customers = pd.read_csv("data/raw/customers.csv", dtype="string", **READ)
print(f"tickets: {len(tickets)} rows, customers: {len(customers)} rows")

joined = tickets.merge(
    customers,
    on="customer_id",
    how="left",
    validate="many_to_one",
    indicator=True,
)
print(f"joined: {len(joined)} rows")
print(joined["_merge"].value_counts())

no_match = joined[joined["_merge"] == "left_only"]
print(f"no customer_id: {no_match['customer_id'].isna().sum()}")
print(f"customer_id not in customers: {no_match['customer_id'].notna().sum()}")

**READ passes the two settings in the dictionary READ to both read_csv calls, so they cannot differ. Predict first: how many rows does a left join return?

Output

tickets: 1417 rows, customers: 240 rows
joined: 1417 rows
_merge
both          1396
left_only       21
right_only       0
Name: count, dtype: int64
no customer_id: 12
customer_id not in customers: 9

Setting	What it does
how="left"	Keeps every row of the left table (tickets), as LEFT JOIN does.
validate="many_to_one"	A rule: many tickets can have the same customer, but each customer_id must occur once in customers. If not, merge stops with an error, before rows multiply.
indicator=True	Adds the column _merge: both (a match), left_only (no match) or right_only.

The counts agree with Module 2. The result has 1,417 rows, the same as the left table. 1,396 rows matched, the inner-join count. 21 tickets have no customer row: 12 guests and 9 deleted customers.

The key rule is the important part. Mei now joins the outcome events to the extract’s tickets, to add the team to each event. Each event belongs to one ticket, so the rule is many_to_one:

Interactive Python

>>> import pandas as pd
>>> tickets = pd.read_csv("data/extract/tickets.csv", dtype="string", keep_default_na=False, na_values=[""])
>>> events = pd.read_csv("data/extract/outcomes.csv", dtype="string", keep_default_na=False, na_values=[""])
>>> len(events)
1869
>>> len(events.merge(tickets[["ticket_id", "team"]], on="ticket_id", how="left"))
1892
>>> events.merge(tickets[["ticket_id", "team"]], on="ticket_id", how="left", validate="many_to_one")
Traceback (most recent call last):
  ...
pandas.errors.MergeError: Merge keys are not unique in right dataset; not a many-to-one merge

Duplicates in right:
 ticket_id
  T-10031
  T-10156
  T-10260
  T-10337
  T-10347 ...

Without validate, the join returns 1,892 rows from 1,869 events: 23 events now appear twice, because their ticket appears twice in the extract. Nothing fails. With validate, pandas stops and names the duplicated keys. These are the 14 exact duplicates from the profile. After drop_duplicates(), the rule holds. You try that in the variation below.

Change values in named steps

A transformation is a change to the values of a column. Mei follows four rules for each one:

1. One named step for one problem, with a comment that says why.
2. Keep the original in a column next to it, for example channel_raw.
3. Count the rows that the step changes.
4. Change rows with .loc, in one statement.

Here is the channel respelling from the profile. Mei tries it in a short script before she puts it in clean.py:

scripts/try_channel.py

import pandas as pd

from clean import load_tickets

tickets = load_tickets().drop_duplicates()
print(f"rows: {len(tickets)}")

channel = tickets["channel"].str.strip().str.lower().replace({"webform": "web_form"})
changed = channel != tickets["channel"]
print(changed.value_counts(dropna=False))
print(f"respelled: {changed.fillna(False).sum()}")

tickets["channel_raw"] = tickets["channel"]
tickets["channel"] = channel
print(tickets.loc[changed.fillna(False), ["ticket_id", "channel_raw", "channel"]].drop_duplicates("channel_raw"))
print(tickets["channel"].value_counts(dropna=False))

.str.strip() removes spaces at the start and the end, .str.lower() makes all letters small, and .replace(...) changes the one wrong word.

Output

rows: 1402
channel
False    1325
True       39
<NA>       38
Name: count, dtype: Int64
respelled: 39
    ticket_id channel_raw   channel
2     T-10003       Email     email
12    T-10013     webform  web_form
111   T-10111       EMAIL     email
124   T-10124        chat      chat
channel
email       564
chat        415
web_form    203
phone       182
<NA>         38
Name: count, dtype: Int64

• 1,402 rows: the 14 exact duplicates are gone.
• 39 rows changed. In the profile, 40 rows had other spellings. One EMAIL row was one of the duplicates, so 39 remain.
• In row T-10124, channel_raw is chat, with the space at the start. The table does not show the space, but the comparison does.
• 38 rows are <NA>: a missing channel stays missing. The step does not invent a value. Lesson 3 decides what to do with it.

Why fillna(False)?

With the "string" type, a comparison with a missing value gives <NA>, not True or False: pandas does not know if an unknown value changed. fillna(False) says “a missing value did not change”. With the default str type, the same comparison gives True for the 38 missing values, because NaN is never equal to NaN. You try this in the guided practice.

Change rows with .loc, not in two steps

pandas 3 uses copy-on-write: when you select rows, you get a new table that behaves as a copy. A change to the copy never changes the original table. This matters for a common pattern:

cow.py

import pandas as pd

df = pd.DataFrame({
    "ticket_id": ["T-1", "T-2", "T-3"],
    "team": ["account", "delivery", "returns"],
    "order_value": [None, 40.0, -1.0],
})

df[df["order_value"] == -1]["order_value"] = None
print(df)

df.loc[df["order_value"] == -1, "order_value"] = None
print(df)

Output

.../cow.py:9: ChainedAssignmentError: A value is being set on a copy of a DataFrame or Series through chained assignment.
Such chained assignment never works to update the original DataFrame or Series, because the intermediate object on which we are setting values always behaves as a copy (due to Copy-on-Write).

Try using '.loc[row_indexer, col_indexer] = value' instead, to perform the assignment in a single step.
...
  ticket_id      team  order_value
0       T-1   account          NaN
1       T-2  delivery         40.0
2       T-3   returns         -1.0
  ticket_id      team  order_value
0       T-1   account          NaN
1       T-2  delivery         40.0
2       T-3   returns          NaN

The first line selects the rows, then sets a column on that selection: two steps, a chained assignment. In pandas 3 it never changes df. T-3 still has -1.0. pandas prints a warning, but the program continues. The .loc[rows, column] = value form does the change in one step, on df itself. (In pandas 2, the first form sometimes worked, with a different warning. Code from older tutorials can do nothing in pandas 3.)

Guided practice: start clean.py

1. Make scripts/clean.py with the code from the worked example.

2. Run python scripts/clean.py. Check the output: Loaded 1416 ticket rows, and created_at from 2026-01-05 07:30:24 to 2026-06-30 20:04:36.

3. Make scripts/try_channel.py, and run python scripts/try_channel.py. Check: respelled: 39.

4. Make scripts/try_str.py with the code below. It does the same respelling, but loads the file with no dtype. Predict the count first.

   scripts/try_str.py

   import pandas as pd
   
   plain = pd.read_csv("data/extract/tickets.csv").drop_duplicates()
   print(plain["channel"].dtype)
   respelled = plain["channel"].str.strip().str.lower().replace({"webform": "web_form"})
   print("changed:", (respelled != plain["channel"]).sum())

5. Run python scripts/try_str.py.

6. Add your result to reports/profile.md under “Categories”: 39 rows respelled after duplicates are removed.

What step 5 shows

Output

str
changed: 77

77 = 39 respelled rows + 38 missing channels. With the guessed str type, a missing value is NaN, and NaN != NaN is True. So every missing channel counts as “changed”. With "string", the comparison gives <NA> for those rows, and fillna(False) makes the decision visible in the code. Both scripts run without an error; only the count shows the difference.

Your turn: load and join the outcome events

Load data/extract/outcomes.csv with types: csat as Int64, the other columns as "string", and recorded_at as a date with TIME_FORMAT. Then join the events to the deduplicated tickets with validate="many_to_one" and indicator=True. Predict the number of rows and the _merge counts first.

Show an example answer

scripts/try_events.py

import pandas as pd

from clean import IN, TIME_FORMAT, load_tickets

events = pd.read_csv(
    IN / "outcomes.csv",
    dtype={"outcome_id": "string", "ticket_id": "string", "status": "string",
           "resolution_code": "string", "csat": "Int64"},
    keep_default_na=False,
    na_values=[""],
)
events["recorded_at"] = pd.to_datetime(events["recorded_at"], format=TIME_FORMAT)
print(events.dtypes)

tickets = load_tickets().drop_duplicates()
print(f"events: {len(events)}, tickets: {len(tickets)}")
joined = events.merge(
    tickets[["ticket_id", "team"]], on="ticket_id", how="left",
    validate="many_to_one", indicator=True,
)
print(f"joined: {len(joined)}")
print(joined["_merge"].value_counts())

Output

outcome_id                 string
ticket_id                  string
recorded_at        datetime64[us]
status                     string
resolution_code            string
csat                        Int64
dtype: object
events: 1869, tickets: 1402
joined: 1869
_merge
both          1869
left_only        0
right_only       0
Name: count, dtype: int64

The rule holds after drop_duplicates(): 1,869 events in, 1,869 rows out, and every event found its ticket. The extract left out the 4 orphan events in SQL, so left_only is 0.

Common mistake

The dates look correct in a sample, and are wrong

Symptom: Mei tests a date format on the first 50 rows of the extract. There is no error. Later, a chart shows tickets from May to October, but the export ends in June:

Interactive Python

>>> import pandas as pd
>>> sample = pd.read_csv("data/extract/tickets.csv", nrows=50)
>>> created = pd.to_datetime(sample["created_at"], format="%Y-%d-%m %H:%M:%S")
>>> sample["created_at"].iloc[0], created.iloc[0]
('2026-01-05 07:30:24', Timestamp('2026-05-01 07:30:24'))
>>> created.min(), created.max()
(Timestamp('2026-05-01 07:30:24'), Timestamp('2026-10-01 15:36:08'))

Cause: the format has the day and the month in the wrong places. The first 50 tickets are from 5 to 12 January, so every day is 12 or less, and every value is a possible date. On the full file, pandas stops at 2026-01-13 09:57:09 with doesn't match format.

Fix:

1. Write the format from the data, part by part: 2026-01-05 is year, month, day, so %Y-%m-%d.
2. Parse the full column, not a sample.
3. After parsing, check the smallest and largest date against the profile. The extract runs from 2026-01-05 to 2026-06-30.

In AI work: the date decides which rows go into training and which into testing (Module 5). Wrong dates put January tickets in October, so the model trains on rows that look like they come from the future.

Check your understanding

You read a CSV file without dtype. The column priority has one empty cell. What type does pandas give the column, and what do the values look like?

Show the answer

float64: the values look like 1.0 and 3.0, and the empty cell is NaN. The standard integer type cannot hold a missing value, so pandas changes the type. With dtype={"priority": "Int64"}, the values stay 1 and 3, and the empty cell is <NA>.

Check your understanding

A join of 1,869 events to the tickets returns 1,892 rows. What does validate="many_to_one" do in this case, and why is that better?

Show the answer

It stops the join with a MergeError that names the ticket_id values that occur more than once in the tickets table. Without it, 23 events are counted twice, and every count and average after the join is wrong, with no error. The rule makes the quiet mistake loud.

Check your understanding

In pandas 3, what does df[df.team == "account"]["order_value"] = 0 change in df?

Show the answer

Nothing. It is a chained assignment. The first selection gives a table that behaves as a copy (copy-on-write). The assignment changes only that copy. pandas shows a ChainedAssignmentError warning. Use df.loc[df.team == "account", "order_value"] = 0.

Recap

• Give every column a type with dtype=. Use Int64, Float64 and "string" when values can be missing.
• keep_default_na=False, na_values=[""]: only an empty cell is missing, so a real value such as NA survives.
• Parse dates with an explicit format, and check the smallest and largest date after parsing. A wrong format can give valid but wrong dates.
• merge(..., validate="many_to_one", indicator=True) checks the key rule and shows which rows matched. Count the rows before and after.
• A transformation is one named step that keeps the original column and counts its changes. In pandas 3, change rows with .loc in one statement.

Next, you decide what to do with each problem from the profile: remove, correct, or keep as missing, and you account for every row.

Next: Handle messy values