Getting a Map of arrays by grouping the records

Question

I am trying to transform some data with D3 to get an average amount per day grouped by months (a Map of arrays). The data looks like this:

data = [
    {day: "mon", amount: "4", month: "jan"},
    {day: "tue", amount: "2", month: "jan"},
    {day: "wed", amount: "3", month: "jan"},
    {day: "wed", amount: "1", month: "jan"}
]

The output that I am trying to get should look like this:

{"jan": [
    {day:"mon", avg_amount:"4"},
    {day:"tue", avg_amount:"2"},
    {day:"wed", avg_amount:"2"}
]}

I already tried using D3's group and rollup functions but I am only able to get a Map of Maps which is not exactly what I want.

d3.rollup(data, 
    v => d3.mean(v, d => d.amount), 
    d => d.month, d => d.day);

I'm new to D3 so I am not sure how such a result could be achieved. Is there any simple way to do this with D3?

Answer 1

Despite your question's tittle saying "Map" and you saying "Map" several times in the question's body, you wrote the desired outcome as an object, probably because it's hard to write a Map in the question's body. A better way could be:

Map(1) {
    "jan" => [
        {day:"mon", avg_amount:"4"},
        {day:"tue", avg_amount:"2"},
        {day:"wed", avg_amount:"2"}
    ]
}

So I'll assume that you are in fact asking for a Map (specially because you're using d3.rollup, which returns a Map). However, just for safety, I'll provide two solutions, one with d3.rollup creating a real Map and another one with d3.nest creating an object, just in case.

Map with d3.rollup

If what you want is indeed a Map, just change the reduce function inside d3.rollup:

d3.rollup(iterable, reduce, ...keys)
//this part ----------^

Or, in your case:

d3.rollup(data, 
    v => d3.mean(v, d => d.amount),//this is the reduce 
    d => d.month, d => d.day);//2 keys here

This is the reduce I'll use:

v => v.reduce((a, b) => {
    const found = a.find(d => d.day === b.day);
    if (!found) {
        a.push({
            day: b.day,
            avg_amount: +b.amount
        })
    } else {
        found.avg_amount = (found.avg_amount + (+b.amount)) / 2
    };
    return a;
}, [])

By the way, you have 2 keys in that rollup, drop the last one. Finally, remember that you have strings, not numbers. Coerce them accordingly.

Here is a demo. Don't use the snippet console (it will show just {}), check your browser's console:

data = [{
    day: "mon",
    amount: "4",
    month: "jan"
  },
  {
    day: "tue",
    amount: "2",
    month: "jan"
  },
  {
    day: "wed",
    amount: "3",
    month: "jan"
  },
  {
    day: "wed",
    amount: "1",
    month: "jan"
  }
];

const nested = d3.rollup(data,
  v => v.reduce((a, b) => {
    const found = a.find(d => d.day === b.day);
    if (!found) {
      a.push({
        day: b.day,
        avg_amount: +b.amount
      })
    } else {
      found.avg_amount = (found.avg_amount + (+b.amount)) / 2
    };
    return a;
  }, []),
  d => d.month);

console.log(nested)

<script src="https://d3js.org/d3-array.v2.min.js"></script>

Object with d3.nest

On the other hand, if what you want is an object using d3.nest, there are two important things:

1. You want to use nest.object(), not nest.entries(), since you want a nested object, not a nested array;
2. nest.rollup() will replace the array of nested values, that's the expected behaviour. Therefore, for getting the averages the way you want, you have to specify the array that nest.rollup() will return. In the demo below I'll use the same reduce used above.

Here is the demo using d3.nest:

data = [{
    day: "mon",
    amount: "4",
    month: "jan"
  },
  {
    day: "tue",
    amount: "2",
    month: "jan"
  },
  {
    day: "wed",
    amount: "3",
    month: "jan"
  },
  {
    day: "wed",
    amount: "1",
    month: "jan"
  }
];

const nested = d3.nest()
  .key(d => d.month)
  .rollup(v => v.reduce((a, b) => {
    const found = a.find(d => d.day === b.day);
    if (!found) {
      a.push({
        day: b.day,
        avg_amount: +b.amount
      })
    } else {
      found.avg_amount = (found.avg_amount + (+b.amount)) / 2
    };
    return a;
  }, []))
  .object(data);

console.log(nested)

<script src="https://cdnjs.cloudflare.com/ajax/libs/d3/5.7.0/d3.min.js"></script>

Answer 2

This is what I came up with.

It's a bit different from the other answers since it has a complete structure pre-filled with 0 average amounts. This might prove useful as checking for months and days with no entries will not return undefined. And it also stores all the entries individually, just in case. If it somehow breaks things, you can throw it a way of course - after counting the averages, that is.

Ok, so here are the entries:

data = [
 {day: "mon", amount: "4", month: "jan"},
 {day: "tue", amount: "2", month: "jan"},
 {day: "wed", amount: "3", month: "jan"},
 {day: "wed", amount: "1", month: "jan"}
];

We then build the R object holding the averages. You could do this simply by hand, but writing out the day elements 84 times would be quite tedious. Also, this enables to change the names of months and days quite easily - ie. when you need to work with a different language.

var m, M=['jan','feb','mar','apr','may','jun','jul','aug','sep','oct','nov','dec'];
var d, D={'mon':0,'tue':1,'wed':2,'thu':3,'fri':4,'sat':5,'sun':6};
var R = {};
for(m=0;m<12;m++){
 R[M[m]] = []; // M[0] = jan,...
 for(d in D){ // d = mon,...
  R[M[m]][D[d]] = {day:d,avg_amount:0,all:[]}; // D[d] = 0,...
  // R = { jan:[ {day:'mon',avg_amount:0,all:[]},...
 }
}

And here's the rest, running through the data array, storing the amounts inside the R object and finally going over each day in the R object, calculating the averages.

var sum,n;
data.map( x => R[x.month][D[x.day]].all.push(parseInt(x.amount)) );
for(m in R) for(d=0;d<7;d++){
 sum = R[m][d].all.reduce((total,x)=>total+x,0);
 n = R[m][d].all.length;
 R[m][d].avg_amount = ((n==0)? 0 : sum/n);
 // delete R[m][d].all;
 // in case the structure can't have any extra fields
 // we can dispose of the list of all entries belonging
 // to a particular month and day
}

You can also make a function to include new data, like so:

function processData(data){
 var sum,n;
 data.map( x => R[x.month][D[x.day]].all.push(parseInt(x.amount)) );
 for(m in R) for(d=0;d<7;d++){
  sum = R[m][d].all.reduce((total,x)=>total+x,0);
  n = R[m][d].all.length;
  R[m][d].avg_amount = ((n==0)? 0 : sum/n);
 }
}

Finally, have you considered replacing

{"jan":["mon",avg_amount:"4"},{day:"tue", avg_amount:"2"}]}

with

{"jan":{"mon":5,"tue":3}}

Because unless this specific structure is being forced on you, mixing objects and arrays like this seems a bit clunky to me. It could be made quite neater. Just a suggestion though.

R.jan[0].avg_value; // avg_amount of january, monday

vs

R.jan.mon; // avg_amount of january, monday

Answer 3

You can use reduce and map

So here the basic idea is first club the data into month and day. and for each month and day we add amount together and keep one property called count to check number of days. Now using map we map out data into month and days and change amount to amount_avg.

let data = [{day: "mon", amount: "4", month: "jan"},{day: "tue", amount: "2", month: "jan"},{day: "wed", amount: "3", month: "jan"},{day: "wed", amount: "1", month: "jan"}]

let output = data.reduce((op,{day,amount,month})=>{
  if(op[month] && op[month][day]){
    op[month][day]['amount'] += parseInt(amount)||0
    op[month][day]['count']++
  } else {
    if(!op[month]) {
      op[month]={}
    }
    op[month][day] = {day,amount: parseInt(amount)||0,count:1}
  } 
  return op
},{})


let desired = Object.keys(output).map(el=>{
  return  ({[el]: Object.values(output[el]).map(({day,amount,count})=>({day,avg_amount: (amount/count).toFixed(2)}) )
})})


console.log(desired[0])

Answer 4

One option (without using d3.js) is to first use Array::reduce() to create an structure grouped by month and day and get total amount by each group. When this is done, we can map the resulting structure to match your expected output using Object.values() and Array.map():

const data = [
    {day: "mon", amount: "4", month: "jan"},
    {day: "tue", amount: "2", month: "jan"},
    {day: "wed", amount: "3", month: "jan"},
    {day: "wed", amount: "1", month: "jan"},
    {day: "mon", amount: "5", month: "feb"},
    {day: "mon", amount: "7", month: "feb"},
    {day: "tue", amount: "9", month: "feb"},
    {day: "tue", amount: "7", month: "feb"}
];

// Reduce to group by month/day and get total amount by group.

let res = data.reduce((acc, {day, amount, month}) =>
{
    acc[month] = acc[month] || {};
    acc[month][day] = acc[month][day] || {day, amount: 0, count: 0};
    acc[month][day].amount += +amount;
    acc[month][day].count++;
    return acc;
}, {});

console.log(res);

// Transfrom previous result to final structure.

for (key in res)
{
    res[key] = Object.values(res[key])
        .map(({day, amount, count}) => ({day, avg_amount: amount/count}));
}

console.log(res);