AWS S3 csv file as D3 report data source

This is an example of how to read a csv file retrieved from an AWS S3 bucket as a data source for a D3 javascript visualization.

The D3 visualization would be an HTML document hosted on a web server. 

You will use the AWS SDK to get the csv file from the S3 bucket and so you need to have an AWS S3 bucket key and secret but I won’t cover that in this post.

The key point of this post is to highlight that the bucket.getObject function data is read into D3 using  d3.csv.parse(data.Body.toString());  

Another note is that d3.csv.parse is for D3 version 3. Older versions use d3.csvParse. 

Once implemented, whenever the webpage is refreshed it retrieves latest csv file from the S3 bucket and the D3 visualization is updated.

<script src="https://sdk.amazonaws.com/js/aws-sdk-2.6.3.min.js"></script>

<script type="text/javascript">

// aws key and secret (note these should be retrieved from server not put as plain text into html code)
AWS.config.accessKeyId = 'xxxxxxxxxxxxxxxxxxxxxxx';
AWS.config.secretAccessKey = 'xxxxxxxxxxxxxxxxxxxxxxx';
AWS.config.region = 'us-east-1';

// create the AWS.Request object
var bucket = new AWS.S3();

// use AWS SDK getobject to retrieve csv file
bucket.getObject({
    Bucket: 'my-S3-bucket', 
    Key: 'myfile.csv'
}, 

// function to use the data retrieve 
function awsDataFile(error, data) {
    if (error) {
        return console.log(error);
    }

        // this where magic happens using d3.csv.parse to read myCSVdata.Body.toString()
    myCSVdata = d3.csv.parse(data.Body.toString()); 

        // now loop through data and get fields desired for visualization 
    var counter = 0;
    myCSVdata.forEach(function(d) {
            d.field1= +d.field1;
            d.field2= +d.field2;
            countLoop = counter++;
    });

        // now you can create rest of D3 vizualization here 
        // for example like this example https://gist.github.com/d3noob/4414436

        my D3 vizualization code here

// this closes bucket.getObject 
});

</script>

 

 

Leaflet.js choropleth map color by count using geoJSON datasource

I have a Django web application that needed an interactive map with shapes corresponding to Canadian postal code FSA areas that were different colors based on how many properties were in each FSA. It ended up looking something like the screenshot below.

map1

This exercise turned out to be relatively easy using the awesome open-source Javascript map library Leaflet.js.

I used this Leaflet.js tutorial as the foundation for my map.

One of the biggest challenges was finding a suitable data source for the FSAs. Chad Skelton (now former) data journalist at the Vancouver Sun wrote a helpful blog post about his experience getting a suitable FSA data source. I ended up using his BC FSA data source for my map.

Statistics Canada hosts a Canada Post FSA boundary files for all of Canada. As Chad Skelton notes these have boundaries that extend out into the ocean among other challenges.

Here is a summary of the steps that I followed to get my choropleth map:

1. Find and download FSA boundary file. See above.

2. Convert FSA boundary file to geoJSON from SHP file using qGIS.

3. Create Django queryset to create data source for counts of properties by FSA to be added to the Leaflet map layer.

4. Create Leaflet.js map in HTML page basically the HTML DIV that holds the map and separate Javascript script that loads Leaflet.js, the FSA geoJSON boundary data and processes it to create the desired map.

Find and download FSA boundary file.

See above.

Convert FSA boundary file to geoJSON from SHP file using qGIS.

Go to http://www.qgis.org/en/site/ and download qGIS. Its free and open source.

Use qGIS to convert the data file from Canada Post or other source to geoJSON format. Lots of blog posts and documentation about how to use qGIS for this just a Google search away.

My geoJSON data source looked like this:

var bcData = {
    "type": "FeatureCollection",
    "crs": { "type": "name", "properties": { "name": "urn:ogc:def:crs:EPSG::4269" } },
    "features": [
    { "type": "Feature", "properties": { "CFSAUID": "V0A", "PRUID": "59", "PRNAME": "British Columbia \/ Colombie-Britannique" }, "geometry": { "type": "MultiPolygon", "coordinates": [ [ [ [ -115.49499542, 50.780018587000029 ], [ -115.50032807, 50.77718343600003 ], [ -115.49722732099997, 50.772528975000057 ], [ -115.49321284, 50.770504059000075 ], [ -115.49393662599999, 50.768143038000062 ], [ -115.50289288699997, 50.762270941000054 ], [ -115.50846411599997, 50.754243300000041 ], [ -115.5104796, 50.753297703000044 ], [ -115.51397592099994, 50.748953800000038 ], [ -115.51861431199995, 50.745737989000077 ], [ -115.52586378899997, 50.743771099000071 ], [ -115.53026371899995, 50.74397910700003 ], [ -115.53451319199996,

 

Create Django queryset to create data source for counts of properties by FSA to be added to the Leaflet map layer.

I used a SQL query in the Django View to get count of properties by FSA.

This dataset looks like this in the template. These results have only one FSA, if it had more it would have more FSA / count pairs.

   var fsa_array = [["V3J", 19]];

Below is code for  the Django view query to create the fsa_array FSA / counts data source.

    cursor = connection.cursor()
    cursor.execute(
    "select fsa, count(*) \
    from properties \
    group by fsa \
    order by fsa;")
    fsas_cursor = list(cursor.fetchall())

    fsas_array = [(x[0].encode('utf8'), int(x[1])) for x in fsas_cursor]

My Javascript largely retains the Leaflet tutorial code with some modifications:

1. How the legend colors and intervals are assigned is changed but otherwise legend functions the same.

2. Significantly changed how the color for each FSA is assigned. The tutorial had the color in its geoJSON file so only had to reference it directly. My colors were coming from the View so I had to change code to include new function to match FSA’s in both my Django view data and the geoJSON FSA boundary file and return the appropriate color based on the Django View data set count.

var map = L.map('map',{scrollWheelZoom:false}).setView([ active_city_center_lat, active_city_center_lon], active_city_zoom);

map.once('focus', function() { map.scrollWheelZoom.enable(); });

var fsa_array = fsas_array_safe;

L.tileLayer('https://api.tiles.mapbox.com/v4/{id}/{z}/{x}/{y}.png?access_token=pk.eyJ1IjoibWFwYm94IiwiYSI6ImNpandmbXliNDBjZWd2M2x6bDk3c2ZtOTkifQ._QA7i5Mpkd_m30IGElHziw', {
    maxZoom: 18,
    attribution: 'Map data ©

OpenStreetMap

 contributors, ' +
        '

CC-BY-SA

, ' +
        'Imagery ©

Mapbox

',
    id: 'mapbox.light'
}).addTo(map);

// control that shows state info on hover
var info = L.control();

info.onAdd = function (map) {
    this._div = L.DomUtil.create('div', 'info');
    this.update();
    return this._div;
};

info.update = function (props) {
    this._div.innerHTML = (props ?
        '' + props.CFSAUID + ' ' + getFSACount(props.CFSAUID) + ' lonely homes' 
        : 'Hover over each postal area to see lonely home counts to date.');
};

info.addTo(map);

// get color 
function getColor(n) {
    return n > 30 ? '#b10026'
           : n > 25 ? '#e31a1c' 
           : n > 25 ? '#fc4e2a' 
           : n > 20 ? '#fd8d3c'
           : n > 15  ? '#feb24c'
           : n > 10  ? '#fed976'
           : n > 5  ? '#ffeda0'
           : n > 0  ? '#ffffcc'
           : '#ffffff';
}     

function getFSACount(CFSAUID) {
    var fsaCount;
    for (var i = 0; i < fsa_array.length; i++) {
        if (fsa_array[i][0] === CFSAUID) {
            fsaCount = ' has ' + fsa_array[i][1];
            break;
        }
    }
    if (fsaCount == null) {
         fsaCount = ' has no '; 
    }
    return fsaCount;
}

function getFSAColor(CFSAUID) {
    var color;
    for (var i = 0; i < fsa_array.length; i++) {
    if (fsa_array[i][0] === CFSAUID) {
        color = getColor(fsa_array[i][1]);
        //console.log(fsa_array[i][1] + '-' + color)
        break;
        }
    }
    return color;
}
    
function style(feature) {
    return {
        weight: 1,
        opacity: 1,
        color: 'white',
        dashArray: '3',
        fillOpacity: 0.7,
        fillColor: getFSAColor(feature.properties.CFSAUID)
    };
}

function highlightFeature(e) {
    var layer = e.target;
    layer.setStyle({
        weight: 2,
        color: '#333',
        dashArray: '',
        fillOpacity: 0.7
    });

    if (!L.Browser.ie && !L.Browser.opera) {
        layer.bringToFront();
    }

    info.update(layer.feature.properties);
}

var geojson;

function resetHighlight(e) {
    geojson.resetStyle(e.target);
    info.update();
}

function zoomToFeature(e) {
    map.fitBounds(e.target.getBounds());
}

function onEachFeature(feature, layer) {
    layer.on({
        mouseover: highlightFeature,
        mouseout: resetHighlight,
        click: zoomToFeature
    });
}

geojson = L.geoJson(bcData, {
    style: style,
    onEachFeature: onEachFeature
}).addTo(map);

var legend = L.control({position: 'bottomright'});

legend.onAdd = function (map) {

    var div = L.DomUtil.create('div', 'info legend'),
        grades = [0, 1, 5, 10, 15, 20, 25, 30],
        labels = [],
        from, to;

    for (var i = 0; i < grades.length; i++) {
        from = grades[i];
        if (i === 0) {
            var_from_to = grades[i];
            var_color = getColor(from);
        } else {
            var_from_to =  from + (grades[i + 1] ? '–' + grades[i + 1] : '+') ;
            var_color = getColor(from + 1);
        }
        
        labels.push(
            ' ' +
             var_from_to);
    }

    div.innerHTML = labels.join('
');
    return div;
};

legend.addTo(map);

That is pretty much all there is to creating very nice looking interactive free open-source choropleth maps for your Django website application!

Chart.js tooltip format number with commas

Chart.js V2.0 is a useful javascript charting library. It looks great, has ton of features though it is new enough that there is still some work to find out how to get some relatively simple things done.

In this case I wanted to format the chart’s tooltip. Tooltips are the pop-ups that show when you hover mouse over a bar or line in a chart and show the yAxis value along with any other information you want to include.

By default Chart.js tooltips do not format numbers with commas and there was no simple option to do this.

After some Googling, I found out it required using Chart.js callbacks feature which can be used to format chart elements. Note V1 used a different method that modified a tooltip’s template but that is now deprecated in V2.0.

The callback is in the Options’ tooltips section. You put function into the callback that uses regex to insert commas.

callbacks: {
    label: function(tooltipItem, data) {
        return tooltipItem.yLabel.toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",");
    },
},

This can be done as a global change to all charts in the page or just to a specific chart which is what I used and is shown in the example below.

The result is that the tooltip now has a commas.

chartjs tooltip number comma



Display Django queryset results on Google Map

I wanted to be able to show locations by latitude and longitude with data from Django 1.8 website on a Google Map using the Google Maps API.

I used the accepted answer from a stackoverflow question to create a Google Map with multiple markers that had the store name with a link that would open that store’s details when clicked.

I did the following:

  • copied the stackoverflow solution javascript and html code into new Django template called fountain_map.html
  • created new Django view called fountain_map for that template
  • create new urls.py line to route the fountain_map url for new view/template

The stackoverflow answer used Google Maps javascript that had a javascript array like this:

    var locations = [
      [‘Bondi Beach’, -33.890542, 151.274856, 4],
      [‘Coogee Beach’, -33.923036, 151.259052, 5],
      [‘Cronulla Beach’, -34.028249, 151.157507, 3],
      [‘Manly Beach’, -33.80010128657071, 151.28747820854187, 2],
      [‘Maroubra Beach’, -33.950198, 151.259302, 1]
    ];

However while the example has this hard coded list of locations I wanted a dynamic list populated by queryset records from the new view.

So I created a queryset in the view that retrieved the location records:

   

map_points = Fountains.objects.filter(lat__isnull=False)

Note that I filtered to only retrieve records that had a lat value so I wasn’t sending records that couldn’t be mapped.

Since the queryset object is not immediately readable by the javascript as the location variable, it needed to be transformed into a format acceptable for the javascript.

There are a couple of options:

  • Use Django’s serialization to turn it into JSON
  • Loop through queryset object and manually build the array in correct format, this could be done in the view or in the template

I choose to do this transformation in the template. Django’s serialization has lots of documentation and lots of SO question and answer but seemed easier to do this in template for now.

So in the template i simply looped through the map_point queryset object to create the array that the var locations required.

The javascript required the square brackets as shown in example above along with quotes around the location name.

Note that the Stack Overflow answer also has a digit as the fourth item in the record but I excluded that in mine. Not sure what it was but user obviously wanted to show it in marker label or something like that.

Anyways my template loop looked like this:

      var locations = [
        {% for point in map_points %}
            {% if point.lat = None %}
            {% else %}
              {{ point.name }}’, {{ point.lat }}, {{ point.lon }}],
            {% endif %}
        {% endfor%}
        ]

You can see that I retrieved the following values for the locations array from the queryset results:

  • name (fountain name to show on marker label popup)
  • id (so that it could be used to create marker link to the Django view for that store)
  • lat
  • lon

That was all I needed to do and gave me a Google Map showing each fountain’s location with a Google red pin marker. When user clicked on marker, the fountain name would show that had link to that fountain’s detail page.

google map

How to move Google Chart x-axis to top of chart

The newest Google Charts API allows you to more easily change the axis that you want the ticks and labels are on.

In older version you had to create a secondary axis with dummy data and hide it showing only the axis with the real data on the where you want it (eg right side instead of left or top instead of bottom).

You will still do this in the new version but it is easier because you can use the ‘axes’ option which is part of the ‘Dual-axis’ feature .

Note that you will continue to use the hAxis and vAxis options as normal but will also use a new option called axes in which you specify the ‘side’ you want axis on.

Also note that the axes option will be used to name the axis so the hAxis or vAxis option axis title will be ignored.

Here is example options showing axes options being used to the x-axis on the top of a horizontal bar chart:

//set chart options
var options = {
     title: ”,
     width: ‘80%’,
     height: 700,
     bar: { groupWidth: ‘95%’ },
     legend: { position: ‘none’ },
     bars: ‘horizontal’,
     hAxis: {
          title: ‘my x axis’,
          minValue: 0,
          format: ‘decimal’,
          textPosition: ‘out’,
     },
     vAxis: {
          title: ‘my y axis’,
          textPosition: ‘out’
     },
     axes: {
          x: {
               0: { side: ‘top’, label: ‘my x axis’}
          },
          y: {
               0: { side: ‘left’, label: ‘my y axis’}
          }
}