Three rows, two of them for the same week and product. Run both reshapes and notice that only one of them says anything.
import pandas as pd
notes = pd.DataFrame({
'week': ['w1', 'w1', 'w2'],
'product': ['a', 'a', 'a'],
'units': [3, 5, 7],
})
notes.pivot(index='week', columns='product', values='units')
# ValueError: Index contains duplicate entries, cannot reshape
# pivot is a pure reshape. Two rows for one cell is a contradiction,
# so it refuses — which is the most useful thing it could do.
notes.pivot_table(index='week', columns='product', values='units')
# product a
# week
# w1 4.0 <- the mean of 3 and 5. No warning.
# w2 7.0
notes.pivot_table(index='week', columns='product', values='units',
aggfunc='sum')
# w1 8
# w2 7 <- and the dtype is int again, because sum of ints is int
# The cells that never happened, and the keyword for them:
sparse = pd.DataFrame({'week': ['w1', 'w2'], 'product': ['a', 'b'],
'units': [3, 7]})
sparse.pivot_table(index='week', columns='product', values='units',
aggfunc='sum')
# a b
# w1 3.0 NaN <- and now everything is float, because of the NaN
# w2 NaN 7.0
sparse.pivot_table(index='week', columns='product', values='units',
aggfunc='sum', fill_value=0)
# a b
# w1 3 0
# w2 0 7
- pivot refuses; pivot_table averages
They look like the same function with different names, and on data with one row per cell they return the same thing. The difference only shows up on a repeat: pivot raises ValueError because a cell cannot hold two values, while pivot_table quietly aggregates. If you believe your data has no repeats, pivot is the better call precisely because it will tell you when you are wrong.
- aggfunc='mean' is the default, and mean is rarely what a grid of counts wants
Units, orders, clicks, revenue — every one of those is a sum, and every one of them gets averaged by a pivot_table nobody passed aggfunc to. The habit worth forming is to always pass it, even when the default happens to be right, so the choice is written down where the next reader can see it rather than inferred from a table that reconciles.
- A missing cell brings NaN, and NaN brings float
A week/product pair with no rows has nothing to aggregate, so pivot_table puts NaN there — and because NaN only exists in a float column, one absent combination silently turns your whole grid of integers into floats. fill_value=0 fixes both at once: it fills the gaps during the reshape, so the column never has to hold a NaN and never has to widen to float.
Add the grid up and compare it with frame['units'].sum(). The gap tells you the cells are not totals. Then ask what pivot_table does when two rows want the same cell — the answer is in the first line of its documentation.
aggfunc defaults to 'mean'. Pass aggfunc='sum'. That fixes the reported cell and leaves the second half of the problem: the week/product pairs with no rows are still NaN, and NaN has made the whole grid float.
fill_value=0 handles the gaps inside the reshape, which keeps the dtype integer — better than .fillna(0) afterwards, which fills the holes but leaves every value a float. Then sort both axes on the way out, and guard the empty frame before the pivot_table rather than after it.
import pandas as pd def units_grid(frame): if frame.____: return {} table = frame.pivot_table( index='week', columns='product', values='units', aggfunc='____', fill_value=____, ) return { week: {product: int(table.loc[week, product]) for product in ____(table.columns)} for week in ____(table.index) }
import pandas as pd
def units_grid(frame):
# pivot_table on an empty frame has no axes to build, so stop early.
if frame.empty:
return {}
# aggfunc='sum' is the whole bug fix; fill_value=0 fills the cells that
# never happened *during* the reshape, so no NaN ever appears and the
# grid stays integer.
table = frame.pivot_table(
index="week",
columns="product",
values="units",
aggfunc="sum",
fill_value=0,
)
return {
week: {
product: int(table.loc[week, product])
for product in sorted(table.columns)
}
for week in sorted(table.index)
}
The cost of writing aggfunc='mean' explicitly is nine characters. The cost of leaving it out is that nobody reading the code — including you, in four months — can tell whether the mean was chosen or inherited. Every reshape that collapses rows is making a decision about what to do with a repeat, and decisions belong in the source.
Both put a 0 where the cell was empty. The difference is the dtype: pivot_table has to create the column before it can fill it, so without fill_value it creates a float column to hold the NaN, and .fillna(0) afterwards leaves you with 0.0 and 8.0 rather than 0 and 8. The int() in the comprehension would paper over it here — and would not, on a grid of counts you hand to somebody as JSON.
pivot raises on a duplicate cell, which makes it a reshape *and* an assertion: 'I believe there is one row per week and product, and if I am wrong I want to hear about it now'. pivot_table with aggfunc='sum' says something different — 'repeats are expected, and they add up'. Both are reasonable; what is not reasonable is using the aggregating one because it did not complain.
A dict of dicts is fine for four products and gets unusable somewhere around fifty, where a sparse grid means most of the cells are zeros nobody asked for — at which point the long form (one row per week/product/units, from groupby) is the smaller and more useful object, and the caller can pivot it when they actually want to display it. fill_value=0 is also a claim about the data: 'no delivery note' genuinely means zero units here, but on a grid of prices or temperatures the same keyword would invent readings that were never taken. When absent does not mean zero, leave the NaN and let the caller see the hole.
- Add a total row and a total column, and check both against frame['units'].sum().
- Use pivot instead, and handle the ValueError by grouping first — then compare which version you would rather maintain.
- Return counts of delivery notes alongside the units, so a cell of 8 from one note looks different from a cell of 8 from four.