MongoDB map/reduce process for calculating daily or weekly averages from Nightscout

NightscoutMapReduce.js

// if you need to clean data from duplicates, this checks for records with the same type and exact same entry time, and deletes the dupes
// exporting and importing your data into a new collection before you run this on your production data highly recommended!

db.eero_collection_temp.aggregate([
    { "$group": {
        "_id": { "date": "$date", "type": "$type" },
        "dups": { "$push": "$_id" },
        "count": { "$sum": 1 }
    }},
    { "$match": { "count": { "$gt": 1 } }}
]).forEach(function(doc) {
    doc.dups.shift();
    db.eero_collection_temp.remove({ "_id": {"$in": doc.dups }});
});

// add function to generate a YYYY-MM key

db.system.js.save(
   {
     _id: "getYearWeek",
     value : function(date) {
      
    var target  = new Date(date);
    var dayNr   = (date.getDay() + 6) % 7;
    target.setDate(target.getDate() - dayNr + 3);
    var firstThursday = target.valueOf();
    target.setMonth(0, 1);  
    if (target.getDay() != 4) {
        target.setMonth(0, 1 + ((4 - target.getDay()) + 7) % 7);  
    }  
    var week = 1 + Math.ceil((firstThursday - target) / 604800000);

    target  = new Date(date);  
    target.setDate(target.getDate() - ((date.getDay() + 6) % 7) + 3);      
    var year = target.getFullYear();
    
    var zeropad = (week < 10) ? "0" : "";

    return "" + year + "-" + zeropad + week;
      
      }
   }
)

// map the readings
// change the emitter to use the dateKey if you want daily stats


function map() {
	if (isNaN(this.sgv)) return;
	var sgv = Number(this.sgv);
	var low = (sgv < 3.8*18) ? 1 : 0;
	var high = (sgv >= 10*18) ? 1 : 0;
	var date = new Date(this.date);

	var dateKey = new Date(this.date).toISOString().slice(0,10).replace(/-/g,"");
	var yearweekKey = getYearWeek(date);

	emit( yearweekKey,
         {sum: sgv, // the field you want stats for
          min: sgv,
          max: sgv,
          count:1,
          lowcount: low,
          highcount: high,
          diff: 0, // M2,n:  sum((val-mean)^2)
    });
    
    var dayTimeKey = (date.getHours() >= 21 || date.getHours() <= 7) ? "night" : "day";
    
    emit( yearweekKey + "-" + dayTimeKey,
         {sum: sgv, // the field you want stats for
          min: sgv,
          max: sgv,
          count:1,
          lowcount: low,
          highcount: high,
          diff: 0, // M2,n:  sum((val-mean)^2)
    });
}

// reduce to a single object
// note the result object is passed to the reducer multiple times, so only add up data

function reduce(key, values) {
    var a = values[0]; // will reduce into here
    for (var i=1/*!*/; i < values.length; i++){
        var b = values[i]; // will merge 'b' into 'a'

        // temp helpers
        var delta = a.sum/a.count - b.sum/b.count; // a.mean - b.mean
        var weight = (a.count * b.count)/(a.count + b.count);
        
        // do the reducing
        a.diff += b.diff + delta*delta*weight;
        a.sum += b.sum;
        a.count += b.count;
        a.lowcount += b.lowcount;
        a.highcount += b.highcount;
        a.min = Math.min(a.min, b.min);
        a.max = Math.max(a.max, b.max);
    }

    return a;
}

// finally calculate the averages of the reduced objects
// set mmolDivisor to 1 for mg/dl values

function finalizer(key, value){

	var mmolDivisor = 18;

    value.avg = value.sum / value.count / mmolDivisor;
    value.variance = value.diff / value.count / mmolDivisor;
    value.stddev = Math.sqrt(value.variance) / mmolDivisor;
    value.high = value.highcount / value.count;
    value.low = value.lowcount / value.count;
    value.a1c = (((value.sum / value.count)+46.7)/28.7).toFixed(1);
    value.min = value.min / mmolDivisor;
    value.max = value.max / mmolDivisor;
    
    return value;
}

// drop aggregate if it exists

db.sgv_aggregate.drop()

// run the map/reduce process

db.eero_collection_temp.mapReduce( map,
                     reduce,
                     {
                       out: { merge: "sgv_aggregate" },
                       finalize: finalizer
                     }
                   )

// view results in the database

db.sgv_aggregate.find()

// export to CSV for Excel

mongoexport -d eero-nightscout -c sgv_aggregate --type=csv --out report.csv --fields _id,value.min,value.max,value.count,value.lowcount,value.highcount,value.avg,value.high,value.low,value.a1c

treatment_stats.js

db.sgv_aggregate.drop()

-----

INSULIN ONLY CORRECTIONS

function map_treatments() {
	if (isNaN(this.insulin)) return;	
	if (!isNaN(this.carbs) && Number(this.carbs) > 0) return;

	var date = new Date(this.created_at);
	var insulin = Number(this.insulin);
	
	if (insulin == 0) return;
	
	var dateKey = new Date(this.created_at).toISOString().slice(0,10).replace(/-/g,"");
	var yearweekKey = getYearWeek(date);

	emit( yearweekKey,
         {sum: insulin, // the field you want stats for
          count:1,
          avg: 0,
          treatmentsPerDay: 0
    });
    
    var dayTimeKey = (date.getHours() >= 21 || date.getHours() <= 7) ? "night" : "day";
    
    emit( yearweekKey + "/" + dayTimeKey,
         {sum: insulin, // the field you want stats for
          count:1,
          avg: 0,
          treatmentsPerDay: 0
    });
}

-------

CARB ONLY CORRECTIONS

function map_treatments() {
	if (!isNaN(this.insulin) && Number(this.insulin) > 0) return;
	if (isNaN(this.carbs)) return;

	var date = new Date(this.created_at);
	var carbs = Number(this.carbs);
	
	var dateKey = new Date(this.created_at).toISOString().slice(0,10).replace(/-/g,"");
	var yearweekKey = getYearWeek(date);

	emit( yearweekKey,
         {sum: carbs, // the field you want stats for
          count:1,
          avg: 0,
          treatmentsPerDay: 0
    });
    
    var dayTimeKey = (date.getHours() >= 21 || date.getHours() <= 7) ? "night" : "day";
    
    emit( yearweekKey + "/" + dayTimeKey,
         {sum: carbs, // the field you want stats for
          count:1,
          avg: 0,
          treatmentsPerDay: 0
    });
}


function reduce_treatments(key, values) {
    var a = values[0]; // will reduce into here
    for (var i=1/*!*/; i < values.length; i++){
        var b = values[i]; // will merge 'b' into 'a'

        // temp helpers
        var delta = a.sum/a.count - b.sum/b.count; // a.mean - b.mean
        var weight = (a.count * b.count)/(a.count + b.count);
        
        // do the reducing
        a.sum += b.sum;
        a.count += b.count;
    }

    return a;
}

function finalize_treatments(key, value){

	value.avg = value.sum / value.count;
	value.treatmentsPerDay = value.count / 7.0;
	    
    return value;
}

db.treatments.mapReduce( map_treatments,
                     reduce_treatments,
                     {
                       out: { merge: "sgv_aggregate" },
                       finalize: finalize_treatments
                     }
                   )

mongoexport -d eero-nightscout -c sgv_aggregate --type=csv --out report.csv --fields _id,value.sum,value.avg,value.treatmentsPerDay