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.

Importing CSV/TSV data: Tableau example

From now on, charte.ca allows you to import flat comma- or tab-separated data. Usually, CSV/TSV data is just a set of samples that are nor suitable for displaying in a Pie, Bar or Line charts. To overcome this limitation, powerful software packages offer additional functionality like Excel pivot tables. charte.ca allows you to build a pivot table in a few easy steps.

This example shows how you can import data from a Public Tableau visualization. Fortunately, Tableau has a CSV export feature and we will take advantage of it.

1. Login to charte.ca and create a new Regular 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 

http://public.tableau.com/views/WorldIndicators_0/GDPpercapitabyregion.csv

Please note the data we are importing is flat indeed - series, categories and values are just raw data columns. On the next step, we will define the pivot table structure.

4. On Wizard screen #2, select series, category and value columns:

Please keep in mind that in charte.ca editor, series become grid columns, and categories become grid rows. 

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

6. Nothing to select on Wizard screen #4 (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 line chart:

Please note that pivot tables make sense only for Pie, Bar, or Line charts. Scatter or Bubble charts are based on flat data, so there is no need to perform this pivot table magic.

You have probably noticed that this example does not involve any aggregation: every series/category pair in the flat data points to only one value. If this is not the case, and our flat data samples have to be grouped, aggregate function should be used. But this is a good subject for a separate discussion.