/ javascript

Parsing a CSV file using Node/JavaScript

Last year, I was heavily interested into crypto currency (check out my Crypto Coaster website. This interest was driven by monetary reasons. This combined with the fact that I was a greedy, emotional trader resulted in a large number of panic driven coin buys and sells. This year, I decided to offload all my crypto currency in favour of less speculative investments like index traded funds.

I downloaded all the trades I did as a CSV file, which had thousands of rows. I simply could not have analysed it manually. So I decided to use the programming language I'm strongest at: JavaScript, to analyse my trade data.

I found it harder than expected to find out an up to date article on how to read and parse a CSV file using Node. So hopefully my article helps someone trying to achieve the same thing.

Prerequisites

For this tutorial, I'm using Node v8.11.4, I would recommend installing Node 8+ if you want to follow this tutorial.

Packages

We will be using the csv-parse npm package so go ahead an install it:

npm install csv-parse

CSV File

Of course, you'll need a CSV file you are processing, for example puposes I've generated a CSV file containg 1000 randomly generated users using Mockaroo. There are 7 columns: (id, first_name, last_name, age, email, gender and country), the first 5 rows are:

id,first_name,last_name,age,email,gender,country
1,Malchy,Teliga,55,mteliga0@un.org,Male,Mexico
2,Yasmeen,Ruddlesden,89,yruddlesden1@bigcartel.com,Female,China
3,Fonzie,Skeldon,22,fskeldon2@dedecms.com,Male,Portugal
4,Krystalle,Meineken,7,kmeineken3@apple.com,Female,Russia
5,Garnet,Jossum,19,gjossum4@berkeley.edu,Female,China

This CSV is saved as data.csv.

The Code!

Reading and transforming the data

Create a file called calculate.js (or whatever you want really):

var fs = require("fs");
var parse = require("csv-parse");

var csvFile = "data.csv";

class User {
  constructor(id, firstName, lastName, age, email, gender, country) {
    this.id = id;
    this.firstName = firstName;
    this.lastName = lastName;
    this.age = age;
    this.email = email;
    this.gender = gender;
    this.country = country;
  }
}

const processData = (err, data) => {
  if (err) {
    console.log(`An error was encountered: ${err}`);
    return;
  }

  data.shift(); // only required if csv has heading row

  const userList = data.map(row => new User(...row));

  analyseUsers(userList);
}

fs.createReadStream(csvFile)
  .pipe(parse({ delimiter: ',' }, processData));

What we're doing here is:

  1. Defining the shape of our Users
  2. Reading the CSV using fs (which comes with Node) and piping the output into csv-parse
  3. Shifting the array (removing the CSV headings: id,first_name,last_name,age,email,gender,country)
  4. Mapping each entry in the array entries into a User object

Analysing the data

Now, we are free to process our list of users in our analyseUsers function. For example, we can find the average age of our users:

const analyseUsers = userList => {
  const ageSum = userList.reduce((acc, val) => acc += val.age, 0);
  const averageAge = ageSum / userList.length;
  console.log(averageAge);
}

Or the most common country:

const analyseUsers = userList => {
  let countryCountDict = new Map();

  userList.forEach(user => {
    let countryEntry = countryCountDict.get(user.country);

    const newVal = countryEntry === undefined
      ? 0
      : countryEntry + 1;

    countryCountDict.set(user.country, newVal);
  });

  let maxEntry = ['', Number.MIN_SAFE_INTEGER];

  for(const entry of countryCountDict.entries()) {
    if (entry[1] > maxEntry[1]) {
      maxEntry = entry;
    }
  }

  console.log(maxEntry);
}

Run the file using node calculate.js.

Good luck with analysing your data using Node!