Add data from two arrays into CSV output

Question

I have some JSON which takes the following form.

[
  [
    {
      "ID": 1,
      "col1": "something",
      "col2": "something"
    },
    {
      "ID": 2,
      "col1": "something",
      "col2": "something"
    }
  ],
  [
    {
      "ID": 1,
      "col3": "something else"
    },
    {
      "ID": 2,
      "col3": "something else"
    }
  ]
]

Using D3, I parse this JSON into an multidimensional array. I am then preparing the data for download as a CSV. So at the moment, I am doing

let csvData = 'ID,col1,col2\n';
data[0].forEach(function(row) {
    let line = [];
    Object.keys(row).forEach(function(key) {
        line.push(row[key])
    });
    csvData = csvData + line.join(',') + '\n';
});

The above will produce a flat csv file like so

id,col1,col2
1,something,something
2,something,something

What I am trying to do now is add the match up the id in the next array element, and add col3 to the data. So the overall output of the csvData should be

id,col1,col2,col3
1,something,something,something else
2,something,something,something else

How can I add this data to my existing csvData?

Thanks

Just a note, this all appears to be vanilla JavaScript. I see no D3 code here, nor any need for it either — ADyson
– ADyson, Commented May 2, 2019 at 11:09
I am using D3 for charts, and as I have access to this, I used d3.json to parse the JSON file. D3 is not part of this question, I just wanted to let everyone know how the JSON is being parsed. — katie hudson
– katie hudson, Commented May 2, 2019 at 11:11
It's irrelevant really, you're working on an array now, the fact it used to be JSON doesn't particularly matter. And anyway, even if you passed it via some D3 code, the JSON is likely being parsed by the standard JSON.parse() method under the hood. I highly doubt D3 has its own parser, it would be pointless to re-invent what's already there. Sorry I know I'm being a little pedantic, but it's best not to include irrelevant or diversionary details in the question. — ADyson
– ADyson, Commented May 2, 2019 at 11:13

ADyson · Accepted Answer · 2019-05-02 11:20:37Z

1

You could match them by using a for loop, so that you've got an explicit counter variable, and can use it to refer to the same index in both arrays.

Of course this relies on the data being ordered the same way in each array, and the same number of elements being in each array. If that's not always the case then you can't use this approach.

var data = [
  [
    {
      "ID": 1,
      "col1": "something",
      "col2": "something"
    },
    {
      "ID": 2,
      "col1": "something",
      "col2": "something"
    }
  ],
  [
    {
      "ID": 1,
      "col3": "something else"
    },
    {
      "ID": 2,
      "col3": "something else"
    }
  ]
];

let csvData = 'ID,col1,col2,col3\n';
let arr1 = data[0];
let arr2 = data[1];

for (i = 0; i < arr1.length; i++)
{
    let row1 = arr1[i];
    let row2 = arr2[i];
    let line = [];
    Object.keys(row1).forEach(function(key) {
        line.push(row1[key])
    });

    line.push(row2["col3"]);
    csvData = csvData + line.join(',') + '\n';
}

console.log(csvData);

answered May 2, 2019 at 11:20

ADyson

62.9k16 gold badges86 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

katie hudson Over a year ago

Is there a way to do this without relying on the index?

ADyson Over a year ago

Yes, you could search the other array for an object which has the same value in the "ID" property, and when you find it, use the col3 value from that object. My way is just faster and easier, as long as you can get the data into a neat state first.

Mr. Polywhirl · Accepted Answer · 2019-05-02 12:03:57Z

This is not fool-proof, but it dynamically builds a CSV string based on an array of JSON arrays.

I tried to keep it concise, but left in key-points of conversion fields, records, etc...

if (Set.prototype.addAll === undefined) {
  Set.prototype.addAll = function(array) {
    let args = arguments.length > 1 ? arguments : array;
    args.forEach(item => this.add(item)); /* Add multiple items from array to a set */
    return this;
  };
}

console.log(multiDataToCsv(getData(), 'ID', '\t'));

function multiDataToCsv(jsonArrays, primaryKey, delimiter) {
  let keys = Array.from(jsonArrays.reduce((fields, jsonArray) => {
    return fields.addAll(Object.keys(jsonArray[0]));
  }, new Set()));
  let records = jsonArrays[0].reduce((records, data) => {
    return records.concat(jsonArrays.reduce((record, jsonArray) => {
      return Object.assign(record, jsonArray.filter(item => item[primaryKey] === data[primaryKey])[0]);
    }, {}));
  }, []);
  let csvData = [keys].concat(records.map(record => keys.map(key => record[key])));
  return csvData.map(row => row.join(delimiter)).join('\n');
}

function getData() {
  return [
    [{
      "ID": 1,
      "col1": "something 1.0",
      "col2": "something 1.1"
    }, {
      "ID": 2,
      "col1": "something 2.0",
      "col2": "something 2.1"
    }],
    [{
      "ID": 1,
      "col3": "something else 1"
    }, {
      "ID": 2,
      "col3": "something else 2"
    }]
  ];
}

.as-console-wrapper { top: 0; max-height: 100% !important; }

Collectives™ on Stack Overflow

Add data from two arrays into CSV output

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related