CSV in JavaScript Using Albuquerque Open Data

28 Jan

In my last two posts, I showed you how to read KML (XML) over the web using AJAX. In this example, we will do the same with a tab delimited file from Albuquerque Open Data. The application will look like the image below.

The finished app. Choose a committee and see their financial information.

The finished app. Choose a committee and see their financial information.

 

Get the Data

On the Albuquerque Open Data site, we will use Campaign Finalized. You will see two options: XML or CSV. We will use the CSV – which is really tab delimited. Grab the data using AJAX.

var url = “http://data.cabq.gov/government/campaignfinalized/CampaignReportFinalizedVersionCABQ-en-us.csv”;

http=new XMLHttpRequest();
http.open(“GET”, url, true);
http.setRequestHeader(“Content-type”, “application/x-www-form-urlencoded”);
http.onreadystatechange = function() {//Call a function when the state changes.
if(http.readyState == 4 && http.status == 200) {
var results=http.responseText;

Parse the Data

The data is tab delimited in rows that have a newline character at the end. This file also has new line characters in fields – but they are wrapped in quotes. Handling this will be difficult on our own, so let’s use a library for it. Download PapaParse 4. This will simplify the process significantly. We can grab the data with:

p=Papa.parse(results,{
delimiter:”\t”,
newline:”\n” });

Now I have an array with each line as p.data[x] and each item as p.data[x][0-8]. For this example, we will use the committee name(p.data[x][0]) and the amount(p.data[x][8]).

Let’s populate the dropdown box. First, we need to create it in the HTML and add a default value.

<select id=’D1′></select>
var select = document.getElementById(“D1”);
var d= document.createElement(“option”);
d.value = -1;
d.id=-1;
d.textContent = “—Choose—“;
select.appendChild(d);

We can now iterate through our data and create an array of individual groups.

for(r=1;r<p.data.length;r++){
if(groups.indexOf(p.data[r][0].trim())>-1){
//do nothing
}
else{groups.push(p.data[r][0].trim());}
}

And then populate the combo box

for(g=0;g<groups.length-1;g++){
var opt = document.createElement(“option”);
opt.value = g;
opt.id=g;
opt.textContent = groups[g];
select.appendChild(opt);
}

Displaying the Data

We need to add an Event Listener on the combo box and execute a function.

document.getElementById(‘D1’).addEventListener(‘change’, money, false);

The function will grab the value of the item selected then get its text content (name). With this information we can pull all the data from the CSV for the financials of the committee selected. Lastly, update the DOM to display the results.

function money(m){
m=document.getElementById(this.value).textContent;
console.log(m);
for(y=0;y<p.data.length;y++){
if(p.data[y][0].trim()==m){
if(parseFloat(p.data[y][8])>0){
raised+=parseFloat(p.data[y][8]);
}
else{spent+=parseFloat(p.data[y][8]);}
}
else{}
}
document.getElementById(“output”).innerHTML='<h3>Raised:</h3>’+raised.toLocaleString()+'<h3>Spent:</h3>’+spent.toLocaleString()+'<h3>Difference</h3>’+(raised+spent).toLocaleString();

}

Without Papa Parse 4, this would have been a much more complicated task. There is no CSV standard, so dealing with individuals CSV files requires a lot of modification. In this example, having new line characters in a field wreaked havoc on my first attempts to parse it out manually. Also, I originally set the PapaParse option header=true, but the application complained. Instead of figuring out why it didn’t like it, I just started my loops at an index of 1 – dropping the headers at index 0.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: