What I've got is an array of objects with categories, dates and values, like so
const records =
[ { category: 'Cat 1', date: '2020-11-03', value: '300.00' }
, { category: 'Cat 2', date: '2020-11-03', value: '350.00' }
, { category: 'Cat 3', date: '2020-11-03', value: '50.00' }
, { category: 'Cat 1', date: '2020-11-13', value: '200.00' }
, { category: 'Cat 1', date: '2020-12-23', value: '100.00' }
, { category: 'Cat 2', date: '2020-11-23', value: '350.00' }
, { category: 'Cat 3', date: '2020-11-15', value: '50.00' }
, { category: 'Cat 3', date: '2021-01-15', value: '50.00' }
]
What I want to get is the new array with each category, month and monthly total value, like this:
const newRecords =
[ { category: 'Cat 1'
, totalPerMonth:
[ { month: '2020-11', totalMonthlyValue: '500.00' }
, { month: '2020-12', totalMonthlyValue: '100.00' }
] }
, { category: 'Cat 2'
, totalPerMonth:
[ { month: '2020-11', totalMonthlyValue: '700.00' }
] }
, { category: 'Cat 3'
, totalPerMonth:
[ { month: '2020-11', totalMonthlyValue: '100.00' }
, { month: '2021-01', totalMonthlyValue: '50.00' }
] } ]
You can do this by grouping the data first by category and then by date. Then, you need to sum all values up and return the structure like defined. Here is an example which does exactly that.
const records = [
{category: 'Cat 1', date: '2020-11-03', value: '300.00'},
{category: 'Cat 2', date: '2020-11-03', value: '350.00'},
{category: 'Cat 3', date: '2020-11-03', value: '50.00'},
{category: 'Cat 1', date: '2020-11-13', value: '200.00'},
{category: 'Cat 1', date: '2020-12-23', value: '100.00'},
{category: 'Cat 2', date: '2020-11-23', value: '350.00'},
{category: 'Cat 3', date: '2020-11-15', value: '50.00'},
{category: 'Cat 3', date: '2021-01-15', value: '50.00'}
];
// first you need to group it by category, you can do that by
// assigning a group indicator as key to an object and add an
// array as value containing all values. you can also use lodash's
// groupBy for grouping values.
const groupedRecords = {};
records.forEach(record => {
if (groupedRecords[record.category] === undefined) {
groupedRecords[record.category] = [];
}
groupedRecords[record.category].push(record);
});
// then, you need to go through each category group and group all
// records again by date. you can use the same pattern as above.
const result = Object.entries(groupedRecords).map(([category, recordsOfOneCategory]) => {
const groupedDates = {};
recordsOfOneCategory.forEach(record => {
// this regex extracts the date per month. it's probably better
// to use a date library for this part
const month = record.date.match(/^(\d{4}-\d{2})/)[1];
if (groupedDates[month] === undefined) {
groupedDates[month] = [];
}
groupedDates[month].push(record);
});
// now you have for each category group a group of dates with
// records in it. you now need to make a sum for each group of
// dates. you can do this by mapping the values into an array of
// only values and then sum them up using reduce.
const totalPerMonth = Object.entries(groupedDates).map(([month, items]) => ({
month,
totalMonthlyValue: items.map(item => parseFloat(item.value)).reduce((a,b) => a + b).toFixed(2)
}));
return { category, totalPerMonth };
});
console.log(result);this way
const records =
[ { category: 'Cat 1', date: '2020-11-03', value: '300.00' }
, { category: 'Cat 2', date: '2020-11-03', value: '350.00' }
, { category: 'Cat 3', date: '2020-11-03', value: '50.00' }
, { category: 'Cat 1', date: '2020-11-13', value: '200.00' }
, { category: 'Cat 1', date: '2020-12-23', value: '100.00' }
, { category: 'Cat 2', date: '2020-11-23', value: '350.00' }
, { category: 'Cat 3', date: '2020-11-15', value: '50.00' }
, { category: 'Cat 3', date: '2021-01-15', value: '50.00' }
]
const result =
Object.values(
records.reduce((r,{category,date,value}) =>
{
let Ym = date.substring(0,7)
if (!r[category]) r[category] = { category, sum : {} }
if (!r[category].sum[Ym]) r[category].sum[Ym] = 0
r[category].sum[Ym] += Number(value)
return r
},{}
)).map(({category,sum}) =>
{
let totalPerMonth = Object.entries(sum).map(([month,sum])=>({month,totalMonthlyValue:sum.toFixed(2)}))
return { category,totalPerMonth }
},{})
console.log ( result ).as-console-wrapper {max-height: 100%!important;top:0 }You can group based on the category and month using array#reduce in an object. Then extract all the values from this object using Object.values() and array#map.
const records = [ { category: 'Cat 1', date: '2020-11-03', value: '300.00' } , { category: 'Cat 2', date: '2020-11-03', value: '350.00' } , { category: 'Cat 3', date: '2020-11-03', value: '50.00' } , { category: 'Cat 1', date: '2020-11-13', value: '200.00' } , { category: 'Cat 1', date: '2020-12-23', value: '100.00' } , { category: 'Cat 2', date: '2020-11-23', value: '350.00' } , { category: 'Cat 3', date: '2020-11-15', value: '50.00' } , { category: 'Cat 3', date: '2021-01-15', value: '50.00' } ],
result = records.reduce((r, {category, date, value}) => {
const month = date.substring(0, 7);
r[category] ??= {category};
r[category][month] ??= {month, total: 0};
r[category][month].total += +value;
return r;
},{}),
newRecords = Object
.values(result)
.map( ({category, ...rest}) =>
({ category,
totalPerMonth: Object.values(rest).map(({ month, total }) =>
({ month, totalMonthlyValue: total.toFixed(2) })
) }) );
console.log(newRecords);.as-console-wrapper { max-height: 100% !important; top: 0; }