Data workflow guides

Pivot a regional sales CSV and reconcile its totals

·

A pivot table can answer several questions from the same export. Use a sum to reconcile decimal amounts, then switch to averages or counts to see how blank records affect the result.

Pivot Table Generator

Reconcile amounts by region and quarter

Load the example. It selects region for rows, quarter for columns and amount for values. Build the table with Sum. North Q1 adds 9007199254740993.10 and 0.20 to give the exact result 9007199254740993.3. Trailing decimal zeros are optional in this output format.

Check that North and South are in the order they first appear, with Q1 before Q2. One South Q2 amount is blank. The numeric sum skips that blank and keeps the other amount, 5.125.

Expected pivot CSV for Sum
region,Q1,Q2
North,9007199254740993.3,2.345
South,-1.005,5.125

RFC 4180, CSV records and quoting

Compare averages and record counts

Change the aggregation to Average and keep two decimal places. Build the table again. North Q1 is 4503599627370496.65 and North Q2 rounds to 2.35. South Q1 rounds from -1.005 to -1.01. Halfway values round away from zero after the group’s exact sum is divided by its numeric count.

Change to Count records. North Q1 and South Q2 each show 2. The South Q2 count includes the blank amount, while its average uses only the numeric value.

Expected pivot CSV for Average at two places
region,Q1,Q2
North,4503599627370496.65,2.35
South,-1.01,5.13

Python decimal rounding modes and exact decimal arithmetic

Check group labels and the exported table

If the export seems to split one region into two rows, compare the group cells as exact text. Case, spaces and leading zeros affect membership. A blank label can be a group too.

Download the complete CSV and reopen it with commas as the separator. Check unexpected empty numeric cells against the source records. Correct a nonblank invalid number at its reported record and field before rebuilding.

RFC 4180, CSV records and quoting

Common questions

Can I make a one-dimensional pivot?

Choose Single series for the column field to produce one value column and one row per distinct row-field value.

How does the tool handle numbers with decimal exponents?

It interprets decimal scientific notation exactly when the resulting value fits the supported integer and fractional places. Use a period as the decimal separator.

Sources

Put it into practice.

Related guides