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

Andreas · Accepted Answer · 2019-10-15 16:07:44Z

2

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

answered Oct 15, 2019 at 16:07

Andreas

21.9k7 gold badges52 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

KSails Over a year ago

Thank you for taking the time to answer; your answer works great. I wish I could award everyone.

Nikhil · Accepted Answer · 2019-10-15 16:15:28Z

1

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

answered Oct 15, 2019 at 16:15

Nikhil

6,68110 gold badges36 silver badges74 bronze badges

3 Comments

KSails Over a year ago

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 Over a year ago

Hi @nikhil Would you mind taking a stab? NEXT PROBLEM

Nikhil Over a year ago

Sure @KSails, I'll take a look and reply or answer the question within next few hours.

slider · Accepted Answer · 2019-10-15 16:13:22Z

1

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)

edited Oct 15, 2019 at 16:13

answered Oct 15, 2019 at 16:07

slider

13.1k1 gold badge29 silver badges45 bronze badges

3 Comments

KSails Over a year ago

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.

slider Over a year ago

@KSails that fine :) You can look for "template literals" to understand what that syntax means: developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/…

KSails Over a year ago

Thank you! On my unofficial list of things to learn effectively.

Collectives™ on Stack Overflow

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 Answers 3

1 Comment

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Actual results: anything and everything but.

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

3 Answers 3

1 Comment

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related