These 2D-arrays contain the same information structured differently. How do I convert one to the other?

Question

I have this array of arrays, where each combination of categories is a row and is associated with a number.

var input = [
    ['Season',  'Type',     'Dollars'], // header
    ['Winter',  'Sales',     1000],
    ['Winter',  'Expenses',  400],
    ['Winter',  'Profit',    250],
    ['Spring',  'Sales',     1170],
    ['Spring',  'Expenses',  460],
    ['Spring',  'Profit',    250],
    ['Summer',  'Sales',     660],
    ['Summer',  'Expenses',  1120],
    ['Summer',  'Profit',    300],
    ['Fall',    'Sales',     1030],
    ['Fall',    'Expenses',  540],
    ['Fall',    'Profit',    350]
];

And I have this array of arrays, where each category is an axis.

var desiredOutput = [
    ['Season',  'Sales', 'Expenses', 'Profit'], // header
    ['Winter',  1000,    400,        200],
    ['Spring',  1170,    460,        250],
    ['Summer',  660,     1120,       300],
    ['Fall',    1030,    540,        350]
];

First of all, anyone know if there are different names for these two types of arrays? I frequently have data organized in one way and need to manually switch it to the other in order to graph it or analyze it or something, I just wonder if there's a formal distinction.

I managed to convert input to desiredOutput using this atrocious function:

function restructure(input) {
    var output = [];

    var cat1 = [],
        cat1Lookup = {},
        cat2 = [],
        cat2Lookup = {};

    input.forEach(function (d, i) {
        cat1.push(d[0]);
        cat2.push(d[1]);
    });

    unique(cat1).forEach(function (d, i) {
        var r = [d];

        if (i === 0) {
            output.push([d]);
            return true;
        }

        unique(cat2).forEach(function (d, i) {
            r.push();
        });

        output.push(r);
        cat1Lookup[d] = i;
    });

    unique(cat2).forEach(function (d, i) {
        if (i === 0) return true;
        output[0].push(d);
        cat2Lookup[d] = i;

    });

    input.forEach(function (d, i) {

        if (i === 0) return true;
        var y = cat1Lookup[d[0]];
        var x = cat2Lookup[d[1]];

        output[y][x] = d[2];
    });
    return output;
}

function unique(input) {
    var u = {}, a = [];
    for (var i = 0, l = input.length; i < l; ++i) {
        if (u.hasOwnProperty(input[i])) {
            continue;
        }
        a.push(input[i]);
        u[input[i]] = 1;
    }
    return a;
}

But I'm sure there must be a better way to do it. Can anyone help provide a cleaner solution?

JSFiddle

Answer 1

If the data is consistent, you might get away with some version/s of this:

var temp = input[1][0],
    output = [[input[0][0]],[temp]],
    len = input[1].length,
    j = 1;

for (var i=1; i<input.length; i++){
  if (input[i][0] != temp){
    temp = input[i][0];
    output[++j] = [temp];
  }
  output[j].push(input[i][len-1]);
  if (j == 1){
    output[0].push(input[i][1]);
  }
}

Answer 2

I agree with the above comments and answer that Objects would be better to use than arrays with a label, but if you can't, then here's my way that preserved your formatting:

// helper function
function addIfMissing(array, value) {
    var found = false;
    for(var i = 0; i < array.length; i++)
        if(array[i] === value)
            return array;
    array.push(value);
    return array;
}

function restructure(input) {
    var output = [], headerX = [], headerY = [], xCoor, yCoor;

    // first create non-repeating headers
    headerX.push(input[0][0]);
    headerY.push(input[0][0]);
    for(var i = 1; i < input.length; i++)
        headerX = addIfMissing(headerX, input[i][0]), headerY = addIfMissing(headerY, input[i][1]);

    // put headers into output array
    for(var i = 0; i < headerX.length; i++)
        output.push([headerX[i]]);
    output[0] = headerY;

    // find correct headers on both axes and input data
    for(var i = 1; i < input.length; i++) {
        for(var k = 1; k < headerX.length; k++)
            if(output[k][0] == input[i][0])
                xCoor = k;
        for(var j = 1; j < headerY.length; j++)
            if(output[0][j] == input[i][1])
                yCoor = j;
        output[xCoor][yCoor] = input[i][2];
    }

    return output;
}

Here's it on JSFiddle.net.

Answer 3

I would use Objects, not arrays for this.

And you should not store headers in the same array as the content...

// header: Season, Type, Dollars
    var input = [
        ['Winter',  'Sales',     1000],
        ['Winter',  'Expenses',  400],
        ['Winter',  'Profit',    250],
        ['Spring',  'Sales',     1170],
        ['Spring',  'Expenses',  460],
        ['Spring',  'Profit',    250],
        ['Summer',  'Sales',     660],
        ['Summer',  'Expenses',  1120],
        ['Summer',  'Profit',    300],
        ['Fall',    'Sales',     1030],
        ['Fall',    'Expenses',  540],
        ['Fall',    'Profit',    350]
    ];

    var desired_output = {};

    for(var i in input) {
        var season = input[i][0];
        if(!desired_output.hasOwnProperty(season)) {
            desired_output[season] = {};
        }
        switch(input[i][1]) {
            case 'Sales':
                if(!desired_output[season].hasOwnProperty('sales')) {
                    desired_output[season]['sales'] = 0;
                }
                desired_output[season]['sales'] += input[i][2];
                break;
            case 'Expenses': 
                if(!desired_output[season].hasOwnProperty('expenses')) {
                    desired_output[season]['expenses'] = 0;
                }
                desired_output[season]['expenses'] += input[i][2];
                break;
            case 'Profit':
                if(!desired_output[season].hasOwnProperty('profit')) {
                    desired_output[season]['profit'] = 0;
                }
                desired_output[season]['profit'] += input[i][2];
                break;
            default:

                break; 
        }
    }
    console.log(desired_output);

That will return:

{
"Winter": {
    "sales": 1000,
    "expenses": 400,
    "profit": 250
},
"Spring": {
    "sales": 1170,
    "expenses": 460,
    "profit": 250
},
"Summer": {
    "sales": 660,
    "expenses": 1120,
    "profit": 300
},
"Fall": {
    "sales": 1030,
    "expenses": 540,
    "profit": 350
}
}

UPDATE:

If you still need the values in that specific format,

// header: Season, Type, Dollars
    var input = [
        // ['Season',  'Type',     'Dollars'], // header
        ['Winter',  'Sales',     1000],
        ['Winter',  'Expenses',  400],
        ['Winter',  'Profit',    250],
        ['Spring',  'Sales',     1170],
        ['Spring',  'Expenses',  460],
        ['Spring',  'Profit',    250],
        ['Summer',  'Sales',     660],
        ['Summer',  'Expenses',  1120],
        ['Summer',  'Profit',    300],
        ['Fall',    'Sales',     1030],
        ['Fall',    'Expenses',  540],
        ['Fall',    'Profit',    350]
    ];

    var desired_output_object = {};

    for(var i in input) {
        var season = input[i][0];
        if(!desired_output_object.hasOwnProperty(season)) {
            desired_output_object[season] = {};
        }
        switch(input[i][1]) {
            case 'Sales':
                if(!desired_output_object[season].hasOwnProperty('sales')) {
                    desired_output_object[season]['sales'] = 0;
                }
                desired_output_object[season]['sales'] += input[i][2];
                break;
            case 'Expenses': 
                if(!desired_output_object[season].hasOwnProperty('expenses')) {
                    desired_output_object[season]['expenses'] = 0;
                }
                desired_output_object[season]['expenses'] += input[i][2];
                break;
            case 'Profit':
                if(!desired_output_object[season].hasOwnProperty('profit')) {
                    desired_output_object[season]['profit'] = 0;
                }
                desired_output_object[season]['profit'] += input[i][2];
                break;
            default:

                break; 
        }
    }
    console.log(desired_output_object);


    var desired_output = [];
    for(var o in desired_output_object) {
        var output = desired_output_object[o];
        desired_output.push(output);
    }
    console.log(desired_output);

The result will be:

[{
"sales": 1000,
"expenses": 400,
"profit": 250
}, {
"sales": 1170,
"expenses": 460,
"profit": 250
}, {
"sales": 660,
"expenses": 1120,
"profit": 300
}, {
"sales": 1030,
"expenses": 540,
"profit": 350
}]