How to sum if name and date match two API JSON data?

Question

There are two API Request/ URL

This is first API Request URL and data coming from

     http://127.0.0.1:8000/api/onlineUserData

                   {"onlineUserData":[
                      ["Month"  , "Amania"   , "Shyral" , "Dorpan"],
                      ["Nov-16" ,    9       ,   11     ,    6    ] ,
                      ["Nov-18" ,    5       ,   0      ,    0    ]
                    ]};

This is second API Request URL and data coming from

     http://127.0.0.1:8000/api/offlineUserData

                   {"offlineUserData":[
                      ["Month"  , "Amania" , "Shyral" , "Diskhant"],
                      ["Nov-16" ,    4     ,    7     ,     0     ], 
                      ["Nov-20" ,    12    ,    8     ,     9     ], 
                    ]};

Now i want to sum two JSON data if their name of user and month/date match between two API data

  {"AllUser":[
      [" Month"   , "Amania" , "Shyral" , "Dorpan","Diskhant" ],
      [" Nov-16"  ,  13      ,   18     ,    6    ,    0      ], 
      [" Nov-18 " ,  5       ,    0     ,    0    ,    0       ],
      [" Nov-20 " ,  12      ,    8     ,    0    ,    9      ],           ]
   ]

user_11619647 · Answer

The biggest hurdle in combining these two sets is that the values are placed inside of nested arrays. This means that the position that they're in is the only indicator of what the value means.

Therefor the data needs to be converted into a structure with keys and values. This gives each value a name and meaning of what it is and also makes combining them less prone to errors.

[
  {
    'Month': 'Nov-16',
    'Amania': 9,
    'Shyral': 11,
    'Dorpan': 6
  },
  {
    ...
  },
  ...
];

Now you need to make sure that all your keys match up, so that when you start counting you have equal sets of data to count with.

Notice that Diskhant has been added to the object below. Now that name exists in every object and a total of his count can be calculated.

[
  {
    'Month': 'Nov-16',
    'Amania': 9,
    'Shyral': 11,
    'Dorpan': 6,
    'Diskhant': 0
  },
  {
    ...
  },
  ...
];

From here you can combine the two sets into one and start filtering for duplicate Month values. When such a duplicate occurs add the value of the current object to and return the result.

I do believe this can be made simpler, but my attempt left me making all of these helper functions to make sure that the data is manipulated correctly. It also enables you to enter more than 2 datasets if you need to combine them.

const online = {
  "onlineUserData": [
    ["Month", "Amania", "Shyral", "Dorpan"],
    ["Nov-16", 9, 11, 6],
    ["Nov-18", 5, 0, 0]
  ]
};

const offline = {
  "offlineUserData": [
    ["Month", "Amania", "Shyral", "Diskhant"],
    ["Nov-16", 4, 7, 0],
    ["Nov-20", 12, 8, 9],
  ]
};

/**
 * Converts the nested arrays to an array of objects.
 */
const tableToObjects = data => {
  const [ headers, ...entries ] = data;
  return entries.map(entry => entry.reduce((acc, cur, i) => {
    acc[headers[i]] = cur;
    return acc;
  }, {}));
};

/**
 * Converts an array of objects into nested arrays.
 */
const objectsToTable = data => [
  Object.keys(data[0]),
  ...(data.map(entry => Object.values(entry)))
];

/**
 * Returns an array of all keys that are present.
 */
const getKeyMap = data => [...data.reduce((set, entry) => {
  Object.keys(entry).forEach(key => set.add(key));
  return set;
}, new Set())];

/**
 * Modifies the data to ensure that all objects have the
 * same keys. This is necessary to get a correct output
 * when counting and adding up the values of each object.
 */
const equalizeData = data => {
  const keys = getKeyMap(data);
  return data.map(entry => keys.reduce((obj, key) => {
    obj[key] = entry[key] || 0
    return obj;
  }, {}));
}

/**
 * Merges the data by the Month property.
 * If a Month is already present in the array then the values
 * of that object will be added to the existing object.
 */
const mergeData = data => data.reduce((arr, entry) => {
  const { Month, ...rest } = entry;
  const present = arr.find(newEntry => newEntry['Month'] === Month);
  if (present) {
    Object.entries(rest).forEach(([ key, value ]) => {
      present[key] = (present[key] || 0) + value;
    });
  } else {
    arr.push(entry);
  }
  return arr;
}, []);

/**
 * Function that uses all the functions from above to combine
 * the two nested arrays into a single nested array.
 * Returns an object with a single `allUsers` property.
 */
const allUsers = (...dataSets) => {
  const allData = dataSets.flatMap(dataSet => {
    const table = Object.values(dataSet)[0];
    return tableToObjects(table);
  });
  const equalizedData = equalizeData(allData)
  const mergedData = mergeData(equalizedData);
  return {
    'allUsers': objectsToTable(mergedData)
  };
};

// Call the function.
const result = allUsers(online, offline);

// This is just to see the output of the function.
const output = document.getElementById('output');
output.innerHTML = JSON.stringify(result, null, 2);

pre {
  display: inline-block;
  background-color: #f7f7f7;
  border: 1px solid #d0d0d0;
  border-radius: 5px;
  padding: 15px;
}

How to sum if name and date match two API JSON data?

1 Answers