CODEWITHSUNDEEP
javascriptjqueryjson
Ponyboy
Ponyboy

Reputation: 986

Restructure and Condense JSON Object

There are tons of post about formatting JSON, but I can't find one that fits my strange circumstance... here's my data source:

data = [{ "LoanOfficer": "Brett", "Year": 2014, "Month": 10, "FundedVolume": 304032.0000, "FundedUnits": 2.0000, "NewAppUnits": 2.0000, "RateLockUnits": 1.0000 },
    { "LoanOfficer": "Brett", "Year": 2014, "Month": 11, "FundedVolume": 179450.0000, "FundedUnits": 1.0000, "NewAppUnits": 1.0000, "RateLockUnits": 3.0000 },
    { "LoanOfficer": "Carlos", "Year": 2014, "Month": 10, "FundedVolume": 576615.0000, "FundedUnits": 3.0000, "NewAppUnits": 5.0000, "RateLockUnits": 3.0000 },
    { "LoanOfficer": "Carlos", "Year": 2014, "Month": 11, "FundedVolume": 341303.0000, "FundedUnits": 2.0000, "NewAppUnits": 5.0000, "RateLockUnits": null },
    { "LoanOfficer": "Carlos", "Year": 2014, "Month": 12, "FundedVolume": null, "FundedUnits": null, "NewAppUnits": null, "RateLockUnits": 1.0000 },
    { "LoanOfficer": "Elaine", "Year": 2014, "Month": 10, "FundedVolume": null, "FundedUnits": null, "NewAppUnits": 1.0000, "RateLockUnits": 1.0000 },
    { "LoanOfficer": "Elaine", "Year": 2014, "Month": 11, "FundedVolume": 348500.0000, "FundedUnits": 2.0000, "NewAppUnits": 1.0000, "RateLockUnits": 1.0000 },
    { "LoanOfficer": "Eric", "Year": 2014, "Month": 10, "FundedVolume": 768464.0000, "FundedUnits": 6.0000, "NewAppUnits": 5.0000, "RateLockUnits": 7.0000 }];

So, you can see I have multiple entries for the same LoanOfficer. Ideally, I would like to reformat this and sum it up so that there's only one entry per LoanOfficer and add the FundedVolume & *Units to this:

data = [{ "LoanOfficer": "Brett", "FundedVolume": 483482.0000, "FundedUnits": 3.0000, "NewAppUnits": 3.0000, "RateLockUnits": 4.0000 },
    { "LoanOfficer": "Carlos", "FundedVolume": 917918.0000, "FundedUnits": 5.0000, "NewAppUnits": 10.0000, "RateLockUnits": 4.0000 },
    { "LoanOfficer": "Elaine", "FundedVolume": 348500.0000, "FundedUnits": 2.0000, "NewAppUnits": 2.0000, "RateLockUnits": 2.0000 },
    { "LoanOfficer": "Eric", "FundedVolume": 768464.0000, "FundedUnits": 6.0000, "NewAppUnits": 5.0000, "RateLockUnits": 7.0000 }];

I tried this as a starting point, but i'm pretty sure this not the right route. 

var UniqueD = {};
$.each(data, function () {
    i = 0;
    if (UniqueD[this.LoanOfficer])
        i = UniqueD[this.LoanOfficer];
    UniqueD[this.LoanOfficer] = i + this.NewAppUnits;
});

EDIT: I have a working function that gives me exactly what I want:

var rawdata = [...] // data source
var listOfOfficerStats = []; // new data
function Combine() {
    for (i = 0; i < rawData.length; i++)
    {
        var loExist = -1;
        for (j = 0; j < listOfOfficerStats.length; j++)
        {
            if (listOfOfficerStats[j].LoanOfficer == rawData[i].LoanOfficer)
                loExist = j;
        }

        if (loExist == -1)
        {
            //New
            var newObject = new Object();
            newObject.LoanOfficer = rawData[i].LoanOfficer;
            newObject.FundedVolume = rawData[i].FundedVolume;
            newObject.FundedUnits = rawData[i].FundedUnits;
            newObject.NewAppVolume = rawData[i].NewAppVolume;
            newObject.NewAppUnits = rawData[i].NewAppUnits;
            newObject.RateLockVolume = rawData[i].RateLockVolume;
            newObject.RateLockUnits = rawData[i].RateLockUnits;
            listOfOfficerStats.push(newObject);
        }
        else
        {
            //Add To Existing
            listOfOfficerStats[loExist].FundedVolume += rawData[i].FundedVolume;
            listOfOfficerStats[loExist].FundedUnits += rawData[i].FundedUnits;
            listOfOfficerStats[loExist].NewAppVolume += rawData[i].NewAppVolume;
            listOfOfficerStats[loExist].NewAppUnits += rawData[i].NewAppUnits;
            listOfOfficerStats[loExist].RateLockVolume += rawData[i].RateLockVolume;
            listOfOfficerStats[loExist].RateLockUnits += rawData[i].RateLockUnits;
        }
    }
}

Upvotes: 1

Views: 118

Answers (2)

Sergiu Paraschiv
Sergiu Paraschiv

Reputation: 10153

Here's a version using lodash and a chain of groupBy, map and reduce:

var data = [...]; //assuming your original data array here

function sum(total, n) {
    return total + n;
}

function sumProperty(data, prop) {
    return _.reduce(_.pluck(data, prop), sum);
}

var groups = _.groupBy(data, 'LoanOfficer');
var reduced = _.map(groups, function(group, groupName) {
    return {
        LoanOfficer: groupName,
        FundedVolume: sumProperty(group, 'FundedVolume'),
        NewAppUnits: sumProperty(group, 'NewAppUnits'),
        RateLockUnits: sumProperty(group, 'RateLockUnits')
    };
});

This is in no way intended to be optimal. Using some more specialized code you could improve this by only looping once over data to do the grouping and build all sums at the same time.

Upvotes: 1

georg
georg

Reputation: 214959

Here's a generic function:

function sum(data, key, fields) {
    return data.reduce(function(out, rec) {
        var k = rec[key];
        out[k] = out[k] || {};
        fields.forEach(function(f) {
            out[k][f] = (out[k][f] || 0) + (rec[f] || 0);
        });
        return out;
    }, {})
}


// test


data = [{ "LoanOfficer": "Brett", "Year": 2014, "Month": 10, "FundedVolume": 304032.0000, "FundedUnits": 2.0000, "NewAppUnits": 2.0000, "RateLockUnits": 1.0000 },
    { "LoanOfficer": "Brett", "Year": 2014, "Month": 11, "FundedVolume": 179450.0000, "FundedUnits": 1.0000, "NewAppUnits": 1.0000, "RateLockUnits": 3.0000 },
    { "LoanOfficer": "Carlos", "Year": 2014, "Month": 10, "FundedVolume": 576615.0000, "FundedUnits": 3.0000, "NewAppUnits": 5.0000, "RateLockUnits": 3.0000 },
    { "LoanOfficer": "Carlos", "Year": 2014, "Month": 11, "FundedVolume": 341303.0000, "FundedUnits": 2.0000, "NewAppUnits": 5.0000, "RateLockUnits": null },
    { "LoanOfficer": "Carlos", "Year": 2014, "Month": 12, "FundedVolume": null, "FundedUnits": null, "NewAppUnits": null, "RateLockUnits": 1.0000 },
    { "LoanOfficer": "Elaine", "Year": 2014, "Month": 10, "FundedVolume": null, "FundedUnits": null, "NewAppUnits": 1.0000, "RateLockUnits": 1.0000 },
    { "LoanOfficer": "Elaine", "Year": 2014, "Month": 11, "FundedVolume": 348500.0000, "FundedUnits": 2.0000, "NewAppUnits": 1.0000, "RateLockUnits": 1.0000 },
    { "LoanOfficer": "Eric", "Year": 2014, "Month": 10, "FundedVolume": 768464.0000, "FundedUnits": 6.0000, "NewAppUnits": 5.0000, "RateLockUnits": 7.0000 }];

res = sum(data, "LoanOfficer", ["FundedVolume", "FundedUnits"]);
document.write(JSON.stringify(res));

Upvotes: 1

Related Questions