Organzing data with Javascript into a table

Question

I have about 5k in documents that I need to sort through and organize, pulling from mongodb, through express, and out to a ejs template. I have been able to get the documents to a ejs template successfully, but I'm having a tough time on how to tackle the second part of my project- Organizing the data.

Below is an example of how my data looks. My objective is to list all the defectlocations on the far left column (there about 30 in total), and count how many times defectlocation occur according to each year and month. I am not against using frameworks or jquery. The only thing I can think of is assigning a function to each cell which iterates over the array to see if it matches requirements for that cell. (but this seems like it goes against what programming is meant to be). Eventually, I would like to add graphs, but that seems really far-fetched at this point. One thing to add- this is not the only date range I will be using, they stretch as far back as 2012 up to 2017.

  [{
    "_id": "59cee5ce8ffdc0134854f0c1",
    "repairorder": 7192822,
    "month": 2,
    "year": 2015,
    "defectlocation": "MB"
  }, {
    "_id": "59cee5ce8ffdc0134854f0c2",
    "repairorder": 7192822,
    "month": 5,
    "year": 2015,
    "defectlocation": "COVER/HOUSING"
  }, {
    "_id": "59cee5ce8ffdc0134854f0c3",
    "repairorder": 7192822,
    "month": 2,
    "year": 2015,
    "defectlocation": "MB"
  }, {
    "_id": "59cee5ce8ffdc0134854f0c5",
    "repairorder": 7192822,
    "month": 3,
    "year": 2015,
    "defectlocation": "TOUCH PAD"
  }, {
    "_id": "59cee5ce8ffdc0134854f0c6",
    "repairorder": 7192822,
    "month": 4,
    "year": 2015,
    "defectlocation": "MB"
  }]

Below is how I need it to display:

  -----------------------------------------------------------------------
  Defect Location | 01-2015 |  02-2015 |  03-2015 |  04-2015 |  05-2015 |
  -----------------------------------------------------------------------
  MB              |         |    2     |          |    1     |          |
  -----------------------------------------------------------------------
  Touch Pad       |         |          |     1    |          |          |
  -----------------------------------------------------------------------
  Cover/ Housing  |         |          |          |          |     1    |
  -----------------------------------------------------------------------
  TOTAL           |         |     2    |     1    |     1    |     1    |

score 1 · Answer 1 · answered Oct 06 '17 at 01:12

The main idea is to re-build your data into a data structure that you can easily use to build your ideal table.

Let's change your array of objects into a nested object:

var deflects = {
    'MB': {
        '02-2015': 2,
        '04-2015': 1
    },
    'TOUCH PAD':  {
        '03-2015': 1
    }
    'COVER/HOUSING': {
        '05-2015': 1
    },
    'TOTAL': {
        '02-2015': 2,
        '03-2015': 1,
        '04-2015': 1,
        '05-2015': 1
    }
};

var data = [{
    "_id": "59cee5ce8ffdc0134854f0c1",
    "repairorder": 7192822,
    "month": 2,
    "year": 2015,
    "defectlocation": "MB"
  }, {
    "_id": "59cee5ce8ffdc0134854f0c2",
    "repairorder": 7192822,
    "month": 5,
    "year": 2015,
    "defectlocation": "COVER/HOUSING"
  }, {
    "_id": "59cee5ce8ffdc0134854f0c3",
    "repairorder": 7192822,
    "month": 2,
    "year": 2015,
    "defectlocation": "MB"
  }, {
    "_id": "59cee5ce8ffdc0134854f0c5",
    "repairorder": 7192822,
    "month": 3,
    "year": 2015,
    "defectlocation": "TOUCH PAD"
  }, {
    "_id": "59cee5ce8ffdc0134854f0c6",
    "repairorder": 7192822,
    "month": 4,
    "year": 2015,
    "defectlocation": "MB"
}];

function pad(n, width, z) {
     z = z || '0';
    n = n + '';
    return n.length >= width ? n : new Array(width - n.length + 1).join(z) + n;
}

// PART 1: create object i.e. dictionary

// create a dictionary where each key is the defectlocation
// and its value is another dictionary;

// each inner dictionary will have a key for each date found
// and its value as a counter
var defects = {};

// let's create another inner dictionary for tallying
defects.TOTAL = {};

data.forEach(function (d) {
    if (!defects.hasOwnProperty(d.defectlocation)) {
        defects[d.defectlocation] = {};
    }

    var date = pad(d.month, 2) + '-' + d.year;
    if (!defects[d.defectlocation].hasOwnProperty(date)) {
        defects[d.defectlocation][date] = 0;
    }
    defects[d.defectlocation][date]++;
  
    if (!defects.TOTAL.hasOwnProperty(date)) {
        defects.TOTAL[date] = 0;
    }
    defects.TOTAL[date]++;
});

var dates = Object.keys(defects.TOTAL).sort();

// you would pass deflects and dates into your view

// PART 2: build the table (view)

var html = '<table>';
html += '<tr>';
html += '<th>Defect Location</th>';
dates.forEach(function (date) {
     html += '<th>' + date + '</th>';
});
html += '</tr>';

['MB', 'TOUCH PAD', 'COVER/HOUSING', 'TOTAL'].forEach(function (location) {
    html += '<tr>';
    html += '<td>' + location + '</td>';
    dates.forEach(function (date) {
        html += '<td>' + (defects[location][date] || '') + '</td>';
    });
    html += '</tr>';
})

html += '</table>';

document.getElementById('preview').innerHTML = html;

table {
  border-collapse: collapse;
}
th, td {
  border: 1px solid #000;
}
th:first-child {
  text-align: left; 
}
td:not(:first-child) {
  text-align: center;
}

<div id="preview"></div>

Although, the HTML generator in PART 2 is not in EJS format, you can easily build it following the same logic. PART 1 is the important part.

Also, padding logic taken from this answer.

Thank you! this works really well. I am getting a better understanding of how it works by commenting everything. — gfunkjeff, Oct 06 '17 at 22:36
Hey I gotta thank you for your help on this, I ended up breaking your code down and learned a ton from it which helped me create my entire application. — gfunkjeff, Nov 16 '17 at 05:12

CoolestUsername · Accepted Answer · 2017-10-07T00:31:23.797

It sounds like your question is how to organize the data to count the instances of each defect location within each time slot. This will work. You can shorten this but I tried to use easy to read js. From the end point you can use any table library such as datatables.net or manually create an html table as Mikey's answer demonstrates. Hope this helps.

*** Update: I had initially overlooked the TOTAL row. I've updated my answer to include it (and as the final row in the array). I also added sorting the columns first by year and then by month (because your example of desired end result displays that way) and I separated the code into two functions in hope of making it a little more readable.

var data = [{
    "_id": "59cee5ce8ffdc0134854f0c1",
    "repairorder": 7192822,
    "month": 2,
    "year": 2015,
    "defectlocation": "MB"
  }, {
    "_id": "59cee5ce8ffdc0134854f0c2",
    "repairorder": 7192822,
    "month": 5,
    "year": 2015,
    "defectlocation": "COVER/HOUSING"
  }, {
    "_id": "59cee5ce8ffdc0134854f0c3",
    "repairorder": 7192822,
    "month": 2,
    "year": 2015,
    "defectlocation": "MB"
  }, {
    "_id": "59cee5ce8ffdc0134854f0c5",
    "repairorder": 7192822,
    "month": 3,
    "year": 2015,
    "defectlocation": "TOUCH PAD"
  }, {
    "_id": "59cee5ce8ffdc0134854f0c6",
    "repairorder": 7192822,
    "month": 4,
    "year": 2015,
    "defectlocation": "MB"
  }];

  var tableData = {};
  var totalRow = {};
  var allCols = [];
  var asArray = [];

  tallyInstances();
  prepForTable();

  function tallyInstances () {
    var i;
    var monthDate;
    for (i = 0; i < data.length; i++) {
      monthDate = data[i].month.toString() + '-' + data[i].year.toString();
      allCols.indexOf(monthDate) < 0 ? allCols.push(monthDate) : null;
      if (!tableData[data[i].defectlocation]) {
        tableData[data[i].defectlocation] = {}; // if our tableData object doesn't have a property for this defect location yet then add it and make it an object
      }
      if (tableData[data[i].defectlocation][monthDate]) {
        tableData[data[i].defectlocation][monthDate] ++; // if this defect location object has a property for this year/month combination already then increment it's value by one
      } else {
        tableData[data[i].defectlocation][monthDate] = 1; // if this defect location object does not have a property for this year/month combination yet then create the property and give it a value of 1
      }
      totalRow[monthDate] ? totalRow[monthDate] ++ : totalRow[monthDate] = 1; // ternary operator saying if the totalRow object already has a property for this year/month combination then increment it's value by one, otherwise create it and give it a value of 1
    }
  }

  function prepForTable () {
    allCols.sort(function(a, b) {
      var aParts = a.split("-");
      var bParts = b.split("-");
      var x = {
        month : aParts[0],
        year  : aParts[1]
      };
      var y = {
        month : bParts[0],
        year  : bParts[1]
      };
      var n = x.year - y.year;
      if (n !== 0) {
        return n;
      }
      return x.month - y.month;
    });
    var keys = Object.keys(tableData);
    var e;
    var rowObj;
    for (e = 0; e < keys.length; e++) {
      rowObj = {};
      rowObj["Defect Location"] = keys[e];
      var a;
      for (a = 0; a < allCols.length; a++) {
        rowObj[allCols[a]] = tableData[keys[e]][allCols[a]] ? tableData[keys[e]][allCols[a]] : '';
      }
      asArray.push(rowObj);
    }
    rowObj = {};
    rowObj["Defect Location"] = "TOTAL";
    var o;
    for (o = 0; o < allCols.length; o++) {
      rowObj[allCols[o]] = totalRow[allCols[o]] ? totalRow[allCols[o]] : '';
    }
    asArray.push(rowObj);
  }

  console.log("tableRows: ", JSON.stringify(asArray, null, 4));
  /*
 tableRows:  [
    {
        "Defect Location": "MB",
        "2-2015": 2,
        "3-2015": "",
        "4-2015": 1,
        "5-2015": ""
    },
    {
        "Defect Location": "COVER/HOUSING",
        "2-2015": "",
        "3-2015": "",
        "4-2015": "",
        "5-2015": 1
    },
    {
        "Defect Location": "TOUCH PAD",
        "2-2015": "",
        "3-2015": 1,
        "4-2015": "",
        "5-2015": ""
    },
    {
        "Defect Location": "TOTAL",
        "2-2015": 2,
        "3-2015": 1,
        "4-2015": 1,
        "5-2015": 1
    }
]

*/

This nails it on the head! thanks for making your code so explicit. I'm still learning all the intricacies of how the language works and seems i'll be learning basics for a while. Like Mikey's code, i'm commenting each line to better understand the process. The only part i'm having a issue understanding is the if statements which end with = {};, ++; and =1; — gfunkjeff, Oct 06 '17 at 22:46
@JeffFasulkey in addition to commenting out lines, if you're not doing so already, pepper in console logs as well to better understand what's going on. I'll go back and see if I can add in some comments to make those lines you mentioned easier to understand. — CoolestUsername, Oct 07 '17 at 00:23
I cant thank you enough for the help here. I had a question: All my "data" is coming from mongoose as an object and rendering into a ejs template. Then I convert that data object into json and pass it into the code you provided. My question is, do I even need to convert it? I'm sure there are changes to be made if the answer is no, but I want to know If i'm taking too many steps to reach the end result. — gfunkjeff, Oct 12 '17 at 17:34
@JeffFasulkey it's really hard to give you a quality answer without seeing the code. You wouldn't want to JSON.stringify() it because then you wouldn't be able to interact with it as a javascript object. If it's already a JSON string then you may be JSON.parse()'ing it, which you would need to continue doing. I hope I'm not misunderstanding your question, feel free to comment back if you think I am or if you need further assistance. — CoolestUsername, Oct 14 '17 at 00:31
This project has taken on new life, lets say I wanted to total the columns going horizontallly, for instance the total number of MB in 2015. I have hit a hard wall with getting something to work. Ive gone back to the basics of arrays, and I'm trying to rewrite the project, because even though your code really helped, I don't know if i'm able to grasp it the same way as if is started from scratch. if that makes any sense — gfunkjeff, Oct 17 '17 at 00:06

Organzing data with Javascript into a table

2 Answers2