Home > Back-end >  calculate list of the sums of each column from csv
calculate list of the sums of each column from csv

Time:12-13

I'm trying to calculate sums of each columns of csv. I'm able to read a csv in js using readfile method. I also was able to loop through it and parsed data into array of objects. Now I just to figure out a way to add up all the column elements, that's where I'm struggling. My csv object is in array of object format which looks like this.

[
  { item: '18', count: '180' },
  { item: '19', count: '163' },
  { item: '20', count: '175' },
  { item: '', count: undefined }
]

CSV input is like this:

item,count
18,180
19,163
20,175

I want to add 18 19 20 and final answer should look like this [57,518].

Here's I've done so far, I just need help to make this better and column wise adding logic in JS, please help.

       const fs = require('fs')
        let result = []

        var dataArray = []
        fs.readFile(filename, 'utf8', function (err, data) {
            dataArray = data.split(/\r?\n/);
            // console.log("dataArray", dataArray)
            var headers = dataArray[0].split(",");


            for (var i = 1; i < dataArray.length; i  ) {

                var obj = {};
                console.log("dataArray", dataArray)
                var currentline = dataArray[i].split(",");


                for (var j = 0; j < headers.length; j  ) {
                    obj[headers[j]] = currentline[j];
                }

                result.push(obj);

            }

        })
 

CodePudding user response:

You can iterate through each row of your csv and sum up values of items and count using array#reduce and array#forEach.

const fs = require('fs').promises;

const fileName = 'data.csv'

const calculateSum = async () => {
    const data = await fs.readFile(fileName, 'utf-8');
    const dataArray = data.split(/\r?\n/);
    const header = dataArray[0].split(',')
    const result = dataArray.slice(1).reduce((sum, arr) => {
        arr.split(',').forEach((v, i) => {
            sum[i] = (sum[i] || 0)   Number(v.trim());
        })
        return sum;
    }, []);
    console.log(result);
}

CodePudding user response:

Generic function

let dataArray = [
    { item: '18', count: '180' },
    { item: '19', count: '163' },
    { item: '20', count: '175' },
    { item: '', count: undefined }
]

const sums = dataArray.reduce((sum, tableRow) => {
    Object.keys(tableRow).forEach((obj) => {
       if (Number(tableRow[obj])) sum[obj] = (sum[obj] || 0)   Number(tableRow[obj]);
    })
    return sum;
}, []);

console.log(sums) // [ item: 57, count: 518 ]
  • Related