JavaScript - Group CSV fields based on empty cells

I have data that come from a CSV file that can have blank fields and must have data grouped based on that.

Here's a simple example of a data of persons by location. The location data only appears for the first person and every following person that don't have a location assigned belongs to the location that comes before:

"Objects:", [{
    location: "New York",
    date: "10/10/2021",
    name: "Max",
    surname: "Payne"
}, {
    location: "",
    date: "",
    name: "Duke",
    surname: "Nuken"
}, {
    location: "",
    date: "",
    name: "Jack",
    surname: "Carver"
}, {
    location: "Las Vegas",
    date: "30/10/2021",
    name: "Leon",
    surname: "Kennedy"
}, {
    location: "",
    date: "",
    name: "Donkey",
    surname: "Kong"
}, {
    location: "",
    date: "",
    name: "Ryu",
    surname: "Hayabusa"
}]

I need to join the list of persons into a list and add it to the corresponding location:

"Objects:", [{
    location: "New York",
    date: "10/10/2021",
    persons: [{
        name: "Max",
        surname: "Payne"
    }, {
        name: "Duke",
        surname: "Nuken"
    }, {
        name: "Jack",
        surname: "Carver"
    }]
}, {
    location: "Las Vegas",
    date: "30/10/2021",
    persons: [{
        name: "Leon",
        surname: "Kennedy"
    }, {
        name: "Donkey",
        surname: "Kong"
    }, {
        name: "Ryu",
        surname: "Hayabusa"
    }]
}]

The data originally comes as an array of arrays in this form:

const [headers, ...lines] = [
    ["location",  "date",       "name",   "surname" ],
    ["New York",  "10/10/2021", "Max",    "Payne"   ],
    ["",          "",           "Duke",   "Nuken"   ],
    ["",          "",           "Jack",   "Carver"  ],
    ["Las Vegas", "30/10/2021", "Leon",   "Kennedy" ],
    ["",          "",           "Donkey", "Kong"    ],
    ["",          "",           "Ryu",    "Hayabusa"],
];

The code I currently have to convert the array into the first list of objects in this question is this:

const locations = lines.map( (line) =>
   line.reduce((object, value, index) =>
        ({...object, [ headers[index] ]: value})
      , {}
   ));

What would be a good way to solve this?

1 answer

  • answered 2021-05-14 21:17 pilchard

    here's an example using Array#reduce() and hard coding the header names.

    const
      [headers, ...lines] = [["location", "date", "name", "surname"], ["New York", "10/10/2021", "Max", "Payne"], ["", "", "Duke", "Nuken"], ["", "", "Jack", "Carver"], ["Las Vegas", "30/10/2021", "Leon", "Kennedy"], ["", "", "Donkey", "Kong"], ["", "", "Ryu", "Hayabusa"],],
    
      result = lines.reduce((a, [location, date, name, surname]) => {
        if (location && date) a.push({ location, date, persons: [] });
    
        a[a.length - 1].persons.push({ name, surname });
    
        return a;
      }, []);
    
    console.log(JSON.stringify(result, null, 2));
    .as-console-wrapper { max-height: 100% !important; top: 0; }

    Here is a more dynamic example allowing you to specify 'requiredIndeces' that should be present to group by. You can adjust for your particular needs.

    const
      input = [["location", "date", "name", "surname"], ["New York", "10/10/2021", "Max", "Payne"], ["", "", "Duke", "Nuken"], ["", "", "Jack", "Carver"], ["Las Vegas", "30/10/2021", "Leon", "Kennedy"], ["", "", "Donkey", "Kong"], ["", "", "Ryu", "Hayabusa"],],
    
      refactorCSV = (arr, requiredIndeces) => {
        const
          reduceByRequiredIndex = (arr) => arr.reduce((a, x, i) =>
            (a[+requiredIndeces.includes(i)].push(x), a), [[], []]),
          [headers, ...lines] = arr,
          [entryProps, requiredProps] = reduceByRequiredIndex(headers);
    
        return lines.reduce((a, line) => {
          const [personValues, requiredValues] = reduceByRequiredIndex(line);
    
          if (requiredValues.every(v => v !== '')) {
            const required = {};
            requiredProps.forEach((prop, i) => required[prop] = requiredValues[i]);
    
            a.push({ ...required, persons: [] });
          }
    
          const person = {};
          entryProps.forEach((prop, i) => person[prop] = personValues[i]);
    
          a[a.length - 1].persons.push(person);
    
          return a;
        }, []);
      },
    
      result = refactorCSV(input, [0, 1]);
    
    console.log(JSON.stringify(result, null, 2));
    .as-console-wrapper { max-height: 100% !important; top: 0; }