I have the following data:
const rawData=[
{
Order_Date: "2020-08-11",
Region: "South",
Product_Name: "Bookcase",
Sales: 261.96
},
{
Order_Date: "2020-08-13",
Region: "South",
Product_Name: "Stacking Chairs",
Sales: 731.94
},
{
Order_Date: "2020-10-06",
Region: "South",
Product_Name: "Stacking Chairs",
Sales: 700.45
},
{
Order_Date: "2020-12-06",
Region: "East",
Product_Name: "Self-Adhesive Address Labels for Typewriters by Universal",
Sales: 14.62
},
{
Order_Date: "2019-11-15",
Region: "East",
Product_Name: "Table",
Sales: 957
},
{
Order_Date: "2019-11-10",
Region: "East",
Product_Name: "Eldon Fold",
Sales: 22
}
]
Expected Output:
[
{
Region: "South",
data: [
{
Order_Date: "2020-08",
Sales: 993.90
},
{
Order_Date: "2020-10",
Sales: 700.45
},
]
},
{
Region: "East",
data: [
{
Order_Date: "2019-11",
Sales: 989
},
{
Order_Date: "2020-12",
Sales: 14.62
},
]
}
]
I want to first group by region and for each region I want to group by year and month ('YYYY-MM') of Order_Date and sum by sales for each Order_Date('YYYY-MM') group.
I tried the following code but unable to get as expected output. I'm not understanding how to do this with multiple group by.
const groupByRegion = _(rawData).groupBy('Region').value();
const groupByRegionDate = _.forEach(groupByRegion, (value, key) => {
groupByRegion[key] = _.groupBy(groupByRegion[key], (item) =>
moment(item.Order_Date).startOf('month').format('YYYY-MM')
);
});
let data = [{ "Order_Date": "2020-08-11", "Region": "South", "Product_Name": "Bookcase", "Sales": 261.96 }, { "Order_Date": "2020-08-11", "Region": "South", "Product_Name": "Stacking Chairs", "Sales": 731.94 }, { "Order_Date": "2020-10-06", "Region": "South", "Product_Name": "Stacking Chairs", "Sales": 700.45 }, { "Order_Date": "2020-12-06", "Region": "East", "Product_Name": "Typewriters by Universal", "Sales": 14.62 }, { "Order_Date": "2019-11-10", "Region": "East", "Product_Name": "Table", "Sales": 957 }, { "Order_Date": "2019-11-10", "Region": "East", "Product_Name": "Eldon Fold", "Sales": 22 }]
let results = []
for (let item of data) {
let { Region, Order_Date, Sales } = item;
let group = results.find(v => v.Region == Region);
if (!group) results.push(group = { Region, data: [] });
let groupItem = group.data.find(v => v.Order_Date == Order_Date);
if (!groupItem)
group.data.push({ Order_Date, Sales })
else
groupItem.Sales += Sales;
}
console.log(results)