Converting JSON to CSV on the fly

I wanted to create a real-time graph of COVID-19 India Vaccination Progress. The data for which will come from the datameet/covid19 project. My initial idea was to build one using frappe similar to the COVID-19 cases chart. But many on the DataMeet community use Datawrapper. I like Datawrapper too. Datawrapper has a feature where it can pull the data from external sources and keep the chart up-to-date. That suits my requirement.

But the Datawrapper wants CSV. They don't handle the JSON data directly. If it was a one-time conversion. I would have used a tool like CSVKIT or Eric Mill's JSON-to-CSV converter. But I needed a tool that could do this on a real-time basis.

Initially, I thought of writing an AWS lambda that would do this for me. But then I settled for something more straightforward and easier like pipedream's HTTP event handler. Once you deploy, you will get an URL, and you can use that with Datawrapper. The code for converting the JSON to CSV is inside the event handler. Here the event being HTTP GET.

You can also make a copy of it for yourself and make your changes before deploying.

The code for converting the JSON to CSV is below. It uses pieces of code from multiple projects that I like and use. I have the links to them within the code for your reference.

The real-time graph is embedded above. You can also find it at DataWrapper.

//########################################################################################
// Thejesh GN https://thejeshgn.com
//
// This converts the JSON into CSV so it can be used by DataWrapper
// This is a hack where I combined piecies of code from various projects
// I have added link to those below if you want to check them out
//
// I love JSON to CSV converter by Eric Mill, https://konklone.io/json/ 
// Source code of it is at https://github.com/konklone/json
// It does an awesome job of converting JSON to CSV in the front end
//
// I wanted to do the same in backend and the pipedream seemed like a good place
// To host that serverless code. at some point I will write a proper serverless code
// Where if I send the url of JSON it will return CSV. Until then this hack is good 
// enough for me.
//
// The final datawrapper graph is at https://datawrapper.dwcdn.net/ThC0E/2/
// It caches the data, so its not a major hit on pipedream.
//########################################################################################
const axios = require('axios')

//########################################################################################
// Pass in the objects to merge as arguments.
// For a deep extend, set the first argument to `true`.
// https://gomakethings.com/vanilla-javascript-version-of-jquery-extend/
var extend = function () {

    // Variables
    var extended = {};
    var deep = false;
    var i = 0;
    var length = arguments.length;

    // Check if a deep merge
    if ( Object.prototype.toString.call( arguments[0] ) === '[object Boolean]' ) {
        deep = arguments[0];
        i++;
    }

    // Merge the object into the extended object
    var merge = function (obj) {
        for ( var prop in obj ) {
            if ( Object.prototype.hasOwnProperty.call( obj, prop ) ) {
                // If deep merge and property is an object, merge properties
                if ( deep && Object.prototype.toString.call(obj[prop]) === '[object Object]' ) {
                    extended[prop] = extend( true, extended[prop], obj[prop] );
                } else {
                    extended[prop] = obj[prop];
                }
            }
        }
    };

    // Loop through each object and conduct a merge
    for ( ; i < length; i++ ) {
        var obj = arguments[i];
        merge(obj);
    }

    return extended;

};

//########################################################################################
//https://konklone.io/json/
//https://github.com/konklone/json/blob/gh-pages/assets/site.js
function parse_object(obj, path) {
    if (path == undefined)
        path = "";

    var type = typeof(obj);
    var scalar = (type == "number" || type == "string" || type == "boolean" || type == "null");

    if (type == "array" || type == "object") {
        var d = {};
        for (var i in obj) {

            var newD = parse_object(obj[i], path + i + "/");
            d = extend(d, newD);
        }

        return d;
    }

    else if (scalar) {
        var d = {};
        var endPath = path.substr(0, path.length-1);
        d[endPath] = obj;
        return d;
    }

    // ?
    else return {};
}

// otherwise, just find the first one
function arrayFrom(json) {
    var queue = [], next = json;
    while (next !== undefined) {
        if (typeof(next) == "array") {

            // but don't if it's just empty, or an array of scalars
            if (next.length > 0) {

              var type = typeof(next[0]);
              var scalar = (type == "number" || type == "string" || type == "boolean" || type == "null");

              if (!scalar)
                return next;
            }
        } if (typeof(next) == "object") {
          for (var key in next)
             queue.push(next[key]);
        }
        next = queue.shift();
    }
    // none found, consider the whole object a row
    return [json];
}

function removeTrailingComma(input) {
  if (input.slice(-1) == ",")
    return input.slice(0,-1);
  else
    return input;
}

function flatten(inArray) {
    var outArray = [];
    for (var row in inArray)
        outArray[outArray.length] = parse_object(inArray[row]);
    return outArray
}
//########################################################################################
//https://www.newline.co/@anthonygore/how-to-convert-json-to-csv-in-nodejs--23c6b226
function convert_to_csv(data){
    csv = data.map(row => Object.values(row));
    csv.unshift(Object.keys(data[0]));
    csv = csv.join('\n');
    console.log(csv)
    return csv
}
//########################################################################################
const dataSourceUrl = "https://raw.githubusercontent.com/datameet/covid19/master/data/mohfw_vaccination_status.json"
const data_json =  (await axios.get(dataSourceUrl)).data
const data = flatten(data_json.rows)
console.log(data)


$respond({
  immediate: true,
  status: 200,
  headers: {"Content-type":"text/plain"},
  body: convert_to_csv(data)
})

Update: Added the headers so it gets formatted properly as text, One could also use "text/csv" instead of "text/plain"

You can read this blog using RSS Feed. But if you are the person who loves getting emails, then you can join my readers by signing up.

Join 2,132 other subscribers

2 Responses

  1. Sundar L says:

    Nice, but why are the labels repeating after 1M? Are they getting rounded off?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.