How to aggregate daily JSON data by week using Node?

Question

I have a data structure like so:

[
    {"Day": "12-06-2021", "ROAS": 17.3, "ConversionRate": 0.1472, "VisitRate": 0.0488},
    {"Day": "12-07-2021", "ROAS": 14.79, "ConversionRate": 0.1251, "VisitRate": 0.0555},
    {"Day": "12-08-2021", "ROAS": 17.12, "ConversionRate": 0.1358, "VisitRate": 0.052},
    {"Day": "12-09-2021", "ROAS": 14.95, "ConversionRate": 0.1391, "VisitRate": 0.0537},
    {"Day": "12-10-2021", "ROAS": 19.85, "ConversionRate": 0.1394, "VisitRate": 0.0853},
    {"Day": "12-11-2021", "ROAS": 25.03, "ConversionRate": 0.1565, "VisitRate": 0.1033},
    {"Day": "12-12-2021", "ROAS": 24.58, "ConversionRate": 0.1528, "VisitRate": 0.1172},
    {"Day": "12-13-2021", "ROAS": 14.2, "ConversionRate": 0.1517, "VisitRate": 0.0481},
    {"Day": "12-14-2021", "ROAS": 14.32, "ConversionRate": 0.1564, "VisitRate": 0.0488},
    {"Day": "12-15-2021", "ROAS": 12.54, "ConversionRate": 0.1436, "VisitRate": 0.0473},
    {"Day": "12-16-2021", "ROAS": 14.47, "ConversionRate": 0.1574, "VisitRate": 0.0442},
    {"Day": "12-17-2021", "ROAS": 18.2, "ConversionRate": 0.1283, "VisitRate": 0.0729},
    {"Day": "12-18-2021", "ROAS": 72.93, "ConversionRate": 0.1366, "VisitRate": 0.2267},
    {"Day": "12-19-2021", "ROAS": 72.29, "ConversionRate": 0.1638, "VisitRate": 0.1715}
]

What I'm trying to do seems impossible for me as I struggle with date math a lot, so I'm hoping one of you geniuses can help me out.

What I am trying to do is aggregate this daily data into an object that gives me week-over-week averages for each of the three attributes. What I was coming up with was a mess of nested foreaches that I couldn't keep straight in my head.

I'm more than happy to use any npm modules as well. Anything to make this easier

Answer 1

So here is a stolen code from a previous answer to obtain week number from a date.

function getWeek(date) {
  if (!(date instanceof Date)) date = new Date();

  // ISO week date weeks start on Monday, so correct the day number
  var nDay = (date.getDay() + 6) % 7;

  // ISO 8601 states that week 1 is the week with the first Thursday of that year
  // Set the target date to the Thursday in the target week
  date.setDate(date.getDate() - nDay + 3);

  // Store the millisecond value of the target date
  var n1stThursday = date.valueOf();

  // Set the target to the first Thursday of the year
  // First, set the target to January 1st
  date.setMonth(0, 1);

  // Not a Thursday? Correct the date to the next Thursday
  if (date.getDay() !== 4) {
    date.setMonth(0, 1 + ((4 - date.getDay()) + 7) % 7);
  }

  // The week number is the number of weeks between the first Thursday of the year
  // and the Thursday in the target week (604800000 = 7 * 24 * 3600 * 1000)
  return 1 + Math.ceil((n1stThursday - date) / 604800000);
}

Whic gives you the week number of a date. Now all you need is to group your array into weeks accoding to the week number.

var data = [
    {"Day": "12-06-2021", "ROAS": 17.3, "ConversionRate": 0.1472, "VisitRate": 0.0488},
    {"Day": "12-07-2021", "ROAS": 14.79, "ConversionRate": 0.1251, "VisitRate": 0.0555},
    {"Day": "12-08-2021", "ROAS": 17.12, "ConversionRate": 0.1358, "VisitRate": 0.052},
    {"Day": "12-09-2021", "ROAS": 14.95, "ConversionRate": 0.1391, "VisitRate": 0.0537},
    {"Day": "12-10-2021", "ROAS": 19.85, "ConversionRate": 0.1394, "VisitRate": 0.0853},
    {"Day": "12-11-2021", "ROAS": 25.03, "ConversionRate": 0.1565, "VisitRate": 0.1033},
    {"Day": "12-12-2021", "ROAS": 24.58, "ConversionRate": 0.1528, "VisitRate": 0.1172},
    {"Day": "12-13-2021", "ROAS": 14.2, "ConversionRate": 0.1517, "VisitRate": 0.0481},
    {"Day": "12-14-2021", "ROAS": 14.32, "ConversionRate": 0.1564, "VisitRate": 0.0488},
    {"Day": "12-15-2021", "ROAS": 12.54, "ConversionRate": 0.1436, "VisitRate": 0.0473},
    {"Day": "12-16-2021", "ROAS": 14.47, "ConversionRate": 0.1574, "VisitRate": 0.0442},
    {"Day": "12-17-2021", "ROAS": 18.2, "ConversionRate": 0.1283, "VisitRate": 0.0729},
    {"Day": "12-18-2021", "ROAS": 72.93, "ConversionRate": 0.1366, "VisitRate": 0.2267},
    {"Day": "12-19-2021", "ROAS": 72.29, "ConversionRate": 0.1638, "VisitRate": 0.1715}
];

function getWeek(date) {
  !(date instanceof Date) && (date = new Date());
  var nDay = (date.getDay() + 6) % 7;
  date.setDate(date.getDate() - nDay + 3);
  var n1stThursday = date.valueOf();
  date.setMonth(0, 1);
  (date.getDay() !== 4) && date.setMonth(0, 1 + ((4 - date.getDay()) + 7) % 7);
  return 1 + Math.ceil((n1stThursday - date) / 604800000);
}


var groups = data.reduce( function(r,d){
                            var week = getWeek(new Date(d.Day));
                            r[week] = r[week] ? r[week].concat(d)
                                              : [d]
                            return r;
                          }, {}),
    result = Object.keys(groups)
                   .map(week => groups[week] = groups[week].reduce((a,o) => ( a.ROAS           += o.ROAS / groups[week].length
                                                                            , a.ConversionRate += o.ConversionRate / groups[week].length
                                                                            , a.VisitRate      += o.VisitRate / groups[week].length
                                                                            , a
                                                                            ) ,{"Week":week, "ROAS":0, "ConversionRate": 0, "VisitRate":0}));
console.log(result);

Answer 2

Here's my attempt with dayjs (can be easily changed to moment) https://jsfiddle.net/wajsc548/7/

So what I do here is loop the data, convert the date to week and use it as a key to store the total of the values + a counter to determine how many values has been added

Then I loop it again to get the average.

let data =[
    {"Day": "12-06-2021", "ROAS": 17.3, "ConversionRate": 0.1472, "VisitRate": 0.0488},
    {"Day": "12-07-2021", "ROAS": 14.79, "ConversionRate": 0.1251, "VisitRate": 0.0555},
    {"Day": "12-08-2021", "ROAS": 17.12, "ConversionRate": 0.1358, "VisitRate": 0.052},
    {"Day": "12-09-2021", "ROAS": 14.95, "ConversionRate": 0.1391, "VisitRate": 0.0537},
    {"Day": "12-10-2021", "ROAS": 19.85, "ConversionRate": 0.1394, "VisitRate": 0.0853},
    {"Day": "12-11-2021", "ROAS": 25.03, "ConversionRate": 0.1565, "VisitRate": 0.1033},
    {"Day": "12-12-2021", "ROAS": 24.58, "ConversionRate": 0.1528, "VisitRate": 0.1172},
    {"Day": "12-13-2021", "ROAS": 14.2, "ConversionRate": 0.1517, "VisitRate": 0.0481},
    {"Day": "12-14-2021", "ROAS": 14.32, "ConversionRate": 0.1564, "VisitRate": 0.0488},
    {"Day": "12-15-2021", "ROAS": 12.54, "ConversionRate": 0.1436, "VisitRate": 0.0473},
    {"Day": "12-16-2021", "ROAS": 14.47, "ConversionRate": 0.1574, "VisitRate": 0.0442},
    {"Day": "12-17-2021", "ROAS": 18.2, "ConversionRate": 0.1283, "VisitRate": 0.0729},
    {"Day": "12-18-2021", "ROAS": 72.93, "ConversionRate": 0.1366, "VisitRate": 0.2267},
    {"Day": "12-19-2021", "ROAS": 72.29, "ConversionRate": 0.1638, "VisitRate": 0.1715}
];

let average = {};


data.forEach(day => {
  let week = dayjs(day.Day, "MM-DD-YYYY").isoWeek();
  let averageWeek = average[[week]];
  if (averageWeek) {
    average = {...average, [week]: {"ROAS": day.ROAS + averageWeek.ROAS, "ConversionRate": day.ConversionRate + averageWeek.ConversionRate, "VisitRate": day.VisitRate + averageWeek.VisitRate, count: averageWeek.count + 1}};
  }
  else {
    average = {...average, [week]: {"ROAS": day.ROAS, "ConversionRate": day.ConversionRate, "VisitRate": day.VisitRate, count: 1}};
  }
  
});

let newData = [];
Object.entries(average).forEach(
    ([key, value]) => {
        newData.push({"Week": key, "ROAS": value.ROAS/value.count, "ConversionRate": value.ConversionRate/value.count, "VisitRate": value.VisitRate/value.count})
    }
);

console.log(newData);

Answer 3

You can make use of reduce to group each data item into weeks, and map through the data again to calculate averages.

Here's my attempt, hopefully it's something similar to what you had expected:

function getWeek(date) {
  var onejan = new Date(date.getFullYear(), 0, 1);
  var millisecsInDay = 86400000;
  return Math.ceil(
    ((date - onejan) / millisecsInDay + onejan.getDay() + 1) / 7
  );
}

const data = [
  { Day: "12-06-2021", ROAS: 17.3, ConversionRate: 0.1472, VisitRate: 0.0488 },
  { Day: "12-07-2021", ROAS: 14.79, ConversionRate: 0.1251, VisitRate: 0.0555 },
  { Day: "12-08-2021", ROAS: 17.12, ConversionRate: 0.1358, VisitRate: 0.052 },
  { Day: "12-09-2021", ROAS: 14.95, ConversionRate: 0.1391, VisitRate: 0.0537 },
  { Day: "12-10-2021", ROAS: 19.85, ConversionRate: 0.1394, VisitRate: 0.0853 },
  { Day: "12-11-2021", ROAS: 25.03, ConversionRate: 0.1565, VisitRate: 0.1033 },
  { Day: "12-12-2021", ROAS: 24.58, ConversionRate: 0.1528, VisitRate: 0.1172 },
  { Day: "12-13-2021", ROAS: 14.2, ConversionRate: 0.1517, VisitRate: 0.0481 },
  { Day: "12-14-2021", ROAS: 14.32, ConversionRate: 0.1564, VisitRate: 0.0488 },
  { Day: "12-15-2021", ROAS: 12.54, ConversionRate: 0.1436, VisitRate: 0.0473 },
  { Day: "12-16-2021", ROAS: 14.47, ConversionRate: 0.1574, VisitRate: 0.0442 },
  { Day: "12-17-2021", ROAS: 18.2, ConversionRate: 0.1283, VisitRate: 0.0729 },
  { Day: "12-18-2021", ROAS: 72.93, ConversionRate: 0.1366, VisitRate: 0.2267 },
  { Day: "12-19-2021", ROAS: 72.29, ConversionRate: 0.1638, VisitRate: 0.1715 },
];

const aggregatedData = data
  .reduce((acc, val) => {
    const date = new Date(val.Day);
    const weekNum = getWeek(date);
    const weekYear = `${weekNum}-${date.getFullYear()}`;
    const lastWeekYear = acc[acc.length - 1]?.weekYear;
    if (lastWeekYear === weekYear) {
      acc[acc.length - 1].data.push(val);
      return acc;
    } else {
      return [...acc, { weekYear, data: [val] }];
    }
  }, [])
  .map(({ weekYear, data }) => {
    const [roasCount, roasSum] = data.reduce(
      (acc, val) => [acc[0] + 1, acc[0] + val.ROAS],
      [0, 0]
    );
    const roasAverage = roasSum / roasCount;
    const [conversionRateCount, conversionRateSum] = data.reduce(
      (acc, val) => [acc[0] + 1, acc[0] + val.ConversionRate],
      [0, 0]
    );
    const conversionRateAverage = conversionRateSum / conversionRateCount;
    const [visitRateCount, visitRateSum] = data.reduce(
      (acc, val) => [acc[0] + 1, acc[0] + val.VisitRate],
      [0, 0]
    );
    const visitRateAverage = visitRateSum / visitRateCount;
    return {
      weekYear,
      averages: { roasAverage, conversionRateAverage, visitRateAverage },
    };
  });

console.log(aggregatedData);

If you are already using a library such as lodash, this could definitely be cleaned up to be a smaller amount of code.