01Brief07 / 12

One row per channel, and a cost per click

campaign_report(frame) takes a frame of individual campaigns — channel, clicks, spend — and returns one row per channel: how many campaigns ran, the totals, and what a click cost. Biggest spender first.

Function	campaign_report()
Track	Tables with pandas
Difficulty
Time	~26 min
Concepts	groupby with named aggregations reset_index turns keys back into a column a division that has to be guarded
Cases	10
Needs	pandas about 9 MB, because pandas brings numpy with it, fetched once and then cached by your browser

02The contract

Return a list of dicts, one per channel, with exactly the keys 'channel', 'campaigns', 'clicks', 'spend', 'cost_per_click'.
'campaigns' is an int count of rows; 'clicks' is an int total; 'spend' is a float total.
'cost_per_click' is spend / clicks rounded to 4 decimal places.
A channel with zero clicks has a cost_per_click of None, not an error and not infinity.
Rows are sorted by spend descending, and by channel name where two channels spent the same.
An empty frame gives an empty list.
The caller’s frame is not modified.

03Why this one

Almost every table you are handed is longer than the table anybody wants to read. The reduction from rows-as-events to rows-as-subjects is the most common thing pandas is asked to do, and the shape of the result is where it usually goes wrong: the group keys end up in the index instead of a column, the aggregated columns end up with names nobody can read, and the derived ratio divides by a zero that was always going to be there.

—The concept behind it

Dictionaries on pythonmastery.io →

That is the reading half. Read it there if the idea is new, then come back here — explanation is what makes something make sense, and practice is what makes it stay.

—Worth doing first

The region that vanished from the report

Not a gate — start this one whenever you like. These cover ideas it leans on, and a tick means you have already done them.

—What this leads to

These build on what you are about to write here.

04Study first: the four shapes a groupby can hand you

The aggregation is the easy part. Everything below differs only in what shape comes back, and picking the wrong one is what makes the next five lines awkward.

import pandas as pd
frame = pd.DataFrame({'k': ['a', 'a', 'b'], 'n': [1, 2, 10], 'm': [5.0, 5.0, 1.0]})

# 1. One column, one function -> a Series indexed by the key.
frame.groupby('k')['n'].sum()
# k
# a     3
# b    10        the key is the index, not a column

# 2. Named aggregations -> a DataFrame with the columns you asked for.
frame.groupby('k').agg(total=('n', 'sum'), runs=('n', 'size'))
#    total  runs
# k
# a      3     2
# b     10     1  still indexed by k

# 3. reset_index() -> k becomes an ordinary column again.
frame.groupby('k').agg(total=('n', 'sum')).reset_index()
#    k  total
# 0  a      3
# 1  b     10

# 4. as_index=False -> the same thing, said at the groupby.
frame.groupby('k', as_index=False).agg(total=('n', 'sum'))

# And the row-by-row form, once you have a frame:
frame.to_dict('records')
# -> [{'k': 'a', 'n': 1, 'm': 5.0}, ...]   one dict per row

agg(name=(column, function)) names the output columns for you
The alternative — agg({'n': ['sum', 'size']}) — gives you a MultiIndex on the columns, so every later reference reads frame[('n', 'sum')] and every rename is a fight. Named aggregation is a keyword per output column, each one a (source column, function) pair, and the names come out exactly as you typed them. Reach for it by default.
The group key lives in the index until you move it
That is why a groupby result so often needs .reset_index() before it will behave like a table: sorting by the key, merging on it, or turning the rows into dicts all want it as a column. as_index=False does the same job at the other end. Neither is more correct — but knowing which shape you are holding is what stops you writing .index.name and .rename in the same function.
The derived column comes after the aggregation, never inside it
Cost per click is total spend over total clicks, which is not the mean of the per-campaign ratios — a campaign with four clicks would count as much as one with four thousand. Aggregate the numerator and denominator first, then divide the columns. This is the same trap as averaging percentages, and it is invisible in the output.

your_code.py

05One way to write it

import pandas as pd


def campaign_report(frame):
    # Named aggregation: one keyword per output column, so the names come out
    # readable and there is no MultiIndex to unpick afterwards.
    totals = frame.groupby("channel").agg(
        campaigns=("clicks", "size"),
        clicks=("clicks", "sum"),
        spend=("spend", "sum"),
    )

    # channel is the index at this point; the report wants it as a column.
    totals = totals.reset_index()

    # Biggest spender first, name breaking the ties so the report is stable.
    totals = totals.sort_values(
        ["spend", "channel"], ascending=[False, True]
    )

    report = []
    for row in totals.to_dict("records"):
        clicks = int(row["clicks"])
        report.append(
            {
                "channel": row["channel"],
                "campaigns": int(row["campaigns"]),
                "clicks": clicks,
                "spend": float(row["spend"]),
                # Spend with no clicks is unmeasurable, not infinite.
                "cost_per_click": (
                    round(float(row["spend"]) / clicks, 4) if clicks else None
                ),
            }
        )
    return report

'size' counts rows, 'count' counts non-missing values

They agree until a column has a NaN in it, and then they quietly differ: size is the number of rows in the group, count is how many of them had a value. For 'how many campaigns ran' the answer is size — a campaign with a missing click figure still ran. Choosing between them deliberately is worth more than the four characters it costs.

The ratio is built per row, so the guard is a plain conditional

Dividing the two columns is the faster, more pandas-ish move — and then you own an inf, which you have to find (replace([np.inf, -np.inf], np.nan)) and then convert to None on the way out, because JSON has no infinity. At this size the loop that builds the dicts is where the guard belongs: one conditional expression, no cleanup pass, and the intent legible on the line where the division happens.

int() and float() at the boundary, not before

Inside the frame, numpy’s int64 and float64 are what you want — they are what make the aggregation fast. The moment the data leaves as ordinary Python, convert: np.int64 is not JSON-serialisable, np.float64 formats differently in an f-string, and both will eventually surprise something downstream that was written against plain ints. to_dict('records') does not convert for you.

—Other ways to write itall of these pass

Every version below is correct and verified against the same cases by the build. They are here because knowing which one to reach for is the part that takes judgement, and the trade-off is stated rather than left for you to guess.

as_index=False instead of reset_indexfine

import pandas as pd


def campaign_report(frame):
    totals = frame.groupby('channel', as_index=False).agg(
        campaigns=('clicks', 'size'),
        clicks=('clicks', 'sum'),
        spend=('spend', 'sum'),
    ).sort_values(['spend', 'channel'], ascending=[False, True])

    return [
        {
            'channel': row['channel'],
            'campaigns': int(row['campaigns']),
            'clicks': int(row['clicks']),
            'spend': float(row['spend']),
            'cost_per_click': (
                round(float(row['spend']) / int(row['clicks']), 4)
                if int(row['clicks']) else None
            ),
        }
        for row in totals.to_dict('records')
    ]

The same shape, said at the groupby rather than after it. Whether it reads better depends on whether you think of the key as 'in the index and then moved' or 'never in the index at all' — for a function that only ever wants a table, this is the more honest of the two.

vectorised division, with the zero masked before it happensfine

import pandas as pd


def campaign_report(frame):
    totals = frame.groupby('channel').agg(
        campaigns=('clicks', 'size'),
        clicks=('clicks', 'sum'),
        spend=('spend', 'sum'),
    ).reset_index()

    safe_clicks = totals['clicks'].mask(totals['clicks'] == 0)
    ratio = (totals['spend'] / safe_clicks).round(4)
    totals['cost_per_click'] = ratio.astype(object).where(ratio.notna(), None)

    totals = totals.sort_values(['spend', 'channel'], ascending=[False, True])
    totals['campaigns'] = totals['campaigns'].astype(int)
    totals['clicks'] = totals['clicks'].astype(int)
    totals['spend'] = totals['spend'].astype(float)

    return [
        {
            'channel': row['channel'],
            'campaigns': int(row['campaigns']),
            'clicks': int(row['clicks']),
            'spend': float(row['spend']),
            'cost_per_click': row['cost_per_click'],
        }
        for row in totals.to_dict('records')
    ]

The version that scales: the ratio is computed for the whole column at once, and the zero denominators are turned into missing values *before* the division rather than into infinities to be cleaned up after. The where() at the end is what turns pandas' NA back into Python’s None.

06When not to write this at all

The loop at the end stops paying its way somewhere around a hundred thousand groups — at that point do the division vectorised, with the denominator masked: totals['spend'].div(totals['clicks'].replace(0, pd.NA)) leaves a missing value exactly where the guard would have put None, and one .where() is cheaper than a hundred thousand Python iterations. Also, if the report is going straight into another frame rather than to a caller who wants dicts, stop at the sort and hand back the frame — to_dict('records') on the way to a to_csv is two conversions to get back where you started.

07Take it further

Add a 'share_of_spend' column: each channel’s spend as a fraction of the total, to three places, and check that the shares sum to 1.
Take a minimum-clicks argument and report the channels below it separately, so a channel with nine clicks does not get a headline cost per click.
Group by channel and month together, and see what reset_index() does with two keys.