How to get colums instead of rows #3114

New Issue

Rednas83 · 2024-04-24T14:18:44Z

Rednas83 commented

2024-04-24 14:18:44 +00:00

Something like

Transposed to something like

{
  Colors: ['red', 'blue', 'green'],
  Animals: ['dog', 'horse', 'cat']
}

or

[
  ['Colors', 'red', 'blue', 'green'],
  ['Animals', 'dog', 'horse', 'cat']
}

Something like ![image](/attachments/4b60aed1-be58-435a-93e8-4fdacb9a3504) Transposed to something like ``` { Colors: ['red', 'blue', 'green'], Animals: ['dog', 'horse', 'cat'] } ``` or ``` [ ['Colors', 'red', 'blue', 'green'], ['Animals', 'dog', 'horse', 'cat'] } ```

image.png

2.1 KiB

sheetjs commented

2024-04-24 16:35:30 +00:00

You can manually transpose the data. https://docs.sheetjs.com/docs/demos/math/tensorflow#exporting-datasets-to-a-worksheet shows an example that creates a copy of the array of arrays:

var wb = XLSX.read("Colors,Animals\nred,dog\nblue,horse\ngreen,cat", {type:"string"});
var ws = wb.Sheets.Sheet1;
var data = XLSX.utils.sheet_to_json(ws, { header: 1 });

var aoa = [];
for(var i = 0; i < data.length; ++i) {
  for(var j = 0; j < data[i].length; ++j) {
    if(!aoa[j]) aoa[j] = [];
    aoa[j][i] = data[i][j];
  }
}

This gives you the second form. The first form can be recovered with Object.fromEntries:

var object = Object.fromEntries(aoa.map(r => [r[0], r.slice(1)]))

You can manually transpose the data. https://docs.sheetjs.com/docs/demos/math/tensorflow#exporting-datasets-to-a-worksheet shows an example that creates a copy of the array of arrays: ```js var wb = XLSX.read("Colors,Animals\nred,dog\nblue,horse\ngreen,cat", {type:"string"}); var ws = wb.Sheets.Sheet1; var data = XLSX.utils.sheet_to_json(ws, { header: 1 }); var aoa = []; for(var i = 0; i < data.length; ++i) { for(var j = 0; j < data[i].length; ++j) { if(!aoa[j]) aoa[j] = []; aoa[j][i] = data[i][j]; } } ``` This gives you the second form. The first form can be recovered with `Object.fromEntries`: ```js var object = Object.fromEntries(aoa.map(r => [r[0], r.slice(1)])) ```

🚀 1

Rednas83 commented

2024-04-25 09:48:40 +00:00

Thanks, that worked!

I also rewrote it with map instead of for.

const data = XLSX.utils.sheet_to_json(ws, { header: 1 });
const columns = data[0] as string[]
const lists= Object.fromEntries(
  Object.keys(columns)
  .map((c) => data.map((r: any) => r[c])) // Transpose
  .map((r) => [r[0], r.slice(1)]) // Split into key and values
)

Returning

Perhaps add a transpose option to support it natively?

Thanks, that worked! I also rewrote it with `map` instead of `for`. ``` const data = XLSX.utils.sheet_to_json(ws, { header: 1 }); const columns = data[0] as string[] const lists= Object.fromEntries( Object.keys(columns) .map((c) => data.map((r: any) => r[c])) // Transpose .map((r) => [r[0], r.slice(1)]) // Split into key and values ) ``` Returning ![image](/attachments/d9123e6d-0d0f-4a6d-b91b-033226dcb2c1) Perhaps add a transpose option to support it natively?

image.png

2.2 KiB

image.png

2.2 KiB

Sign in to join this conversation.