Build wide counts, totals, and means directly from search events.
Step 1 of 3 · Learn
.pivot_table() can combine repeated index-column pairs. Always choose aggfunc explicitly: "size" counts rows, "sum" adds values, and "mean" averages recorded values. Its default is mean, not count.
| index | region | device | event |
|---|---|---|---|
| 0 | A | phone | 1 |
| 1 | A | phone | 2 |
| 2 | A | web | 3 |
| 3 | B | phone | 4 |
| region | phone | web |
|---|---|---|
| A | 2 | 1 |
| B | 1 | 0 |
fill_value=0 fills empty result cells after aggregation. It suits event counts and totals when no events means zero. Leave absent mean cells missing; a missing average is not a measured value of zero.
| index | region | device | value |
|---|---|---|---|
| 0 | A | phone | 10 |
| 1 | A | phone | 30 |
| 2 | B | web | 20 |
| region | phone | web |
|---|---|---|
| A | 20 | NA |
| B | NA | 20 |
For these tasks, first drop rows missing country or device. Order device columns with .reindex(columns=[...]) and sort the country index. With count and total reports, fill_value=0 in reindex also supplies a device column absent from the entire input. Do not use that fill for mean reports. For means, dropna=False keeps all-missing result rows and columns after those input rows with missing keys have been excluded.
From searches, exclude rows missing country or device and count search rows for each country-device pair. Return a country-indexed DataFrame sorted by country, with desktop, mobile, and tablet columns in that order. Fill absent pairs and device columns with 0. Preserve axis names country and device. Save the DataFrame as result.
Loading Python and Pandas…
Run the code to see DataFrame results here.
