The same load score, computed one row at a time with iterrows, took 10,500 milliseconds on a month of one cluster metrics. Written as a single column expression it took 1.2 milliseconds. Same answer, same machine, about 8,700 times apart. That gap is the whole subject of this part, because the row at a time habit is exactly the one awk and jq trained into you, and it is the one thing that will make your data code crawl.
From row-by-row to whole-column thinking
awk reads one line, splits it into fields, and acts, then moves to the next line. jq walks a JSON stream one value at a time. Both are superb, and both teach a mental model that betrays you in pandas, because a DataFrame is not a stream you march down, it is a set of columns you operate on all at once. Your unit of work is the column, not the row. When you want a derived field, you do not visit each row, you describe the arithmetic on the whole column and let NumPy run it in compiled code underneath. That single shift, from marching down rows to naming a column operation, is most of what separates slow data code from fast data code. NumPy also broadcasts a scalar or a shorter array across a whole column for you, so multiplying by 0.7 touches every element without a loop you ever write.
Where the project stands: last part you got real frames into Python from SQL, an API and a monitoring endpoint, correctly typed and time indexed. This part you learn to transform those frames, filter them, group them and shrink them, using the same operations you already reach for on the command line, translated to their vectorised form. Nothing here needs new data, it works on the one cluster export you have been carrying since Part 7. Four moves, shown below, turn a raw frame into features, and every one of them has a plain awk or jq cousin.
Vectorised arithmetic instead of a loop
Start with the move you make most, deriving one column from others. A load score of 0.7 times CPU plus 0.3 times memory is the kind of thing you would write in awk as arithmetic on two fields. In pandas you can write it the awk way, looping over the rows, or the column way, and they give exactly the same answer.
# tested on Python 3.10.12, pandas 2.3.3, numpy 2.2.6
import numpy as np, pandas as pd
# metrics: a month of one cluster, 8 nodes at 1 minute, carried from Part 8
score_loop = []
for cpu, mem in zip(metrics['cpu_pct'], metrics['mem_pct']):
score_loop.append(0.7*cpu + 0.3*mem) # the awk habit, row by row
score_vec = 0.7*metrics['cpu_pct'] + 0.3*metrics['mem_pct'] # whole column at once
print('rows', len(metrics))
print('identical', np.allclose(score_loop, score_vec))
rows 345600 identical True
Same numbers, so the only question is speed, and the speed is not close. Timed with timeit on this frame, the zip loop above took 51 ms and the column expression took 1.2 ms. Worse, the version most tutorials reach for first, df.iterrows(), took 10,500 ms for the identical result, because it rebuilds a Series for every one of the 345,600 rows. That is the reference number to burn in: iterrows was about 8,700 times slower than the column form, and it was the natural translation of the awk habit.
import timeit
loop = lambda: [0.7*c + 0.3*m for c, m in zip(metrics['cpu_pct'], metrics['mem_pct'])]
vec = lambda: 0.7*metrics['cpu_pct'] + 0.3*metrics['mem_pct']
print('zip loop ', round(min(timeit.repeat(loop, number=1, repeat=5))*1000), 'ms')
print('vectorised', round(min(timeit.repeat(vec, number=20, repeat=5))/20*1000, 1), 'ms')
zip loop 51 ms vectorised 1.2 ms
Boolean masks and the copy trap
Filtering rows is where the awk model maps most cleanly. A field test like a CPU above eighty becomes a boolean mask, an array of true and false the same length as the frame, that you index with. metrics[metrics[‘cpu_pct’] > 80] keeps the hot rows, exactly as awk would keep lines where the third field clears the threshold. A mask is a first class value, you can combine masks with the bitwise and and or, and you can count how many rows it selects. Compound tests read cleanly too, metrics[(metrics[‘cpu_pct’] > 80) & (metrics[‘mem_pct’] > 70)] keeps rows where both hold, with parentheses around each clause because the bitwise and binds tighter than the comparison. What bites here is not the selection, it is what you do next.
# masking is your awk field test: keep rows where cpu is above 80
hot = metrics[metrics['cpu_pct'] > 80]
print('hot rows', len(hot))
hot['flag'] = 'hot' # feels right, changes nothing, warns instead
hot rows 19655 SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead
hot is a copy of a slice, not a window onto the original, so writing to it sets a value on a throwaway frame and pandas warns that your change went nowhere useful. Fixing it means dropping the two step habit, filter then assign, and doing it in one, assigning on the parent through .loc with the mask and the column named together.
metrics['flag'] = 'normal'
metrics.loc[metrics['cpu_pct'] > 80, 'flag'] = 'hot' # assign on the parent
print(metrics['flag'].value_counts().to_string())
# three way classify with no loop, nested np.where reads like nested awk
metrics['state'] = np.where(metrics['cpu_pct'] > 80, 'hot',
np.where(metrics['cpu_pct'] < 25, 'idle', 'normal'))
print(metrics['state'].value_counts().to_string())
flag normal 325945 hot 19655 state normal 253217 idle 72728 hot 19655
np.where is the vectorised if, and nesting it gives you the bucketing you would write as a chain of awk conditions, run over the whole column at once. One warning about the warning itself, and it contradicts a lot of older advice. pandas 3.0, the current major line, turns copy on write on by default and drops SettingWithCopyWarning entirely, so the same hot[‘flag’] = ‘hot’ now edits a throwaway copy and tells you nothing at all, which is arguably worse because there is no message to catch the mistake. The .loc habit is correct in every version, on 2.x and on 3.0 alike, so build it now and you never have to care which pandas you are on.
Grouping without an associative array
Every operator has written the awk one liner that totals a field per key, building an associative array as it walks the file. pandas does the whole thing in one call, and it does it in compiled code, which is why the war story below dropped from minutes to seconds. groupby splits the frame by a key, applies an aggregation to each group, and hands back one row per key. Ask for several statistics at once and you get a small summary table.
# awk would total per key with sum[node] += cpu; pandas groups in one call
per_node = metrics.groupby('node')['cpu_pct'].agg(['mean', 'max', 'count']).round(1)
print(per_node.to_string())
# groupby.transform adds a per node zscore aligned back to every original row
grp = metrics.groupby('node')['cpu_pct']
metrics['cpu_z'] = (metrics['cpu_pct'] - grp.transform('mean')) / grp.transform('std')
print('cpu_z overall std', round(metrics['cpu_z'].std(), 2))
mean max count node node-0 39.8 74.4 43200 node-1 37.2 70.1 43200 node-2 34.3 73.4 43200 node-3 37.8 72.6 43200 node-4 25.8 58.7 43200 node-5 39.0 73.7 43200 node-6 22.4 55.5 43200 node-7 78.3 100.0 43200 cpu_z overall std 1.0
node-7 jumps out at a mean of 78.3 against the twenties and thirties everywhere else, which is the kind of thing you want a summary to surface, not a scroll through 345,600 rows. The second call shows the move that trips up newcomers most, transform against agg. agg collapses each group to one row, transform keeps the original shape and broadcasts the group statistic back onto every row, so a per node zscore lines up with the frame you already have and can be modelled directly. That distinction matters, agg for a report, transform for a feature, and reaching for the wrong one is the quiet cause of a shape mismatch later. An overall standardised spread of 1.0 is what a correct per group zscore should give, a quick sanity check that the grouping aligned.
This is the same command line vocabulary you already own, moved onto columns. The Data Science Series works through vectorisation and the memory model in more depth in its part on NumPy and pandas, which is worth a read once your frames pass a few million rows and the constant factors start to matter.
Dtypes and the memory they cost
awk never made you think about types, a field was text until you did math on it. pandas makes the type a decision with a real cost, because a column stored as generic Python text carries a pointer and an object per value, while the same values as a category or a narrow number are packed tight. Node is the sharp example here. Stored as text it holds the same eight short strings repeated hundreds of thousands of times, one object each.
# same values, different storage, measured with memory_usage(deep=True)
print('node object ', round(metrics['node'].memory_usage(deep=True)/1e6, 2), 'MB')
print('node category', round(metrics['node'].astype('category').memory_usage(deep=True)/1e6, 2), 'MB')
print('frame float64', round(metrics.memory_usage(deep=True).sum()/1e6, 1), 'MB')
node object 21.77 MB node category 0.35 MB frame float64 32.8 MB
That node column drops from 21.77 MB to 0.35 MB by declaring it a category, a factor of sixty two, because a category stores each label once and keeps a compact integer per row. Convert the float columns from float64 to float32 where the precision is fine, which metric percentages certainly are, and the whole frame falls from 32.8 MB to 7.3 MB, roughly a fifth of the original. That is not a micro optimisation on a laptop, it is the difference between a frame that fits in memory and one that swaps, and it is free. This table is the artifact to keep.
| Column | As loaded | Typed properly | Smaller by |
|---|---|---|---|
| node, text | 21.77 MB, object | 0.35 MB, category | about 62 times |
| cpu_pct | 2.76 MB, float64 | 1.38 MB, float32 | 2 times |
| whole frame | 32.8 MB | 7.3 MB | about 4.5 times |
Not paying for memory you do not need is the same discipline the AI Engineering Series applies to model calls in its part on cost control and model routing, where the cheapest request is the one you never send. Here the cheapest byte is the one you never store, and a category column earns its place the moment a label repeats.
Vectorised pandas checklist and project status
Where the project stands now: the frame that arrived from SQL and Prometheus in Part 8 can be transformed, filtered, grouped and shrunk with column operations instead of loops, which is the working posture every later part assumes. Keep the translation table below as the reference artifact, one row per thing you already do on the command line and its vectorised pandas form, and run the checklist under it before you commit any transform.
| What you want | awk or jq | Vectorised pandas |
|---|---|---|
| Field from other fields | awk print 0.7*$3 plus 0.3*$4 | 0.7*df[‘cpu’] + 0.3*df[‘mem’] |
| Keep rows on a test | awk field greater than 80 | df[df[‘cpu’] > 80] |
| Classify into buckets | nested awk if and else | np.where(cond, a, np.where(…)) |
| Total or mean per key | awk sum[node] plus equals cpu | df.groupby(‘node’)[‘cpu’].mean() |
| Pull a nested JSON field | jq dot labels dot node | json_normalize then df[‘labels.node’] |
| Count distinct values | sort minus u then wc minus l | df[‘node’].nunique() |
Vectorised transform checklist
- No for loop or iterrows over rows for arithmetic, use a column expression.
- Row selection is a boolean mask, not a Python filter.
- Any assign on selected rows uses .loc on the parent, never a filtered copy.
- Multi way classification uses np.where or a mapping, not an if ladder.
- Per key summaries use groupby.agg, per row group statistics use groupby.transform.
- Repeated string columns are category, and float columns are float32 where precision allows.
- Check frame memory with memory_usage(deep=True) before it grows.
Rewrite one loop as a vector this week
If you do one thing after this, open the automation you already run over your metrics and find one place you loop over rows or call iterrows, then rewrite it as a column expression, an np.where, or a groupby, and time both with timeit. Keep the two numbers, because the gap is the argument you will make to yourself every time you are tempted back into the row at a time habit. Next part starts the genuinely new ground, the statistics an operator cannot skip, worked on the very frame you just learned to reshape. Bring it, typed and grouped, exactly as you leave it here.
References
- NumPy documentation, broadcasting rules for vectorised operations
- pandas documentation, group by split apply combine
- pandas documentation, returning a view versus a copy
- pandas documentation, copy on write, default in pandas 3.0
- pandas documentation, scaling to large datasets and dtypes
- Data Science Series, NumPy and pandas vectorisation and memory
- Infra to Data Science, the Complete Guide
About The Author
DrJha