Manipulate Charts and Data

Available Charts

Bar Chart

barChart({
  horizontal: false,
  series: [
    {name: 'PRODUCT A', data: [14, 25, 21, 17, 12, 13, 11, 19]},
    {name: 'PRODUCT B', data: [13, 23, 20, 8, 13, 27, 33, 12]},
    {name: 'PRODUCT C', data: [11, 17, 15, 15, 21, 14, 15, 13]}],
  categories:['2011 Q1', '2011 Q2', '2011 Q3', '2011 Q4', '2012 Q1', '2012 Q2', '2012 Q3', '2012 Q4']
});

Line Chart

lineChart({
  series: [
    {name: 'Music', data: [1, 15, 26, 20, 33, 27]},
    {name: 'Photos', data: [3, 33, 21, 42, 19, 32]},
    {name: 'Files', data: [0, 39, 52, 11, 29, 43]}],
  labels: ['01/15/2002', '01/16/2002', '01/17/2002', '01/18/2002', '01/19/2002', '01/20/2002']
});

Area Chart

areaChart({
  series: [
    {name: 'Music', data: [11, 15, 26, 20, 33, 27]},
    {name: 'Photos', data: [32, 33, 21, 42, 19, 32]},
    {name: 'Files', data: [20, 39, 52, 11, 29, 43]}],
  categories:['2011 Q1', '2011 Q2', '2011 Q3', '2011 Q4', '2012 Q1', '2012 Q2']
});

Bubble Chart

bubbleChart({
  series: [
  {'name': 'Serie 1', 'data': [['2024-02-20',1,1], ['2024-02-24',1,1], ['2024-02-26',1,2]]},
  {'name': 'Serie 2', 'data': [['2024-02-21',2,1], ['2024-02-22',2,2], ['2024-02-23',2,1]]},
  {'name': 'Serie 3', 'data': [['2024-02-21',3,1], ['2024-02-22',3,1]]}
]});

Radial Chart

radialChart({series: [71, 63, 77], labels:['June', 'May', 'April']});

Data manipulation

From SQL

Example of data from SQL database

SELECT 
  channel,
  DATE_FORMAT(sale_date, '%Y-%m') AS month,
  COUNT(*) AS total_sales
FROM 
  Sales
GROUP BY 
  channel, month
ORDER BY month ASC;

//Check this post to learn how to configure an SQL client: 👉 https://blog.znote.io/2021/sql-functions/
//const sql = loadBlock("orders-sql");
//const json = await localSQL(sql);
// The example above gives a JSON formatted like this
const json = [
  {'channel': 'Facebook', 'month': '2023-01', 'total_sales': 150},
  {'channel': 'Facebook', 'month': '2023-02', 'total_sales': 113},
  {'channel': 'Facebook', 'month': '2023-03', 'total_sales': 83},
  {'channel': 'Facebook', 'month': '2023-04', 'total_sales': 193},
  {'channel': 'Google', 'month': '2023-01', 'total_sales': 235},
  {'channel': 'Google', 'month': '2023-02', 'total_sales': 195},
  {'channel': 'Google', 'month': '2023-03', 'total_sales': 125},
  {'channel': 'Google', 'month': '2023-04', 'total_sales': 155}
];

// Labels on X axis (Months deduped)
const categories = Array.from(new Set(json.map(e=>e.month)));

// Convert data to a format comprehensible for the Chart library. You can omit the category parameter if it is not necessary (optional).
const series = toSeries({data:json, x:"month", y:"total_sales", category:"channel"})
//printJSON(series);
//[
//  {"name": "Facebook", "data": [150,113,83,193]},
//  {"name": "Google", "data": [235,195,125,155]}
//]

barChart({
  horizontal:true,
  series: series,
  categories: categories
});

Format axis labels

const formatterOptions = {
  yaxis: {
    labels: {
      formatter: function (value) {
         const date = new Date();
        date.setMonth(value - 1);
        return date.toLocaleString('en-US', { month: 'short' });
      }
    },
  },
  xaxis: {
    labels: {
      formatter: function (value) {
        return value.toLocaleString('en-US', { style: 'currency', currency: 'USD' });
        return value;
      }
    },
  }
}

const json = [
  {'channel': 'Facebook', 'month': '2023-01', 'total_sales_amount': 15000.00},
  {'channel': 'Facebook', 'month': '2023-02', 'total_sales_amount': 11300.00},
  {'channel': 'Facebook', 'month': '2023-03', 'total_sales_amount': 8300.00},
  {'channel': 'Facebook', 'month': '2023-04', 'total_sales_amount': 19300.00},
  {'channel': 'Google', 'month': '2023-01', 'total_sales_amount': 23500.00},
  {'channel': 'Google', 'month': '2023-02', 'total_sales_amount': 19500.00},
  {'channel': 'Google', 'month': '2023-03', 'total_sales_amount': 12500.00},
  {'channel': 'Google', 'month': '2023-04', 'total_sales_amount': 15500.00}
];

barChart({
  options: formatterOptions,
  horizontal: true,
  series: toSeries({
    data:json, 
    x:"month", 
    y:"total_sales_amount", 
    category:"channel"
    })
});

For Bubble with Formatter

let json = [
  {'date':'2024-02-20','mood':1},
  {'date':'2024-02-21','mood':2},
  {'date':'2024-02-21','mood':3},
  {'date':'2024-02-22','mood':2},
  {'date':'2024-02-22','mood':3},
  {'date':'2024-02-22','mood':2},
  {'date':'2024-02-23','mood':2},
  {'date':'2024-02-24','mood':1},
  {'date':'2024-02-26','mood':1},
  {'date':'2024-02-26','mood':1}
];


const xFormatter = (value) => {
  if (value === 1) return "☀️";
  if (value === 2) return "⛅️";
  if (value === 3) return "☁️";
  if (value === 4) return "🌧️";
  if (value === 5) return "⛈️";
}
const serieFormatter = (value) => {
  if (value === 1) return "Good";
  if (value === 2) return "Almost Good";
  if (value === 3) return "It's ok";
  if (value === 4) return "I saw better";
  if (value === 5) return "Should be better";
}

bubbleChart({
    options: {
    yaxis: {
      labels: {
        formatter: xFormatter
      },
    },
  },

  series: toBubbleSeries({data:json, x:'date', y:'mood', serieFormatter:serieFormatter})
});

For Timeseries

const json = [
  {"sensor": 74, "date": "2022-09-29 10:41:54.000000 +02:00", "battery": 90.92},
  {"sensor": 74, "date": "2023-06-21 10:14:59.000000 +02:00",  "battery": 88.76},
  {"sensor": 74, "date": "2023-08-24 20:34:20.000000 +02:00", "battery": 88.08},
  {"sensor": 60, "date": "2023-04-22 20:34:20.000000 +02:00", "battery": 78.02},
  {"sensor": 60, "date": "2023-07-24 20:34:20.000000 +02:00", "battery": 75.02}
]

lineChart({
  options: {
    xaxis: {
      type: "datetime",
    },
  },
  series: toTimeSeries({data:json, x:"date", y:"battery", category:"sensor"})
});

Data from JSON

Znote includes some useful functions to help you manipulate JSON.

Filter/Exclude keys

// Giving this example
const json = [
  {'id': 1, 'channel': 'Facebook', 'month': '2023-01', 'total_sales': 150},
  {'id': 2, 'channel': 'Referral', 'month': '2023-01', 'total_sales': 193},
  {'id': 3, 'channel': 'Google', 'month': '2023-01', 'total_sales': 155}
];

// Remove cols: 'id' and 'month' from JSON
printJSON(exclude(json, ['id', 'month']));

// Keep only cols: 'channel', 'month'
printJSON(filter(json, ["channel", "month"]));

CSV2JSON

Convert a CSV to JSON

const csv = 
"latitude,longitude,brightness,scan,track,acq_date,acq_time,satellite,confidence,version,bright_t31,frp,daynight\n"+
"-3.22141,-39.51899,323,2.83,1.61,2024-01-16,0005,T,100,6.1NRT,292.68,87.72,N\n"+
"-3.21447,-39.49986,306.1,2.82,1.6,2024-01-16,0005,T,41,6.1NRT,292.62,23.36,N\n"+
"-3.21796,-39.52462,317.82,2.83,1.61,2024-01-16,0005,T,94,6.1NRT,292.62,64.55,N\n"
const json = csvJSON(csv, ',');
printJSON(json);

Flatten JSON

This operation could be helpful to load a dataset into the datagrid

const json = [
  {
    "party": "Democrat",
    "person": {
      "bioguideid": "C000127",
      "birthday": "1958-10-13",
      "firstname": "Maria",
      "gender": "female",
      "gender_label": "Female",
      "lastname": "Cantwell",
      "youtubeid": "SenatorCantwell"
    },
    "phone": "202-224-3441",
    "role_type": "senator",
  },
  {
    "party": "Republican",
      "person": {
        "bioguideid": "B001261",
        "birthday": "1952-07-21",
        "firstname": "John",
        "gender": "male",
        "gender_label": "Male",
        "lastname": "Barrasso",
        "youtubeid": "barrassowyo"
      },
      "phone": "202-224-6441",
      "role_type": "senator",
  }
]
const flattenJSON = json.map(e=>flatten(e));
printJSON(flattenJSON);

Datagrid

const r = await fetch('https://www.govtrack.us/api/v2/role?current=true&role_type=senator')
const json = await r.json();
const senators = json.objects.map(s=>flatten(s));
showDatagrid({data: filter(senators,['party', 'person.firstname', 'person.lastname']), pagination: true});