Data operations (fill, min, max) on CSV data in JavaScript?

Question

I'm loading different indicator CSV files into JavaScript, example:

CSV for population:

id,year,value
AF,1800,3280000
AF,1820,3280000
AF,1870,4207000
AG,1800,37000
AG,1851,37000
AG,1861,37000

For each indicator file I need to:

• Gap fill missing years for each entity (id)
• Find the time span for each entity
• Find the min and max for each entity
• Find the time span for the indicator
• Find the min and max for the indicator

What is an inexpensive way of performing these operations? Alternatively, is there a good JavaScript library for performing these kind of common data operations and storing the data effectively in various object representations?

I'd like the final representation of the above file to look something like:

data = {
    population : {
        entities : 
            AF : {
                data : {
                    1800 : 3280000,
                    1801 : 3280000,
                 },
                entity_meta : {
                    start : 1800,
                    end : 
                    min : 
                    max :
             },
            [...]
        indicator_meta : {
                start : 1700,
                end : 
                min : 
                max :
        }
        [...]

Thanks!

Answer 1

Lets Assume that you have the CSV data in a 2d array:

var data = [[AF,1800,3280000],
[AF,1820,3280000],
[AF,1870,4207000],
[AG,1800,37000],
[AG,1851,37000],
[AG,1861,37000]]

For this example I will use jQuerys utility functions as it will make the job a lot easier without any real overhead.

// we will loop thru all the rows
// if the id does not belong to the entities then we will add the property.
// if the property does exist then we update the values

var entities = {}
$.each(data, function (i, n) {

    // set property
    if (!entities[n[0]]) {
        entities[n[0]] = {
            data : {
                n[1]: n[2]
            },
            entity_meta: {
                start: n[1],
                end: n[1]
                min: n[1]
                max: n[1]
            }
        }

    // update property
    } else {

        // add new data property
        entities[n[0]]['data'][n[1]] = n[2];

        // if the property should change then update it
        if ( entities[n[0]]['entity_meta']['min'] > n[1] ) {
             entities[n[0]]['entity_meta']['min'] = n[1];
        }
    }
});

That obviously isn't all the code but it should explain clearly the approach that should be taken.

Also not that your intended final object structure is very much over complicated you should really use arrays where appropriate, especially for entities and data.