Extracting monthly values from data with irregular dates

Question

I have bunch of electricity meter readings which have irregular dates. See below :

ReadingDate Meter
19/01/2021  5270
06/03/2021  5915
11/05/2021  6792
08/07/2021  7367
9/9/2021    8095
8/11/2021   8849
02/12/2021  9065
17/01/2022  9950

Now I'd like to transform this into monthly readings, using just this data, to end up with a table like this

Month   Usage
2021-01 452
2021-02 393
2021-03 416
2021-04 399
2021-05 341
2021-06 297
2021-07 347
2021-08 358
2021-09 369
2021-10 389
2021-11 295
2021-12 586
2022-01 308

Now, I have a working solution, but I'm sure there's a more beautiful concise way of doing it.

What I do is to create an intermediate array that has one line for each date between first and last meter readings. Each item in the array has 3 values :

• the date
• the average value for that date (calculated by counting the days between meter readings and dividing that by change in the meter.
• the corresponding month

The last step then is to loop over this intermediate array and sum the values for each different month.

Here's the working code (its taken from Google Apps Script so please ignore the spreadsheet specific stuff:

var DailyAveragesArray = [['Date','Usage','Month']];
var monthlyObject = {};
var monthlyArray = [['Month','Usage']];

function calculateAverageDailyFigures() {
  // give indices for the useful columns, 0 numbered
  var ReadingDateColumn = 0;
  var MeterReading = 1;

  // Read into an array
  var MeterReadingData = ss.getDataRange().getValues()     // Get array of values
  const sortedReadings = MeterReadingData.slice(1).sort((a, b) =>  a[0] - b[0]);  
  // from https://flaviocopes.com/how-to-sort-array-by-date-javascript/
  // First calculate the number of days and average daily figure for each row
  // Note we don't do this for the last row
  for(i=0; i < sortedReadings.length - 1 ; i++){
    var NumberOfDays = (sortedReadings[i+1][0] - sortedReadings[i][0])/(1000*3600*24);
    sortedReadings[i].push(NumberOfDays);
    var MeterDifference = sortedReadings[i+1][1] - sortedReadings[i][1];
    var AverageDailyFigure = MeterDifference/NumberOfDays;
    sortedReadings[i].push(AverageDailyFigure); 
  }
  BuildDailyArray(sortedReadings);
}

function BuildDailyArray(sortedReadings){
  // For each row in sorted , loop from the date to the next date-1 and create columns date and Usage
  for(i=0; i<sortedReadings.length -1 ;i++){
    for (var d = sortedReadings[i][0]; d < sortedReadings[i+1][0]; d.setDate(d.getDate() + 1)) {
      var newDate = new Date(d);
      var month = newDate.getFullYear() + '-' + ('0' + (newDate.getMonth() + 1)).slice(-2);
      DailyAveragesArray.push([newDate,sortedReadings[i][3],month]);
      // Check if the month is in the object and add value, otherwise create object an add value
      if(month in monthlyObject){
        monthlyObject[month] = monthlyObject[month] + sortedReadings[i][3];
      } else { 
        Logger.log('Didnt find month so create it');
        monthlyObject[month] = sortedReadings[i][3];
      }
    }
  }
  Logger.log(DailyAveragesArray.length);
  Logger.log(monthlyObject);
  var DailyUsageData = ss.getRange('D1:F'+DailyAveragesArray.length);
  DailyUsageData.setValues(DailyAveragesArray);
  BuildMonthlyArray();
}

function BuildMonthlyArray(){
  const keys = Object.keys(monthlyObject);
  Logger.log(keys);
  keys.forEach((key, index) => {
    monthlyArray.push([key,Math.round(monthlyObject[key])]);
  });
  var MonthlyUsageData = ss.getRange('H1:I'+monthlyArray.length);
  MonthlyUsageData.setValues(monthlyArray);
}

So, my question is, how would I do this nicer, more beautifully, not so verbose ? I'm not sure what the correct term is for what I want to do. I don't think it's resampling .

I'd appreciate any comments.

Thanks / Colm

Answer 1

Here is my shot on this.

The way i'm doing it:

• Initializing all days and its value
• Grouping by month
• Calculating the average per month

---

Explanation a bit more precise

initDateFromString

The method initDateFromString takes a dates with the format DD/MM/YYYY and return the associated js date object

initAllDates

The method initAllDates will split the data into day and add the average value of the difference for each day

for example, for the first two readings, it will result to an array of dates looking like :

date	value
19/01/2021	14.02
20/01/2021	14.02
....	....
05/03/2021	14.02
06/03/2021	14.02

The value 14.02 comme from the following calcul :

(newReadingMeter - oldReadingMeter)/nbDaysBetweenDates

Which in this example is (5915 - 5270)/46 = 14.02

joinToMonth

The joinToMonth method will then group the days into month with all the days value summed !

const data = [{
  ReadingDate: '19/01/2021',
  Meter: 5270
},
  {
    ReadingDate: '06/03/2021',
    Meter: 5915
  },
  {
    ReadingDate: '11/05/2021',
    Meter: 6792
  },
  {
    ReadingDate: '08/07/2021',
    Meter: 7367
  },
  {
    ReadingDate: '9/9/2021',
    Meter: 8095
  },
  {
    ReadingDate: '8/11/2021',
    Meter: 8849
  },
  {
    ReadingDate: '02/12/2021',
    Meter: 9065
  },
  {
    ReadingDate: '17/01/2022',
    Meter: 9950
  }
]

function initDateFromString(dateString){
  let dateParts = dateString.split("/");
  return new Date(+dateParts[2], dateParts[1] - 1, +dateParts[0]);
}

function initAllDates(data){
  let dates = []
  let currentValue = data.shift()
  const currentDate = initDateFromString(currentValue.ReadingDate)
  data.forEach(metric => {
    const date = initDateFromString(metric.ReadingDate)
    const newDates = []
    while(currentDate < date){
      newDates.push({date: new Date(currentDate)})
      currentDate.setDate(currentDate.getDate() + 1)
    }

    dates = dates.concat(newDates.map(x => {
      return {Usage: (metric.Meter - currentValue.Meter) / newDates.length, date: x.date}}
    ))
    currentDate.setDate(date.getDate())
    currentValue = metric
  })
  return dates
}

function joinToMonth(dates){
  return dates.reduce((months, day) => {
    const month = day.date.getMonth()
    const year = day.date.getFullYear()
    const existingObject = months.find(x => x.month === month && x.year === year)
    if (existingObject) {
      existingObject.total += day.Usage
    } else {
      months.push({
        month: day.date.getMonth(),
        year: day.date.getFullYear(),
        total: day.Usage,
      })
    }

    return months;
  }, []);
}

const dates = initAllDates(data)
const joinedData = joinToMonth(dates)

console.log(joinedData)