Turn Complex Data Sets Into Public Tables Readers Can Check

Suppose your workbook has one sheet of grant awards and another of payments. You want a public page where readers can compare each award with the amount paid so far. Combining every row into one table seems convenient—but it can make the award total wrong.

For publishing, the useful question about complex data sets is not just “How many rows are there?” It is “What relationships and definitions must survive the export?” Start by choosing what one published row represents, then build and check that table before uploading it.

1. Define the published row

Write a sentence such as:

Each row represents one grant award, with payments recorded through a stated cutoff date.

That row definition—often called the table’s grain—determines which fields belong together. An award and a payment are different observations: one award may have several payments.

The tidy-data principles put variables in columns, observations in rows and individual values in cells. Apply that structure to each source table rather than treating a multi-sheet workbook as one undifferentiated dataset.

Here is a fictional example, with all amounts in USD and complete payment records through September 30, 2026.

Awards: one row per award

award_id	recipient	award_amount_usd
A001	North Library	100000
A002	East Museum	60000

Payments: one row per payment

payment_id	award_id	payment_amount_usd
P001	A001	40000
P002	A001	60000
P003	A002	20000

award_id connects the sheets. It must be unique in Awards, but repetition is expected in Payments. payment_id identifies each payment.

2. Aggregate before joining

A join matches records using a shared field. If you join these awards directly to individual payments, A001 appears twice. Its award amount appears twice too.

Summing that repeated award column gives $260,000, although the original awards total is $160,000. The payment rows are legitimate; the mistake is adding an award-level measure at payment level.

Repeated matching keys can create still larger expansions when both tables contain multiple matches. The pandas join documentation explains that many-to-many joins produce every matching combination of records.

For an award-level publication:

1. Restrict Payments to the documented cutoff. Use a source extract already limited to that date, as in this example, or filter using the relevant payment-date field.
2. Group Payments by award_id.
3. Sum payment_amount_usd within each group and count the payment records.
4. Join that summary to Awards using award_id, retaining every award—a left join with Awards on the left.
5. Confirm that the result still has one row per award.

The expected public table is:

award_id	recipient	award_amount_usd	paid_total_usd	payment_count	award_minus_paid_usd
A001	North Library	100000	100000	2	0
A002	East Museum	60000	20000	1	40000

Define award_minus_paid_usd as award amount minus recorded payments. Do not label it “overdue” or “unspent”: this example has neither a payment schedule nor expenditure records.

If readers need transaction dates or individual payment amounts, prepare a separate payment-level table. Do not force both levels of detail into a summary whose totals readers might add.

3. Validate the relationships and totals

Before export, check:

• Keys: Award IDs are nonblank and unique in Awards; payment IDs are nonblank and unique in Payments.
• Unmatched records: Every payment’s award ID exists in Awards. Investigate exceptions rather than silently dropping them.
• Row count: The award summary contains two rows, not three.
• Reconciliation: Award amounts total $160,000; recorded payments total $120,000; payment counts total three.
• Types and missing amounts: Amounts remain numeric, and identifiers remain intact. Investigate blank payment amounts before summing; a total that skips unknown amounts is not a complete paid total.
• Exceptions: Investigate negative payments, payments exceeding awards and incomplete coverage before interpreting them.

In real data, no matching payment record does not automatically prove zero payments. Use zero only when the source coverage supports that interpretation. Otherwise leave the amount missing and add a field explaining why, such as payment_data_status. If the paid total is unknown, keep award_minus_paid_usd missing too.

For broader file checks, use the spreadsheet exploration workflow before building the publication export.

4. Publish the reviewed table with its context

Export the award summary as a clean rectangular file: one header row, consistent columns, no merged headings and no subtotal rows mixed with observations. Keep the source sheets and transformation steps separately so you can reproduce the summary.

Alongside the public table, document:

• What each row represents and which awards are included.
• Source organization, source links and retrieval date.
• Payment cutoff date and currency.
• Definitions of calculated fields and missing-value rules.
• Grouping, joins, exclusions and known limitations.
• Release date or version and applicable reuse terms.

This follows W3C guidance on provenance, data quality and versioning: readers should know where data came from, what changed and which release they are using. The caption and summary guide helps turn those notes into readable context.

As of October 25, 2026, TablePage lists CSV, TSV, XLSX and XLS uploads that generate public dataset pages with filterable tables. Upload only the reviewed publication file—not a working workbook containing sensitive sheets or personal information.

Before sharing the link, inspect the published headers, identifiers, numeric values and row count. Filter to A001 in this example: it should show one row with payment_count equal to 2 and paid_total_usd equal to 100000. The public page should preserve the meaning you validated, not merely display the file.