2
\$\begingroup\$

I have an XLSX file that contains a hierarchical list of categories. Each category has a basic query associated with it. I need to flatten these categories which involves combining the categories. I believe it easiest to show a picture of what I'm referring to.

Here is an XLSX file that I receive: enter image description here

These categories need to be flattened, so the first one would have the following label:

Satisfaction - Committed - Helpfulness

The second label would be:

Satisfaction - Committed - Cleanliness

The third label would be:

Satisfaction - Technology

The fourth label would be:

Happiness

The queries also need to be combined and the commas should be switched to "or."

Here is what the query should look like for each

  1. Satisfaction - Committed - Helpfulness
    • (( satisfied or satisfaction )) and (( committed or comitted or commited or committing ) and ( very ) and not ( committee )) and (( helpful or helpful ))
  2. Satisfaction - Committed - Cleanliness
    • (( satisfied or satisfaction )) and (( committed or comitted or commited or committing ) and ( very ) and not ( committee )) and (( cleanliness ))
  3. Satisfaction - Technology
    • (( satisfied or satisfaction )) and (( tech or technology ) and not ( techno ))
  4. Happiness
    • ( happy ) and not ( upset )

My code uses recursion to loop through each category and build the query. The base case is reaching a "leaf node." When it reaches a leaf node, the query is added to an array.

const XLSX = require('xlsx');

exports.parseFile = function(worksheet) {

    // Convert the XLSX file into JSON
    let xlsxJson = XLSX.utils.sheet_to_json(worksheet);

    let operations = [];
    // Creating an object with a counter to keep track of the number of categories processed.
    // We create it as an object so that the counter will be pass by reference.
    let indexObj = { counter: 0 };
    let n = Object.keys(xlsxJson).length;
    while (indexObj.counter < n) {
        parseCategoryRecursive(xlsxJson, Object.keys(xlsxJson), "", "", indexObj, operations);
        indexObj.counter = indexObj.counter + 1;
    }

    console.log(operations);
};

/**
* Parses each category recursively.
*/
var parseCategoryRecursive = function(xlsxJson, keys, query, label, indexObj, operations) {

    let category = xlsxJson[keys[indexObj.counter]];


    // Build label
    let additionalLabel = category[Object.keys(category)[0]];
    label += (label.length > 0) ? " - " : "";
    label += additionalLabel;

    // Determine if this category has a child
    let hasMoreChildren = isNextCategoryChild(xlsxJson, keys, indexObj.counter, category);

    // Build query
    let additionalQuery = buildQuery(category);
    if (additionalQuery.length > 0) {

        // If there is not a parent query, and the current cateogry has no children,
        // just add the query.
        if (query.length == 0 && !hasMoreChildren) {
            query += additionalQuery;
        } else {
            // If there is already a parent query, add an "and"
            query += (query.length > 0) ? " and " : "";
            query += "(" + additionalQuery + ")";

        }
    }
    /* 
    * The Base Case
    */
    if (!hasMoreChildren) {
        operations.push(query);
    }

    /*
    * If "hasMoreChildren" is true, then the current category is a parent category,
    * continue recursing through the children.
    */
    while (hasMoreChildren) {
        indexObj.counter = indexObj.counter + 1;
        parseCategoryRecursive(xlsxJson, keys, query, label, indexObj, operations);
        hasMoreChildren = isNextCategoryChild(xlsxJson, keys, indexObj.counter, category);
    }

};

var isNextCategoryChild = function(xlsxJson, keys, index, category) {

    if (index + 1 >= keys.length) {
        return false;
    }

    let currentCategory = Object.keys(category)[0];
    let nextCategory = Object.keys(xlsxJson[keys[index + 1]])[0];

    return (currentCategory < nextCategory);

};

var buildQuery = function(category) {

    let query = "";

    if (category.hasOwnProperty("Keywords")) {
        query = category["Keywords"].replace(/,/g, ' or');
        query = "( " + query + " )";
    }
    if (category.hasOwnProperty("And Words")) {
        query += " and ( " + category["And Words"].replace(/,/g, ' or') + " )";
    }
    if (category.hasOwnProperty("Not Words")) {
        if (query.length > 0) {
            query += " and";
        }
        query += " not ( " + category["Not Words"].replace(/,/g, ' or') + " )";
    }

    return query;
};

I'm looking mostly to get feedback on the algorithm/recursion I have. I had to create a "pass-by-reference" counter which seemed like a bit of a code smell to me, but I found it to be much easier.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.