Working with CSV files
How to read data from external files in CSV format. By Benedikt Groß
CSV - Data to a circles
In this short tutorial we are going to use a dataset of the swimming pool count of all neighborhoods in Los Angeles to create a diagram of circles. The pool count of the individual LA neighborhood will be encode to the area of the circles. Data courtesy The Big Atlas of LA Pools.
But first of all, what is a CSV file?
A CSV file is a text file with comma-separated values, think of a spreadsheet or a Microsoft Excel file. The first row in the file specifies the names of the columns, all other rows hold the data. If you open a CSV file in an text editor your should see something like this:
hoodname,poolcount Long Beach,2859 Rancho Palos Verdes,2592 Beverly Hills,2481 ...
Preparations
- Create somewhere a folder for the tutorial and save a blank indesign file into that folder e.g. LA_Pools/circles.indd
- Create a data folder within the project folder to hold the data: LA_Pools/data/
- Get the dataset and copy it to the data folder: LA_Pools/data/LA_Pools-Pools_per_Hood.csv
Load the data
Loading the CSV data is basically just one line of code:
b.CSV.decode( b.loadString("data.csv") ).
Reading it from inside to outside reveals the following, loadString() just loads the entire content of the file into basil. b.CSV.decode() decodes then this content to columns and rows. The schema to get the data is:
// data[ row_number ].column_name // get the 2nd row b.println( data[2].hoodname ); // -> Beverly Hills b.println( data[2].poolcount ); // –> 2481
As the CSV doesn't have types, it is not clear whether the data is a numbers or a string. Hence we have to take care that all columns of numbers are converted to the correct type. You can do that for instance with the functions parseInt() or parseFloat().
#includepath "~/Documents/;%USERPROFILE%Documents"; #include "basiljs/bundle/basil.js"; function draw() { //load data var data = b.CSV.decode( b.loadString("LA_Pools-Pools_per_Hood.csv") ); b.println("loaded "+data.length+" neighborhoods"); // parse to numbers for (var i = 0; i < data.length; i++) { data[i].poolcount = parseInt(data[i].poolcount); b.println(i+" "+data[i].hoodname+" "+data[i].poolcount); }; } b.go();
Draw the circles
After loading the data it is now time to draw the data. The poolcount value is mapped to the area of the circles with the function calcRadiusByArea(). The gird layout is created with the help of the two for loops. It takes a bit of fiddling around to come up with the right dimensions of 10 x 14 units to fit the layout onto the page, just play around with the for loops (e.g. change the values of the 10 and 14) ... And last, labeling the circles was done with a combination of the hoodname and poolcount value.
#includepath "~/Documents/;%USERPROFILE%Documents"; #include "basiljs/bundle/basil.js"; function draw() { // load & convert var data = b.CSV.decode( b.loadString("LA_Pools-Pools_per_Hood.csv") ); for (var i = 0; i < data.length; i++) { data[i].poolcount = parseInt(data[i].poolcount); }; // circles b.textSize(6); b.textAlign(Justification.CENTER_ALIGN); b.units(b.MM); var i = 0; var gridUnitSize = 20; var paddingBottom = 3 for (var y = 0; y < 14; y++) { for (var x = 0; x < 10; x++) { var posX = x*gridUnitSize; var posY = y*(gridUnitSize+paddingBottom); var poolcount = data[i].poolcount; var radius = calcRadiusByArea(poolcount) * 0.5; // scale down var diameter = radius * 2; b.ellipse(posX,posY,diameter,diameter); var hoodname = data[i].hoodname; b.text(hoodname+"n"+poolcount, posX-gridUnitSize/2, posY+radius+2, 20,10); // stop drawing if no more rows are available if (i > data.length) break; i++; }; }; } function calcRadiusByArea(A){ // A = Pi * r * r return Math.sqrt(A/Math.PI); } b.go();
Your results should look similar to this one :)
The circle gird
Cirlce layout detail