JavaScript code that filters and combines two arrays based on the matching of two fields or columns

Question

JavaScript code that filters and combines two arrays based on the matching of two fields or columns

Looking for a solution with childcare schools - I have two sets of data that I need to match based on specific columns:

Attendance by Department: [date, department, attended]

    0: (3) ["09-30-2019", "00_Infants", 22]
    1: (3) ["09-30-2019", "01_Ones", 38]
    2: (3) ["09-30-2019", "02_Twos", 40]
    3: (3) ["09-30-2019", "03_Threes", 42]
    4: (3) ["09-30-2019", "04_Fours", 19]
    5: (3) ["10-01-2019", "00_Infants", 27]
    6: (3) ["10-01-2019", "01_Ones", 42]
    7: (3) ["10-01-2019", "02_Twos", 51]

ETC...

Expenses by Department [date, department, expense]

    0: (3) ["09-30-2019", "00_Infants", "584.56"]
    1: (3) ["09-30-2019", "01_Ones", "701.51"]
    2: (3) ["09-30-2019", "02_Twos", "614.02"]
    3: (3) ["09-30-2019", "03_Threes", "442.50"]
    4: (3) ["09-30-2019", "04_Fours", "166.65"]
    5: (3) ["09-30-2019", "06_Floater", "141.37"]
    6: (3) ["09-30-2019", "07_Office", "246.91"]
    7: (3) ["09-30-2019", "08_Administration", "0.00"]
    8: (3) ["09-30-2019", "09_Director", "0.00"]
    9: (3) ["09-30-2019", "12_Kitchen", "0.00"]
    10: (3) ["10-01-2019", "00_Infants", "760.37"]
    11: (3) ["10-01-2019", "01_Ones", "756.48"]
    12: (3) ["10-01-2019", "02_Twos", "640.23"]
    13: (3) ["10-01-2019", "03_Threes", "552.66"]

--

The goal is to link Expense.date && Expense.department to Attendance.date && Attendance.department ONLY if the .department appears in Attendance[] THEN, add Expense.expense to the corresponding record in Attendance

I've experimented with mapping, filtering, d3.js nest(), Object.assign(), ifs, $.each...

Here's the messy code snippet where I attempted different solutions even though they didn't really work as expected.

    let ffs = attended.map(function (x, i) {
    return {
        "date": emp[x],
        "other": emp[i][0]
    }
    }.bind(this));

let mfer = attended.map(x => Object.assign(x, emp.find(y => y[0] === x[0]));

Expected outcome: [date, department, attended, expense]

    0: (3) ["09-30-2019", "00_Infants", 22, 584.56]
    1: (3) ["09-30-2019", "01_Ones", 38, 701.51]
    2: (3) ["09-30-2019", "02_Twos", 40, 613.02]

Actual results: pretty much everything but that.

My browser history shows 115 searches related to this issue from yesterday at 7 PM until today at 8:30 AM.

I feel like there should be a simple solution to this, but being in a learning phase, it's more challenging than I anticipated.

- I think I'm getting closer to what I need, although things get blurry when trying to apply the second filter to target departments:

Applying filter within a filter in JavaScript

some tabs stay open...

Find all matching elements within an array of objects

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/some

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Operators/Destructuring_assignment

Javascript multiple filters array

merge two arrays including some fields from each

javascript arrays json d3.js

Answer 1

Answer №1

Loop through the Attendance array and use Array.prototype.find() to search for a matching entry in the Departments array. If a match is found, the parsed amount is added to the attendance entry.

const attendance = [["09-30-2019", "00_Infants", 22],["09-30-2019", "01_Ones", 38],["09-30-2019", "02_Twos", 40],["09-30-2019", "03_Threes", 42],["09-30-2019", "04_Fours", 19],["10-01-2019", "00_Infants", 27],["10-01-2019", "01_Ones", 42],["10-01-2019", "02_Twos", 51]];
const departments = [["09-30-2019", "00_Infants", "584.56"],["09-30-2019", "01_Ones", "701.51"],["09-30-2019", "02_Twos", "614.02"],["09-30-2019", "03_Threes", "442.50"],["09-30-2019", "04_Fours", "166.65"],["09-30-2019", "06_Floater", "141.37"],["09-30-2019", "07_Office", "246.91"],["09-30-2019", "08_Administration", "0.00"],["09-30-2019", "09_Director", "0.00"],["09-30-2019", "12_Kitchen", "0.00"],["10-01-2019", "00_Infants", "760.37"],["10-01-2019", "01_Ones", "756.48"],["10-01-2019", "02_Twos", "640.23"], ["10-01-2019", "03_Threes", "552.66"]];

attendance.forEach(a => {
  const department = departments.find(d => a[0] === d[0] && a[1] === d[1]);
  
  if (department) {
    a.push(parseFloat(department[2]));
  }
});

console.log(attendance);

Answer 2

Loop through the Attendance array and use Array.prototype.find() to search for a matching entry in the Departments array. If a match is found, the parsed amount is added to the attendance entry.

const attendance = [["09-30-2019", "00_Infants", 22],["09-30-2019", "01_Ones", 38],["09-30-2019", "02_Twos", 40],["09-30-2019", "03_Threes", 42],["09-30-2019", "04_Fours", 19],["10-01-2019", "00_Infants", 27],["10-01-2019", "01_Ones", 42],["10-01-2019", "02_Twos", 51]];
const departments = [["09-30-2019", "00_Infants", "584.56"],["09-30-2019", "01_Ones", "701.51"],["09-30-2019", "02_Twos", "614.02"],["09-30-2019", "03_Threes", "442.50"],["09-30-2019", "04_Fours", "166.65"],["09-30-2019", "06_Floater", "141.37"],["09-30-2019", "07_Office", "246.91"],["09-30-2019", "08_Administration", "0.00"],["09-30-2019", "09_Director", "0.00"],["09-30-2019", "12_Kitchen", "0.00"],["10-01-2019", "00_Infants", "760.37"],["10-01-2019", "01_Ones", "756.48"],["10-01-2019", "02_Twos", "640.23"], ["10-01-2019", "03_Threes", "552.66"]];

attendance.forEach(a => {
  const department = departments.find(d => a[0] === d[0] && a[1] === d[1]);
  
  if (department) {
    a.push(parseFloat(department[2]));
  }
});

console.log(attendance);

Answer 3

Answer №2

In order to find the expense for a specific date and department in the attendance array, you can utilize the map() method along with the find() method.

If the expense is found, it will be appended; otherwise, the item remains unchanged.

var attendance = [ ["09-30-2019", "00_Infants", 22], ["09-30-2019", "01_Ones", 38], ["09-30-2019", "02_Twos", 40], ["09-30-2019", "03_Threes", 42], ["09-30-2019", "04_Fours", 19], ["10-01-2019", "00_Infants", 27], ["10-01-2019", "01_Ones", 42], ["10-01-2019", "02_Twos", 51] ];

var expenses = [ ["09-30-2019", "00_Infants", "584.56"], ["09-30-2019", "01_Ones", "701.51"], ["09-30-2019", "02_Twos", "614.02"], ["09-30-2019", "03_Threes", "442.50"], ["09-30-2019", "04_Fours", "166.65"], ["09-30-2019", "06_Floater", "141.37"], ["09-30-2019", "07_Office", "246.91"], ["09-30-2019", "08_Administration", "0.00"], ["09-30-2019", "09_Director", "0.00"], ["09-30-2019", "12_Kitchen", "0.00"], ["10-01-2019", "00_Infants", "760.37"], ["10-01-2019", "01_Ones", "756.48"], ["10-01-2019", "02_Twos", "640.23"], ["10-01-2019", "03_Threes", "552.66"] ];

var result = attendance.map(item => {
  let expense = expenses.find(expense => expense[0] === item[0] && expense[1] === item[1]);
  if (expense) {
    return [...item, expense[2]];
  } else {
    return item;
  }
});

console.log(result);

Answer 4

In order to find the expense for a specific date and department in the attendance array, you can utilize the map() method along with the find() method.

If the expense is found, it will be appended; otherwise, the item remains unchanged.

var attendance = [ ["09-30-2019", "00_Infants", 22], ["09-30-2019", "01_Ones", 38], ["09-30-2019", "02_Twos", 40], ["09-30-2019", "03_Threes", 42], ["09-30-2019", "04_Fours", 19], ["10-01-2019", "00_Infants", 27], ["10-01-2019", "01_Ones", 42], ["10-01-2019", "02_Twos", 51] ];

var expenses = [ ["09-30-2019", "00_Infants", "584.56"], ["09-30-2019", "01_Ones", "701.51"], ["09-30-2019", "02_Twos", "614.02"], ["09-30-2019", "03_Threes", "442.50"], ["09-30-2019", "04_Fours", "166.65"], ["09-30-2019", "06_Floater", "141.37"], ["09-30-2019", "07_Office", "246.91"], ["09-30-2019", "08_Administration", "0.00"], ["09-30-2019", "09_Director", "0.00"], ["09-30-2019", "12_Kitchen", "0.00"], ["10-01-2019", "00_Infants", "760.37"], ["10-01-2019", "01_Ones", "756.48"], ["10-01-2019", "02_Twos", "640.23"], ["10-01-2019", "03_Threes", "552.66"] ];

var result = attendance.map(item => {
  let expense = expenses.find(expense => expense[0] === item[0] && expense[1] === item[1]);
  if (expense) {
    return [...item, expense[2]];
  } else {
    return item;
  }
});

console.log(result);

Answer 5

Answer №3

To organize these components, you can use a Map with a designated "key". In your scenario, the key could be the date and department. Here's how you can achieve this:

Create a mapping based on your expenses array.
Go through the attendance array, retrieve the expense associated with the matching key from the created grouping, and add the expense to that array.

const attendance = [
  ["09-30-2019", "00_Infants", 22],
  ["09-30-2019", "01_Ones", 38],
  ["09-30-2019", "02_Twos", 40],
  ["09-30-2019", "03_Threes", 42],
  ["09-30-2019", "04_Fours", 19],
  ["10-01-2019", "00_Infants", 27],
  ["10-01-2019", "01_Ones", 42],
  ["10-01-2019", "02_Twos", 51]
];
const expenses = [
  ["09-30-2019", "00_Infants", "584.56"],
  ["09-30-2019", "01_Ones", "701.51"],
  ["09-30-2019", "02_Twos", "614.02"],
  ["09-30-2019", "03_Threes", "442.50"],
  ["09-30-2019", "04_Fours", "166.65"],
  ["09-30-2019", "06_Floater", "141.37"],
  ["09-30-2019", "07_Office", "246.91"],
  ["09-30-2019", "08_Administration", "0.00"],
  ["09-30-2019", "09_Director", "0.00"],
  ["09-30-2019", "12_Kitchen", "0.00"],
  ["10-01-2019", "00_Infants", "760.37"],
  ["10-01-2019", "01_Ones", "756.48"],
  ["10-01-2019", "02_Twos", "640.23"],
  ["10-01-2019", "03_Threes", "552.66"]
];

const key = (date, department) => `${date};${department}`;
const expenseMap = new Map(expenses.map(([date, dept, expense]) => [key(date, dept), expense]));

attendance.forEach(a => a.push(expenseMap.get(key(a[0], a[1])));
console.log(attendance)

Answer 6

To organize these components, you can use a Map with a designated "key". In your scenario, the key could be the date and department. Here's how you can achieve this:

Create a mapping based on your expenses array.
Go through the attendance array, retrieve the expense associated with the matching key from the created grouping, and add the expense to that array.

const attendance = [
  ["09-30-2019", "00_Infants", 22],
  ["09-30-2019", "01_Ones", 38],
  ["09-30-2019", "02_Twos", 40],
  ["09-30-2019", "03_Threes", 42],
  ["09-30-2019", "04_Fours", 19],
  ["10-01-2019", "00_Infants", 27],
  ["10-01-2019", "01_Ones", 42],
  ["10-01-2019", "02_Twos", 51]
];
const expenses = [
  ["09-30-2019", "00_Infants", "584.56"],
  ["09-30-2019", "01_Ones", "701.51"],
  ["09-30-2019", "02_Twos", "614.02"],
  ["09-30-2019", "03_Threes", "442.50"],
  ["09-30-2019", "04_Fours", "166.65"],
  ["09-30-2019", "06_Floater", "141.37"],
  ["09-30-2019", "07_Office", "246.91"],
  ["09-30-2019", "08_Administration", "0.00"],
  ["09-30-2019", "09_Director", "0.00"],
  ["09-30-2019", "12_Kitchen", "0.00"],
  ["10-01-2019", "00_Infants", "760.37"],
  ["10-01-2019", "01_Ones", "756.48"],
  ["10-01-2019", "02_Twos", "640.23"],
  ["10-01-2019", "03_Threes", "552.66"]
];

const key = (date, department) => `${date};${department}`;
const expenseMap = new Map(expenses.map(([date, dept, expense]) => [key(date, dept), expense]));

attendance.forEach(a => a.push(expenseMap.get(key(a[0], a[1])));
console.log(attendance)

JavaScript code that filters and combines two arrays based on the matching of two fields or columns

Actual results: pretty much everything but that.

My browser history shows 115 searches related to this issue from yesterday at 7 PM until today at 8:30 AM.

Applying filter within a filter in JavaScript

Answer №1

Answer №2

Answer №3

Similar questions

Easy method to create an array of increasing numbers in Swift such as 1, 3, 5

unraveling JSON information in PHP through AJAX

Materialize CSS is throwing an error: 'velocity is not a function'

The Dropdown Button Functions Once and Then Stops

Tips for sending an array containing objects with an ID using Angular

Timing of Bindings in AngularJS

When using v-for to render an array list fetched from AsyncData, an error is thrown: The virtual DOM tree rendered on the client-side does not match the one

How to extract only the truthy keys from an object using Angular.js

PHP Command Line Interface can effectively parse JSON strings provided as arguments

Can bumpbox be activated from flash?

Tips for displaying a loader image with a centered message and preventing the bootstrap modal dialogue box from closing during an AJAX response from a PHP file

Task allocation is encountering issues with the implementation of a multidimensional array

jQuery does not change the scroll position of elements

Issues with previewing PDF files in AngularJS

Remove the preset text in an input field by clicking on it

What are the methods for differentiating between a deliberate user click and a click triggered by JavaScript?

What is the best way to implement <li> in place of <div> to ensure the tool-tip code functions properly?

Assigning values to jagged arrays in a class

Leveraging global attributes beyond Vue components - Vue 3

The JQuery script has not been initialized, causing the function to not activate