combine datasets (CSVs) with javascript using common key, where one dataset has multiple entries with that key

user_4451600 0
Viewed 45

I'm trying to combine two CSV files.

One file (places.csv) has the latitude and longitude of four locations: enter image description here

The other file (data.csv) has values for those locations on multiple days (~180 days each): enter image description here

Both files have a 'key' in common -- a three letter code that belongs to each specific location. I'm trying to combine these two datasets like this: enter image description here

However, when I try to combine, I just get just four entries -- one for each place in the locations file (places.csv) -- that uses the last date for the matching place in the other file (data.csv).

What am I doing wrong? Any help most appreciated!

PS - this is basically the same problem, but the answer is in Bash, and I want to solve it with Javascript. I'm already using d3.js and jquery if either of those libraries are helpful, but plain js is preferable.

Here is my code in a Plunker

Code:

<!DOCTYPE html>

<head>
    <meta charset="utf-8">
    <script src="https://d3js.org/d3.v4.min.js"></script>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

</head>

<body>
    <pre>
      <div id="data"></div>
    </pre>
    <script>
        const parseDate = d3.timeParse("%Y-%m-%d");

        d3.csv("places.csv", function (places) {
            d3.csv("data.csv", function (data) {

                const dataset = data.sort((a, b) => b.date - a.date)
                const locations = places.filter(d => d.latitude !== '' && d.longitude !== '')

                const newDict = {};

                dataset.forEach(function (d) {
                    d.date = parseDate(d.date);
                    d.value = +d.value;
                    newDict[d.code_name] = { val: d.value, name: d.name, date: d.date };
                })

                console.log('dataset', dataset)
                console.log('newDict', newDict)

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

                location_data.forEach(function (d) {
                    d.value = newDict[d.code].val;
                    d.name = newDict[d.code].name;
                    d.date = newDict[d.code].date;
                    d.lat = +d.lat;
                    d.lng = +d.lng;
                })
                console.log('location data', location_data)

                const combined = JSON.stringify(location_data, null, 2);
                $("#data").html(combined)

            })
        })

    </script>
</body>

places.csv:

code,lng,lat
ABC,33.126338,-116.900024
BEC,37.8149,-122.061
XYZ,37.1782,-121.848
RMA,38.5138,-123.12

data.csv:

code_name,name,date,value
ABC,facility A,2001-01-01,7
ABC,facility A,2001-01-02,16
ABC,facility A,2001-01-03,20
ABC,facility A,2001-01-04,8
ABC,facility A,2001-01-05,15
ABC,facility A,2001-01-06,19
ABC,facility A,2001-01-07,10
ABC,facility A,2001-01-08,16
ABC,facility A,2001-01-09,22
ABC,facility A,2001-01-10,5
ABC,facility A,2001-01-11,13
ABC,facility A,2001-01-12,24
ABC,facility A,2001-01-13,6
ABC,facility A,2001-01-14,22
ABC,facility A,2001-01-15,9
ABC,facility A,2001-01-16,10
ABC,facility A,2001-01-17,19
ABC,facility A,2001-01-18,25
ABC,facility A,2001-01-19,14
ABC,facility A,2001-01-20,18
ABC,facility A,2001-01-21,23
ABC,facility A,2001-01-22,14
ABC,facility A,2001-01-23,9
ABC,facility A,2001-01-24,13
ABC,facility A,2001-01-25,10
ABC,facility A,2001-01-26,13
ABC,facility A,2001-01-27,16
ABC,facility A,2001-01-28,20
ABC,facility A,2001-01-29,15
ABC,facility A,2001-01-30,13
ABC,facility A,2001-01-31,25
ABC,facility A,2001-02-01,22
ABC,facility A,2001-02-02,18
ABC,facility A,2001-02-03,12
ABC,facility A,2001-02-04,8
ABC,facility A,2001-02-05,19
ABC,facility A,2001-02-06,7
ABC,facility A,2001-02-07,13
ABC,facility A,2001-02-08,19
ABC,facility A,2001-02-09,4
ABC,facility A,2001-02-10,19
ABC,facility A,2001-02-11,10
ABC,facility A,2001-02-12,25
ABC,facility A,2001-02-13,3
ABC,facility A,2001-02-14,16
ABC,facility A,2001-02-15,17
ABC,facility A,2001-02-16,13
ABC,facility A,2001-02-17,17
ABC,facility A,2001-02-18,22
ABC,facility A,2001-02-19,8
ABC,facility A,2001-02-20,18
ABC,facility A,2001-02-21,4
ABC,facility A,2001-02-22,7
ABC,facility A,2001-02-23,24
ABC,facility A,2001-02-24,17
ABC,facility A,2001-02-25,3
ABC,facility A,2001-02-26,3
ABC,facility A,2001-02-27,17
ABC,facility A,2001-02-28,22
ABC,facility A,2001-03-01,3
ABC,facility A,2001-03-02,4
ABC,facility A,2001-03-03,10
ABC,facility A,2001-03-04,18
ABC,facility A,2001-03-05,15
ABC,facility A,2001-03-06,23
ABC,facility A,2001-03-07,15
ABC,facility A,2001-03-08,17
ABC,facility A,2001-03-09,17
ABC,facility A,2001-03-10,15
ABC,facility A,2001-03-11,21
ABC,facility A,2001-03-12,24
ABC,facility A,2001-03-13,4
ABC,facility A,2001-03-14,23
ABC,facility A,2001-03-15,22
ABC,facility A,2001-03-16,4
ABC,facility A,2001-03-17,24
ABC,facility A,2001-03-18,23
ABC,facility A,2001-03-19,6
ABC,facility A,2001-03-20,21
ABC,facility A,2001-03-21,24
ABC,facility A,2001-03-22,21
ABC,facility A,2001-03-23,24
ABC,facility A,2001-03-24,5
ABC,facility A,2001-03-25,25
ABC,facility A,2001-03-26,7
ABC,facility A,2001-03-27,12
ABC,facility A,2001-03-28,4
ABC,facility A,2001-03-29,6
ABC,facility A,2001-03-30,8
ABC,facility A,2001-03-31,6
ABC,facility A,2001-04-01,11
ABC,facility A,2001-04-02,21
ABC,facility A,2001-04-03,15
ABC,facility A,2001-04-04,24
ABC,facility A,2001-04-05,9
ABC,facility A,2001-04-06,6
ABC,facility A,2001-04-07,5
ABC,facility A,2001-04-08,12
ABC,facility A,2001-04-09,4
ABC,facility A,2001-04-10,6
ABC,facility A,2001-04-11,15
ABC,facility A,2001-04-12,15
ABC,facility A,2001-04-13,14
ABC,facility A,2001-04-14,3
ABC,facility A,2001-04-15,20
ABC,facility A,2001-04-16,20
ABC,facility A,2001-04-17,10
ABC,facility A,2001-04-18,10
ABC,facility A,2001-04-19,14
ABC,facility A,2001-04-20,6
ABC,facility A,2001-04-21,12
ABC,facility A,2001-04-22,13
ABC,facility A,2001-04-23,20
ABC,facility A,2001-04-24,14
ABC,facility A,2001-04-25,23
ABC,facility A,2001-04-26,4
ABC,facility A,2001-04-27,22
ABC,facility A,2001-04-28,23
ABC,facility A,2001-04-29,15
ABC,facility A,2001-04-30,9
ABC,facility A,2001-05-01,14
ABC,facility A,2001-05-02,21
ABC,facility A,2001-05-03,13
ABC,facility A,2001-05-04,24
ABC,facility A,2001-05-05,15
ABC,facility A,2001-05-06,13
ABC,facility A,2001-05-07,19
ABC,facility A,2001-05-08,12
ABC,facility A,2001-05-09,7
ABC,facility A,2001-05-10,22
ABC,facility A,2001-05-11,15
ABC,facility A,2001-05-12,15
ABC,facility A,2001-05-13,19
ABC,facility A,2001-05-14,10
ABC,facility A,2001-05-15,3
ABC,facility A,2001-05-16,17
ABC,facility A,2001-05-17,3
ABC,facility A,2001-05-18,7
ABC,facility A,2001-05-19,25
ABC,facility A,2001-05-20,14
ABC,facility A,2001-05-21,20
ABC,facility A,2001-05-22,10
ABC,facility A,2001-05-23,7
ABC,facility A,2001-05-24,12
ABC,facility A,2001-05-25,24
ABC,facility A,2001-05-26,8
ABC,facility A,2001-05-27,16
ABC,facility A,2001-05-28,23
ABC,facility A,2001-05-29,9
ABC,facility A,2001-05-30,3
ABC,facility A,2001-05-31,8
ABC,facility A,2001-06-01,3
ABC,facility A,2001-06-02,9
ABC,facility A,2001-06-03,13
ABC,facility A,2001-06-04,6
ABC,facility A,2001-06-05,13
ABC,facility A,2001-06-06,3
ABC,facility A,2001-06-07,3
ABC,facility A,2001-06-08,12
ABC,facility A,2001-06-09,23
ABC,facility A,2001-06-10,4
ABC,facility A,2001-06-11,22
ABC,facility A,2001-06-12,4
ABC,facility A,2001-06-13,11
ABC,facility A,2001-06-14,20
ABC,facility A,2001-06-15,19
ABC,facility A,2001-06-16,18
ABC,facility A,2001-06-17,10
ABC,facility A,2001-06-18,10
ABC,facility A,2001-06-19,21
ABC,facility A,2001-06-20,25
ABC,facility A,2001-06-21,25
ABC,facility A,2001-06-22,9
ABC,facility A,2001-06-23,9
ABC,facility A,2001-06-24,17
ABC,facility A,2001-06-25,7
ABC,facility A,2001-06-26,5
ABC,facility A,2001-06-27,18
ABC,facility A,2001-06-28,17
ABC,facility A,2001-06-29,13
ABC,facility A,2001-06-30,19
BEC,facility B,2001-01-01,0
BEC,facility B,2001-06-12,3
BEC,facility B,2001-06-13,4
BEC,facility B,2001-06-14,12
BEC,facility B,2001-06-15,6
BEC,facility B,2001-06-16,15
BEC,facility B,2001-06-17,15
BEC,facility B,2001-06-18,9
BEC,facility B,2001-06-19,17
BEC,facility B,2001-06-20,13
BEC,facility B,2001-06-21,15
BEC,facility B,2001-06-22,18
BEC,facility B,2001-06-23,19
BEC,facility B,2001-06-24,13
BEC,facility B,2001-06-25,18
BEC,facility B,2001-06-26,13
BEC,facility B,2001-06-27,23
BEC,facility B,2001-06-28,20
BEC,facility B,2001-06-29,11
BEC,facility B,2001-06-30,22
XYZ,facility C,2001-01-01,0
XYZ,facility C,2001-04-12,24
XYZ,facility C,2001-04-13,5
XYZ,facility C,2001-04-14,13
XYZ,facility C,2001-04-15,15
XYZ,facility C,2001-04-16,21
XYZ,facility C,2001-04-17,5
XYZ,facility C,2001-04-18,11
XYZ,facility C,2001-04-19,13
XYZ,facility C,2001-04-20,23
XYZ,facility C,2001-04-21,12
XYZ,facility C,2001-04-22,7
XYZ,facility C,2001-04-23,6
XYZ,facility C,2001-04-24,5
XYZ,facility C,2001-04-25,7
XYZ,facility C,2001-04-26,14
XYZ,facility C,2001-04-27,7
XYZ,facility C,2001-04-28,18
XYZ,facility C,2001-04-29,24
XYZ,facility C,2001-04-30,17
XYZ,facility C,2001-05-01,8
XYZ,facility C,2001-05-02,4
XYZ,facility C,2001-05-03,5
XYZ,facility C,2001-05-04,15
XYZ,facility C,2001-05-05,10
XYZ,facility C,2001-05-06,3
XYZ,facility C,2001-05-07,20
XYZ,facility C,2001-05-08,25
XYZ,facility C,2001-05-09,23
XYZ,facility C,2001-05-10,13
XYZ,facility C,2001-05-11,6
XYZ,facility C,2001-05-12,11
XYZ,facility C,2001-05-13,24
XYZ,facility C,2001-05-14,20
XYZ,facility C,2001-05-15,8
XYZ,facility C,2001-05-16,6
XYZ,facility C,2001-05-17,15
XYZ,facility C,2001-05-18,21
XYZ,facility C,2001-05-19,24
XYZ,facility C,2001-05-20,16
XYZ,facility C,2001-05-21,24
XYZ,facility C,2001-05-22,17
XYZ,facility C,2001-05-23,20
XYZ,facility C,2001-05-24,10
XYZ,facility C,2001-05-25,22
XYZ,facility C,2001-05-26,18
XYZ,facility C,2001-05-27,11
XYZ,facility C,2001-05-28,9
XYZ,facility C,2001-05-29,24
XYZ,facility C,2001-05-30,19
XYZ,facility C,2001-05-31,8
XYZ,facility C,2001-06-01,5
XYZ,facility C,2001-06-02,25
XYZ,facility C,2001-06-03,13
XYZ,facility C,2001-06-04,18
XYZ,facility C,2001-06-05,23
XYZ,facility C,2001-06-06,14
XYZ,facility C,2001-06-07,13
XYZ,facility C,2001-06-08,10
XYZ,facility C,2001-06-09,20
XYZ,facility C,2001-06-10,20
XYZ,facility C,2001-06-11,20
XYZ,facility C,2001-06-12,21
XYZ,facility C,2001-06-13,23
XYZ,facility C,2001-06-14,8
XYZ,facility C,2001-06-15,8
XYZ,facility C,2001-06-16,4
XYZ,facility C,2001-06-17,8
XYZ,facility C,2001-06-18,22
XYZ,facility C,2001-06-19,19
XYZ,facility C,2001-06-20,18
XYZ,facility C,2001-06-21,23
XYZ,facility C,2001-06-22,7
XYZ,facility C,2001-06-23,8
XYZ,facility C,2001-06-24,21
XYZ,facility C,2001-06-25,20
XYZ,facility C,2001-06-26,9
XYZ,facility C,2001-06-27,3
XYZ,facility C,2001-06-28,23
XYZ,facility C,2001-06-29,25
XYZ,facility C,2001-06-30,20
RMA,facility D,2001-01-01,19
RMA,facility D,2001-01-02,7
RMA,facility D,2001-01-03,10
RMA,facility D,2001-01-04,3
RMA,facility D,2001-01-05,23
RMA,facility D,2001-01-06,11
RMA,facility D,2001-01-07,4
RMA,facility D,2001-01-08,18
RMA,facility D,2001-01-09,8
RMA,facility D,2001-01-10,9
RMA,facility D,2001-01-11,11
RMA,facility D,2001-01-12,16
RMA,facility D,2001-01-13,16
RMA,facility D,2001-01-14,25
RMA,facility D,2001-01-15,23
RMA,facility D,2001-01-16,14
RMA,facility D,2001-01-17,10
RMA,facility D,2001-01-18,8
RMA,facility D,2001-01-19,5
RMA,facility D,2001-01-20,4
RMA,facility D,2001-01-21,18
RMA,facility D,2001-01-22,3
RMA,facility D,2001-01-23,19
RMA,facility D,2001-01-24,14
RMA,facility D,2001-01-25,22
RMA,facility D,2001-01-26,5
RMA,facility D,2001-01-27,9
RMA,facility D,2001-01-28,5
RMA,facility D,2001-01-29,17
RMA,facility D,2001-01-30,6
RMA,facility D,2001-01-31,5
RMA,facility D,2001-02-01,11
RMA,facility D,2001-02-02,22
RMA,facility D,2001-02-03,12
RMA,facility D,2001-02-04,12
RMA,facility D,2001-02-05,12
RMA,facility D,2001-02-06,3
RMA,facility D,2001-02-07,23
RMA,facility D,2001-02-08,9
RMA,facility D,2001-02-09,13
RMA,facility D,2001-02-10,13
RMA,facility D,2001-02-11,6
RMA,facility D,2001-02-12,5
RMA,facility D,2001-02-13,4
RMA,facility D,2001-02-14,17
RMA,facility D,2001-02-15,12
RMA,facility D,2001-02-16,12
RMA,facility D,2001-02-17,8
RMA,facility D,2001-02-18,8
RMA,facility D,2001-02-19,3
RMA,facility D,2001-02-20,17
RMA,facility D,2001-02-21,17
RMA,facility D,2001-02-22,24
RMA,facility D,2001-02-23,19
RMA,facility D,2001-02-24,22
RMA,facility D,2001-02-25,19
RMA,facility D,2001-02-26,6
RMA,facility D,2001-02-27,7
RMA,facility D,2001-02-28,25
RMA,facility D,2001-03-01,8
RMA,facility D,2001-03-02,13
RMA,facility D,2001-03-03,18
RMA,facility D,2001-03-04,7
RMA,facility D,2001-03-05,21
RMA,facility D,2001-03-06,11
RMA,facility D,2001-03-07,5
RMA,facility D,2001-03-08,10
RMA,facility D,2001-03-09,7
RMA,facility D,2001-03-10,6
RMA,facility D,2001-03-11,3
RMA,facility D,2001-03-12,25
RMA,facility D,2001-03-13,10
RMA,facility D,2001-03-14,15
RMA,facility D,2001-03-15,25
RMA,facility D,2001-03-16,6
RMA,facility D,2001-03-17,14
RMA,facility D,2001-03-18,10
RMA,facility D,2001-03-19,10
RMA,facility D,2001-03-20,25
RMA,facility D,2001-03-21,17
RMA,facility D,2001-03-22,17
RMA,facility D,2001-03-23,10
RMA,facility D,2001-03-24,14
RMA,facility D,2001-03-25,24
RMA,facility D,2001-03-26,17
RMA,facility D,2001-03-27,5
RMA,facility D,2001-03-28,9
RMA,facility D,2001-03-29,12
RMA,facility D,2001-03-30,20
RMA,facility D,2001-03-31,10
RMA,facility D,2001-04-01,4
RMA,facility D,2001-04-02,8
RMA,facility D,2001-04-03,10
RMA,facility D,2001-04-04,22
RMA,facility D,2001-04-05,18
RMA,facility D,2001-04-06,7
RMA,facility D,2001-04-07,15
RMA,facility D,2001-04-08,11
RMA,facility D,2001-04-09,13
RMA,facility D,2001-04-10,20
RMA,facility D,2001-04-11,17
RMA,facility D,2001-04-12,8
RMA,facility D,2001-04-13,10
RMA,facility D,2001-04-14,3
RMA,facility D,2001-04-15,15
RMA,facility D,2001-04-16,6
RMA,facility D,2001-04-17,11
RMA,facility D,2001-04-18,15
RMA,facility D,2001-04-19,6
RMA,facility D,2001-04-20,24
RMA,facility D,2001-04-21,7
RMA,facility D,2001-04-22,7
RMA,facility D,2001-04-23,11
RMA,facility D,2001-04-24,13
RMA,facility D,2001-04-25,3
RMA,facility D,2001-04-26,42
RMA,facility D,2001-04-27,10
RMA,facility D,2001-04-28,22
RMA,facility D,2001-04-29,17
RMA,facility D,2001-04-30,15
RMA,facility D,2001-05-01,5
RMA,facility D,2001-05-02,10
RMA,facility D,2001-05-03,6
RMA,facility D,2001-05-04,5
RMA,facility D,2001-05-05,14
RMA,facility D,2001-05-06,21
RMA,facility D,2001-05-07,14
RMA,facility D,2001-05-08,20
RMA,facility D,2001-05-09,9
RMA,facility D,2001-05-10,10
RMA,facility D,2001-05-11,7
RMA,facility D,2001-05-12,8
RMA,facility D,2001-05-13,6
RMA,facility D,2001-05-14,23
RMA,facility D,2001-05-15,22
RMA,facility D,2001-05-16,9
RMA,facility D,2001-05-17,5
RMA,facility D,2001-05-18,12
RMA,facility D,2001-05-19,12
RMA,facility D,2001-05-20,10
RMA,facility D,2001-05-21,20
RMA,facility D,2001-05-22,23
RMA,facility D,2001-05-23,10
RMA,facility D,2001-05-24,3
RMA,facility D,2001-05-25,17
RMA,facility D,2001-05-26,5
RMA,facility D,2001-05-27,3
RMA,facility D,2001-05-28,6
RMA,facility D,2001-05-29,24
RMA,facility D,2001-05-30,10
RMA,facility D,2001-05-31,25
RMA,facility D,2001-06-01,24
RMA,facility D,2001-06-02,23
RMA,facility D,2001-06-03,10
RMA,facility D,2001-06-04,7
RMA,facility D,2001-06-05,5
RMA,facility D,2001-06-06,15
RMA,facility D,2001-06-07,33
RMA,facility D,2001-06-08,8
RMA,facility D,2001-06-09,24
RMA,facility D,2001-06-10,11
RMA,facility D,2001-06-11,10
RMA,facility D,2001-06-12,5
RMA,facility D,2001-06-13,22
RMA,facility D,2001-06-14,12
RMA,facility D,2001-06-15,20
RMA,facility D,2001-06-16,3
RMA,facility D,2001-06-17,6
RMA,facility D,2001-06-18,9
RMA,facility D,2001-06-19,15
RMA,facility D,2001-06-20,15
RMA,facility D,2001-06-21,10
RMA,facility D,2001-06-22,5
RMA,facility D,2001-06-23,15
RMA,facility D,2001-06-24,21
RMA,facility D,2001-06-25,15
RMA,facility D,2001-06-26,22
RMA,facility D,2001-06-27,4
RMA,facility D,2001-06-28,9
RMA,facility D,2001-06-29,21
RMA,facility D,2001-06-30,8
csv javascript
1 Answers
let placesDict = {};
d3.csv("places.csv", function (places) {

    const locations = places.filter(d => d.latitude !== '' && d.longitude !== '')
    locations.forEach((p)=>{
        placesDict[p.code] = {...p};
    })
})
let dataList = [];
d3.csv("data.csv", function (data) {
    const dataset = data.sort((a, b) => b.date - a.date)

    const newDict = {};
    dataset.forEach(function (d) {
        d.date = parseDate(d.date);
        d.value = +d.value;
        dataList .push(Object.assign({...(placesDict[d.code_name] || {})}, { val: d.value, name: d.name, date: d.date }));
    })

    const combined = JSON.stringify(dataList, null, 2);
    $("#data").html(combined)

})

I think you overcomplicated it a bit. If you just turn the csv file containing locations into a dictionary you can iterate over the second file and simply add the location information to each entry and output a list.

user_35047490
Related