pivot-table-coredeprecated

A data analsis tool for viewing and editing aggregated data.

Usage no npm install needed!

<script type="module">
  import pivotTableCore from 'https://cdn.skypack.dev/pivot-table-core';
</script>

README

Table of Contents

  1. Install
  2. Test
  3. Build
  4. Usage
  5. API

Install

Make sure you have Node 9.4.0, NPM 5.6.0 and git. Clone the repository:

git clone git@bitbucket.org:goodcarrot/pivot-table.git

Install dependencies:

npm install

Test

Run the test suite:

npm test

Build

Build everything:

npm run build-all

Build for development (node and web):

npm run build-dev

Build for production (node and web):

npm run build-prod

Build for development (node only):

npm run build-dev-node

Build for development (web only):

npm run build-dev-web

Build for production (node only):

npm run build-prod-node

Build for production (web only):

npm run build-prod-web

Usage

Configuration file

Here's an example:

module.exports = {
  columns: [
    // pivots
    {
      data: 'rollup_name',
      export: true,
      id: 'rollup_id',
      kind: 'pivot',
    },
    {
      data: 'geography_name',
      export: true,
      id: 'geography_id',
      kind: 'pivot',
    },
    {
      base: true,
      data: 'name',
      export: true,
      id: 'media_id',
      kind: 'pivot',
    },
    // values
    {
      data: 'plan_spend',
      export: true,
      kind: 'value',
      reducer: 'sum',
    },
    {
      data: 'opt_spend',
      export: true,
      kind: 'value',
      reducer: 'sum',
    },
    {
      data: 'override_impact',
      export: true,
      kind: 'value',
      reducer: 'mean',
    },
    // derived
    // first order
    {
      data: 'spend_difference',
      derive: aggregate => aggregate.opt_spend - aggregate.plan_spend,
      export: true,
      kind: 'derived',
      order: 1,
    },
    // second order
    {
      data: 'spend_difference_per_impact',
      derive: aggregate => aggregate.spend_difference / aggregate.override_impact,
      export: true,
      kind: 'derived',
      order: 2,
    },
  ],
  postProcessing: {
    createRowData(x) {
      x.postProcessing = true;
    },
    initialize(x) {
      x.postProcessing = true;
    },
    summarizeAggregate(x) {
      x.postProcessing = true;
    },
  },
  subTable: {
    data: 'creative',
    columns: [
      {
        data: 'mct_name',
        kind: 'pivot',
      },
      {
        data: 'opt_spend',
        kind: 'value',
        reducer: 'sum',
      },
      {
        data: 'mct_name_opt_spend',
        derive: aggregate => `${aggregate.mct_name}_${aggregate.opt_spend}`,
        kind: 'derived',
        order: 1,
        reducer: 'sum',
      },
    ],
  },
  options: {
    paging: {},
  },
};

Node

Example data JSON

{
  "1": {
    "geography_id": 1,
    "geography_name": "National",
    "media_id": 1,
    "name": "Network TV - Spanish Language Network - Non-Prime",
    "opt_spend": 90,
    "override_impact": 100,
    "plan_spend": 100,
    "rollup_id": 17,
    "rollup_name": "Network TV"
  },
  "2": {
    "geography_id": 1,
    "geography_name": "National",
    "media_id": 2,
    "name": "Network TV - Spanish Language Network - Prime",
    "opt_spend": 50,
    "override_impact": 200,
    "plan_spend": 60,
    "rollup_id": 17,
    "rollup_name": "Network TV"
  },
  "3": {
    "creative": {
      "704": {
        "mct_name":  "color",
        "opt_spend": 10
      }
    },
    "geography_id": 1,
    "geography_name": "National",
    "media_id": 3,
    "name": "INVERSE»SWTO_Mid Spot 8/22»1 x 1»NA»RMB»DT»CPM»CTX»W»NA»SS»NA»NULLSG»PJ3ZWP",
    "opt_spend": 30,
    "override_impact": 100,
    "plan_spend": 25,
    "rollup_id": 51,
    "rollup_name": "Rich Media"
  },
  "4": {
    "creative": {
      "704": {
        "mct_name":  "color",
        "opt_spend": 10
      }
    },
    "geography_id": 1,
    "geography_name": "National",
    "media_id": 4,
    "name": "INVERSE»SPON_Innovation Section Takeover_Mid Spot»300 x 250»NA»BAN»MO»CPM»CTX»W»NA»TP»NA»NULLSG»PJ7DKL",
    "opt_spend": 30,
    "override_impact": 200,
    "plan_spend": 25,
    "rollup_id": 51,
    "rollup_name": "Rich Media"
  }
}

Import the PivotTableCore package, configuration file and data, and define metaData:

const PivotTableCore = require('./PivotTableCore');
const config = require('./config');
const data = require('./data.json')
const metaData = { initialPivots: ['rollup_name'] };

Instantiate and initialize:

const pivotTable = new PivotTableCore();
pivotTable.initialize(data, config, metaData);

Pivot on some columns and inspect:

// set the active pivot
pivotTable.setActivePivot(['rollup_name', 'geography_name']);
console.log(pivotTable.getActivePivotKey());
// "rollup_name,geography_name"

// aggregate and summarize rows
pivotTable.pivot();

// get aggregates for active pivot
let aggregates = pivotTable.getAggregates();
console.log(Object.keys(aggregates));
// ["17,1", ["51,1"]]

// get a specific aggregate
let agg = pivotTable.getAggregate(['17,1'])

// keys are composites made from IDs
console.log(agg);
// { geography_name: "National", name: "Mixed", opt_spend: 140, ... }

API

Table of Contents

PivotTableCore

Class representing a PivotTable.

initialize

Initialize the pivot table.

Parameters

  • data object Row data.
  • config object PivotTableCore configuration.
  • metaData object Meta data.

getAggregate

Returns an aggregate from the active pivot.

Parameters

  • key string Aggregate key (i.e. it's id).

Returns Object

getAggregates

Returns all aggregates from the active pivot.

Returns Object

getActivePivotColumns

Returns the active pivot's columns names.

Returns Array

getActivePivotKey

Returns a composite key formed by the active pivot's columns names.

Returns string

getPivot

Returns a pivot (an object containing aggregates)

Parameters

  • key string Pivot key (column names separated by comma)

Returns Object A pivot.

pivot

Aggregates row data by active pivots, summarizes data and runs optional post processing specified in config.

query

Returns the aggregate data filtered by the query set, with pagination info.

setActivePivots

Sets the active pivot. (Doesn't perform the pivot logic though)

Parameters

  • activePivots Array An array of column names.

setFilter

Sets filter options for the query set

Parameters

  • conjunction string Determines if all or any of the predicates should be matched ('&&' or '||') (optional, default '&&')
  • predicates Array A list of predicate objects: column name (string) , value (any), and a comparison (string). Supported comparisons: '==', '!=', '<', '<=', '>', '>=', and 'in'. (optional, default [])

setPagination

Sets pagination options for the query set

Parameters

  • isActive (optional, default true)
  • currentPage number (optional, default 0)
  • rowsPerPage number (optional, default 15)

setSorting

Sets sorting options for the query set

Parameters

  • columnName string Column to sort by.
  • direction string Sorting direction ('ASC' or 'DESC'). (optional, default Directions.ASC)