Finding unique values in a .CSV

Question

I have a .CSV data file with a ton, and I mean a TON (80+ million lines) of data.

The data is all in two columns, and looks like the following:

 src      |      dst 
123123    |     456456
321321    |     654654
987987    |     789789
123123    |     456456

and so on for 80 million lines.

(note: I know that the delimiter should be a ',' in a .CSV, but in this case it's an '|' . The file extension is still .CSV)

I'm trying to figure out how to write a program that will read in all the data, and print out the number of repeated values in the 'src' field. For example, in my example, the output would look like '123123: showed up 2 times'

I've tried a few solutions, most notably this: How to read the csv file properly if each row contains different number of fields (number quite big)?

I wrote a loop to split the 'src' from the 'dst' with 'newData' being the .CSV file

    //go through each line and split + link the data to src/dst
  data.forEach(function (line) {
    newData = line.split('|'); //note, split returns an array

    let src = newData[0]; //src from data.csv
    let dst = newData[1]; //dst from data.csv


    //test print the data
    //console.log(newData);
  });

But am having issues getting a count duplicate values from the newData[0] (src) column.

Will you always know how many lines you got in the file, every time you get the file ? — pparas, Jun 07 '16 at 21:25

BryanGrezeszak · Accepted Answer · 2016-06-07T21:53:43.153

4

It can be done in a single loop (an O(N) complexity solution...very important if you have 80 million lines...):

function solution(A)
{
    var lines = A.split(/\r?\n/g);

    var counts = {};
    var multiples = {};

    for (var i=0, ii=lines.length; i<ii; i++)
    {
        var splt = lines[i].split(/\s*\|\s*/g);
        var val = splt[0];

        if (!counts[val]) {
            counts[val] = 1;
        } else {
            counts[val]++;
            multiples[val] = counts[val];
        }
    }

    return multiples;
}

That returns an object with key of all the values that exist multiple times in the first column, and their values represent how many times they appear. For example your given string would return the object:

{ '123123': 2 }

because that value is seen twice.

Here is a jsfiddle of it working (it logs it to the console, so open your dev tools): https://jsfiddle.net/x8b7ko3g/

edited Jun 07 '16 at 21:53

answered Jun 07 '16 at 21:44

BryanGrezeszak

577
2
8

sorry, I now see you want it just for the "src" column...not a hard change, just gimme a minute... – BryanGrezeszak Jun 07 '16 at 21:47
this is a great solution, thanks! I'll wait on your change – Edon Jun 07 '16 at 21:48
...an *approximately* O(n) solution, which probably is as fast as it gets. – le_m Jun 07 '16 at 22:07

score 0 · Answer 2 · answered Jun 07 '16 at 21:34

0

I would try to sort the file first, e.g. using the command line tool "sort". After that, you can count how often the same "src" repeats until you find another "src".

answered Jun 07 '16 at 21:34

Dennis B.

1,567
1
13
20

Finding unique values in a .CSV

2 Answers2