Integration API: charts with live data

charte.ca integration API comes handy when you want your published chart to display up-to-date information and you do not have the luxury of logging in to the editor, tweaking the data and re-publishing the chart manually every time the data changes. Consider the following P/E ratio vs. Price to book bubble chart that we want to be updated every hour:

charte.ca number formatting

Users want to have the ability to tweak number formatting. No, users need this feature. Just imagine having labels like "1382323332" and "1340152688" on a diagram comparing Chinese and Indian population. Or imagine a US GDP chart with a "$20000000000000" mark on the vertical axis. Or imagine that your audience wants to see negative amounts of some specific currency: "-£349.3300".

charte.ca supports number formatting for axis marks, data labels and callouts:

Since the rules of number formatting can be complicated, we decided not to invent our own mechanism for it. We just use the standard instead: ECMAScript Internationalization API Specification. It's a long specification, but the part that we are interested in boils down to this Mozilla document. This document defines how Javascript developers can specify the locale and formatting options when converting numbers to strings. For example, an object that enables Japanese yen formatting can be instantiated as follows:

new Intl.NumberFormat('ja-JP', { style: 'currency', currency: 'JPY' });

and it can produce strings like "￥123,457". charte.ca does not provide any programming platform for chart creators, but it allows to pass the locale 'ja-JP' and the option object { style: 'currency', currency: 'JPY' } as additional parameters to the {value} keyword used in axis marks, data labels and callouts. The convention is to extend the "value" keyword with a semicolon followed by the number format specification:

NumberFormat:{locale:'<desired locale>', options:<desired options>}

For example:

{value;NumberFormat:{locale:'en', options:{useGrouping: true }}} will produce numbers with comma as a thousands separator

{value;NumberFormat:{locale:'en-US',options: style:'currency',currency:'GBP',minimumFractionDigits:4}}} will produce British pound amounts with 4 digits after decimal point.

In the example below, the callout format

{series}: {value;NumberFormat:{locale:'en', options:{useGrouping: true }}}

is specified, which makes the callout for the "0-4" age group with value 1453708 to be formatted as "0-4: 1,453,708":

Comparative histogram charts: population by age group

Great news: charte.ca now supports comparative histograms. This kind of charts comes  handy when you want to compare two or more groups of series side-by-side. In most cases, this type of charts is used to draw a population pyramid, but it has other uses as well.

The following chart shows country population by age group, for multiple years, comparing male and female population.

This post discusses comparative histogram creation process step-by-step.

US and Canada demographics: immigration, temporary workers, refugees in 1935-2014

I was curious about the differences between American and Canadian immigration systems. After 2016 US elections, we hear a lot about those differences, but I could not find a good source with actual numbers compared. Let's do it here. As usual, here is the Google Docs spreadsheet with all relevant data. As usual, all charts below are interactive, just move your mouse over the line or the legend item.

Let's start with the simplest part - total population numbers provided by Wikipedia:

No surprises here - these countries are economically and culturally close, so population growth patterns are similar.

Let's have a look at natural population growth numbers pulled from UNDESA (UN Department of Economic and Social Affairs) and The World Bank:

Scatter/Bubble charts and regression analysis: median income and obesity levels

Is there a correlation between median income and obesity? I have put some numbers from a few data sources (see full list in the end of the post) in a single GoogleDocs spreadsheet. I have created a new grouped bubble chart and imported the data using CSV/TSV wizard, using Region/Haplogroup column for grouping:

Importing CSV/TSV data: SUM and AVG aggregate functions

These are accountant's favourite aggregate functions. Consider a sample data spreadsheet from Tableau slightly massaged to expose order year, quarter and weekday explicitly - here is the Google spreadsheet we will be working on. About 8000 order lines spanning a few years of an online superstore sales activity.

Quarterly sales by province

Let's create a Line chart with totals for each quarter by province (it happened to be a Canadian superstore).

1. Login to charte.ca and create a new Line chart.

2. In the Data panel, click "Import" (you can find it right under the data grid).

3. On wizard screen #1, paste data from the spreadsheet (we will re-use this data in all examples):

4. On Wizard screen #2, select series (Province), category(Year-Quarter) and value (Sales) columns:

5. On Wizard screen #3 (filtering), leave all fields blank - we do not need any filtering.

6. On wizard screen #4, select SUM aggregate function:

7. On Wizard screen #5 (sorting), leave default values in all fields and click "Done".

8. As a result, you will get this data grid and chart:

Importing CSV/TSV data: COUNT aggregate function

Refresh your data journalism skills. Let's look at San Francisco police dept incident database for the years 2003-2013 available here: data.sfgov.org. For convenience, let's use a snapshot of it stored as Google spreadsheet. It contains about 15000 records, one record per incident.

Totals
Let's create a Pie chart with totals for each crime category.

1. Login to charte.ca and create a new Pie chart.

2. In the Data panel, click "Import" (you can find it right under the data grid).

3. On wizard screen #1, paste data from the spreadsheet (we will re-use this data in all examples):

4. On Wizard screen #2, select series (crime category), category(NONE) and value (NONE) columns:

5. On Wizard screen #3 (filtering), leave all fields blank - we do not need any filtering.

6. On wizard screen #4, keep aggregate function selection at COUNT, so data import routine simply counts correspondent records.

7. On Wizard screen #5 (sorting), leave default values in all fields and click "Done"

8. As a result, you will get this data grid and chart:

Totals by year
Naturally, now we are curious how crime picture progressed through the years. Initial steps are the same, but the chart type is Line now.