Yawning Milk Yawning Milk - 6 months ago 64
MySQL Question

Jquery or PHP group array of objects by date field

So I have json response containing data formatted for creating a chart (using Canvas.js), it's an array of objects, each of which contains y value (which is int) and label (which is SQL date, like 2016-02-06).

What I need is to group these values by dates and get average from them. Data are sorted by date. The problem is, there can be random number of points for one day, and the other day may not have any points at all. So, I need kind of week index (calculating the week of the year is not a solution, because there can be multiple years). For example, I could have the following array:

[0] {y: 2; label: 2016-04-01} // this is week 1 from the start of the array
[2] {y: 6; label: 2016-04-02} // this is still week 1
[3] {y: 1; label: 2016-04-13} // this is week 2
[4] {y: 10; label: 2016-04-28} // this is week 3, if we count weeks only by appearance in the array, not by their actual position
[5] {y: 2; label: 2016-05-01} // this is week 4
[6] {y: 4; label: 2016-05-02} // still week 4


So I need to get these weeks somehow, and then find the average y value, so from the array above I would get:

[0] {y: 4; label: 2016-04-01}
[2] {y: 1; label: 2016-04-13}
[3] {y: 10; label: 2016-04-28}
[4] {y: 3; label: 2016-05-01}


How could this be handled? I suppose I should use PHP methods and give up trying make it on front-end, or may be there are more simple ways to handle this? Or maybe there are js chart plugins that allow automatic grouping? I would highly appreciate any possible help!

Answer

In Javascript, you could use an object for collecting and an array for the result.

This proposal utilised the answer of RobG to Get week of year in JavaScript like in PHP.

The later code is a grouping part, which takes the year and the week number and collect the data for the average calculation.

// taken from http://stackoverflow.com/a/6117889/1447675
Date.prototype.getWeekNumber = function () {
    var d = new Date(+this);
    d.setHours(0, 0, 0);
    d.setDate(d.getDate() + 4 - (d.getDay() || 7));
    return Math.ceil((((d - new Date(d.getFullYear(), 0, 1)) / 8.64e7) + 1) / 7);
};

var data = [{ y: 2, label: '2016-04-01' }, { y: 6, label: '2016-04-02' }, { y: 1, label: '2016-04-13' }, { y: 10, label: '2016-04-28' }, { y: 2, label: '2016-05-01' }, { y: 4, label: '2016-05-02' }],
    avg = [];

data.forEach(function (a) {
    var year = a.label.slice(0, 4),
        week = new Date(a.label).getWeekNumber(),
        key = year + '|' + week;

    if (!this[key]) {
        this[key] = { count: 0, sum: 0, result: { y: 0, label: a.label } };
        avg.push(this[key].result);
    }
    this[key].count++;
    this[key].sum += a.y;
    this[key].result.y = this[key].sum / this[key].count;
}, Object.create(null));

console.log(avg);