Python for AI: From Zero to Your First AI App

100%

Page 14 of 20 · 29 min left

Chapter 4 · Python for Data

pandas: Working with Tables

Page 14 of 20
4 min read

Most real data arrives as a table: a spreadsheet export, a database query, a log of every API call your app made. pandas is the Python library for tables. Its DataFrame is like a spreadsheet you control with code — and every data scientist's daily tool for cleaning data before it goes anywhere near a model.

Install with python -m pip install pandas; by convention it is imported as pd. The examples use this small file of customer-support tickets, tickets.csv (note that T4 has no satisfaction score):

Output

ticket_id,channel,category,minutes_to_resolve,satisfaction
T1,email,billing,45,4
T2,chat,technical,12,5
T3,chat,billing,8,5
T4,phone,technical,30,
T5,email,account,60,2
T6,chat,technical,15,4
T7,phone,billing,25,3
T8,email,technical,90,1

Loading and looking

Python

import pandas as pd

df = pd.read_csv("tickets.csv")
print(df.head(3))
print(df.shape)
print(df.dtypes)

Output

  ticket_id channel   category  minutes_to_resolve  satisfaction
0        T1   email    billing                  45           4.0
1        T2    chat  technical                  12           5.0
2        T3    chat    billing                   8           5.0
(8, 5)
ticket_id                 str
channel                   str
category                  str
minutes_to_resolve      int64
satisfaction          float64
dtype: object

Always look first: head() shows the top rows, shape gives (rows, columns), and dtypes shows the type pandas chose for each column. Notice that satisfaction became float64, not int64: the missing value is stored as NaN ("not a number"), which only floats can hold. The text columns are str. The numbers on the left are the index, the row labels.

Selecting columns and filtering rows

Python

import pandas as pd

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

print(df["minutes_to_resolve"].mean())                 # one column is a Series
print(df[["ticket_id", "channel"]].tail(2))             # a list of columns is a DataFrame

slow = df[df["minutes_to_resolve"] > 40]                # filter rows with a condition
print(slow[["ticket_id", "category", "minutes_to_resolve"]])

chat_billing = df[(df["channel"] == "chat") & (df["category"] == "billing")]
print(len(chat_billing), "chat billing ticket(s)")

Output

35.625
  ticket_id channel
6        T7   phone
7        T8   email
  ticket_id   category  minutes_to_resolve
0        T1    billing                  45
4        T5    account                  60
7        T8  technical                  90
1 chat billing ticket(s)

df["col"] gives one column, a Series. df[["a", "b"]] (note the double brackets) gives a smaller DataFrame.
df[condition] keeps the rows where the condition is True — the same boolean-mask idea as NumPy.
Combine conditions with & (and), | (or), ~ (not), and wrap each condition in brackets. Python's and/or do not work here.

Adding columns, sorting and counting

Python

import pandas as pd

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

df["hours"] = (df["minutes_to_resolve"] / 60).round(2)          # a new column
df["is_slow"] = df["minutes_to_resolve"] > 40
df["label"] = df["category"].str.upper() + "/" + df["channel"]  # text methods via .str

print(df[["ticket_id", "hours", "is_slow", "label"]].sort_values("hours", ascending=False).head(3))
print(df["channel"].value_counts())

Output

  ticket_id  hours  is_slow            label
7        T8   1.50     True  TECHNICAL/email
4        T5   1.00     True    ACCOUNT/email
0        T1   0.75     True    BILLING/email
channel
email    3
chat     3
phone    2
Name: count, dtype: int64

New columns are calculated for every row at once, without a loop. Text columns have a .str accessor with the string methods you know from page 4.

Group and summarise

"What is the average resolution time per channel?" is a group-by: split the rows into groups, summarise each, combine the results:

Python

import pandas as pd

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

summary = df.groupby("channel").agg(
    tickets=("ticket_id", "count"),
    avg_minutes=("minutes_to_resolve", "mean"),
    avg_satisfaction=("satisfaction", "mean"),
).round(1)
print(summary.sort_values("avg_minutes"))

Output

         tickets  avg_minutes  avg_satisfaction
channel                                        
chat           3         11.7               4.7
phone          2         27.5               3.0
email          3         65.0               2.3

Chat is resolved fastest and makes customers happiest; email is slowest. A few lines turn raw rows into a finding you could act on.

Missing data

Real data always has gaps. Find them, then decide: fill them with a sensible value, or drop the incomplete rows:

Python

import pandas as pd

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

print(df.isna().sum())                       # missing values per column
print(df[df["satisfaction"].isna()])

filled = df["satisfaction"].fillna(df["satisfaction"].median())
print(filled.tolist())
print(len(df.dropna()), "rows have no missing values")

df.to_csv("tickets_clean.csv", index=False)

Output

ticket_id             0
channel               0
category              0
minutes_to_resolve    0
satisfaction          1
dtype: int64
  ticket_id channel   category  minutes_to_resolve  satisfaction
3        T4   phone  technical                  30           NaN
[4.0, 5.0, 5.0, 4.0, 2.0, 4.0, 3.0, 1.0]
7 rows have no missing values

Which choice is right depends on the question. Filling with the median keeps every ticket; dropping is safer when a guessed value could mislead. Either way, decide on purpose — pandas skips NaN when it calculates a mean, which can quietly hide how much data is missing. to_csv() saves the result.

Before an AI project, a large part of the work is exactly this: loading data, finding the gaps and errors, and shaping it. Models trained or evaluated on messy data give messy results.

Try it yourself

Find the category with the lowest average satisfaction.
Add a column fast that is True when a ticket took 15 minutes or less, and count the fast tickets per channel.
Export only the technical tickets, sorted by resolution time, to technical.csv.

Python for AI: From Zero to Your First AI App

100%

Page 14 of 20 · 29 min left

Chapter 4 · Python for Data

pandas: Working with Tables

Page 14 of 20
4 min read

Output

ticket_id,channel,category,minutes_to_resolve,satisfaction
T1,email,billing,45,4
T2,chat,technical,12,5
T3,chat,billing,8,5
T4,phone,technical,30,
T5,email,account,60,2
T6,chat,technical,15,4
T7,phone,billing,25,3
T8,email,technical,90,1

Loading and looking

Python

import pandas as pd

df = pd.read_csv("tickets.csv")
print(df.head(3))
print(df.shape)
print(df.dtypes)

Output

  ticket_id channel   category  minutes_to_resolve  satisfaction
0        T1   email    billing                  45           4.0
1        T2    chat  technical                  12           5.0
2        T3    chat    billing                   8           5.0
(8, 5)
ticket_id                 str
channel                   str
category                  str
minutes_to_resolve      int64
satisfaction          float64
dtype: object

Selecting columns and filtering rows

Python

import pandas as pd

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

print(df["minutes_to_resolve"].mean())                 # one column is a Series
print(df[["ticket_id", "channel"]].tail(2))             # a list of columns is a DataFrame

slow = df[df["minutes_to_resolve"] > 40]                # filter rows with a condition
print(slow[["ticket_id", "category", "minutes_to_resolve"]])

chat_billing = df[(df["channel"] == "chat") & (df["category"] == "billing")]
print(len(chat_billing), "chat billing ticket(s)")

Output

35.625
  ticket_id channel
6        T7   phone
7        T8   email
  ticket_id   category  minutes_to_resolve
0        T1    billing                  45
4        T5    account                  60
7        T8  technical                  90
1 chat billing ticket(s)

df["col"] gives one column, a Series. df[["a", "b"]] (note the double brackets) gives a smaller DataFrame.
df[condition] keeps the rows where the condition is True — the same boolean-mask idea as NumPy.
Combine conditions with & (and), | (or), ~ (not), and wrap each condition in brackets. Python's and/or do not work here.

Adding columns, sorting and counting

Python

import pandas as pd

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

df["hours"] = (df["minutes_to_resolve"] / 60).round(2)          # a new column
df["is_slow"] = df["minutes_to_resolve"] > 40
df["label"] = df["category"].str.upper() + "/" + df["channel"]  # text methods via .str

print(df[["ticket_id", "hours", "is_slow", "label"]].sort_values("hours", ascending=False).head(3))
print(df["channel"].value_counts())

Output

  ticket_id  hours  is_slow            label
7        T8   1.50     True  TECHNICAL/email
4        T5   1.00     True    ACCOUNT/email
0        T1   0.75     True    BILLING/email
channel
email    3
chat     3
phone    2
Name: count, dtype: int64

New columns are calculated for every row at once, without a loop. Text columns have a .str accessor with the string methods you know from page 4.

Group and summarise

"What is the average resolution time per channel?" is a group-by: split the rows into groups, summarise each, combine the results:

Python

import pandas as pd

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

summary = df.groupby("channel").agg(
    tickets=("ticket_id", "count"),
    avg_minutes=("minutes_to_resolve", "mean"),
    avg_satisfaction=("satisfaction", "mean"),
).round(1)
print(summary.sort_values("avg_minutes"))

Output

         tickets  avg_minutes  avg_satisfaction
channel                                        
chat           3         11.7               4.7
phone          2         27.5               3.0
email          3         65.0               2.3

Chat is resolved fastest and makes customers happiest; email is slowest. A few lines turn raw rows into a finding you could act on.

Missing data

Real data always has gaps. Find them, then decide: fill them with a sensible value, or drop the incomplete rows:

Python

import pandas as pd

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

print(df.isna().sum())                       # missing values per column
print(df[df["satisfaction"].isna()])

filled = df["satisfaction"].fillna(df["satisfaction"].median())
print(filled.tolist())
print(len(df.dropna()), "rows have no missing values")

df.to_csv("tickets_clean.csv", index=False)

Output

ticket_id             0
channel               0
category              0
minutes_to_resolve    0
satisfaction          1
dtype: int64
  ticket_id channel   category  minutes_to_resolve  satisfaction
3        T4   phone  technical                  30           NaN
[4.0, 5.0, 5.0, 4.0, 2.0, 4.0, 3.0, 1.0]
7 rows have no missing values

Before an AI project, a large part of the work is exactly this: loading data, finding the gaps and errors, and shaping it. Models trained or evaluated on messy data give messy results.

Try it yourself

Find the category with the lowest average satisfaction.
Add a column fast that is True when a ticket took 15 minutes or less, and count the fast tickets per channel.
Export only the technical tickets, sorted by resolution time, to technical.csv.