Combine two arrays of objects according to different periods of time in javascript

Question

I have two arrays of objects periods and people. periods refers to the different periods of time that an organization went through. people refers to the people that were part of that organization. In the result, I want to attach each individual person to the period when they were part of the organization. To do that I am planing to do two different transformations. A sample of the data follows:

DATA

periods = [
  { start_date: "2017-01-01", end_date: "2017-12-01", period: 1 },
  { start_date: "2018-01-01", end_date: "2018-12-01", period: 2 },
  { start_date: "2019-01-01", end_date: "2019-12-01", period: 3 }
]

people = [
  { name: "Paul", 
    start_date_org: "2017-01-01", 
    end_date_org: "2018-04-01" },
  { name: "Terence",
    start_date_org: "2018-06-01",
    end_date_org: "2019-12-01" },
  { name: "Kylian",
    start_date_org: "2017-06-01", 
    end_date_org: "2019-12-01" }
]

How can I combine/merge these two files to get the following transformations?

1st transformation: Attach each individual person to the period/s when they were part of the organization. If possible, add years only as a reference.

result1 = [
  {
    name: "Paul",
    start_date_org: "2017-01-01",
    end_date_org: "2018-06-01",
    periods: [1, 2],
    year: [2017, 2018]
  },
  {
    name: "Terence",
    start_date_org: "2018-06-01",
    end_date_org: "2019-12-01",
    periods: [2, 3],
    year: [2018, 2019]
  },
  {
    name: "Kylian",
    start_date_org: "2017-06-01",
    end_date_org: "2019-12-01",
    periods: [1, 2, 3],
    year: [2017, 2018, 2019]
  }
]

2nd transformation: Expand the result. One object per person and period.

result2 = [
  {
    name: "Paul",
    start_date_org: "2017-01-01",
    end_date_org: "2018-06-01",
    period: 1,
    year: 2017
  },
  {
    name: "Paul",
    start_date_org: "2017-01-01",
    end_date_org: "2018-06-01",
    period: 2,
    year: 2018
  },
  {
    name: "Terence",
    start_date_org: "2018-06-01",
    end_date_org: "2019-12-01",
    period: 2,
    year: 2018
  },
  {
    name: "Terence",
    start_date_org: "2018-06-01",
    end_date_org: "2019-12-01",
    period: 3,
    year: 2019
  },
  {
    name: "Kylian",
    start_date_org: "2017-06-01",
    end_date_org: "2019-12-01",
    period: 1,
    year: 2017
  },
  {
    name: "Kylian",
    start_date_org: "2017-06-01",
    end_date_org: "2019-12-01",
    period: 2,
    year: 2018
  },
  {
    name: "Kylian",
    start_date_org: "2017-06-01",
    end_date_org: "2019-12-01",
    period: 3,
    year: 2019
  }
]

My best attempt so far using an if statement for the first transformation follows:

parseTime = d3.timeParse("%Y-%m-%d")

people.map((d, i) => {
  periods.map((x, i) => {
    if (
      parseTime(x.start_date_org) >= parseTime(d.start_date) &&
      parseTime(x.start_date_org) <= parseTime(d.end_date)
    ) {
      d.period = x.period;
    }
  });
  return d;
})

Any advice is welcome!

Answer 1

Here's an example that first maps over the periods and creates relevant properties for later comparison.

Preparation

// map over periods and create dates and isolate 'year'
const periods_ = periods.map((p) => ({
  ...p,
  period_start_date: parseTime(p.start_date),
  period_end_date: parseTime(p.end_date),
  year: parseTime(p.start_date).getFullYear(),
}));

Transform 1

Is mostly taken up determining which periods intersect the persons tenure within a filter() call, then returning these periods mapped to period and year props

const transform1 = people.map((p) => {

  const person_periods = periods_.filter((p_) =>
    p_.period_start_date <= parseTime(p.end_date_org)
    && parseTime(p.start_date_org) <= p_.period_end_date
  );

  return {
    ...p,
    periods: person_periods.map(({ period }) => period),
    years: person_periods.map(({ year }) => year),
  };
});

Transform 2

Based on transform 1 this is a one line flatMap() flattening the previously mapped periods and years.

const transform2 = transform1.flatMap(({ periods, years, ...p }) =>
  years.map((year, i) => ({ ...p, period: periods[i], year }))
);

const periods = [ { start_date: '2017-01-01', end_date: '2017-12-01', period: 1 }, { start_date: '2018-01-01', end_date: '2018-12-01', period: 2 }, { start_date: '2019-01-01', end_date: '2019-12-01', period: 3 }, ];
const people = [ { name: 'Paul', start_date_org: '2017-01-01', end_date_org: '2018-04-01', }, { name: 'Terence', start_date_org: '2018-06-01', end_date_org: '2019-12-01', }, { name: 'Kylian', start_date_org: '2017-06-01', end_date_org: '2019-12-01', }, ];

const parseTime = d3.timeParse('%Y-%m-%d');

// map over periods and create dates and isolate 'year'
const periods_ = periods.map((p) => ({
  ...p,
  period_start_date: parseTime(p.start_date),
  period_end_date: parseTime(p.end_date),
  year: parseTime(p.start_date).getFullYear(),
}));

// transform1
const transform1 = people.map((p) => {

  const person_periods = periods_.filter((p_) =>
    p_.period_start_date <= parseTime(p.end_date_org)
    && parseTime(p.start_date_org) <= p_.period_end_date
  );

  return {
    ...p,
    periods: person_periods.map(({ period }) => period),
    years: person_periods.map(({ year }) => year),
  };
});

// transform 2
const transform2 = transform1.flatMap(({ periods, years, ...p }) =>
  years.map((year, i) => ({ ...p, period: periods[ i ], year }))
);

console.log(transform1);
console.log(transform2);

.as-console-wrapper { max-height: 100% !important; top: 0; }

<script src="https://cdnjs.cloudflare.com/ajax/libs/d3/7.0.1/d3.min.js"></script>

---

Using Array#reduce()

Alternatively, you can combine the two into a single Array#reduce() call, still preparing the periods array by mapping over it and parsing the dates and adding a year property for convenience later, and then reducing into a tuple accumulating transform1 and transform2.

const [ transform1, transform2 ] = people.reduce(([t1, t2], p) => {

  const person_periods = periods_.filter((p_) =>
    p_.period_start_date <= parseTime(p.end_date_org)
    && parseTime(p.start_date_org) <= p_.period_end_date
  );

  t1.push({
    ...p,
    periods: person_periods.map(({ period }) => period),
    years: person_periods.map(({ year }) => year),
  });

  t2.push(...person_periods.map(({ period, year }, i) => ({ ...p, period, year })));

  return [t1, t2];
}, [[], []]);

const periods = [ { start_date: '2017-01-01', end_date: '2017-12-01', period: 1 }, { start_date: '2018-01-01', end_date: '2018-12-01', period: 2 }, { start_date: '2019-01-01', end_date: '2019-12-01', period: 3 }, ];
const people = [ { name: 'Paul', start_date_org: '2017-01-01', end_date_org: '2018-04-01', }, { name: 'Terence', start_date_org: '2018-06-01', end_date_org: '2019-12-01', }, { name: 'Kylian', start_date_org: '2017-06-01', end_date_org: '2019-12-01', }, ];

const parseTime = d3.timeParse('%Y-%m-%d');

// map over periods and create dates and isolate 'year'
const periods_ = periods.map((p) => ({
  ...p,
  period_start_date: parseTime(p.start_date),
  period_end_date: parseTime(p.end_date),
  year: parseTime(p.start_date).getFullYear(),
}));

// reduce into tuple [t1, t2] and destructure
const [ transform1, transform2 ] = people.reduce(([t1, t2], p) => {

  const person_periods = periods_.filter((p_) =>
    p_.period_start_date <= parseTime(p.end_date_org)
    && parseTime(p.start_date_org) <= p_.period_end_date
  );

  t1.push({
    ...p,
    periods: person_periods.map(({ period }) => period),
    years: person_periods.map(({ year }) => year),
  });

  t2.push(...person_periods.map(({ period, year }, i) => ({ ...p, period, year })));

  return [t1, t2];
}, [[], []]);


console.log('Transform 1: \n', transform1);
console.log('Transform 2: \n', transform2);

.as-console-wrapper { max-height: 100% !important; top: 0; }

<script src="https://cdnjs.cloudflare.com/ajax/libs/d3/7.0.1/d3.min.js"></script>

Answer 2

Push the met criteria into an array and using flatMap -> expand the array list into a new array list by making a function call.

    let periods = [
  { start_date: "2017-01-01", end_date: "2017-12-01", period: 1 },
  { start_date: "2018-01-01", end_date: "2018-12-01", period: 2 },
  { start_date: "2019-01-01", end_date: "2019-12-01", period: 3 }
];

let people = [
  { name: "Paul", 
    start_date_org: "2017-01-01", 
    end_date_org: "2018-04-01" },
  { name: "Terence",
    start_date_org: "2018-06-01",
    end_date_org: "2019-12-01" },
  { name: "Kylian",
    start_date_org: "2017-06-01", 
    end_date_org: "2019-12-01" }
];

//let parseTime = d3.timeParse("%Y-%m-%d")
function parseTime(dateStrYMD) {
   let ts = new Date(dateStrYMD).getTime() / 1000;
   return ts;
}

function resultSet(people, periods) {
 return people.flatMap((d, i) => {
  let peoplePeriods = [];
  periods.map((x, i) => {
    if (
      parseTime(x.start_date) >= parseTime(d.start_date_org) &&  
      parseTime(x.start_date) <= parseTime(d.end_date_org)
    ) {
      peoplePeriods.push(x.period);
    }
  });

  return peoplePeriods.flatMap(p => {
    d.period = p;
    d.year = new Date(d.start_date_org).getFullYear();
    return {
      ...d
    }
  })
 })
}

console.log(JSON.stringify(resultSet(people, periods)));

Answer 3

Here's the one I made for the first example.

periods = [{
    start_date: "2017-01-01",
    end_date: "2017-12-01",
    period: 1
  },
  {
    start_date: "2018-01-01",
    end_date: "2018-12-01",
    period: 2
  },
  {
    start_date: "2019-01-01",
    end_date: "2019-12-01",
    period: 3
  }
]

people = [{
    name: "Paul",
    start_date_org: "2017-01-01",
    end_date_org: "2018-04-01"
  },
  {
    name: "Terence",
    start_date_org: "2018-06-01",
    end_date_org: "2019-12-01"
  },
  {
    name: "Kylian",
    start_date_org: "2017-06-01",
    end_date_org: "2019-12-01"
  }
]

wanted_result = [{
    name: "Paul",
    start_date_org: "2017-01-01",
    end_date_org: "2018-06-01",
    periods: [1, 2],
    year: [2017, 2018]
  },
  {
    name: "Terence",
    start_date_org: "2018-06-01",
    end_date_org: "2019-12-01",
    periods: [2, 3],
    year: [2018, 2019]
  },
  {
    name: "Kylian",
    start_date_org: "2017-06-01",
    end_date_org: "2019-12-01",
    periods: [1, 2, 3],
    year: [2017, 2018, 2019]
  }
]

function usingNames(people, periods) {
  return people.map(person => {
    const personsPeriods = [];

    for (period of periods) {
      if (new Date(period.start_date) <= new Date(person.end_date_org) && new Date(person.start_date_org) <= new Date(period.end_date)) personsPeriods.push(period)
    }

    return {
      ...person,
      periods: [...personsPeriods.map(p => p.period)],
      year: [...personsPeriods.map(p => new Date(p.start_date).getFullYear())],
    }
  })
}

console.log(usingNames(people, periods))

Here's the example for the second example.

periods = [{
    start_date: "2017-01-01",
    end_date: "2017-12-01",
    period: 1
  },
  {
    start_date: "2018-01-01",
    end_date: "2018-12-01",
    period: 2
  },
  {
    start_date: "2019-01-01",
    end_date: "2019-12-01",
    period: 3
  }
]

people = [{
    name: "Paul",
    start_date_org: "2017-01-01",
    end_date_org: "2018-04-01"
  },
  {
    name: "Terence",
    start_date_org: "2018-06-01",
    end_date_org: "2019-12-01"
  },
  {
    name: "Kylian",
    start_date_org: "2017-06-01",
    end_date_org: "2019-12-01"
  }
]

wanted_result = [
  {
    name: "Paul",
    start_date_org: "2017-01-01",
    end_date_org: "2018-06-01",
    period: 1,
    year: 2017
  },
  {
    name: "Paul",
    start_date_org: "2017-01-01",
    end_date_org: "2018-06-01",
    period: 2,
    year: 2018
  },
  {
    name: "Terence",
    start_date_org: "2018-06-01",
    end_date_org: "2019-12-01",
    period: 2,
    year: 2018
  },
  {
    name: "Terence",
    start_date_org: "2018-06-01",
    end_date_org: "2019-12-01",
    period: 3,
    year: 2019
  },
  {
    name: "Kylian",
    start_date_org: "2017-06-01",
    end_date_org: "2019-12-01",
    period: 1,
    year: 2017
  },
  {
    name: "Kylian",
    start_date_org: "2017-06-01",
    end_date_org: "2019-12-01",
    period: 2,
    year: 2018
  },
  {
    name: "Kylian",
    start_date_org: "2017-06-01",
    end_date_org: "2019-12-01",
    period: 3,
    year: 2019
  }
]

function usingNames(people, periods) {
  return people.flatMap(person => {
    const personsPeriods = [];

    for (period of periods) {
      if (new Date(period.start_date) <= new Date(person.end_date_org) && new Date(person.start_date_org) <= new Date(period.end_date)) personsPeriods.push(period)
    }
    
    return personsPeriods.flatMap(p => {
      return {
        ...person,
        period: p.period,
        year: new Date(p.start_date).getFullYear(),
      }
    })
  })
}

console.log(usingNames(people, periods))

Answer 4

Here is not the most beautiful but working solution:

var periods = [
    { start_date: "2017-01-01", end_date: "2017-12-01", period: 1 },
    { start_date: "2018-01-01", end_date: "2018-12-01", period: 2 },
    { start_date: "2019-01-01", end_date: "2019-12-01", period: 3 }
];

var people = [
    { name: "Paul",
        start_date_org: "2017-01-01",
        end_date_org: "2018-04-01" },
    { name: "Terence",
        start_date_org: "2018-06-01",
        end_date_org: "2019-12-01" },
    { name: "Kylian",
        start_date_org: "2017-06-01",
        end_date_org: "2019-12-01" }
];

periods.map(p => {
    p.start_date = new Date(p.start_date);
    p.end_date = new Date(p.end_date);
});

people.map(p => {
    p.start_date_org = new Date(p.start_date_org);
    p.end_date_org = new Date(p.end_date_org);
});

var first_transformation = people;

first_transformation.map(p => {
    var chosen_periods = periods.filter(per => {
        return (p.start_date_org >= per.start_date && p.start_date_org <= per.end_date) ||
            (p.end_date_org >= per.start_date && p.end_date_org <= per.end_date) ||
            (p.start_date_org < per.start_date && p.end_date_org > per.end_date)
    });
    p.periods = chosen_periods.map(per => per.period);
    p.years = chosen_periods.map(per => per.start_date.getFullYear());
});
first_transformation.map(p => {
    p.start_date_org = p.start_date_org.toISOString().substring(0,10);
    p.end_date_org = p.end_date_org.toISOString().substring(0,10);
})

var second_transformation = [];

first_transformation.forEach(p => {
    p.years.forEach((year, i) => {
        second_transformation.push({
            name: p.name,
            start_date_org: p.start_date_org,
            end_date_org: p.end_date_org,
            period: p.periods[i],
            year: year
        })
    })
})

At the end you will have in first_transformation and second_transformation the data you are looking for.