Datasets

A census return typed into a spreadsheet, a CSV exported from a database, a JSON file of catalogue records: Pangur reads each as a dataset. Its columns and rows become text Ask can quote, and questions that need counting are put to the whole table.

Adding a dataset

Drop the file anywhere on the library. It is drafted as a dataset, and Add details… in the tray lets you say what it is. To put it on an item you already have, use Add a copy on that item’s Read tab.

Format	How it is read
CSV, TSV	One table. The first row is the header.
XLSX, ODS	One table per sheet, the first row of each as its header. Formulas are read as their values.
Parquet	One table, with the types the file declares.
JSON	A list of records, or an object holding one such list.
JSON Lines	One record per line.

In JSON, a record’s nested object is flattened one level, so address.town becomes a column. Anything deeper is kept in its cell as JSON text. A column with no heading is named for its letter, “Column C”.

Older .xls workbooks are not read as datasets; save them as .xlsx first. XML is read as plain text.

The file in your library is never changed. Pangur keeps its own copy of each sheet to page and query.

The table view

Every dataset opens as a table on the item’s Read tab: CSV, TSV, workbooks, Parquet, JSON and JSON Lines. The rows come from Pangur a page at a time as you scroll, so a sheet of a hundred thousand rows opens as quickly as one of ten.

• A workbook’s sheets are tabs above the table.
• Each column heading is a button. Press it to sort by that column over every row, press it again for the reverse order, and a third time for the file’s own order. It works from the keyboard as well as the mouse.
• Filter rows keeps the rows where any cell contains what you type.
• Columns opens a list of the columns beside the table, with each one’s type, how many cells are empty, how many different values it holds and, for numbers and dates, its range.
• In the file’s own order, each row carries its number in the file, header as row 1.

A JSON or JSON Lines file can also be shown as a Tree, with its records nested as the file has them. Each branch opens on its own.

Switch the reader to Extracted text to see what Ask and your assistants work from: a short description of the file, then its rows.

Older .xls workbooks open in a simpler table that reads the file in your browser.

Opening a citation

A source that cites cells opens the table at them. 1851!A2:D9 opens the sheet called 1851, scrolls to row 2 and marks columns A to D of rows 2 to 9.

How Ask reads a dataset

Two things go into the index. The first is a description of the dataset: its sheets, how many rows each has, and every column with its type and, for numbers and dates, its range. The second is the rows themselves, written out as Row 12 — Parish: Clun; Miles: 12, for the first 2,000 rows of each sheet.

An answer in Ask that quotes a row cites it by its cells, as a spreadsheet would name them: 1851!A2:D9 is columns A to D, rows 2 to 9, on the sheet called 1851. The header is row 1, so the numbers match what you see when you open the file in a spreadsheet program.

Questions that need counting

A count, a total, an average or a filtered list is not in any passage, and a figure guessed from the first few rows would look just like one that had been found. So when a question needs one, Ask writes a single query against the dataset, and Pangur runs it at once over every row.

The result sits under the answer: the query first, then the rows it returned as a table, up to 20 rows and 8 columns. The answer is written before the query runs, so it introduces the table and leaves the figures to it. Read the query as you would a footnote. It says exactly which rows were counted and how.

If the query cannot be run, the answer says so in a sentence and gives the reason.

Tables and charts in Write

In a draft, type / and choose Comparison table or Chart, then fill it in from the form beside the draft.

• A table from a dataset. In the table’s form, choose Take the rows from a dataset, find the work and the file, and write a query. Run the query shows the first rows; Save to the draft keeps up to 50 rows and 12 columns. Under the table, the draft names the file, the date the rows were taken and the query.
• A chart. A bar, line or scatter chart, over rows typed into the form or rows a dataset’s query returns. Choose the column along the bottom and up to four columns of numbers to plot (one, for a scatter). The preview is the chart as it will be saved. A chart holds up to 200 rows.

When you save a version, every table and chart drawn from a dataset runs its query again and keeps the rows it returns that day, with the date. The version stays as it was saved even if the file changes later. If a query cannot be run then, the figure keeps the rows it had and the version is still saved.

Each chart carries its numbers in a table under it, under The numbers, and every bar or point names its value when you hover over it.

Tables, charts and maps in an exhibition

In the exhibition editor, add Table from a dataset or Chart, choose the dataset and run a query. The rows are kept in the block when you save it, up to 500, so the published page never reads your library and does not change when the file does. To bring a block up to date, open it, run the query again and save.

A Map block can take its places from a dataset: choose Take the places from a dataset, run a query, and say which columns hold the latitude, the longitude and the name of each place. Rows without coordinates are left out, and the form says how many. A map drawn from a dataset may hold up to 500 places.

On iPhone and iPad

A dataset in an item’s files opens as a table, paged from Pangur like the web’s. On an iPad each column of the file is a column on screen and the headings sort. On an iPhone each row is a card of its cells; sort from the button at the top and filter from the search field. Open the file shows the file itself.

Queries are read-only

A query is one SELECT over one sheet. It can read that sheet and nothing else: no other file, no other part of your library, nothing on the network. It cannot write, so no question can alter a dataset.

Assistants

A connected assistant uses query_dataset. Called with no query, it returns the dataset’s profile: sheets, columns with their types, ranges and counts of empty and distinct values, and five sample rows. That is what to read first. Called with sql, it runs one SELECT (or WITH … SELECT) against the chosen sheet, which the query names as the table data, and returns the columns and rows. The SQL is DuckDB’s. Column names with spaces go in double quotes:

SELECT "Parish", count(*) AS households
FROM data
GROUP BY "Parish"
ORDER BY households DESC

See Assistant tools for the full list.

Limits

• A file may have up to 20 sheets, and a sheet up to 256 columns. Past either, the file is stored but not read as a dataset.
• The first 200,000 rows of a sheet are read. A longer sheet is cut there, and the profile says so.
• The first 2,000 rows of each sheet are indexed as passages Ask can quote. A query reaches every row that was read.
• A query returns at most 500 rows to an assistant, and has ten seconds to run.