Dynamically add values from a csv in an html table using javascript/jquery

I have a dynamically generated CSV file from another vendor that I am puling in and need to show in a table on my site. The problem is I need to be able to manipulate the data from the CSV so it can show the corrected values in the html table. In the end I need the HTML table to just display the Products, not the Mixed Sets.

I am using jquery and the papaparse library to get the data and parse it in a table in html. My codepen is here:

https://codepen.io/BIGREDBOOTS/pen/YQojww

The javascript pulls the initial csv values and display in a table, but I can't figure out how to to add together the values. If there is a better way of going about this, like converting the CSV to some other form of data like JSON, That is fine too.

My CSV looks like this:

product_title,product_sku,net_quantity
Product 1,PRD1,10
Product 2,PRD2,20
Product 3,PRD3,30
Mixed Set 1,MIX1,100
Mixed Set 2,MIX2,50
Mixed Set 3,MIX3,75

The Javascript I am using is:

    function arrayToTable(tableData) {
        var table = $('<table></table>');
        $(tableData).each(function (i, rowData) {
            var row = $('<tr class="rownum-' + [i] + '"></tr>');
            $(rowData).each(function (j, cellData) {
                row.append($('<td class="' + [i] + '">'+cellData+'</td>'));
            });
            table.append(row);
        });
        return table;
    }

    $.ajax({
        type: "GET",
        url: "https://cdn.shopify.com/s/files/1/0453/8489/t/26/assets/sample.csv",
        success: function (data) {
            $('body').append(arrayToTable(Papa.parse(data).data));
        }
    });

My rules for the mixed set:

Mixed Set 1 should add 100 to Product 1 and Product 2.
Mixed Set 2 should add 50 to Product 2 and Product 3.
Mixed Set 3 should add 75 to Product 1, Product 2 and Product 3.

I'd like to end up with Just the products output, and the correct numbers added to the formula. The end result would be a table with Product 1 = 185, Product 2 = 245, and Product 3 = 155.

While it would be even better if the top THEAD elements were in a "th", It's fine if that is too complicated.

<table>
    <tbody>
        <tr class="rownum-0">
            <td class="0">product_title</td>
            <td class="0">product_sku</td>
            <td class="0">net_quantity</td>
        </tr>
        <tr class="rownum-1">
            <td class="1">Product 1</td>
            <td class="1">PRD1</td>
            <td class="1">185</td>
        </tr>
        <tr class="rownum-2">
            <td class="2">Product 2</td>
            <td class="2">PRD2</td>
            <td class="2">245</td>
        </tr>
        <tr class="rownum-3">
            <td class="3">Product 3</td>
            <td class="3">PRD3</td>
            <td class="3">155</td>
        </tr>
    </tbody>
</table>

Solution

Without knowing the size of the dataset you're working with, I suggest you first iterate through all the CSV dataset in order to populate a list of products with the correct values, and then iterate again on that to populate your HTML table:

function datasetToMap(data) {
    var ret = {};
    //Initialize a map with all the product rows
    $(data).each(function(index, row) {
        if(row[0].startsWith("Product")) {
            ret[row[1]] = row; //Using the SKU as the key to the map
        }
    });

    //Apply your mixed sets rules to the elements in the ret array        
    $(data).each(function(index, row) {
        if(row[1] === "MIX1") {
            ret["PRD1"][2] += 100;
            ret["PRD2"][2] += 100;
        }
        //Do the same for Mixed sets 2 and 3
    });
    return ret;
}

function appendMapToTable(map) {
    var $table = $('#my-table');
    Object.keys(map).forEach(function(key, i) {
        var rowData = map[key];
        var row = $('<tr class="rownum-' + [i] + '"></tr>');
        $(rowData).each(function (j, cellData) {
            row.append($('<td class="' + [j] + '">'+cellData+'</td>'));
        });
        $table.append(row);
    });
}

$.ajax({
    type: "GET",
    url: "https://cdn.shopify.com/s/files/1/0453/8489/t/26/assets/sample.csv",
    success: function (data) {
        appendMapToTable(datasetToMap(Papa.parse(data).data));
    }
});

Note that this expects a table with id my-table to be already present in your HTML: you could manually parse the first row of your CSV data to add the table headings.

Also note that if your CSV dataset is very big this is definitely not an optimal solution, since it requires iterating through all its lines twice and then iterating again through all the list built with computed values.