Data Transformation

Our implementation for the data implementation methods utilises the Tidy.js library. For more information on Tidy.Js, visit: https://pbeshai.github.io/tidy

Data transformation allows for easy manipulation of data for visualisation. Below is a list of methods that can be used to manipulate your data.

Initial Data

Below is the initial data that is used in some of the examples below. The data structure is formatted as a JSON object.

Where data is not available in the below examples, the data below is assumed the default.

//! JSON formatted object
variable data = ;
[
    {
        "a": 1,
        "b", 1
    }
];

Add Rows (addrow)

To add a new row to a given dataset, the addrow function can be used.

Parameters:

ParameterDescription

newData

The new row to add.

data

The existing data to use.

Example:

//! Add row
addrow {"a":2, "b":2}, {$data};
varchange data = @@;

Result:

//! Data variable tranformed
[
    {
        "a": 1,
        "b": 1
    },
    {
        "a": 2,
        "b": 2
    }
]

Transform (transformed)

To transform a dataset with a given operation description, the transformed function can be used.

Parameters:

ParameterDescription

formula

The tranform operation.

data

The existing data to use.

Example:

//! Transform the data with the operation described
transformed {"c": (d) => d.value * 2, "d": (d) => c.value * 2}, {$data};
varchange data = @@;

Result:

//! Data variable tranformed
[
    {
        "c": 2,
        "d": 4
    }
]

Total Of (total)

To take the sum of values of a key from a given dataset, the total function can be used.

Parameters:

ParameterDescription

formula

The description of operation used.

data

The existing data to use.

Example:

variable data = ;
[
    {
        "a": "row A",
        "b": 1
    },
    {
        "a": "row B",
        "b": 9
    }
];

//! Calculate sum of all row of a specific key
total {"value": sum('b'), "a": 'total'}, {$data};
varchange data = @@;

Result:

//! Data variable tranformed
[
    {
        "a": "row A",
        "b": 1
    },
    {
        "a": "row B",
        "b": 9
    },
    {
        "a": "total",
        "b": 10
    }
]

Take Highest (takeHighest)

To take a number of items with the highest values from a given dataset, the takeHighest function can be used.

Parameters:

ParameterDescription

items

The amount of items to take.

key

The key to which values are sorted by.

data

The existing data to use.

Example:

variable data = ;
[
    { value: 1 },
    { value: 2 },
    { value: 3 },
    { value: 4 },
    { value: 5 },
];

//! Take 2 of the rows with highest values
takeHighest 2, value, {$data};
varchange data = @@;

Result:

//! Data variable tranformed
[
    {
        "value": 5,
    },
    {
        "value": 4,
    }
]

Take Lowest (takeLowest)

To take a number of items with the lowest values from given dataset, the takeLowest function can be used.

Parameters:

ParameterDescription

items

The amount of items to take.

key

The key to which values are sorted by.

data

The existing data to use.

Example:

variable data = ;
[
    { value: 1 },
    { value: 2 },
    { value: 3 },
    { value: 4 },
    { value: 5 },
];

//! Take 2 of the rows with lowset values
takeHighest 2, value, {$data};
varchange data = @@;

Result:

//! Data variable tranformed
[
    {
        "value": 1,
    },
    {
        "value": 2,
    }
]

Take Last (slicedEnd)

To take the last items from a given dataset, the slicedEnd function can be used.

Parameters:

ParameterDescription

items

The amount of items to take.

data

The existing data to use.

Example:

const data = [
  { value: 1 },
  { value: 2 },
  { value: 3 },
  { value: 4 },
  { value: 5 },
];

//! Take the last 2 items in the array
slicedEnd 2, {$data};
varchange data = @@;

Result:

//! Data variable tranformed
[
    {
        "value": 5,
    },
    {
        "value": 4,
    }
]

Take First (slicedStart)

To take the first items from a given dataset, the slicedStart function can be used.

Parameters:

ParameterDescription

items

The amount of items to take.

data

The existing data to use.

Example:

const data = [
  { value: 1 },
  { value: 2 },
  { value: 3 },
  { value: 4 },
  { value: 5 },
];

//! Take the first 2 items in the array
slicedEnd 2, {$data};
varchange data = @@;

Result:

//! Data variable tranformed
[
    {
        "value": 1,
    },
    {
        "value": 2,
    }
]

Take (sliced)

To take items from a given dataset, the sliced function can be used.

Parameters:

ParameterDescription

start

The starting position in the array to take data from.

end

The ending position in the array in which to take data from.

data

The existing data to use.

Example:

const data = [
  { value: 1 },
  { value: 2 },
  { value: 3 },
  { value: 4 },
  { value: 5 },
];

//! Take the items between the starting and ending positions in the array
slicedEnd 1, 3, {$data};
varchange data = @@;

Result:

//! Data variable tranformed
[
    {
        "value": 2,
    },
    {
        "value": 3,
    }
]

Rename (renamed)

To rename a key from a given dataset, the renamed function can be used.

Parameters:

ParameterDescription

formula

The list of keys to rename.

data

The existing data to use.

Example:

//! Rename keys
renamed {"a":"newA", "b":"newB"}, {$data};
varchange data = @@;

Result:

//! Data variable tranformed
[
    {
        "newA": 1,
        "newB": 1
    }
]

Mutate (mutated)

To mutate a given dataset, the mutated function can be used.

Parameters:

ParameterDescription

operation

The mutate operation description.

data

The existing data to use.

Example:

//! Mutate data
variable data = ;
[
  { str: 'foo', value: 3 },
  { str: 'bar', value: 1 },
  { str: 'bar', value: 7 },
];

variable mutation = ;
{
  x2: (d) => d.value * 2,
  x4: (d) => d.x2 * 2,
  constant: 99  
};

mutated {"a":2, "b":2}, {$data};
varchange data = @@;

Result:

//! Data variable tranformed
[
  { str: 'foo', value: 3, x2:  6, x4: 12, constant: 99 },
  { str: 'bar', value: 1, x2:  2, x4:  4, constant: 99 },
  { str: 'bar', value: 7, x2: 14, x4: 28, constant: 99 },
] 

Left Join (leftjoined)

To add a new row to a given dataset, the leftjoined function can be used.

Parameters:

ParameterDescription

joinData

The data to join with existing data.

joinKeys

The keys to join the data by.

data

The existing data to use.

Example:

variable data = ;
[
  { a: 1, J: 'j', b: 10, c: 100 },
  { a: 1, J: 'k', b: 60, c: 600 },
  { a: 1, J: 'J', b: 30, c: 300 },
  { a: 2, J: 'j', b: 20, c: 200 },
  { a: 3, J: 'x', b: 50, c: 500 },
];

variable joinData = ;
[
  { a: 1, J: 'j', altJ: 'j', x: 'x1', y: 'y1' },
  { a: 1, J: 'J', altJ: 'J', x: 'x9', y: 'y9' },
  { a: 2, J: 'j', altJ: 'j', x: 'x2', y: 'y2' },
];

variable joinKeys = { by: ['a', 'J'] };

//! Left join data
leftjoined {$joinData}, {$joinKeys}, {$data};
varchange data = @@;

Result:

//! Data variable tranformed
[
  { a: 1, J: 'j', altJ: 'j', b: 10, c: 100, x: 'x1', y: 'y1' },
  { a: 1, J: 'k', b: 60, c: 600 },
  { a: 1, J: 'J', altJ: 'J', b: 30, c: 300, x: 'x9', y: 'y9' },
  { a: 2, J: 'j', altJ: 'j', b: 20, c: 200, x: 'x2', y: 'y2' },
  { a: 3, J: 'x', b: 50, c: 500 },
] 

Inner Join (innerjoined)

To perform innerjoin on a collection of item to a given dataset, the innerjoined function can be used.

Parameters:

ParameterDescription

joinData

The collection of items to join.

joinKeys

The keys to join the items by.

data

The existing data to use.

Example:

variable data = ;
[
  { a: 1, J: 'j', b: 10, c: 100 },
  { a: 1, J: 'k', b: 60, c: 600 },
  { a: 1, J: 'J', b: 30, c: 300 },
  { a: 2, J: 'j', b: 20, c: 200 },
  { a: 3, J: 'x', b: 50, c: 500 },
];

variable joinData = ;
[
  { a: 1, J: 'j', altJ: 'j', x: 'x1', y: 'y1' },
  { a: 1, J: 'J', altJ: 'J', x: 'x9', y: 'y9' },
  { a: 2, J: 'j', altJ: 'j', x: 'x2', y: 'y2' },
];

variable joinKeys = { by: ['a', 'J'] };

//! Inner join
innerjoined {$joinData}, {$joinKeys}, {$data};
varchange data = @@;

Result:

//! Data variable tranformed
[
  { a: 1, J: 'j', altJ: 'j', b: 10, c: 100, x: 'x1', y: 'y1' },
  { a: 1, J: 'J', altJ: 'J', b: 30, c: 300, x: 'x9', y: 'y9' },
  { a: 2, J: 'j', altJ: 'j', b: 20, c: 200, x: 'x2', y: 'y2' },
]

Full Join (fulljoined)

To perform a fulljoin of an item collection with a given dataset, the fulljoined function can be used.

Parameters:

ParameterDescription

joinData

The collection of items to join.

joinKeys

The keys to join the items by.

data

The existing data to use.

Example:

variable data = ;
[
  { a: 1, J: 'j', b: 10, c: 100 },
  { a: 1, J: 'k', b: 60, c: 600 },
  { a: 1, J: 'J', b: 30, c: 300 },
  { a: 2, J: 'j', b: 20, c: 200 },
  { a: 3, J: 'x', b: 50, c: 500 },
];

variable joinData = ;
[
  { a: 1, J: 'j', altJ: 'j', x: 'x1', y: 'y1' },
  { a: 1, J: 'J', altJ: 'J', x: 'x9', y: 'y9' },
  { a: 2, J: 'j', altJ: 'j', x: 'x2', y: 'y2' },
  { a: 2, J: 'X', altJ: 'x', x: 'x5', y: 'y5' },
];

variable joinKeys = { by: ['a', 'J'] };

//! Full join
addrow {$joinData}, {$joinKeys}, {$data};
varchange data = @@;

Result:

//! Data variable tranformed
[
  { a: 1, J: 'j', altJ: 'j', b: 10, c: 100, x: 'x1', y: 'y1' },
  { a: 1, J: 'k', b: 60, c: 600 },
  { a: 1, J: 'J', altJ: 'J', b: 30, c: 300, x: 'x9', y: 'y9' },
  { a: 2, J: 'j', altJ: 'j', b: 20, c: 200, x: 'x2', y: 'y2' },
  { a: 3, J: 'x', b: 50, c: 500 },
  { a: 2, J: 'X', altJ: 'x', x: 'x5', y: 'y5' },
] 

Group By (grouped)

To restructure a given dataset by a given key, the grouped function can be used.

Parameters:

ParameterDescription

operation

The grouping operation description.

data

The existing data to use.

Example:

variable data = ;
[
  { str: 'a', ing: 'x', foo: 'G', value: 1 },
  { str: 'b', ing: 'x', foo: 'H', value: 100 },
  { str: 'b', ing: 'x', foo: 'K', value: 200 },
  { str: 'a', ing: 'y', foo: 'G', value: 2 },
  { str: 'a', ing: 'y', foo: 'H', value: 3 },
  { str: 'a', ing: 'y', foo: 'K', value: 4 },
  { str: 'b', ing: 'y', foo: 'G', value: 300 },
  { str: 'b', ing: 'z', foo: 'H', value: 400 },
  { str: 'a', ing: 'z', foo: 'K', value: 5 },
  { str: 'a', ing: 'z', foo: 'G', value: 6 },
];

variable operation = ;
['str', 'ing'], [
  summarize({ total: sum('value') })
];

//! Group by
grouped operation, {$data};
varchange data = @@;

Result:

//! Data variable tranformed
[
  { str: 'a', ing: 'x', total: 1 },
  { str: 'a', ing: 'y', total: 9 },
  { str: 'a', ing: 'z', total: 11 },
  { str: 'b', ing: 'x', total: 300 },
  { str: 'b', ing: 'y', total: 300 },
  { str: 'b', ing: 'z', total: 400 },
]

Filter (filtered)

To add a new row to a given dataset, the filtered function can be used.

Parameters:

ParameterDescription

formula

The filter formula to execute.

data

The existing data to use.

Example:

variable data = [{ value: 1 }, { value: 2 }, { value: 3 }];
variable filter = (d) => d.value % 2 === 1);

//! Filter data
filtered {$filter}, {$data};
varchange data = @@;

Result:

//! Data variable tranformed
[{ value: 1 }, { value: 3 }]

Fill (filled)

To fill values for the specified keys to match the last seen value in a given dataset, the filled function can be used.

Parameters:

ParameterDescription

keys

The list of keys to perform a fill.

data

The existing data to use.

Example:

variable data = ;
[
  { a: 1, b: null, c: undefined, d: 1 },
  { a: null, b: 2, c: undefined },
  { a: null, c: 3, d: 3 },
  { a: 4, b: 4, c: 4, d: 4 },
  {},
  { c: 6 },
  { c: 7, d: 7 },
];

variable fill = ['a', 'b', 'c', 'd'];

//! Fill
addrow {$fill}, {$data};
varchange data = @@;

Result:

//! Data variable tranformed
[
  { a: 1, b: null, c: undefined, d: 1 },
  { a: 1, b: 2, c: undefined, d: 1 },
  { a: 1, b: 2, c: 3, d: 3 },
  { a: 4, b: 4, c: 4, d: 4 },
  { a: 4, b: 4, c: 4, d: 4 },
  { a: 4, b: 4, c: 6, d: 4 },
  { a: 4, b: 4, c: 7, d: 7 }
] 

Take Distinct (distinct)

To add a new row to a given dataset, the distinct function can be used.

Parameters:

ParameterDescription

keys

The keys to find distinct values.

data

The existing data to use.

Example:

variable data = ;
[
  { str: 'foo', value: 1 },
  { str: 'foo', value: 3 },
  { str: 'far', value: 3 },
  { str: 'bar', value: 1 },
  { str: 'foo', value: 3 },
];

variable distinguish = ['str', 'value'];

//! Find distinct
distinc {$distinguish}, {$data};
varchange data = @@;

Result:

//! Data variable tranformed
[
  { str: 'foo', value: 1 },
  { str: 'foo', value: 3 },
  { str: 'far', value: 3 },
  { str: 'bar', value: 1 },
]

Complete (completed)

To complete a given dataset where a row of with a given key does not have a pair, the completed function can be used.

Parameters:

ParameterDescription

keys

The keys to find null values.

value

The value to fill where null values are found.

data

The existing data to use.

Example:

variable data = ;
[
  { a: 1, b: 'b1', c: 100 },
  { a: 2, b: 'b1', c: 200 },
  { a: 3, b: 'b1', c: 300 },
  { a: 1, b: 'b2', c: 101 },
  { a: 2, b: 'b2', c: 201 },
];

variable keys = ['a', 'b'];
variable completeWith = { c: 0 };

//! Complete
completed {$keys}, {$completeWith}, {$data};
varchange data = @@;

Result:

//! Data variable tranformed
[
  { a: 1, b: 'b1', c: 100 },
  { a: 1, b: 'b2', c: 101 },
  { a: 2, b: 'b1', c: 200 },
  { a: 2, b: 'b2', c: 201 },
  { a: 3, b: 'b1', c: 300 },
  { a: 3, b: 'b2', c: 0 },
]

Arrange (arranged)

To sort a given dataset with a given key, the arranged function can be used.

Parameters:

ParameterDescription

keyAndComparator

The sorting description.

data

The existing data to use.

Example:

variable data = ;
[
  { str: 'foo', value: 3 },
  { str: 'foo', value: 4 },
  { str: 'bar', value: 2 },
  { str: 'bar', value: 1 },
  { str: 'bar', value: 5 },
];

variable sortBy = ['str', desc('value')];

//! Sort
addrow {"a":2, "b":2}, {$data};
varchange data = @@;

Result:

//! Data variable tranformed
[
  { str: 'bar', value: 5 },
  { str: 'bar', value: 2 },
  { str: 'bar', value: 1 },
  { str: 'foo', value: 4 },
  { str: 'foo', value: 3 },
] 

Tidy.JS

Our thanks goes to the maintainers of the Tidy.JS library! To learn more about the data transformation methods, refer to the Tidy.JS documentations here: https://pbeshai.github.io/tidy/docs/api/tidy

Questions?

Let us know on [email protected]

Last updated