How to build an html table using javascript and having two levels of column headers

Question

I need help using plain java script in building an HTML table that pivots my data.

My data has State, Year, Month, and Sales. It looks like this:

state  year  month  sales
FL     2018    1     100
Fl     2018    2     200
Fl     2018    3     250
FL     2019    1     200
Fl     2019    2     225
Fl     2019    3     175
etc. for other states, years, and months.

I would like to build a table like this. I only show the first 3 months of a year for simplicity:

   Year  2018  2018  2018  2019 2019  2019
   Month   1     2     3     1    2     3 
State
  FL      100   200   250   200  225   175

I know how to build a simple table using java script. So I do not need advice on the normal code, functions, etc. to use. I need help with the logic of pivoting the data and building the two header rows of year and month.

My two issues are:.

1. how to get the data into the correct cell for a given state and month,. i.e. pivoting the data.
2. how to generate the labels for the year and the month on the top of every data column in the table.

In Pandas this would be very easy to do, but we need it in java script so it can be done at the desktop level, not at the server level.

Thanks for any help.

Answer 1

Data transform

• With Array#reduce I collect all dates (in format YYYY_MM) for which there is data.
• Again with reduce I collect the data for all states seperated in a new object.

First 2 rows

• Getting the table and add the header-cells for the first 2 rows with all collected years/months.

Other rows with data

• For each state there is to create one row in the table.
• For the first column can the state taken out of the dataobject.
• For the other columns iterate with foreach over the dates and add for every date a new column.
• If for this state exists an entry in the data for this date (month and year) than write the sale-value in the innerHTML of the cell.

Note: The data in the array does not need to be sorted by state or date, this will be done through the script. The dates needs not to be complete.

Here is the code for playing https://jsfiddle.net/efp8cL53/4/

function createSalesTable(data, id) {
    let table = document.getElementById(id);
    
    let dates = data.reduce((acc, cur) => {
        let date = cur.year * 100 + cur.months;
        if (!acc.includes(date))
             acc.push(date);
        return acc;
    }, []);
    dates.sort();
    
    let dataAll = data.reduce((acc, cur) => {
        if (!acc[cur.state])
            acc[cur.state] = [];
        acc[cur.state].push(cur);
        return acc;
    }, {});
    
    // First line
    let tr = document.createElement('TR');
    let th = document.createElement('TH');
    th.innerHTML = 'Year';
    tr.appendChild(th);
        
    dates.forEach(date => {
        th = document.createElement('TH');
        th.innerHTML = parseInt(date/100);
        tr.appendChild(th);
    });
    table.appendChild(tr);
    
    // Second Line
    tr = document.createElement('TR');
    th = document.createElement('TH');
    th.innerHTML = 'Month';
    tr.appendChild(th);

    dates.forEach(date => {
        th = document.createElement('TH');
        th.innerHTML = parseInt(date%100);
        tr.appendChild(th);
    });
    table.appendChild(tr);
    
    // Table
    Object.entries(dataAll).forEach(([state, entries]) => {
        tr = document.createElement('TR');
        let th = document.createElement('TH');
        th.innerHTML = "Sales - " + state;
        tr.appendChild(th);
  
        dates.forEach(date => {
            let td = document.createElement('TD');
            let entry = entries.find(elem => elem.year === parseInt(date/100) && elem.months===date%100);
            if ( entry!==undefined )
                td.innerHTML = entry.sales;
            tr.appendChild(td);
        });
        table.appendChild(tr);
    });
    table.appendChild(tr);
}


let data = [
    {state: 'FL', year: 2018, months: 1, sales: 100},
    {state: 'FL', year: 2018, months: 2, sales: 100},
    {state: 'FL', year: 2018, months: 3, sales: 100},
    {state: 'FL', year: 2019, months: 1, sales: 100},
    {state: 'FL', year: 2019, months: 2 ,sales: 100},
    {state: 'FL', year: 2019, months: 3, sales: 100},
    {state: 'FL', year: 2017, months: 12, sales: 333},
    {state: 'CH', year: 2018, months: 2, sales: 700},
    {state: 'CH', year: 2018, months: 5, sales: 50},
    {state: 'D', year: 2018, months: 7, sales: 5000},
    {state: 'D', year: 2018, months: 3, sales: 300},
    {state: 'D', year: 2019, months: 2, sales: 700}
];

createSalesTable(data, 'sales');

table, td, th { border: solid 1px black; }

<table id='sales'></table>

Answer 2

You can use D3 for pivoting. It has a .nest() function lets you group data elements by the specified key.

HTML

<div>
    <table id="myTable"></table>
</div>

JS

var dataArray = [];
dataArray.push({
  "STATE": "FL",
  "YEAR": 2018,
  "MONTH": 1,
  "SALES": 100
});
dataArray.push({
  "STATE": "FL",
  "YEAR": 2018,
  "MONTH": 2,
  "SALES": 200
});
dataArray.push({
  "STATE": "FL",
  "YEAR": 2018,
  "MONTH": 3,
  "SALES": 250
});

dataArray.push({
  "STATE": "FL",
  "YEAR": 2019,
  "MONTH": 1,
  "SALES": 200
});
dataArray.push({
  "STATE": "FL",
  "YEAR": 2019,
  "MONTH": 2,
  "SALES": 225
});
dataArray.push({
  "STATE": "FL",
  "YEAR": 2019,
  "MONTH": 3,
  "SALES": 175
});

dataArray.push({
  "STATE": "TX",
  "YEAR": 2018,
  "MONTH": 1,
  "SALES": 300
});
dataArray.push({
  "STATE": "TX",
  "YEAR": 2018,
  "MONTH": 2,
  "SALES": 350
});
dataArray.push({
  "STATE": "TX",
  "YEAR": 2018,
  "MONTH": 3,
  "SALES": 280
});

dataArray.push({
  "STATE": "LA",
  "YEAR": 2017,
  "MONTH": 2,
  "SALES": 100
});
dataArray.push({
  "STATE": "LA",
  "YEAR": 2017,
  "MONTH": 3,
  "SALES": 150
});

dataArray.push({
  "STATE": "NM",
  "YEAR": 2016,
  "MONTH": 2,
  "SALES": 300
})

//Sort the data array
dataArray = dataArray.sort(function(a, b) {
  return (a['YEAR'] == b['YEAR'] ? ((a['MONTH'] == b['MONTH']) ? ((a['STATE'] > b['STATE']) ? 1 : -1) : (a['MONTH'] - b['MONTH'])) : (a['YEAR'] - b['YEAR']))
});

//Nesting for the Years and Months headers i.e. group by Year and then Month
var nestedArrayForHeaders = d3.nest()
  .key(d => d['YEAR'])
  .key(d => d['MONTH'])
  .entries(dataArray);

//Nesting for the data i.e. group by STATE
var nestedArrayForData = d3.nest()
  .key(d => d['STATE'])
  .entries(dataArray);

var yearsRow = $('<tr></tr>');
var monthsRow = $('<tr></tr>');

$(yearsRow).append($('<td></td>').addClass('rowHeader').text('Year'));
$(monthsRow).append($('<td></td>').addClass('rowHeader').text('Month'));

/*START: Rendering the Years and Months rows*/
nestedArrayForHeaders.forEach(function(yearObj) {
  yearObj['values'].forEach(function(monthObj) {
    $(yearsRow).append($('<td></td>').addClass('columnHeader').text(yearObj['key']));
    $(monthsRow).append($('<td></td>').addClass('columnHeader').text(monthObj['key']));
  });

  $('#myTable').append(yearsRow);
  $('#myTable').append(monthsRow);
});
/*END: Rendering the Years and Months rows*/

/*START: Rendering the State rows*/
nestedArrayForData.forEach(function(stateObj){
    var stateRow = $('<tr></tr>');
  $(stateRow).append($('<td></td>').addClass('rowHeader').text(stateObj['key']));

  nestedArrayForHeaders.forEach(function(yearObj) {
    yearObj['values'].forEach(function(monthObj) {
            var currDataObj = stateObj['values'].find(d=>{return d['YEAR']==yearObj['key'] && d['MONTH']==monthObj['key']});
        if(currDataObj){
            $(stateRow).append($('<td></td>').text(currDataObj['SALES']));
        }
        else{
        $(stateRow).append($('<td></td>').text('-'));
        }
    });

  });
 
  $('#myTable').append(stateRow);
});
/*END: Rendering the State rows*/

CSS:

body {
  padding: 20px;

}
#myTable{
  border-collapse: collapse;
}
#myTable td{
  border: 1px solid black;
  border-collapse: collapse;
  color: black;
  padding:4px;
  text-align: center;
}
#myTable td.rowHeader{
  color: red;
}

#myTable td.columnHeader{
  color: blue;
}

You can play around with it [here][1].


  [1]: http://jsfiddle.net/bn4qvkd9/74/