Calculate average of duplicates in a javascript array of objects

Question

I have an array of objects:

[
{"market": "Qacha's nek","commodity": 55,"price": "90","month": "04","year": "2017"}, 
{"market": "Mohales Hoek","commodity": 55,"price": "75","month": "04","year": "2017"}, 
{"market": "Mafeteng","commodity": 55,"price": "75","month": "04","year": "2017"}, 
{"market": "Maseru","commodity": 55,"price": "69","month": "04","year": "2017"}, 
{"market": "Butha-Buthe","commodity": 55,"price": "66","month": "04","year": "2017"}, 
{"market": "Leribe","commodity": 55,"price": "64","month": "04","year": "2017"}, 
{"market": "Butha-Buthe","commodity": 55,"price": "65","month": "04","year": "2017"}, 
{"market": "Thaba-Tseka","commodity": 55,"price": "82","month": "04","year": "2017"},
{"market": "Thaba-Tseka","commodity": 55,"price": "81","month": "04","year": "2017"},
{"market": "Maseru",    "commodity": 55,"price": "74,99","month": "04","year": "2017"}
]

I'm trying to aggregate duplicates by price average.

So the keys to identifying duplicated rows are all the properties except price, that must be aggregated by average.

In the data above, for example, line 5 and 7:

5) "market": "Butha-Buthe","commodity": 55,"price": "66","month": "04","year": "2017"
7) "market": "Butha-Buthe","commodity": 55,"price": "65","month": "04","year": "2017"

are duplicates and I want to merge them and make the average of their price value.

I was trying to use the reduce function, but I can't figure out how to identify duplicated values, especially if they are not sorted.

I post the code, but it's useless as I can't understand how to identify duplicates with reduce:

var data = [
{"market": "Qacha's nek","commodity": 55,"price": "90","month": "04","year": "2017"}, 
{"market": "Mohales Hoek","commodity": 55,"price": "75","month": "04","year": "2017"}, 
{"market": "Mafeteng","commodity": 55,"price": "75","month": "04","year": "2017"}, 
{"market": "Maseru","commodity": 55,"price": "69","month": "04","year": "2017"}, 
{"market": "Butha-Buthe","commodity": 55,"price": "66","month": "04","year": "2017"}, 
{"market": "Leribe","commodity": 55,"price": "64","month": "04","year": "2017"}, 
{"market": "Butha-Buthe","commodity": 55,"price": "65","month": "04","year": "2017"}, 
{"market": "Thaba-Tseka","commodity": 55,"price": "82","month": "04","year": "2017"},
{"market": "Thaba-Tseka","commodity": 55,"price": "81","month": "04","year": "2017"},
{"market": "Maseru","commodity": 55,"price": "74,99","month": "04","year": "2017"}
];

var avg = data.reduce(function(result, current) {
			console.log(result,current);
      if(!result){
      	result=current;
      }
      else {
      	if(result.market==current.market){
        	console.log(current.market);
        }
      }
});

Here a jsfiddle where I was trying to understand how the reduce function works: https://jsfiddle.net/brainsengineering/7tmdx0kg/7/

Answer 1

Group prices together for each item by adding them to an array in your reduce call. You can keep track of which items are duplicated in the same function. Then use loop over the duplicate items to compute the averages.

Note I had to change your price 74,99 to 74.99 to parse more easily. You'll probably want some sort of localization/globalization library if this is critical in your use case.

var data = [
{"market": "Qacha's nek","commodity": 55,"price": "90","month": "04","year": "2017"}, 
{"market": "Mohales Hoek","commodity": 55,"price": "75","month": "04","year": "2017"}, 
{"market": "Mafeteng","commodity": 55,"price": "75","month": "04","year": "2017"}, 
{"market": "Maseru","commodity": 55,"price": "69","month": "04","year": "2017"}, 
{"market": "Butha-Buthe","commodity": 55,"price": "66","month": "04","year": "2017"}, 
{"market": "Leribe","commodity": 55,"price": "64","month": "04","year": "2017"}, 
{"market": "Butha-Buthe","commodity": 55,"price": "65","month": "04","year": "2017"}, 
{"market": "Thaba-Tseka","commodity": 55,"price": "82","month": "04","year": "2017"},
{"market": "Thaba-Tseka","commodity": 55,"price": "81","month": "04","year": "2017"},
{"market": "Maseru","commodity": 55,"price": "74.99","month": "04","year": "2017"}
];

function parsePrice(str) {
  // TODO: localization
  return +str;
}

function formatPrice(num) {
  return num.toFixed(2);
}

function getHashKey(item) {
  return JSON.stringify([item.market, item.commodity, item.month, item.year]);
}

var duplicatedItems = {};
var prices = data.reduce(function(result, current) {
  var key = getHashKey(current);
  if (key in result) {
    result[key].push(parsePrice(current.price));
    duplicatedItems[key] = current;
  } else {
    result[key] = [parsePrice(current.price)];
  }
  return result;
}, {});
var avg = Object.keys(duplicatedItems).map(function(key) {
  var item = duplicatedItems[key];
  var avgPrice = prices[key].reduce(function(acc, price) { return acc + price; }, 0) / prices[key].length;
  return {
    market: item.market,
    commodity: item.commodity,
    price: formatPrice(avgPrice),
    month: item.month,
    year: item.year
  };
});

console.log(avg);

Answer 2

You could take a combined key for the wanted properties and replace the price format to a numerical parsable format.

var data = [{ market: "Qacha's nek", commodity: 55, price: "90", month: "04", year: "2017" }, { market: "Mohales Hoek", commodity: 55, price: "75", month: "04", year: "2017" }, { market: "Mafeteng", commodity: 55, price: "75", month: "04", year: "2017" }, { market: "Maseru", commodity: 55, price: "69", month: "04", year: "2017" }, { market: "Butha-Buthe", commodity: 55, price: "66", month: "04", year: "2017" }, { market: "Leribe", commodity: 55, price: "64", month: "04", year: "2017" }, { market: "Butha-Buthe", commodity: 55, price: "65", month: "04", year: "2017" }, { market: "Thaba-Tseka", commodity: 55, price: "82", month: "04", year: "2017" }, { market: "Thaba-Tseka", commodity: 55, price: "81", month: "04", year: "2017" }, { market: "Maseru", commodity: 55, price: "74,99", month: "04", year: "2017" }],
    keys = ['market', 'commodity', 'month', 'year'],
    count = {},
    result = data.reduce(function (r, o) {
        var key = keys.map(function (k) { return o[k]; }).join('|');
        if (!count[key]) {
            count[key] = { sum: +o.price.replace(',', '.'), data: JSON.parse(JSON.stringify(o)) };
            count[key].data.count = 1;
            r.push(count[key].data);
        } else {
            count[key].sum += +o.price.replace(',', '.');
            count[key].data.price = (count[key].sum / ++count[key].data.count).toString();
        }
        return r;
    }, []);

console.log(result);

.as-console-wrapper { max-height: 100% !important; top: 0; }

Answer 3

You could insert the values into a new array and merge if it already exists:

 const result = [];

 outer: for(const {  market, commodity,  price, month, year } of input) {
    for(const other of result) {
       if(market === other.market && commodity === other.commodity && month === other.month && year === other.year) {
         other.prices.push(+price);

         continue outer;
       }
    }
    result.push({ market, commodity, prices: [+price], month, year });
 }

 for(const group of result)
   group.price = group.prices.reduce((a, b) => a + b, 0) / group.prices.length;