Create map that combines data from two files with common column - but data doesn't match one to one

Question

I'm creating a map using two data files. One data file has the latitude and longitude of locations. The other data file has the population of those locations. Both have a column with the location code. So long as both files contain all of the same codes, this works. However, my population dataset only has information for some of the location codes. So I'm getting an error:

test.html:75 Uncaught TypeError: Cannot read property 'pop' of undefined

How can I avoid this error?

My full code is below.

Here is also a Plunker with all of the CSVs/files: https://plnkr.co/edit/pvPv2To72OiSOPaz

user_1065654 · Answer

Solution 1: Remove locations without given population

Replace

const location_data = locations.sort((a, b) => newDict[b.code].pop - newDict[a.code].pop)

with

const location_data =
  locations
    .filter(l => Boolean(newDict[l.code]))
    .sort((a, b) => newDict[b.code].pop - newDict[a.code].pop)

to avoid the error

test.html:75 Uncaught TypeError: Cannot read property 'pop' of undefined

by removing the locations without a given population.

Solution 2: Use a default

If you'd like to keep the location even if the population is not given, you may assume/use a default value, e.g. 0. Therefore, replace

const location_data = locations.sort((a, b) => newDict[b.code].pop - newDict[a.code].pop)
location_data.forEach(function (d) {
  d.pop = newDict[d.code].pop
})

with

const location_data =
  locations
    .map(l => ({
      ...l,
      pop: newDict[l.code] ? newDict[l.code].pop : 0,
    }))
    .sort((a, b) => b.pop - a.pop);

Below is a runnable code snippet. Open it using the "Full page" button to see the map and the logs:

var margin = { top: 10, left: 10, bottom: 10, right: 10 },
width = window.outerWidth,
width = width - margin.left - margin.right,
mapRatio = .5,
height = width * mapRatio;

var projection = geoAlbersUsaTerritories.geoAlbersUsaTerritories()
.scale(width)
.translate([width / 2, height / 2]);

var path = d3.geoPath()
.projection(projection);

var map = d3.select(".g-chart").append("svg")
.style('height', height + 'px')
.style('width', width + 'px')
.call(d3.zoom().on("zoom", function () {
  map.attr("transform", d3.event.transform)
  d3.selectAll()
}))
.append("g");

queue()
.defer(d3.json, "https://gist.githubusercontent.com/maiermic/44da7021b7a3c89374cd8fe54e1a68a2/raw/aaa4b1f676d14571875e005a0b59c6a5165e7e17/us.json")
.defer(d3.csv, "https://gist.githubusercontent.com/maiermic/44da7021b7a3c89374cd8fe54e1a68a2/raw/aaa4b1f676d14571875e005a0b59c6a5165e7e17/locations.csv")
.defer(d3.csv, "https://gist.githubusercontent.com/maiermic/44da7021b7a3c89374cd8fe54e1a68a2/raw/aaa4b1f676d14571875e005a0b59c6a5165e7e17/populations.csv")
.await(ready);

function ready(error, us, loc, pop) {
if (error) throw error;

const data = pop.sort((a, b) => +b.population - +a.population)

console.log('populations', data)

const locations = loc.filter(d => d.latitude !== '' && d.longitude !== '')

const maxPop = d3.max(data, d => +d.population)
console.log('maxPop', maxPop)

const newDict = {};

data.forEach(function (d) {
  d.population = +d.population;
  newDict[d.location_code] = { pop: d.population };
})
map.append("g")
  .attr("class", "states")
  .selectAll("path")
  .data(topojson.feature(us, us.objects.states).features)
  .enter().append("path")
  .attr("d", path);

const location_data =
  locations
    .map(l => ({
      ...l,
      pop: newDict[l.code] ? newDict[l.code].pop : 0,
    }))
    .sort((a, b) => b.pop - a.pop);
console.log('location_data', location_data)

map.append('g')
  .attr('class', 'location')
  .selectAll("circle")
  .data(location_data)
  .enter()
  .append("circle")
  .attr("cx", function (d) {
    return projection([d.longitude, d.latitude])[0];
  })
  .attr("cy", function (d) {
    return projection([d.longitude, d.latitude])[1];
  })
  .attr('r', 10)
  .style("fill", "navy")
  .style("opacity", 0.5)

}

.states {
  fill: #eee;
  stroke: #999;
  stroke-linejoin: round;
}

user_294949 · Answer

The goal is to reshape the data into the form...

{ location_code, population, latitude, longitude }

...accounting for the fact that not all locations have population data. Choose a default for unknown populations based on what works well functionally with d3, say 0.

Build the index from the more complete data set (which defines the locations) setting default population, then add the population data where you have it.

let locationObjs = [
  { location_code: 'AB', latitude: 39.19969535, longitude: -76.59393033 },
  { location_code: 'BC', latitude: 35.0526543, longitude: -106.6292985 },
  { location_code: 'CD', latitude: 40.19177, longitude: -75.91592 },
  { location_code: 'DE', latitude: 32.56212771, longitude: -99.63538265 },
  { location_code: 'EF', latitude: 43.60675502, longitude: -116.269811 },
  { location_code: 'FG', latitude: 37.103848, longitude: -85.3066339 },
  { location_code: 'GH', latitude: 39.986877, longitude: -104.799156 },
  { location_code: 'HI', latitude: 31.56917545, longitude: -91.23046875 },
  { location_code: 'IJ', latitude: 47.12747, longitude: -118.38267 },
  { location_code: 'JK', latitude: 44.0115189, longitude: -73.16354529 },
  { location_code: 'KL', latitude: 34.55772948, longitude: -117.4418648 },
  { location_code: 'LM', latitude: 32.8571484, longitude: -104.4153316 },
  { location_code: 'MN', latitude: 13.476607, longitude: 144.747679 },
  { location_code: 'NO', latitude: 18.49428, longitude: -67.14213 },
  { location_code: 'OP', latitude: 33.8205041, longitude: -117.9096302 },
  { location_code: 'PQ', latitude: 18.439917, longitude: -66.004817 },
  { location_code: 'QR', latitude: 42.505131, longitude: -96.404939 },
  { location_code: 'RS', latitude: 30.471947, longitude: -84.354361 },
  { location_code: 'ST', latitude: 34.015686, longitude: -90.39034 },
  { location_code: 'TU', latitude: 27.959412, longitude: -82.372276 }
];

let populationObjs = [
  { location_code: 'AB', population: 20 },
  { location_code: 'CD', population: 10 },
  { location_code: 'DE', population: 30 },
  { location_code: 'FG', population: 50 },
  { location_code: 'GH', population: 30 },
  { location_code: 'IJ', population: 20 },
  { location_code: 'JK', population: 15 },
  { location_code: 'KL', population: 40 },
  { location_code: 'LM', population: 30 },
  { location_code: 'OP', population: 20 },
  { location_code: 'RS', population: 10 },
  { location_code: 'TU', population: 5 }
];

// presuming the arrays are large, it will be worthwhile to build an index
// what the OP calls "newDict" might be better called locationIndex...

let locationIndex = locationObjs.reduce((acc, el) => {
  acc[el.location_code] = { ...el, population: 0 };  // note we supply a default population
  return acc;
}, {});

// change the default popluations to have values where we know them
populationObjs.forEach(popObj => {
  locationIndex[popObj.location_code].population = popObj.population
});

// if we no longer need O(1) access to objects by location code, discard the index keeping just its values
let location_data = Object.values(locationIndex);

// these can be sorted. the defaulted 0 values will sort first
location_data.sort((a,b) => a.population - b.population)
console.log(location_data)

// from here:
// map.append('g')
//   .attr('class', 'location')
//   .selectAll("circle")
//   .data(location_data)

user_5123186 · Answer

There's a simpler and better-structured way of combining two different object arrays with one common key, instead of as you have done the same thing with multiple manipulations to reach your desired final object array.

Firstly to fix errors in your existing code, here is the fix, where I am adding a check, if the population data doesn't exist for a state, will assume it as 0, that's it. Here is the solved Plunker for it.

FIX:

// if the population data doesn't exist for a state, will assume it as 0
const location_data = locations
   .sort((a, b) => (!!newDict[b.code] ? newDict[b.code].pop : 0) - (newDict[a.code] ? newDict[a.code].pop : 0));

location_data.forEach(function (d) {
  d.pop = !!newDict[d.code] ? newDict[d.code].pop : 0
});

Now, the simpler solution to reach desired final object array or say merge two different object arrays into one, based on one common key.

We have populations data that looks like this: (assumed we have fetched CSV file and saved into JSON)

// populations
var pop = [
  { location_code: "FG", population: 50 },
  { location_code: "KL", population: 40 },
  { location_code: "DE", population: 30 },
  { location_code: "GH", population: 30 },
  { location_code: "LM", population: 30 },
  { location_code: "AB", population: 20 },
  { location_code: "IJ", population: 20 },
  { location_code: "OP", population: 20 },
  { location_code: "JK", population: 15 },
  { location_code: "CD", population: 10 },
  { location_code: "RS", population: 10 },
  { location_code: "TU", population: 5 }
];

And we have location coordinates data that looks like this: (assumed we have fetched CSV file and saved into JSON)

// location coordinates
var loc = [
  { code: "AB", latitude: "39.19969535", longitude: "-76.59393033" },
  { code: "BC", latitude: "35.0526543", longitude: "-106.6292985" },
  { code: "CD", latitude: "40.19177", longitude: "-75.91592" },
  { code: "DE", latitude: "32.56212771", longitude: "-99.63538265" },
  { code: "EF", latitude: "43.60675502", longitude: "-116.269811" },
  { code: "FG", latitude: "37.103848", longitude: "-85.3066339" },
  { code: "GH", latitude: "39.986877", longitude: "-104.799156" },
  { code: "HI", latitude: "31.56917545", longitude: "-91.23046875" },
  { code: "IJ", latitude: "47.12747", longitude: "-118.38267" },
  { code: "JK", latitude: "44.0115189", longitude: "-73.16354529" },
  { code: "KL", latitude: "34.55772948", longitude: "-117.4418648" },
  { code: "LM", latitude: "32.8571484", longitude: "-104.4153316" },
  { code: "MN", latitude: "13.476607", longitude: "144.747679" },
  { code: "NO", latitude: "18.49428", longitude: "-67.14213" },
  { code: "OP", latitude: "33.8205041", longitude: "-117.9096302" },
  { code: "PQ", latitude: "18.439917", longitude: "-66.004817" },
  { code: "QR", latitude: "42.505131", longitude: "-96.404939" },
  { code: "RS", latitude: "30.471947", longitude: "-84.354361" },
  { code: "ST", latitude: "34.015686", longitude: "-90.39034" },
  { code: "TU", latitude: "27.959412", longitude: "-82.372276" }
];

Now, we can use the following steps and following code:

// concatenate both arrays and reduce this concatenated array into simpler one
let locPopObject = pop.concat(...loc).reduce((finalObj, curr) => {
  
  // curr object is a POPULATION OBJECT
  if (curr.hasOwnProperty('location_code')) {
    // current state code
    let currStateCode = curr['location_code'];

    // if state code exist in finalObj, 
    // then use same or create empty obj for current state code
    finalObj[currStateCode] = finalObj[currStateCode] || {};

    // add population data of curr object in finalObj against pop key
    finalObj[currStateCode]['pop'] = +curr['population'];
    return finalObj;
  }

  // -------------------------------------------------------------------
  // curr object is a LOCATION OBJECT

  // current state code
  let currStateCode = curr['code'];
  
  // if state code exist in finalObj, 
  // then use same or create empty obj for current state code
  finalObj[currStateCode] = finalObj[currStateCode] || {};

  // add all key-value pairs of curr object in finalObj
  Array.from(Object.keys(curr)).forEach(key => {
      finalObj[currStateCode][key] = curr[key];
  });

  // since Location array is concatenated last, 
  // therefore till now population array data is processed 
  // and now its processing location array data
  // so if our finalObj (reduced object) doesn't have pop property in it,
  // then assign it as zero
  finalObj[currStateCode]['pop'] = finalObj[currStateCode]['pop'] || +0;
  return finalObj;      
}, {});

// get values of locPopObject, which is a array we want to have
// also sort the array before storing its value to a variable
let locPopArray = Array.from(Object.values(locPopObject))
                    .sort((a, b) => b.pop - a.pop);

That's it, this is an optimized, simpler, and easy solution to manipulate data in any way we want and get the merged output.

Here's is the working map example Plunker using this solution.

Create map that combines data from two files with common column - but data doesn't match one to one

3 Answers