Transform excel 2D array into nested properties object with column names included

Question

I have an Excel file as following:

[["Country", "City", "Zip"], ["CNTR1", "CT1", 101], ["CNTR1", "CT2", 102], ["CNTR2", "CT3", 103]]

The above is a 2D array interpretation of the 4 rows of the Excel. I want from the above, to get the following object:

{
    level1: "Country",
    items: [{
        "CNTR1": {
            level2: "City",
            items: [{
                "CT1": {
                    level3: "Zip",
                    items: [
                        "101"
                    ]
                }
            }, {
                "CT2": {
                    level3: "Zip",
                    items: [
                        "102"
                    ]
                }
            }]
        }
    }, {
        "CNTR2": {
            level2: "City",
            items: [{
                "CT3": {
                    level3: "Zip",
                    items: [
                        "103"
                    ]
                }
            }]
        }
    }]
}

The main purpose is to have a hierarchy, since the above Excel should have a left to right dependency, because the values will be used in dependent dropdowns (so if we have country "CNTR1", all cities and zip codes should be part of its object, since after selecting "CNTR1" in the countries dropdown, only CT1 and CT2 should show in the cities dropdown, and so on for the zip codes), and based on this dependency, we should have the subvalues for each of the nested levels. Also the above should not be limited to three levels deep, so it should be something with reducers/recursive solutions.

If anyone has a better object schema solution, since the above is what I thought would fit the purpose of the dependent dropdowns while also retrieving the column names, feel free to share. The dropdowns will be dynamic, with dependency based on the left to right columns, and values based on the same "higher" hierarchy value.

Hopefully it is clear enough.

Up until now I have:

items.map(row => row.reduceRight((value, key) => ({[key]: value}))).reduce((acc, value) => (Object.assign(acc, value)))

but this one just gives results of type:

{
  "CNTR1": {
    "CT2": 102
  },
  "CNTR2": {
    "CT3": 103
  }
}

(disregard the loss of nested property data since the merge is not deep) The names of the columns are not included, and it seems sort of poor in data structure for the purpose specified.

Thanks

Nenad Vracar · Accepted Answer · 2019-12-02 21:09:10Z

1

You could create similar structure using reduce method by slicing from first element in array and then get current level from first element in the array.

const data = [["Country", "City", "Zip"], ["CNTR1", "CT1", 101], ["CNTR1", "CT2", 102], ["CNTR2", "CT3", 103]]

function build(data, level) {
  return data.slice(1).reduce((r, a) => {
    a.reduce((ac, e, i, arr) => {
      ac.level = data[0][i]
      if (arr[i + 1]) ac[e] = (ac[e] || {})
      else ac.value = e;
      return ac[e];
    }, r)
    return r
  }, {})
}

const result = build(data);
console.log(result)

answered Dec 2, 2019 at 21:09

Nenad Vracar

122k16 gold badges160 silver badges184 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ncifra Over a year ago

The above will overwrite the last level keys. Check it with this array: const data = [["Country", "City", "Zip"], ["CNTR1", "CT1", 101], ["CNTR1", "CT1", 102], ["CNTR2", "CT3", 103]] The result will be:

{   "level": "Country",   "CNTR1": {     "level": "City",     "CT1": {       "level": "Zip",       "value": 102     }   },   "CNTR2": {     "level": "City",     "CT3": {       "level": "Zip",       "value": 103     }   } }

The 101 Zip code will be lost.

Nina Scholz · Accepted Answer · 2019-12-03 10:36:24Z

0

You could reduce the array and build the wanted structure by reducing the nested arrays. and retuning the inner items array.

var data = [["Country", "City", "Zip"], ["CNTR1", "CT1", 101], ["CNTR1", "CT2", 102], ["CNTR2", "CT3", 103]],
    result = data.reduce((r, [...a], i, { [0]: header }) => {
        var last = a.pop();
        if (i === 1) r = { level1: header[0], items: [] };
        a.reduce((items, k, i) => {
            var temp = items.find(q => k in q);
            if (!temp) items.push(temp = { [k]:{ ['level' + (i + 2)]: header[i + 1], items: [] } });
            return temp[k].items;
        }, r.items).push(last);
        return r;
    });

console.log(result);

.as-console-wrapper { max-height: 100% !important; top: 0; }

edited Dec 3, 2019 at 10:36

answered Dec 2, 2019 at 21:58

Nina Scholz

388k26 gold badges367 silver badges417 bronze badges

7 Comments

Ncifra Over a year ago

Yes, thank you, this was it. Do you think this structure is good for the purpose specified? I am thinking that maybe the index in the level key is redundant, since that can be easily retrieved during the iteration, but other than that I think that it helps in deep traversing dependent elements that won't need any querying for the ones in the same level.

Nina Scholz Over a year ago

i don't know how excel works for this purpose. but if this is the structure, you need, go with it. (i personally would choose something without changing properties, like level and use a format which works without retrieving the key with a more flat data structure.)

Ncifra Over a year ago

Well, Excel is more of a data provider, but in the end, the array as provided in the beginning of the question is the data retrieved. The purpose would be to derive a left to right parent-child relationship, and create a sort of tree while also maintaining the names of the levels. Can you provide a simple object of the flat structure you mention? Thanks

Nina Scholz Over a year ago

maybe something like this:

{ level: 1, value: "Country", items: [{ level: 2, value: "City", key: "CNTR1", items: [{ level: 3, key: "CT1", value: "Zip", items: [101] }, { level: 3, key: "CT2", value: "Zip", items: [102] }] }, { level: 2, key: "CNTR2", value: "City", items: [{ level: 3, key: "CT3", value: "Zip", items: [103] }] }] }

Ncifra Over a year ago

I understand, so instead of having the key as a property, have it inside the object, and it is more structured. Thanks.

|

Collectives™ on Stack Overflow

Transform excel 2D array into nested properties object with column names included

2 Answers 2

1 Comment

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related