Filter and combine two arrays, matching two fields/columns : Javascript

Question

Using childcare schools - I have two arrays that I need to match on 2 columns of each array:

Attendance by Department: [date, dept, 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...

Expense by Department [date, dept, 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"]

--

I want to match Expense.date && Expense.department to Attendance.date && Attendance.department ONLY where .department appears in Attendance[] THEN, append Expense.expense to the matched record in Attendance

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

Here is the really bad last things I haven't deleted and started over from scratch - I understand it alone looks awful and not close; however, SO wanted code.

    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 results: [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: anything and everything but.

My browser search history shows 115 relevant queries between 19:00 yesterday and present 08:30.

I believe this should be simple solution but me being in a learning state I am challenged beyond the time I should be spending on it.

- I believe this to be the closest to what I need but everything becomes unclear when trying to apply the second filter to capture department:

Applying filter within a filter in JavaScript

some open tabs...

Find all matching elements with in 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

score 2 · Answer 1 · answered Oct 15 '19 at 16:07

Iterate over Attendance and check with Array.prototype.find() if there's a match in Departments. If we find a matching department we push the (parsed) amount into 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);

Thank you for taking the time to answer; your answer works great. I wish I could award everyone. — KSails, Oct 15 '19 at 16:35

slider · Answer 2 · 2019-10-15T16:13:22.223

You can group these elements in a Map by a "key". In your case, the key can be date and department. So

create a mapping from your expenses array
iterate through the attendance array, fetch the expense for the matching key from the grouping created above and append 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)

Thanks for answering. I tested all 3 current answers and they all work. I wish I could reward everyone. Would you tell me what search terms I can use to understand `${date};${department}`; ?... It's joining 2 keys but I want to read more. thank you. — KSails, Oct 15 '19 at 16:31
@KSails that fine :) You can look for "template literals" to understand what that syntax means: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Template_literals — slider, Oct 15 '19 at 16:39
Thank you! On my unofficial list of things to learn effectively. — KSails, Oct 15 '19 at 16:46

score 1 · Accepted Answer · answered Oct 15 '19 at 16:15

You can use map() on attendance array and use find() to find the expense for that date and department.

If expense exists, then append expense else return the same item.

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);

Thank you for answering. I rewarded you the point (all current 3 answers work great) because of your use in the ...Spread; It allowed me to be able to finally grasp the concept and insert ideas of Next features to add. Thanks for your time. — KSails, Oct 15 '19 at 16:33
Hi @nikhil Would you mind taking a stab? [NEXT PROBLEM](https://stackoverflow.com/questions/58553556/how-to-map-merge-differently-structured-objects-matched-by-date-and-departmen) — KSails, Oct 25 '19 at 16:31
Sure @KSails, I'll take a look and reply or answer the question within next few hours. — Nikhil, Oct 25 '19 at 16:36

Filter and combine two arrays, matching two fields/columns : Javascript

Actual results: anything and everything but.

My browser search history shows 115 relevant queries between 19:00 yesterday and present 08:30.

Applying filter within a filter in JavaScript

3 Answers3