Combine multiple csv files in javascript using filtering

Question

I'm working with 2 csv files in D3 and I want to combine them together. id and Grade correspond to the first csv file and id and status to the second one. id is the same in both files.

To convert the files from string into integers I use something like this

 data.forEach(function(d) {
   d.id = d.id;
   d.Grade = +d.Grade;
 });

 data2.forEach(function(d) {
   d.id = d.id;
   d.Status = d.Status;
 });

What I actually want is to add Status to the corresponding id from data. Actually to check if the id is the same and add Status as a new column according to the id. Any idea how to do this in javascript?

I don't understand your question. Can you make it more specific? Anyway, this library can be helpful: data-forge-js.com — Pawel Zentala
– Pawel Zentala, Commented Jun 5, 2018 at 22:38
Thanks! for example i have the following data in csv1 id 123, Grade 1, id 345, Grade 4 and in csv2 id 123 , Status "yes" , id 345, Status "yes"...I want to add status to the first csv like id 123, Grade 1, Status "yes" and id 345, Grade 4, Status "yes" — Catalin Vasilescu
– Catalin Vasilescu, Commented Jun 5, 2018 at 22:44
Not related to your question, but mind your names: sometimes you write status, sometimes you write Status... that will be a problem, JavaScript is case sensitive. — Gerardo Furtado
– Gerardo Furtado, Commented Jun 5, 2018 at 23:46

Pawel Zentala · Accepted Answer · 2018-06-05 23:34:38Z

The easiest way to read and save files in JS is with Node.js. So this is working example written in Node.js and with data-forge:

const dataForge = require('data-forge');

/**
 * "csv1.csv":
 *
 * id, grade
 * 123, 1,
 * 345, 4,
 * 177, 5,
 */
const dataFrameGrades = dataForge.readFileSync('csv1.csv').parseCSV();

/**
 * csv2.csv:
 *
 * id, status
 * 123, 'yes',
 * 345, 'no',
 * 177, 'yes',
 */
const dataFrameStatuses = dataForge.readFileSync('csv2.csv').parseCSV();

/**
 * Merge columns from two CSV
 * 
 * dataFrame1 is left
 * dataFrame2 is right
 */
const dataFrameMerged = dataFrameGrades.join(
  dataFrameStatuses,
  grade => grade.id,
  status => status.id,
  (grade, status) => {
    return {
      id: grade.id,
      grade: grade.grade,
      status: status.status
    };
  }
);

/** Save CSV */
dataFrameMerged.asCSV().writeFileSync('csvMeged.csv');

Gerardo Furtado · Accepted Answer · 2018-06-05 23:48:52Z

1

There are several ways for doing that. Supposing data1 as the first parsed CSV (with id and grade but no status) and data2 as the second parsed CSV (with id and status), you can use find or filter to get the corresponding object in data2 and copying its status on data1.

For instance, using find:

data1.forEach(function(d) {
  var obj = data2.find(function(e) {
    return d.id === e.id
  });
  d.status = obj.status;
});

Here is a demo (using d3.csvParse, since the S.O. snippet doesn't allow real CSVs):

var csv1 = `id,grade
1,42
2,65
3,17`;

var csv2 = `id,status
2,yes
1,no
3,no`;

var data1 = d3.csvParse(csv1);
var data2 = d3.csvParse(csv2);

data1.forEach(function(d) {
  var obj = data2.find(function(e) {
    return d.id === e.id
  });
  d.status = obj.status;
});

console.log(data1)

<script src="https://d3js.org/d3.v5.min.js"></script>

I'm scrambling the rows in both CSVs just to show that the order doesn't matter, but only their id.

edited Jun 5, 2018 at 23:48

answered Jun 5, 2018 at 23:43

Gerardo Furtado

102k9 gold badges128 silver badges178 bronze badges

3 Comments

Catalin Vasilescu Over a year ago

Very interesting because here works but when I run my program I get vis.js:442 Uncaught TypeError: Cannot read property 'status' of undefined

Catalin Vasilescu Over a year ago

with filter i don't get the error, but status is always undefined

Gerardo Furtado Over a year ago

Please create a Plunker or a Blockbuilder showing exactly how you're doing it.

Collectives™ on Stack Overflow

Combine multiple csv files in javascript using filtering

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related