Two lines from a timing run I did while writing this part, on the same one month of cluster metrics: the pandas loop most people reach for first, iterrows, took 12,207 milliseconds to average one column, and the vectorised version took 21. Same answer, same machine, 581 times apart, and the slow one is the version most tutorials teach an operator to write.
From awk habits to dataframe habits
You have been doing data engineering for years without the title. Every log parser, every capacity report piped through awk, every for loop that walks a metrics file and sums a field is data work already. What changes in data science is not the goal but the shape of the code. An operator instinct is imperative and row oriented: open the file, walk it line by line, split each line, accumulate into a dict. That instinct is correct for a one off on a 200 line log. It falls apart at 345,600 rows, and it falls apart harder the moment you want to join, group, resample or model, because you end up rebuilding pandas by hand and badly.
Data code is column oriented and declarative. You load the whole table once, then describe the transformation you want, average this column, group by that key, and let the library run it in compiled code. Same result, a tenth of the lines, and orders of magnitude faster on anything real. This jump is not syntax memorisation, it is a change of default from loop over rows to operate on columns. Below is the translation guide, and it is the artifact worth keeping from this part, because most shell moves you already make have a one line pandas twin.
| Operator reflex | What it does | Data code equivalent |
|---|---|---|
| for line in open(f) | read and parse rows | pd.read_csv(path) |
| awk sum of a field | total one column | df[col].sum() |
| grep pattern | keep matching rows | df[df[col] == value] |
| sort then uniq minus c | count by value | df[col].value_counts() |
| cut a field | pick a column | df[col] |
| loop building per key totals | group and aggregate | df.groupby(key)[col].mean() |
| date math in a loop | bucket by time | parse_dates then resample(1D) |
Why a column operation runs so much faster, contiguous memory and one compiled loop instead of many Python ones, is covered in the Data Science Series part on NumPy and pandas. Here it is enough to trust that describing the column beats walking the rows, and to build the habit on your own data.
A month of metrics, loaded honestly
Last part we named the export to pull, a month of one cluster CPU and memory metrics. This part it lands in Python. My copy is 345,600 rows, eight hosts sampled every minute through June, two metric columns and a timestamp. Yours will look different in the values and identical in the failure modes. A naive load looks like one line and mostly works, but look at the dtypes before you trust a single number, because that is exactly where the load lies to you.
# tested on Python 3.10, pandas 2.3.3, numpy 2.2.6; runs unchanged on pandas 2.2
import pandas as pd
df = pd.read_csv('cluster_metrics.csv')
print(df.shape)
print(df.dtypes)
# the average an operator expects
print(df['cpu_pct'].mean())
(345600, 4) timestamp object host object cpu_pct object mem_mb int64 dtype: object Traceback (most recent call last): ... TypeError: Could not convert string '20.3029.2022.4037.95...' to numeric
df = pd.read_csv(
'cluster_metrics.csv',
na_values=['-'],
parse_dates=['timestamp'],
)
print(df.dtypes)
print('missing cpu rows:', int(df['cpu_pct'].isna().sum()))
print('cpu mean:', round(df['cpu_pct'].mean(), 2))
timestamp datetime64[ns] host object cpu_pct float64 mem_mb int64 dtype: object missing cpu rows: 1071 cpu mean: 25.39
Two arguments fix it. na_values tells read_csv that a dash means missing, and parse_dates turns the timestamp text into real datetimes so you can bucket by day later. Now cpu_pct is float64, the 1,071 bad scrapes are proper missing values that mean quietly skips, and the average is a believable 25.39 percent instead of an exception. Run this dtype check on every export before you compute anything, because a metric column that came in as text is the single most common silent break in a beginner analysis.
Vectorisation over iteration, with real timings
With the data honest, here is the habit that matters most. My task is a per host average CPU, the kind of group and summarise you have written in awk a hundred times. There are three ways to do it: the pure Python loop you already know, the pandas iterrows loop a tutorial will show you, and the vectorised groupby. I timed all three on the real 345,600 rows, and the ranking is not what a newcomer expects.
import csv
def per_host_scripting():
sums, counts = {}, {}
with open('cluster_metrics.csv') as f:
r = csv.reader(f); next(r)
for ts, host, cpu, mem in r:
if cpu == '-':
continue
sums[host] = sums.get(host, 0) + float(cpu)
counts[host] = counts.get(host, 0) + 1
return {h: sums[h] / counts[h] for h in sums}
def per_host_iterrows(df):
sums, counts = {}, {}
for _, row in df.iterrows():
c = row['cpu_pct']
if pd.isna(c):
continue
h = row['host']
sums[h] = sums.get(h, 0) + c
counts[h] = counts.get(h, 0) + 1
return {h: sums[h] / counts[h] for h in sums}
def per_host_vectorised(df):
return df.groupby('host')['cpu_pct'].mean().to_dict()
scripting loop : 252.0 ms iterrows : 12206.8 ms vectorised : 21.0 ms speedup vectorised vs iterrows: 581x
Read the middle line twice. iterrows, whose name promises it is the pandas way to loop, is the slowest by a wide margin, 48 times slower than the plain file loop it was meant to improve on. It is slow because it builds a fresh Series object for every single row and casts each value to a Python object on the way, so you pay a heavy tax 345,600 times. So the tutorial default is the trap here, not the fix. If you ever truly must loop a frame, itertuples is far faster than iterrows because it skips that per row Series, but the honest advice is simpler: describe the column and do not loop at all. A vectorised groupby came out 581 times faster than iterrows and, tellingly, 12 times faster than the bash style loop you started from. That same instinct, prefer a compiled batch path over a per item Python loop, is what pays off in language model latency work too, where batching and caching beat per request handling for the identical reason.
A reusable cleaning pipeline
Put the pieces into one small recipe you can run against any metrics export. Load with the sentinel declared and the dates parsed, add a derived column with assign rather than a loop, then aggregate. Here it produces a daily peak CPU per host, 240 rows out of 345,600, which is the compact shape a capacity model actually wants to see.
def load_metrics(path):
return (
pd.read_csv(path, na_values=['-'], parse_dates=['timestamp'])
.assign(mem_gb=lambda d: d['mem_mb'] / 1024)
)
df = load_metrics('cluster_metrics.csv')
daily_peak = (
df.set_index('timestamp')
.groupby('host')['cpu_pct']
.resample('1D')
.max()
.rename('daily_peak')
.reset_index()
)
print(daily_peak.shape)
print(daily_peak.head(3).to_string(index=False))
(240, 3) host timestamp daily_peak node-00 2026-06-01 45.52 node-00 2026-06-02 44.91 node-00 2026-06-03 32.42
Notice there is not a single for loop in that recipe. set_index, groupby, resample and assign each describe an operation over columns, and pandas runs them in compiled code. That is the whole mindset shift on one screen. Getting the data in, from files today and from SQL, APIs and monitoring next, is its own subject, and the Data Science Series walks the connection patterns in getting data into Python. For now you have a repeatable load that any export can flow through.
Project status and the recipe to keep
Where the project stands now: one month of raw metrics is loaded, typed correctly, its failed scrapes marked missing rather than silently poisoning the math, and reduced to a tidy daily frame. That is phase two of the roadmap from Part 4 starting exactly on schedule. Your reference artifact from this part is really two things, the reflex map table for translating shell moves into pandas and the short load recipe that declares the sentinel and parses dates so nothing lies to you downstream. Paste both into your project notes today, because Parts 6 through 9 build straight on this loaded frame rather than starting over.
One honest caveat before the next part. All of this ran comfortably in memory because 345,600 rows is small. A year of second by second metrics across a fleet is not, and at that size the same read_csv call will exhaust RAM and fall over. That is a real limit with real fixes, chunked reads and pruning to only the columns you need, that the memory half of the NumPy and pandas material covers. Note it now, and reach for those tools the day your export stops fitting, not before.
Load the export before you write another loop
If you do one thing after reading this, open a notebook and run read_csv on your own export, then immediately print dtypes and scan for any column that should be a number and came back as object. That one check catches the failure that silently breaks more beginner analyses than any other. When a column is wrong, find the sentinel your collector writes, pass it to na_values, parse your timestamp, and confirm the mean is a number you believe. Do not write another parsing loop, because you already have one that works and the point of this part is that you rarely need it again. Next part takes this same loaded frame and makes it reproducible, the version control and environment discipline you already practise in ops, applied to data work. Load your export first, then meet it there.
References
- pandas documentation, read_csv, na_values and parse_dates arguments
- pandas documentation, DataFrame.iterrows and its dtype and performance notes
- pandas user guide, enhancing performance and vectorisation
- Real Python, how to iterate over rows in pandas and why you should not
- Data Science Series, NumPy and pandas, vectorisation and memory
- Infra to Data Science, the Complete Guide
About The Author
DrJha