I have a CSV file as follows:
| PersonID | Name | Game | Date | Score |
|---|---|---|---|---|
| 123 | John | Tennis | 01/01/2023 | 150 |
| 131 | Amanda | Hockey | 01/02/2023 | 134 |
| 123 | John | Tennis | 01/02/2023 | 140 |
| 134 | Peter | Tennis | 02/02/2023 | 127 |
What steps do i need to de to get this:
Tennis:
John:
Total_score: 290
Played_games:
01/01/2023 150
01/02/2023 140
Peter:
Total_score: 127
Played games:
02/02/2023 127
Hockey:
Amanda:
Total_score: 134
Played_games:
01/02/2023 134
The purpose of this is to make a scoreboard grouped at Game>Person. We want to summarize the scores of the played games.
I already tried different things. I got the games list without duplicates and so on. But what is the best practice to make a object?
I read about mapping, filtering and so on, but can't find a fitting solution for me. Maybe I think to complex.
At the moment i have the following Javascript array:
const scores = [
{"PersonID": "1234","Date": "31/12/2024", "game": "tennis", "name":"John", "score":151},
{"PersonID": "1234","Date": "25/12/2024", "game": "tennis", "name":"John", "score":178},
{"PersonID": "55513","Date": "01/12/2024", "game": "Hockey", "name":"Piet", "score":166},
{"PersonID": "24244","Date": "10/11/2024", "game": "tennis", "name":"Klaas", "score":188},
{"PersonID": "1234","Date": "06/12/2024", "game": "Football", "name":"John", "score":133},
{"PersonID": "33245","Date": "24/12/2024", "game": "Baseball", "name":"Marie", "score":121},
{"PersonID": "63542","Date": "11/12/2024", "game": "Kitesurfing", "name":"Peter", "score":185},
]
This is how i filtered the games type
var arrGames = [];
scores.filter(function(item){
var i = arrGames.findIndex(x => x.game== item.game);
if(i <= -1){
arrGames.push({game: item.game});
}
return null;
});
console.log(JSON.stringify(arrGames, null, 2));
The next step is that i need to add the persons as child to the game. How can i arrange this? I know it can be done with some advanced loops. But for my learning process step by step would be the best :)
Please try the following.
const scores = [
{"PersonID": "1234","Date": "31/12/2024", "game": "tennis", "name":"John", "score":151},
{"PersonID": "1234","Date": "25/12/2024", "game": "tennis", "name":"John", "score":178},
{"PersonID": "55513","Date": "01/12/2024", "game": "Hockey", "name":"Piet", "score":166},
{"PersonID": "24244","Date": "10/11/2024", "game": "tennis", "name":"Klaas", "score":188},
{"PersonID": "1234","Date": "06/12/2024", "game": "Football", "name":"John", "score":133},
{"PersonID": "33245","Date": "24/12/2024", "game": "Baseball", "name":"Marie", "score":121},
{"PersonID": "63542","Date": "11/12/2024", "game": "Kitesurfing", "name":"Peter", "score":185},
];
var end = {};
scores.forEach(function(a) {
let game = a.game;
let player = a.name;
let dt = a.Date;
let score = a.score;
if(!end.hasOwnProperty(game)) {
end[game] = {};
}
if(!end[game].hasOwnProperty(player)) {
end[game][player] = {
"Total_score" : 0,
"Played_games" : []
};
}
end[game][player]["Total_score"] += score;
end[game][player]["Played_games"].push({[dt]:score});
});
console.log(JSON.stringify(end));Explanations
By looping through your scores array, we're picking up what you need for the end result.
The first test, if(!end.hasOwnProperty(game)) {...} is simply assigning the current game as the key in the end object, if it's not already there. For example, in our first run, end is empty, and the first game we're picking up from your scores array is tennis.
Next, we're checking if within the specific game (tennis in our first run) there's a player we're currently getting. If the player is not within our specific game, we're putting them there, and giving them a zero score and an empty array of dates.
Finally, we're filling up our keys with an incremented score and date/score values we've picked in our current forEach run.
This gives us:
{
"tennis": {
"John": {
"Total_score": 329,
"Played_games": [
{
"31/12/2024": 151
},
{
"25/12/2024": 178
}
]
},
"Klaas": {
"Total_score": 188,
"Played_games": [
{
"10/11/2024": 188
}
]
}
},
"Hockey": {
"Piet": {
"Total_score": 166,
"Played_games": [
{
"01/12/2024": 166
}
]
}
},
"Football": {
"John": {
"Total_score": 133,
"Played_games": [
{
"06/12/2024": 133
}
]
}
},
"Baseball": {
"Marie": {
"Total_score": 121,
"Played_games": [
{
"24/12/2024": 121
}
]
}
},
"Kitesurfing": {
"Peter": {
"Total_score": 185,
"Played_games": [
{
"11/12/2024": 185
}
]
}
}
}
Btw, Amanda's not mentioned anywhere in your example JSON array, even though she's in the table in the beginning of your question - that's why she's not present in the end result.
Please note that this solution is not covering the case when a player has played more than one game on a specific date. If you need that as well, let me know in the comments.
EDIT
If you want to cover the cases when a player has played more than once in a single day, the original code will already do that for you. However, if you want to sum the entire day up, you would need to change the current code to the following:
// Original array reduced to only tennis players, for simplicity's sake
const scores = [
{"PersonID": "1234","Date": "31/12/2024", "game": "tennis", "name":"John", "score":151},
{"PersonID": "1234","Date": "25/12/2024", "game": "tennis", "name":"John", "score":178},
{"PersonID": "24244","Date": "10/11/2024", "game": "tennis", "name":"Klaas", "score":188},
// The one below was added to illustrate what we're doing
{"PersonID": "1234","Date": "31/12/2024", "game": "tennis", "name":"John", "score":22},
];
let end = {};
scores.forEach(function(a) {
let game = a.game;
let player = a.name;
let dt = a.Date;
let score = a.score;
if(!end.hasOwnProperty(game)) {
end[game] = {};
}
if(!end[game].hasOwnProperty(player)) {
end[game][player] = {
"Total_score" : 0,
"Played_games" : []
};
}
end[game][player]["Total_score"] += score;
// Let'sheck if there's already an entry for the specific player,
// and the specific date, which is actually a key in our array
let existingDate = end[game][player]["Played_games"].find(tmp => Object.keys(tmp)[0] === dt);
if(existingDate) {
// We've already had the current date, that's why
// we're incrementing the score
existingDate[dt] += score;
} else {
// existingDate is undefined - the current date for the
// current player does not exist, so we add it
end[game][player]["Played_games"].push({[dt]:score});
}
});
console.log(JSON.stringify(end));This gives us:
{
"tennis": {
"John": {
"Total_score": 351,
"Played_games": [
{
"31/12/2024": 173
},
{
"25/12/2024": 178
}
]
},
"Klaas": {
"Total_score": 188,
"Played_games": [
{
"10/11/2024": 188
}
]
}
}
}
EDIT #2
If we need to take into consideration the new conditions:
our code becomes this (expand the snippet to see the full code):
const scores = [
{"PersonID": "1234","Date": "31/12/2024", "game": "tennis", "name":"John", "score":151},
{"PersonID": "1234","Date": "25/12/2024", "game": "tennis", "name":"John", "score":178},
{"PersonID": "24244","Date": "10/11/2024", "game": "tennis", "name":"Klaas", "score":188},
// The ones below were added to simplify what we're doing - the other sports
// were removed for brevity
{"PersonID": "1234","Date": "31/12/2024", "game": "tennis", "name":"John", "score":22},
{"PersonID": "1234","Date": "11/12/2024", "game": "tennis", "name":"John", "score":42},
{"PersonID": "24244","Date": "01/11/2024", "game": "tennis", "name":"Klaas", "score":18},
{"PersonID": "24244","Date": "11/11/2024", "game": "tennis", "name":"Klaas", "score":88},
{"PersonID": "24244","Date": "12/11/2024", "game": "tennis", "name":"Klaas", "score":38},
{"PersonID": "24244","Date": "13/11/2024", "game": "tennis", "name":"Klaas", "score":45},
{"PersonID": "1234","Date": "21/12/2024", "game": "tennis", "name":"John", "score":220},
{"PersonID": "1234","Date": "01/12/2024", "game": "tennis", "name":"John", "score":197},
];
let end = {};
scores.forEach(function(a) {
let game = a.game;
let player = a.name;
let dt = a.Date;
let score = a.score;
if (!end.hasOwnProperty(game)) {
end[game] = {};
}
if (!end[game].hasOwnProperty(player)) {
end[game][player] = {
"Total_score": 0,
"Played_games": []
};
}
let existingDate = end[game][player]["Played_games"].find(tmp => Object.keys(tmp)[0] === dt);
/* Activate ONLY if you need the best 3 days, and not the best 3 games
if (existingDate) {
existingDate[dt] += score;
} else {
end[game][player]["Played_games"].push({ [dt]: score });
}
*/
// Deactivate if above is activated - this one is making
// individual entries for days and games, and allowing
// a single day to appear multiple times - it's the games that matter
end[game][player]["Played_games"].push({ [dt]: score });
// Sort the Played_games array based on the scores in descending order
end[game][player]["Played_games"].sort((a, b) => Object.values(b)[0] - Object.values(a)[0]);
// Keep only the top 3 results, and remove the rest
end[game][player]["Played_games"] = end[game][player]["Played_games"].slice(0, 3);
// Set the total score now, with only the best 3 for the current player
end[game][player]["Total_score"] = 0;
// Loop through all the available date:score pairs for the current player
end[game][player]["Played_games"].forEach(function(tmpDate){
// Add the score for the current date to the total
// for our current player
for(tmpIndex in tmpDate) {
end[game][player]["Total_score"] += tmpDate[tmpIndex];
}
});
});
// Once the filling up of the end object array is done
// sort the players by Total_score in descending order
for (let game in end) {
end[game] = Object.entries(end[game]).sort((a, b) => b[1]["Total_score"] - a[1]["Total_score"]);
}
console.log(JSON.stringify(end));The code above gives us:
{
"tennis": [
[
"John",
{
"Total_score": 595,
"Played_games": [
{
"21/12/2024": 220
},
{
"01/12/2024": 197
},
{
"25/12/2024": 178
}
]
}
],
[
"Klaas",
{
"Total_score": 321,
"Played_games": [
{
"10/11/2024": 188
},
{
"11/11/2024": 88
},
{
"13/11/2024": 45
}
]
}
]
]
}