tgerard
Reputation: 293
Nesting or mapping data from key lower in json hierarchy
I need to reorganise my json data so that it's based on a key that's currently nested two levels down in the data. The data comes in with a hierarchy based onthe "id", "name" etc. of an activity. I need to base the hierarchy on "year", which is in the "years" array for each item. I've tried various approaches, but I'm not even sure whether I should be using d3.nest or d3.map (or both).
I haven't bothered posting my d3 code because all I'm doing at this stage is loading the data and logging it to the console.
Any assistance is greatly appreciated.
The data as it comes in:
[{
"id": 2,
"name": "Test activity 51",
"code": "D51",
"description": "Description goes here.",
"years": [{
"id": 8,
"year": "2020-2021",
"target": 65,
"weeks": [{
"week_ending": "2020-12-06",
"hours": 4.0
}, {
"week_ending": "2020-12-06",
"hours": 2.5
}, {
"week_ending": "2020-10-18",
"hours": 2.0
}, {
"week_ending": "2020-07-19",
"hours": 2.0
}]
}, {
"id": 11,
"year": "2019-2020",
"target": 40,
"weeks": [{
"week_ending": "2020-01-05",
"hours": 3.0
}, {
"week_ending": "2019-09-15",
"hours": 5.5
}, {
"week_ending": "2019-07-14",
"hours": 1.5
}]
}, {
"id": 10,
"year": "2018-2019",
"target": 40,
"weeks": [{
"week_ending": "2018-09-30",
"hours": 3.0
}]
}]
}, {
"id": 3,
"name": "Test activity 63",
"code": "D63",
"description": null,
"years": [{
"id": 2,
"year": "2019-2020",
"target": 15,
"weeks": [{
"week_ending": "2020-05-24",
"hours": 2.0
}, {
"week_ending": "2020-03-22",
"hours": 4.0
}, {
"week_ending": "2020-01-26",
"hours": 5.0
}, {
"week_ending": "2020-01-19",
"hours": 4.0
}, {
"week_ending": "2019-12-01",
"hours": 4.5
}, {
"week_ending": "2019-08-25",
"hours": 2.0
}, {
"week_ending": "2019-07-21",
"hours": 3.0
}]
}, {
"id": 9,
"year": "2018-2019",
"target": 30,
"weeks": [{
"week_ending": "2018-08-19",
"hours": 3.5
}, {
"week_ending": "2019-02-10",
"hours": 5.0
}, {
"week_ending": "2018-12-30",
"hours": 3.0
}, {
"week_ending": "2018-10-21",
"hours": 2.0
}, {
"week_ending": "2018-08-26",
"hours": 4.0
}]
}]
}, {
"id": 1,
"name": "Test activity 27",
"code": "D27",
"description": "Description goes here.",
"years": [{
"id": 4,
"year": "2019-2020",
"target": 30,
"weeks": [{
"week_ending": "2020-04-05",
"hours": 2.0
}, {
"week_ending": "2020-03-22",
"hours": 6.0
}, {
"week_ending": "2020-01-12",
"hours": 1.0
}]
}, {
"id": 1,
"year": "2018-2019",
"target": 25,
"weeks": [{
"week_ending": "2018-11-18",
"hours": 6.0
}, {
"week_ending": "2018-10-21",
"hours": 2.0
}, {
"week_ending": "2018-10-07",
"hours": 6.0
}]
}]
}, {
"id": 10,
"name": "Test activity 59",
"code": "D59",
"description": null,
"years": [{
"id": 13,
"year": "2020-2021",
"target": 15,
"weeks": [{
"week_ending": "2021-04-18",
"hours": 4.0
}, {
"week_ending": "2021-02-28",
"hours": 2.0
}, {
"week_ending": "2021-02-14",
"hours": 5.0
}, {
"week_ending": "2020-11-22",
"hours": 3.0
}, {
"week_ending": "2020-08-16",
"hours": 2.0
}]
}, {
"id": 14,
"year": "2019-2020",
"target": 18,
"weeks": [{
"week_ending": "2019-12-01",
"hours": 2.0
}, {
"week_ending": "2019-10-27",
"hours": 9.0
}, {
"week_ending": "2019-09-01",
"hours": 2.5
}]
}, {
"id": 12,
"year": "2018-2019",
"target": 20,
"weeks": [{
"week_ending": "2018-11-11",
"hours": 4.0
}, {
"week_ending": "2018-08-26",
"hours": 1.0
}, {
"week_ending": "2018-08-12",
"hours": 12.0
}]
}]
}]
What I'm trying to achieve:
[{
"year": "2020-2021",
"values": [{
"name": "Test activity 51",
"code": "D51",
"description": "Description goes here.",
"target": 65,
"weeks": [{
"week_ending": "2020-12-06",
"hours": 4.0
}, {
"week_ending": "2020-12-06",
"hours": 2.5
}, {
"week_ending": "2020-10-18",
"hours": 2.0
}, {
"week_ending": "2020-07-19",
"hours": 2.0
}]
}, {
"name": "Test activity 59",
"code": "D59",
"description": null,
"target": 15,
"weeks": [{
"week_ending": "2021-04-18",
"hours": 4.0
}, {
"week_ending": "2021-02-28",
"hours": 2.0
}, {
"week_ending": "2021-02-14",
"hours": 5.0
}, {
"week_ending": "2020-11-22",
"hours": 3.0
}, {
"week_ending": "2020-08-16",
"hours": 2.0
}]
}, ]
}, {
"year": "2019-2020",
"values": [{
"name": "Test activity 51",
"code": "D51",
"description": "Description goes here.",
"target": 40,
"weeks": [{
"week_ending": "2020-01-05",
"hours": 3.0
}, {
"week_ending": "2019-09-15",
"hours": 5.5
}, {
"week_ending": "2019-07-14",
"hours": 1.5
}]
}, {
"name": "Test activity 63",
"code": "D63",
"description": null,
"target": 15,
"weeks": [{
"week_ending": "2020-05-24",
"hours": 2.0
}, {
"week_ending": "2020-03-22",
"hours": 4.0
}, {
"week_ending": "2020-01-26",
"hours": 5.0
}, {
"week_ending": "2020-01-19",
"hours": 4.0
}, {
"week_ending": "2019-12-01",
"hours": 4.5
}, {
"week_ending": "2019-08-25",
"hours": 2.0
}, {
"week_ending": "2019-07-21",
"hours": 3.0
}]
}, {
"name": "Test activity 27",
"code": "D27",
"description": "Description goes here.",
"target": 30,
"weeks": [{
"week_ending": "2020-04-05",
"hours": 2.0
}, {
"week_ending": "2020-03-22",
"hours": 6.0
}, {
"week_ending": "2020-01-12",
"hours": 1.0
}]
}, {
"name": "Test activity 59",
"code": "D59",
"description": null,
"target": 18,
"weeks": [{
"week_ending": "2019-12-01",
"hours": 2.0
}, {
"week_ending": "2019-10-27",
"hours": 9.0
}, {
"week_ending": "2019-09-01",
"hours": 2.5
}]
}, ]
}, {
"year": "2018-2019",
"values": [{
"name": "Test activity 51",
"code": "D51",
"description": "Description goes here.",
"target": 40,
"weeks": [{
"week_ending": "2018-09-30",
"hours": 3.0
}]
}, {
"name": "Test activity 63",
"code": "D63",
"description": null,
"target": 30,
"weeks": [{
"week_ending": "2018-08-19",
"hours": 3.5
}, {
"week_ending": "2019-02-10",
"hours": 5.0
}, {
"week_ending": "2018-12-30",
"hours": 3.0
}, {
"week_ending": "2018-10-21",
"hours": 2.0
}, {
"week_ending": "2018-08-26",
"hours": 4.0
}]
}, {
"name": "Test activity 27",
"code": "D27",
"description": "Description goes here.",
"target": 25,
"weeks": [{
"week_ending": "2018-11-18",
"hours": 6.0
}, {
"week_ending": "2018-10-21",
"hours": 2.0
}, {
"week_ending": "2018-10-07",
"hours": 6.0
}]
}, {
"name": "Test activity 59",
"code": "D59",
"description": null,
"target": 20,
"weeks": [{
"week_ending": "2018-11-11",
"hours": 4.0
}, {
"week_ending": "2018-08-26",
"hours": 1.0
}, {
"week_ending": "2018-08-12",
"hours": 12.0
}]
}]
}]
Upvotes: 4
Views: 136
Answers (1)
Gerardo Furtado
Reputation: 102174
What you're asking is too complicated for a D3 nest. Besides that, d3.nest is about to be deprecated anyway... So, here is a pure JavaScript approach.
We first get a collection of unique years:
const yearsList = [...new Set(data.map(d => d.years.map(e => e.year)).flat())];
Then, with that collection in hand, we loop through your data and create a new array (here called newData), in a solution which is purposely both ad hoc and verbose:
yearsList.forEach(d => {
data.forEach(e => {
const foundObject = e.years.find(f => f.year === d);
if (foundObject) {
const foundInNewData = newData.find(f => f.year === d);
const values = {
name: e.name,
code: e.code,
description: e.description,
target: foundObject.target,
weeks: foundObject.weeks
};
if (foundInNewData) {
foundInNewData.values.push(values);
} else {
newData.push({
year: d,
values: [values]
});
};
};
});
});
And here is the demo:
const data = [{
"id": 2,
"name": "Test activity 51",
"code": "D51",
"description": "Description goes here.",
"years": [{
"id": 8,
"year": "2020-2021",
"target": 65,
"weeks": [{
"week_ending": "2020-12-06",
"hours": 4.0
},
{
"week_ending": "2020-12-06",
"hours": 2.5
},
{
"week_ending": "2020-10-18",
"hours": 2.0
},
{
"week_ending": "2020-07-19",
"hours": 2.0
}
]
},
{
"id": 11,
"year": "2019-2020",
"target": 40,
"weeks": [{
"week_ending": "2020-01-05",
"hours": 3.0
},
{
"week_ending": "2019-09-15",
"hours": 5.5
},
{
"week_ending": "2019-07-14",
"hours": 1.5
}
]
},
{
"id": 10,
"year": "2018-2019",
"target": 40,
"weeks": [{
"week_ending": "2018-09-30",
"hours": 3.0
}]
}
]
},
{
"id": 3,
"name": "Test activity 63",
"code": "D63",
"description": null,
"years": [{
"id": 2,
"year": "2019-2020",
"target": 15,
"weeks": [{
"week_ending": "2020-05-24",
"hours": 2.0
},
{
"week_ending": "2020-03-22",
"hours": 4.0
},
{
"week_ending": "2020-01-26",
"hours": 5.0
},
{
"week_ending": "2020-01-19",
"hours": 4.0
},
{
"week_ending": "2019-12-01",
"hours": 4.5
},
{
"week_ending": "2019-08-25",
"hours": 2.0
},
{
"week_ending": "2019-07-21",
"hours": 3.0
}
]
},
{
"id": 9,
"year": "2018-2019",
"target": 30,
"weeks": [{
"week_ending": "2018-08-19",
"hours": 3.5
},
{
"week_ending": "2019-02-10",
"hours": 5.0
},
{
"week_ending": "2018-12-30",
"hours": 3.0
},
{
"week_ending": "2018-10-21",
"hours": 2.0
},
{
"week_ending": "2018-08-26",
"hours": 4.0
}
]
}
]
},
{
"id": 1,
"name": "Test activity 27",
"code": "D27",
"description": "Description goes here.",
"years": [{
"id": 4,
"year": "2019-2020",
"target": 30,
"weeks": [{
"week_ending": "2020-04-05",
"hours": 2.0
},
{
"week_ending": "2020-03-22",
"hours": 6.0
},
{
"week_ending": "2020-01-12",
"hours": 1.0
}
]
},
{
"id": 1,
"year": "2018-2019",
"target": 25,
"weeks": [{
"week_ending": "2018-11-18",
"hours": 6.0
},
{
"week_ending": "2018-10-21",
"hours": 2.0
},
{
"week_ending": "2018-10-07",
"hours": 6.0
}
]
}
]
},
{
"id": 10,
"name": "Test activity 59",
"code": "D59",
"description": null,
"years": [{
"id": 13,
"year": "2020-2021",
"target": 15,
"weeks": [{
"week_ending": "2021-04-18",
"hours": 4.0
},
{
"week_ending": "2021-02-28",
"hours": 2.0
},
{
"week_ending": "2021-02-14",
"hours": 5.0
},
{
"week_ending": "2020-11-22",
"hours": 3.0
},
{
"week_ending": "2020-08-16",
"hours": 2.0
}
]
},
{
"id": 14,
"year": "2019-2020",
"target": 18,
"weeks": [{
"week_ending": "2019-12-01",
"hours": 2.0
},
{
"week_ending": "2019-10-27",
"hours": 9.0
},
{
"week_ending": "2019-09-01",
"hours": 2.5
}
]
},
{
"id": 12,
"year": "2018-2019",
"target": 20,
"weeks": [{
"week_ending": "2018-11-11",
"hours": 4.0
},
{
"week_ending": "2018-08-26",
"hours": 1.0
},
{
"week_ending": "2018-08-12",
"hours": 12.0
}
]
}
]
}
];
const yearsList = [...new Set(data.map(d => d.years.map(e => e.year)).flat())];
const newData = [];
yearsList.forEach(d => {
data.forEach(e => {
const foundObject = e.years.find(f => f.year === d);
if (foundObject) {
const foundInNewData = newData.find(f => f.year === d);
const values = {
name: e.name,
code: e.code,
description: e.description,
target: foundObject.target,
weeks: foundObject.weeks
};
if (foundInNewData) {
foundInNewData.values.push(values)
} else {
newData.push({
year: d,
values: [values]
})
}
}
})
});
console.log(newData)<script src="https://cdnjs.cloudflare.com/ajax/libs/d3/5.7.0/d3.min.js"></script>As you'll notice, the code above has not just one or two, but three loops. If your data array is too big you can also try a solution with just two nested loops:
const newData = [];
data.forEach(d => {
d.years.forEach(e => {
const foundInNewData = newData.find(f => f.year === e.year);
const values = {
name: d.name,
code: d.code,
description: d.description,
target: e.target,
weeks: e.weeks
};
if (foundInNewData) {
foundInNewData.values.push(values)
} else {
newData.push({
year: e.year,
values: [values]
})
}
});
});
And here is the corresponding demo:
const data = [{
"id": 2,
"name": "Test activity 51",
"code": "D51",
"description": "Description goes here.",
"years": [{
"id": 8,
"year": "2020-2021",
"target": 65,
"weeks": [{
"week_ending": "2020-12-06",
"hours": 4.0
},
{
"week_ending": "2020-12-06",
"hours": 2.5
},
{
"week_ending": "2020-10-18",
"hours": 2.0
},
{
"week_ending": "2020-07-19",
"hours": 2.0
}
]
},
{
"id": 11,
"year": "2019-2020",
"target": 40,
"weeks": [{
"week_ending": "2020-01-05",
"hours": 3.0
},
{
"week_ending": "2019-09-15",
"hours": 5.5
},
{
"week_ending": "2019-07-14",
"hours": 1.5
}
]
},
{
"id": 10,
"year": "2018-2019",
"target": 40,
"weeks": [{
"week_ending": "2018-09-30",
"hours": 3.0
}]
}
]
},
{
"id": 3,
"name": "Test activity 63",
"code": "D63",
"description": null,
"years": [{
"id": 2,
"year": "2019-2020",
"target": 15,
"weeks": [{
"week_ending": "2020-05-24",
"hours": 2.0
},
{
"week_ending": "2020-03-22",
"hours": 4.0
},
{
"week_ending": "2020-01-26",
"hours": 5.0
},
{
"week_ending": "2020-01-19",
"hours": 4.0
},
{
"week_ending": "2019-12-01",
"hours": 4.5
},
{
"week_ending": "2019-08-25",
"hours": 2.0
},
{
"week_ending": "2019-07-21",
"hours": 3.0
}
]
},
{
"id": 9,
"year": "2018-2019",
"target": 30,
"weeks": [{
"week_ending": "2018-08-19",
"hours": 3.5
},
{
"week_ending": "2019-02-10",
"hours": 5.0
},
{
"week_ending": "2018-12-30",
"hours": 3.0
},
{
"week_ending": "2018-10-21",
"hours": 2.0
},
{
"week_ending": "2018-08-26",
"hours": 4.0
}
]
}
]
},
{
"id": 1,
"name": "Test activity 27",
"code": "D27",
"description": "Description goes here.",
"years": [{
"id": 4,
"year": "2019-2020",
"target": 30,
"weeks": [{
"week_ending": "2020-04-05",
"hours": 2.0
},
{
"week_ending": "2020-03-22",
"hours": 6.0
},
{
"week_ending": "2020-01-12",
"hours": 1.0
}
]
},
{
"id": 1,
"year": "2018-2019",
"target": 25,
"weeks": [{
"week_ending": "2018-11-18",
"hours": 6.0
},
{
"week_ending": "2018-10-21",
"hours": 2.0
},
{
"week_ending": "2018-10-07",
"hours": 6.0
}
]
}
]
},
{
"id": 10,
"name": "Test activity 59",
"code": "D59",
"description": null,
"years": [{
"id": 13,
"year": "2020-2021",
"target": 15,
"weeks": [{
"week_ending": "2021-04-18",
"hours": 4.0
},
{
"week_ending": "2021-02-28",
"hours": 2.0
},
{
"week_ending": "2021-02-14",
"hours": 5.0
},
{
"week_ending": "2020-11-22",
"hours": 3.0
},
{
"week_ending": "2020-08-16",
"hours": 2.0
}
]
},
{
"id": 14,
"year": "2019-2020",
"target": 18,
"weeks": [{
"week_ending": "2019-12-01",
"hours": 2.0
},
{
"week_ending": "2019-10-27",
"hours": 9.0
},
{
"week_ending": "2019-09-01",
"hours": 2.5
}
]
},
{
"id": 12,
"year": "2018-2019",
"target": 20,
"weeks": [{
"week_ending": "2018-11-11",
"hours": 4.0
},
{
"week_ending": "2018-08-26",
"hours": 1.0
},
{
"week_ending": "2018-08-12",
"hours": 12.0
}
]
}
]
}
];
const newData = [];
data.forEach(d => {
d.years.forEach(e => {
const foundInNewData = newData.find(f => f.year === e.year);
const values = {
name: d.name,
code: d.code,
description: d.description,
target: e.target,
weeks: e.weeks
};
if (foundInNewData) {
foundInNewData.values.push(values)
} else {
newData.push({
year: e.year,
values: [values]
})
}
});
});
console.log(newData)Upvotes: 2
Related Questions
- transform flat json object to hierarchical d3 object structure
- Nested arrays in JSON with D3
- Javascript - JSON - Nesting and Grouping
- D3- Approach for Json nested key-value
- Create key, value pair for JSON via nest in D3
- Nested JSON array and D3JS
- D3JS: Multi-Nested JSON data
- D3 - dealing with JSON nested data structures
- nested arrays in d3.js
- D3: use nest function to turn flat data with parent key into a hierarchy