Parse, tag, store and reshape statistical tabular data — then feed it straight into your PowerPoint charts and tables.
automizer-data is the data engine behind pptx-automizer. It ingests the messy crosstab exports that come out of statistical software (CSV/XLSX), slices them into clean, tagged two-dimensional tables, stores them in a database, and lets you query and transform them into exactly the result table your slide needs — ready to be rendered as a chart or table.
- Crosstab chaos. Exports from tools like GESStabs or PSPP are huge, repetitive and human-formatted.
automizer-dataparses them into structured, queryable datasets automatically. - "Where is that number?" Every table is tagged with the categories that describe it (country, variable, subgroup, measure…), so you select data by meaning instead of by cell coordinates.
- Reshaping by hand. The built-in
Modelizergrid engine lets you filter, rename, transpose, map tags, compute differences/sums and run custom callbacks to morph raw data points into the precise layout a chart expects. - Last-mile to PowerPoint. The
Converthelper turns a result grid directly into pptx-automizer chart and table objects — bar/line series, combo, scatter, bubble and full tables.
This project is work in progress, but already powers real reporting pipelines. Storage and querying is done with Prisma ORM tools.
- Multiple parsers out of the box —
Gesstabs,Pspp,MySQL,Genericand a flexibleTaggedparser, all built on a sharedParserbase. Tagged mode even reads Excel cell styles and conditional-formatting colors. - Tag-based data model — categories, tags, sheets, rows, columns, metadata and significance markers, stored and browsable via Prisma.
- The
Modelizergrid engine — a cell/row/column model with caching (toCache/fromCache), key-based indexing, transpose, and a rich set of modifiers (filter, map, mapTags, rename, exclude, calcDifference, calcSum, moveTagsToMeta, custom callbacks). - External statistics querying — a
DuckDBconnector to fetch aggregated crosstab results from a statistics API, with tag caching and multi-variable support. - Direct pptx-automizer output —
ConvertemitstoSeriesCategories,toVerticalLines,toCombo,toScatter,toBubblesandtoTablechart/table data. - Caching & performance — modelizer result caching and tags caching keep repeated queries fast.
The example-xlsx in the __test__/data folder is based on GESStabs.
If you are working on an existing project, you can add automizer-data to it using npm or yarn. Run
$ yarn add automizer-data
or
$ npm install automizer-data
in the root folder of your project. This will download and install the most recent version into your existing project.
If you want to see how it works and you like to run own tests, you should clone this repository and install the dependencies:
$ git clone git@github.com:singerla/automizer-data.git my-project
$ cd my-project
$ yarn install
$ yarn prisma generate
You can open prisma studio and take a look at the data:
$ yarn prisma studio
A lot of good stuff can be found at prisma.io.
The project ships with a Jest test suite that runs against an isolated, seeded SQLite database, so you can run it without touching your real data:
$ yarn test
According to parser's configuration, parsed data will sliced, tagged and separated into two-dimensional tables.
The Database contains:
- Categories: Generic nouns to describe the basic structure of your project
- Tags: Values of a certain category
- Sheets: Two-dimensional tables and their additional info
Each Sheet will contain:
- a collection of rows
- a collection of columns
- the two-dimensional table body
- a collection of tags
- a collection of metadata that came along with the sheet
import { PrismaClient } from '@prisma/client'
import { Parser, Store } from '../src/index';
import { ParserOptions, Tagger, RawResultInfo } from "../src/types";
const store = new Store(
new PrismaClient()
)
const config = <ParserOptions> {
// This string separates tables if found in Column A
separator: 'Table Separator',
// A row that fits to any of the strings below will be
// separated into "meta"-field if found in Col A
metaMap: {
base: ['BASE'],
topBox: ['Top-2-Box (1-2)'],
bottomBox: ['Bottom-2-Box (4-5)'],
mean: ['Mean Value']
},
// Rows that equal to one of the labels below will be skipped.
skipRows: [
'company',
'* Annotation',
'(Sum of answers)'
],
// A callback function to be applied to every body row
renderRow: (row: string[]): (number|null)[] => {
return row.map(cell => {
if(cell === ' ') return null
else return Number(cell)
})
},
// The info array of each sub-table will be passed to this
// callback. Tagging can be fine tuned here.
renderTags: (info: RawResultInfo[], tags: Tagger): void => {
info.forEach((info, level) => {
let cat
// info.key contains the info string's original section
// could be 'body' or 'info'
if(info.key === 'body') {
cat = 'vartitle'
} else if(info.value.indexOf('- ') === 0) {
cat = 'measure'
} else if(level === 0) {
// We strip the table separator and pass CountryName
info.value = info.value.replace('Table Separator – ', '')
cat = 'country'
} else if(level === 1) {
cat = 'variable'
} else if(level === 2) {
cat = 'questionText'
}
if(cat) {
tags.push(cat, info.value)
}
})
},
}
const parse = new Gesstabs(config)
const file = `${__dirname}/data/test-data.xlsx`
const datasheets = await parse.fromXlsx(filename)
const summary = await store.run(datasheets)Besides
Gesstabs, you can use thePspp,MySQL,GenericorTaggedparsers in the same way — pick the one that matches your data source, or extend the sharedParserbase for a new format.
Xlsx-Parser will tranform tabular data into an intermediate JSON object. The closer your input data comes to this format, the easier it will be to implement a new parser type.
{
"tags": [
{
"category": "country",
"value": "Norway"
},
{
"category": "variable",
"value": "Q12"
},
{
"category": "category",
"value": "Bar soap"
},
{
"category": "subgroup",
"value": "Age"
},
{
"category": "measure",
"value": "nominal"
}
],
"columns": ["Total", "19-29", "30-39", "40-69"],
"rows": ["answer 1", "answer 2", "answer 3"],
"data": [
[29,18,36,12],
[39,19,24,11],
[19,28,46,10]
],
"meta": {
"significance": [
[null,null,"h",null],
[null,"h","l",null],
[null,null,"h","l"]
]
}
}As all the Sheets are tagged, our queries will use tags to find the desired datasets.
import { getData, Store } from '../src';
import { all } from '../src/filter';
import { value } from '../src/cell';
// A selector is an array of tags.
const selector = [
{
category: 'country',
value: 'Norway'
},
{
category: "variable",
value: "Q12"
}
]
// The grid will define rows, cols and a callback
// to run inside a target cell.
const grid = {
rows: all('row'),
columns: all('column'),
cell: value
}
const result = await getData(selector, grid)
// automizer-data will convert the result directly into
// a pptx-automizer-object.
const chartData = result.toSeriesCategories()Under the hood, every query result is backed by a Modelizer grid. You can transform it before handing it over to a chart, and Convert offers a range of output formats for pptx-automizer:
toSeriesCategories()— classic bar/line/pie series with categoriestoVerticalLines()— vertical line chartstoCombo()— combined chart typestoScatter()/toBubbles()— XY and bubble chartstoTable()— full table data (row labels, column labels and body)
Modelizer also lets you filter, rename, transpose, mapTags, moveTagsToMeta, calcDifference, calcSum or apply custom callbacks, and can cache its state via toCache() / fromCache() for fast repeated rendering.
Instead of (or in addition to) the local Prisma store, automizer-data can fetch pre-aggregated crosstabs from an external statistics service through the DuckDB connector. It supports multi-variable queries, tag caching and a configurable API endpoint/token, making it easy to plug into a remote analytics backend.