Join arbitrary data from google spreadsheets

planemad commented 5 years ago

To enable dynamic visualization #32 , we need a convenient way to attach more properties to constituencies without needing to regenerate and update the Mapbox tileset.

A convenient way to do this would be to make data joins on the fly using a loaded CSV or Google sheets. Ref:

answerquest commented 5 years ago

Here are, once again, some snippets that may help. This is for CSV loading.

Papa.parse library for loading CSVs

Site. CDN: <script src="https://cdnjs.cloudflare.com/ajax/libs/PapaParse/4.6.3/papaparse.min.js"></script>

Loading a CSV in as an array of JSONs:

var globalTable = [];
Papa.parse(`pc_metadata.csv`, {
    download: true,
    header: true, // will convert the data to json
    skipEmptyLines: true,
    dynamicTyping: true, // this reads numbers as numerical; set false to read everything as string
    complete: function(results, file) {
        globalTable = results.data; 
        // the array is now stored in globalTable var, as an array of json objects with column headers as keys
    }
});

Note that this is an asynchronous call. I typically load the csv's at page load and keep them stored in a global var that's declared as an empty array from earlier. The functions needing it will most likely be run long after it's loaded, but if someone wants to be on the safe side they can do the async await or other hacks.

Filtering the data:

Suppose there are two columns: st_code and pc_code which will help you single out your row. You have those values at hand. Here's how to get the matching row:

var filtered = globalTable.filter(function(e, index, array) {
    return ( (e.st_code == ST_CODE) && (e.pc_code == PC_CODE ) );
});

Explanation: whatever evaluates to true, will survive the filtering.
The target row is now in : filtered[0].

So if you want to access the value under pc_hindi column,
filtered[0]['pc_hindi'] should do the job.

Note on reading data from google spreadsheets

I've tried this by using some middle services.. didn't work out so well because it would take quite some time, or the call would error out every now and then. I prefer CSV files. Recently however I learned how to make a google apps script on the spreadsheet that can output everything as json or whatever as the default response when its URL is accessed. That'll take time to set up but is doable. It used to be much simpler a long time ago in a galaxy far away but now google likes to keep things complicated.

planemad commented 5 years ago

Thank you @answerquest, will play with this.

Note on reading data from google spreadsheets

Have been successful in using google sheet after using the publishing to the web option. This example loads geojson from github and makes a chloropleth using values in a google sheet. Completely decentralized with no Mapbox API call :D https://jsfiddle.net/planemad/04w9o3xz/

answerquest commented 5 years ago

Have been successful in using google sheet after using the publishing to the web option.

That's great! In 2015 it had been throwing up a CORS error or something. Glad to see it's working fine now.

publicmap / electionmap