d3 rollup on each csv columns

Question

I am trying to use d3.rollup to get the summation of each column. data.csv looks like this:

DateTime, A, B, C
2020/2/27 7:16  3   0   1
2020/2/26 23:44 1   0   4
2020/2/26 21:35 1   1   0
2020/2/26 15:14 1   0   3
2020/2/25 20:35 1   1   0
2020/2/25 16:10 1   0   3

Desired output:

2020/2/27   3   0   1
2020/2/26   3   1   7
2020/2/25   2   1   3

I would like to group my data on a daily basis. I am trying to nest the data using d3.nest but failing to map the data on each column.

I do not want to call out the columns by name such as d3.sum(d.A). Instead, I want to map the data to all columns.

var nested = d3.nest()
  .key(function(c) {return parseDate(new Date(c.datetime)); })//parseDate 

To get date by month:

.rollup(function(d){return {
  values: d3.sum(d.map(function(v) {
  return +v[d];}))};})
.entries(data);

Any help would be appreciated. I've just started with js and d3.

Answer 1

I'll give an answer with vanilla js and I'll try to come back and give a d3 answer once I've read enough of the docs.

You can do it like this:

csv:

DateTime, A, B, C
2020/2/27 7:16,  3,   0,   1
2020/2/26 23:44, 1,   0,   4
2020/2/26 21:35, 1,   1,   0
2020/2/26 15:14, 1,   0,   3
2020/2/25 20:35, 1,   1,   0
2020/2/25 16:10, 1,   0,   3

Which we could convert to a JSON / JavaScript array many ways, such as:

Taken from: https://stackoverflow.com/a/61080978/9792594:

const csv2json = (str, delimiter = ',') => {
  const titles = str.slice(0, str.indexOf('\n')).split(delimiter);
  const rows = str.slice(str.indexOf('\n') + 1).split('\n');
  return rows.map(row => {
    const values = row.split(delimiter);
    return titles.reduce((object, curr, i) => (object[curr.trim()] = values[i].trim(), object), {})
  });
};


let csv =
`DateTime, A, B, C
2020/2/27 7:16,  3,   0,   1
2020/2/26 23:44, 1,   0,   4
2020/2/26 21:35, 1,   1,   0
2020/2/26 15:14, 1,   0,   3
2020/2/25 20:35, 1,   1,   0
2020/2/25 16:10, 1,   0,   3`;

let word_array = csv2json(csv,',');
console.log(word_array)

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

JSON / JavaScript Array:

[
  {
    "DateTime": "2020/2/27 7:16",
    "A": 3,
    "B": 0,
    "C": 1
  },
  {
    "DateTime": "2020/2/26 23:44",
    "A": 1,
    "B": 0,
    "C": 4
  },
  {
    "DateTime": "2020/2/26 21:35",
    "A": 1,
    "B": 1,
    "C": 0
  },
  {
    "DateTime": "2020/2/26 15:14",
    "A": 1,
    "B": 0,
    "C": 3
  },
  {
    "DateTime": "2020/2/25 20:35",
    "A": 1,
    "B": 1,
    "C": 0
  },
  {
    "DateTime": "2020/2/25 16:10",
    "A": 1,
    "B": 0,
    "C": 3
  }
]

Answer to your question (aggregate data based on date):

• use reduce, to create an object with each day as a key (parse date)
• when the key already exists, merge the values on A, B, C
• convert Object to an array with Object.values

const myData = [{"DateTime":"2020/2/27 7:16","A":3,"B":0,"C":1},{"DateTime":"2020/2/26 23:44","A":1,"B":0,"C":4},{"DateTime":"2020/2/26 21:35","A":1,"B":1,"C":0},{"DateTime":"2020/2/26 15:14","A":1,"B":0,"C":3},{"DateTime":"2020/2/25 20:35","A":1,"B":1,"C":0},{"DateTime":"2020/2/25 16:10","A":1,"B":0,"C":3}];

const groupedData = Object.values(myData.reduce((aggObj, item) => {
  
  const dateOnly = new Date(item.DateTime).toLocaleDateString();
  
  if (aggObj.hasOwnProperty(dateOnly)){
    Object.entries(aggObj[dateOnly]).forEach(([key,val]) => {
      if (key != 'DateTime'){
        aggObj[dateOnly][key] = val + item[key];
      }
    });
  } else {
    aggObj[dateOnly] = {...item, "DateTime": dateOnly};
  }
  
  return aggObj;
  
}, {}));

console.log(groupedData);

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