Under a megabyte on disk. 7.79 MB in memory. Same file, same 7,043 rows, and I had done nothing to it except call read_csv.

That ratio is the subject of this part. pandas will hand you a correct answer while using eight times more memory than it needs, running an operation three thousand times slower than its vectorised equivalent, and quietly changing your row count on a join. None of those surface as errors. They surface as a laptop that starts swapping, a training job that runs overnight instead of for an hour, and a model whose accuracy is wrong in a direction that flatters you. Past the tutorial level, most pandas skill is knowing which of your everyday habits are doing this to you.

Where the churn project stands

Part 3 left us with a project folder, an isolated environment and a lockfile, and nothing in it. This part puts data in. Across the rest of the series we build one thing end to end: a model that predicts which subscribers of a telecoms company are about to cancel, using the public Telco Customer Churn dataset that IBM publishes. Seven thousand and forty three customers, twenty one columns, one label saying whether each of them left in the last month.

By the end of this part the project can load that file correctly, hold it in a tenth of the memory, join it to a second table without corrupting the row count, and reshape it between the layout a human reads and the layout a model needs. Part 5 goes after the messier sources, APIs and SQL pulls. Part 6 turns these columns into features.

One point of vocabulary before the code, because I will use it constantly. Vectorisation means handing an entire array to a routine written in C rather than looping over its elements in Python. NumPy is the library that provides those arrays and routines; pandas is built directly on top of it, and a pandas Series is a NumPy array with a label index attached. When people say pandas is slow, they nearly always mean they wrote a Python loop around something NumPy could have done in one call.

Loading the churn table without silent damage

Start with the naive load and look hard at what came back. Reading a CSV is where most of the damage in a project is done, because pandas guesses a type for every column and never tells you when it guessed defensively.

import pandas as pd

URL = ('https://raw.githubusercontent.com/IBM/'
       'telco-customer-churn-on-icp4d/master/data/Telco-Customer-Churn.csv')
df = pd.read_csv(URL)

print(df.shape)
print(df[['tenure', 'MonthlyCharges', 'TotalCharges']].dtypes)

Tested with pandas 2.3.3 and NumPy 2.2.6 on Python 3.10.12.

(7043, 21)
tenure              int64
MonthlyCharges    float64
TotalCharges       object
dtype: object

Two numeric columns and one that should be numeric and is not.

TotalCharges came back as object, which is pandas speaking for a column of arbitrary Python objects, in practice strings. MonthlyCharges next to it parsed as float64. Something in that column is not a number, and pandas fell back rather than complain. Casting it directly tells you what.

df['TotalCharges'].astype(float)

Traceback (most recent call last):
  File "load.py", line 8, in <module>
    df['TotalCharges'].astype(float)
ValueError: could not convert string to float: ' '

A single space character, not an empty string and not a NaN.

Eleven rows carry a lone space in TotalCharges, and every one of them has tenure equal to zero. Those are customers who signed up and had not yet been billed. Nothing is broken in the data; the file simply encodes a missing value as whitespace, which is a convention pandas cannot guess. Coercion turns them into proper missing values and lets you decide what to do next.

blank = df['TotalCharges'].str.strip() == ''
print('blank rows:', blank.sum())
print('their tenure values:', sorted(df.loc[blank, 'tenure'].unique()))

df['TotalCharges'] = pd.to_numeric(df['TotalCharges'], errors='coerce')
print('missing after coercion:', df['TotalCharges'].isna().sum())

blank rows: 11
their tenure values: [0]
missing after coercion: 11

Eleven brand new customers, correctly identified rather than dropped by accident.

Two habits come out of this. Run dtypes on every fresh load and treat any unexpected object column as a defect to investigate, not a quirk to work around. And prefer pd.to_numeric with errors set to coerce over astype, because coerce marks the bad values instead of stopping at the first one. Deleting the eleven rows is also defensible, and it is what most published notebooks on this dataset do. I keep them, because tenure zero customers are exactly the population a retention team cares about and throwing them away at load time is a decision you will not remember making three parts later. For the wider treatment of missing data conventions, my analyst post on cleaning messy data covers the patterns that recur.

Vectorisation, and what a Python loop actually costs

Everybody repeats that you should not loop over a dataframe. Far fewer people have measured the gap, and the size of it changes how seriously you take the advice. Here is the same trivial calculation, monthly charge multiplied by tenure, written three ways over 704,300 rows, which is the churn file stacked a hundred times.

import time
big = pd.concat([df] * 100, ignore_index=True)

def timed(fn):
    start = time.perf_counter()
    fn()
    return time.perf_counter() - start

loop = timed(lambda: [big['MonthlyCharges'].iat[i] * big['tenure'].iat[i]
                      for i in range(len(big))])
apply_row = timed(lambda: big.apply(lambda r: r['MonthlyCharges'] * r['tenure'], axis=1))
vector = timed(lambda: big['MonthlyCharges'] * big['tenure'])

print(f'rows          {len(big)}')
print(f'iat loop      {loop:.3f}s')
print(f'apply axis=1  {apply_row:.3f}s')
print(f'vectorised    {vector:.4f}s')
print(f'loop is {loop / vector:.0f}x slower')

rows          704300
iat loop      3.972s
apply axis=1  3.767s
vectorised    0.0014s
loop is 2907x slower

Absolute timings depend on your machine. Ratios of this magnitude do not.

Notice that apply with axis set to 1 gave almost no benefit over the explicit loop. That surprises people, because apply looks like a pandas method and therefore feels like it should be fast. It is not. Row wise apply constructs a Series object for every row and calls your Python function on it, which is a loop with extra allocation. Column wise apply, meaning axis set to 0, is a different animal and is usually fine.

Conditional logic is where beginners reach for apply most often, and it is the case with the cleanest vectorised replacement. Two branches call for numpy.where. More than two call for numpy.select, which takes a list of boolean conditions and a matching list of results.

import numpy as np

conditions = [df['tenure'] < 12, df['tenure'] < 36]
labels = ['first year', 'years two and three']
df['tenure_band'] = np.select(conditions, labels, default='long tenured')

print(df['tenure_band'].value_counts().to_string())

numpy.select evaluates conditions in order and takes the first match, so ranges can overlap safely if you order them from narrowest to widest.

My rule, and I apply it without exception in review: if a line contains apply with axis equal to 1, it needs a comment explaining why the vectorised form was impossible. Perhaps one time in twenty there is a genuine reason, usually a call into a library that only accepts scalars. Every other time it is habit.

Memory footprint of a dataframe, measured

Memory matters earlier than people expect. Not because 7 MB is a problem, but because the habits you form on 7 MB are the habits you will still have on 7 GB, and by then the cost of changing them is a rewrite. Ask a dataframe what it weighs, with deep set to True so that the actual string contents are counted rather than just the pointers to them.

def megabytes(frame):
    return frame.memory_usage(deep=True).sum() / 1024 ** 2

print(f'as loaded          {megabytes(df):6.2f} MB')

slim = df.copy()
text_cols = [c for c in slim.columns
             if slim[c].dtype == object and slim[c].nunique() <= 6]
for c in text_cols:
    slim[c] = slim[c].astype('category')
slim['SeniorCitizen'] = slim['SeniorCitizen'].astype('int8')
slim['tenure'] = slim['tenure'].astype('int16')
slim['MonthlyCharges'] = slim['MonthlyCharges'].astype('float32')

print(f'category and downcast {megabytes(slim):6.2f} MB')
print(f'columns converted  {len(text_cols)}')
print(f'reduction          {(1 - megabytes(slim) / megabytes(df)) * 100:.0f}%')

as loaded            7.79 MB
category and downcast   1.04 MB
columns converted    16
reduction            87%

Your figures will land within a few hundred kilobytes of these, depending on interpreter build.

Categorical is the conversion that does the heavy lifting here. A column such as Contract holds three distinct labels repeated seven thousand times; as object dtype pandas stores seven thousand separate Python string objects. As category it stores three labels and a byte per row pointing at one of them. Downcasting numerics matters less on this file but scales the same way, and the table below is the reference I actually use.

Merges that quietly change your row count

Churn work never stays inside one table. Support tickets, usage logs, billing events, marketing contacts, all of them arrive as separate extracts keyed on a customer id, and all of them have to be joined on. Joining is also where I have watched more silent corruption enter a model than anywhere else in the pipeline. If joins are new to you, my analyst post on SQL joins explains the shapes; pandas merge behaves the same way.

Here is a support ticket table where some customers appear several times and many appear not at all, which is what a real ticket extract looks like.

rng = np.random.default_rng(3)
ids = rng.choice(df['customerID'], 4000, replace=True)
tickets = pd.DataFrame({'customerID': ids,
                        'tickets': rng.integers(1, 4, 4000)})

print('customers      ', len(df))
print('ticket rows    ', len(tickets))
print('distinct in it ', tickets['customerID'].nunique())

naive = df.merge(tickets, on='customerID', how='left')
print('after left merge', len(naive))

customers       7043
ticket rows     4000
distinct in it  3058
after left merge 7985

Ticket rows are sampled at random, so your exact count will sit near 7,985 rather than on it. Inflation is guaranteed; its size is not.

A left join promises to keep every row of the left frame. It does. What it does not promise is to keep only one copy of each. Any customer with three tickets now occupies three rows in a table you still think of as one row per customer. Every later count, mean and model weight is now skewed toward whoever complains most, and pandas has raised nothing.

Prevention costs one keyword. merge takes a validate argument that states the cardinality you believe you have, and raises MergeError when reality disagrees.

df.merge(tickets, on='customerID', how='left', validate='one_to_one')

MergeError: Merge keys are not unique in right dataset; not a one-to-one merge

An error at the moment the assumption breaks, rather than a wrong number three weeks later.

Correct handling is to aggregate the right hand table to one row per key first, then merge, then fill the customers who had no tickets at all.

per_customer = tickets.groupby('customerID', as_index=False)['tickets'].sum()

safe = df.merge(per_customer, on='customerID', how='left', validate='one_to_one')
safe['tickets'] = safe['tickets'].fillna(0).astype('int16')

print('rows after safe merge', len(safe))
print('customers with no tickets', (safe['tickets'] == 0).sum())

rows after safe merge 7043
customers with no tickets 3985

Row count preserved, and zero now means no tickets rather than missing.

flowchart TD
  A[Second table to join] --> B{One row per key on the right}
  B -->|Yes| C[merge with validate one_to_one]
  B -->|No| D{Do you want one row per customer}
  D -->|Yes| E[groupby and aggregate first]
  E --> C
  D -->|No| F[merge with validate one_to_many]
  C --> G[Assert row count unchanged]
  F --> G
  G --> H[Fill unmatched keys explicitly]

Reshaping between wide and long

Churn data arrives wide, meaning one row per customer with a column for each add on service. Some questions are far easier to ask when the same data is long, meaning one row per customer per service. Counting how many extras a customer subscribes to is one of them, and it becomes a feature in Part 6.

melt takes you wide to long. pivot_table takes you back. Neither is hard once you see the shape change, which is why I draw it before writing either.

services = ['OnlineSecurity', 'TechSupport', 'StreamingTV', 'DeviceProtection']

long = df.melt(id_vars=['customerID', 'Contract', 'Churn'],
               value_vars=services,
               var_name='service', value_name='status')
print('wide', df.shape, 'long', long.shape)

addons = (long[long['status'] == 'Yes']
          .groupby('customerID', as_index=False)
          .agg(addons=('service', 'size')))

with_addons = df.merge(addons, on='customerID', how='left', validate='one_to_one')
with_addons['addons'] = with_addons['addons'].fillna(0).astype('int8')
print(with_addons['addons'].value_counts().sort_index().to_string())

wide (7043, 21) long (28172, 5)

28,172 is exactly 7,043 times 4, which is the check worth running after any melt.

Notice the validate argument in that merge as well. The addons frame is produced by a groupby and therefore has one row per key by construction, so validate can never fire. I still write it, because the guarantee is cheap and because six months from now somebody will change the groupby into something that no longer guarantees it. Assertions that hold today are how you find out when they stop holding.

Copy on Write and chained assignment in pandas 3.0

Anybody who learned pandas before 2024 carries a scar from SettingWithCopyWarning, the message that appeared when you assigned into a slice and pandas could not tell whether you were editing the original or a copy. pandas 3.0 ends that ambiguity. Copy on Write became the default and only mode in the 3.0 release, and it makes one rule absolute: anything derived from a dataframe behaves as a copy, so you can only change values through the object itself.

Practically, chained assignment now raises rather than doing nothing.

# wrong: two operations chained, the assignment lands on a temporary
df[df['tenure'] == 0]['TotalCharges'] = 0.0

# right: one operation, .loc addresses rows and column together
df.loc[df['tenure'] == 0, 'TotalCharges'] = 0.0

ChainedAssignmentError: A value is trying to be set on a copy of a
DataFrame or Series through chained assignment.
When using the Copy-on-Write mode, 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.

Reproduced on pandas 2.3.3 with copy_on_write enabled, which matches pandas 3.0 behaviour.

Before Copy on Write that first line updated nothing and printed a warning most people had trained themselves to scroll past. I have debugged a pipeline where a fillna written that way had been a no operation for four months, and the model had been training on missing values the whole time without anybody noticing, because a missing value in a tree model is not an error. An exception is a much better outcome than a warning. If you are upgrading an older notebook to pandas 3.0, expect this to be the change that breaks it, and treat every break as a bug you already had.

Rules I apply to every pandas project now

Five of them, in the order I would teach them. Print dtypes immediately after every load and treat an unexpected object column as a defect. Never iterate rows to compute a column; reach for a NumPy expression, and for conditional logic reach for numpy.where or numpy.select rather than apply with axis equal to 1. Convert low cardinality text columns to category as part of loading, not as a later optimisation, so the habit is already there when the file is large enough to matter. Pass validate on every merge and assert the row count afterwards. Address rows and columns in a single .loc call rather than chaining two operations.

Two things I would specifically avoid. Do not use apply with axis set to 1 as a default tool; it reads like pandas and performs like a loop, and it was 2,756 times slower than the vectorised form in the measurement above. And do not use inplace on anything. It rarely saves memory, it returns None so it cannot be chained, and under Copy on Write its behaviour is confusing enough that the pandas team discourage it.

Part 5 goes after the data sources themselves: pulling from APIs with pagination and rate limits, reading SQL into pandas without dragging the whole table across the network, and the file formats that misbehave, chiefly Excel dates and CSV encodings. Then Part 6 takes the churn frame we now hold correctly in memory and builds features from it, which is where most real accuracy comes from. Before you move on, run the load and the safe merge from this part in your Part 3 environment and confirm you get 7,043 rows and 11 coerced values. If both numbers match, your foundation is sound.

References

• What is new in pandas 3.0.0, pandas documentation
• Copy-on-Write user guide, pandas documentation
• DataFrame.merge API reference, pandas documentation
• NumPy release notes
• Telco Customer Churn dataset, IBM on GitHub

About The Author

Dr. Pranay Jha

Dr. Pranay Jha is a Cloud and AI Consultant with 18+ years of experience in hybrid cloud, virtualization, and enterprise infrastructure transformation. He specializes in VMware technologies, multi-cloud strategy, and Generative AI solutions. He holds a PhD in Computer Applications with research focused on Cloud and AI, has published multiple research papers, and has been a VMware vExpert since 2016 and a VMUG Community Leader.

See author's posts