Skip to content

Append data to table from json when headers are a mix of the keys and values

I have an array of objects, I want to add that data to an HTML table but I am having trouble appending it to the table as my table structure is slightly unique.

My headers are a mix of the keys and values and I want to be able to get their corresponding value to populate my cells.

My code:

let data = [{Date:"2021-03-22",device:"Phone",vendor:"Apple",value:11},{Date:"2021-03-22",device:"Phone",vendor:"Google",value:10},{Date:"2021-03-22",device:"Tablet",vendor:"Apple",value:45},{Date:"2021-03-22",device:"Tablet",vendor:"Google",value:57},{Date:"2021-03-22",device:"iOT",vendor:"Apple",value:33},{Date:"2021-03-22",device:"iOT",vendor:"Google",value:11},{Date:"2021-03-22",device:"Smart Watch",vendor:"Apple",value:17},{Date:"2021-03-22",device:"Smart Watch",vendor:"Google",value:25},{Date:"2021-03-29",device:"Phone",vendor:"Apple",value:9},{Date:"2021-03-29",device:"Phone",vendor:"Google",value:20},{Date:"2021-03-29",device:"Tablet",vendor:"Apple",value:23},{Date:"2021-03-29",device:"Tablet",vendor:"Google",value:15},{Date:"2021-03-29",device:"iOT",vendor:"Apple",value:11},{Date:"2021-03-29",device:"iOT",vendor:"Google",value:77},{Date:"2021-03-29",device:"Smart Watch",vendor:"Apple",value:38},{Date:"2021-03-29",device:"Smart Watch",vendor:"Google",value:80}];

let ths = ['device', 'vendor', '2021-03-22', '2021-03-29'];
ths.forEach(d => $(`#ths`).append(`<th id='${d}'>${d}</th>`));

data.forEach(d => {
  $(`#table_body`).append(`<tr class='my_rows'></tr>`);
  ['device', 'vendor'].forEach(x => $(`#table_body > tr:last`).append(`<td>${d[x]}</td>`));
  ['value'].forEach(y => {
    $(`.my_rows:last`).append(`<td>${d[y]}</td>`);
  });
});
table,
td,
th {
  border: 1px solid black;
}

#mytable {
  width: 100%;
  border-collapse: collapse;
}
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

<table id='mytable'>
  <thead>
    <tr id='ths'></tr>
  </thead>
  <tbody id='table_body'></tbody>
</table>

As you can see, my table is populating vertically and adding any data to 2021-03-29 and instead of adding all the data to the first date column.

How do I make my data look like:

device vendor 2021-03-22 2021-03-29
Phone Apple 11 10
Phone Google 45 57
Tablet Apple 33 11
Tablet Google 17 25
iOt Apple 9 20
iOt Google 23 15
Smart Watch Apple 11 77
Smart Watch Google 38 80

Answer

well, basically what I would do is transform the data that you have to a something simplier, so we can move from:

[
  {
    "Date": "2021-03-22",
    "device": "Phone",
    "vendor": "Apple",
    "value": 11
  },
  {
    "Date": "2021-03-29",
    "device": "Phone",
    "vendor": "Apple",
    "value": 9
  },
  ....
]

to something like this:

[
  {
    "2021-03-22": "11",
    "2021-03-29": "9",
    "device": "Phone",
    "vendor": "Apple",
    "value": 11
  }
  ....
]

this can be achieved using reduce and convert it into and object and the with Object.values() we can get the array transformed.

here you have the working solution using the same renderer of the table. notice that I merged both items into one, using the date as a key and the value will be the value of that date.

let data = [{
    "Date": "2021-03-22",
    "device": "Phone",
    "vendor": "Apple",
    "value": 11
  },
  {
    "Date": "2021-03-22",
    "device": "Phone",
    "vendor": "Google",
    "value": 10
  },
  {
    "Date": "2021-03-22",
    "device": "Tablet",
    "vendor": "Apple",
    "value": 45
  },
  {
    "Date": "2021-03-22",
    "device": "Tablet",
    "vendor": "Google",
    "value": 57
  },
  {
    "Date": "2021-03-22",
    "device": "iOT",
    "vendor": "Apple",
    "value": 33
  },
  {
    "Date": "2021-03-22",
    "device": "iOT",
    "vendor": "Google",
    "value": 11
  },
  {
    "Date": "2021-03-22",
    "device": "Smart Watch",
    "vendor": "Apple",
    "value": 17
  },
  {
    "Date": "2021-03-22",
    "device": "Smart Watch",
    "vendor": "Google",
    "value": 25
  },
  {
    "Date": "2021-03-29",
    "device": "Phone",
    "vendor": "Apple",
    "value": 9
  },
  {
    "Date": "2021-03-29",
    "device": "Phone",
    "vendor": "Google",
    "value": 20
  },
  {
    "Date": "2021-03-29",
    "device": "Tablet",
    "vendor": "Apple",
    "value": 23
  },
  {
    "Date": "2021-03-29",
    "device": "Tablet",
    "vendor": "Google",
    "value": 15
  },
  {
    "Date": "2021-03-29",
    "device": "iOT",
    "vendor": "Apple",
    "value": 11
  },
  {
    "Date": "2021-03-29",
    "device": "iOT",
    "vendor": "Google",
    "value": 77
  },
  {
    "Date": "2021-03-29",
    "device": "Smart Watch",
    "vendor": "Apple",
    "value": 38
  },
  {
    "Date": "2021-03-29",
    "device": "Smart Watch",
    "vendor": "Google",
    "value": 80
  }
];

let ths = ['device', 'vendor', '2021-03-22', '2021-03-29'];
ths.forEach(d => $(`#ths`).append(`<th id='${d}'>${d}</th>`));


const reducer = (accum, cv) => {
  const key = cv.vendor + cv.device;
  // if there isnt an entry we create a new one.
  if (!accum[key]) {
    accum[key] = {
      device: cv.device,
      vendor: cv.vendor,
      [cv.Date]: cv.value
    };
  } else {
    // we have an entry we just add the value for the date.
    accum[key][cv.Date] = cv.value
  }

  return accum;


}

const obj = data.reduce(reducer, {});
const newForm = Object.values(obj);

newForm.forEach(d => {
  $(`#table_body`).append(`<tr class='my_rows'></tr>`);
  ['device', 'vendor'].forEach(x => $(`#table_body > tr:last`).append(`<td>${d[x]}</td>`));
  ['2021-03-22', '2021-03-29'].forEach(y => {
    $(`.my_rows:last`).append(`<td>${d[y]}</td>`);
  });
})
table,
td,
th {
  border: 1px solid black;
}

#mytable {
  width: 100%;
  border-collapse: collapse;
}
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

<table id='mytable'>
  <thead>
    <tr id='ths'></tr>
  </thead>
  <tbody id='table_body'></tbody>
</table>