How to sort json type data using javascript or d3.js?

user_12587043 0
Viewed 32

I am working on a d3.js based application where I am getting data from mysql.

I took one column named time_in_service from mysql table whose datatype in mysql is 'int' and grouped it based on number of frequency of values. The grouped function is below:

      tisData = d3.nest()
        .key(function (d) { return d.time_in_service; })
        .rollup(function (v) { return v.length })
        .entries(data);

The tisData which is created above is in below form while printed using console.log:

0: {key: '30', value: 9}
1: {key: '16', value: 30}
2: {key: '6', value: 12}
3: {key: '20', value: 22}
4: {key: '14', value: 15}
5: {key: '21', value: 31}
6: {key: '17', value: 28}
7: {key: '32', value: 7}
8: {key: '8', value: 20}
9: {key: '15', value: 25}
10: {key: 'null', value: 61}
11: {key: '23', value: 28}
12: {key: '18', value: 39}
13: {key: '9', value: 15}
14: {key: '29', value: 8}
15: {key: '27', value: 13}
16: {key: '7', value: 5}
17: {key: '33', value: 3}
18: {key: '11', value: 12}
19: {key: '13', value: 24}
20: {key: '24', value: 29}
21: {key: '10', value: 12}
22: {key: '19', value: 24}
23: {key: '22', value: 32}
24: {key: '1', value: 4}
25: {key: '26', value: 17}
26: {key: '28', value: 15}
27: {key: '25', value: 27}
28: {key: '34', value: 1}
29: {key: '31', value: 1}
30: {key: '4', value: 4}
31: {key: '3', value: 7}
32: {key: '12', value: 14}
33: {key: '5', value: 3}
34: {key: '2', value: 5}

Now I wanted to sort the data in ascending order using key so that null values will come first. Notice the data also has null values. I used below formula to sort the data:

      tisData.sort(function (a, b) {
        return a.key - b.key;
      });

I got below results:

0: {key: '1', value: 4}
1: {key: '2', value: 5}
2: {key: '3', value: 7}
3: {key: '4', value: 4}
4: {key: '5', value: 3}
5: {key: '6', value: 12}
6: {key: '7', value: 5}
7: {key: '8', value: 20}
8: {key: '9', value: 15}
9: {key: '10', value: 12}
10: {key: '11', value: 12}
11: {key: '12', value: 14}
12: {key: '13', value: 24}
13: {key: '14', value: 15}
14: {key: '15', value: 25}
15: {key: '16', value: 30}
16: {key: '17', value: 28}
17: {key: '18', value: 39}
18: {key: '19', value: 24}
19: {key: '20', value: 22}
20: {key: '21', value: 31}
21: {key: '22', value: 32}
22: {key: '23', value: 28}
23: {key: '24', value: 29}
24: {key: '25', value: 27}
25: {key: '26', value: 17}
26: {key: '27', value: 13}
27: {key: '28', value: 15}
28: {key: '29', value: 8}
29: {key: '30', value: 9}
30: {key: '32', value: 7}
31: {key: 'null', value: 61}
32: {key: '31', value: 1}
33: {key: '33', value: 3}
34: {key: '34', value: 1}

If you see, last 4-5 sorted values are giving wrong order. Can somebody please tell me what is wrong in here and what is the correct piece of code I can use to sort it and get null values at first place?

d3.js javascript
1 Answers

Without seeing more of your code, I can't be sure, but one important fact is: the key property is a string, which means it will be sorted according to lexicographic rules, i.e. alphabetically. That means "1000" will come before "2".

If you want to sort them as integers, you will need to convert them to numbers. You could convert them in the sort function, although that will result in each value being converted multiple times. Or you could convert the dataset once after you get it from mysql, before feeding it into your front-end sorting code.

Second, your sort implementation is pretty crude: it works fine when all the inputs are numbers, but it will handle other values badly because Javascript engines perform type coercion when doing math.

Your JS console should reveal that 1 - null yields undefined. The underlying quicksort algorithm will interpret that to mean the null item is already in the correct place -- that is, the placement of the null row will be influenced by its original position in the unsorted array as well as how many comparisons it appears in, rather than being determined by the rules dictating the desired total order.

Again, you'll need to define special handling for null values (and any other non-digit values). The three obvious choices are:

  • omit null entries completely: garbage data should be omitted
  • sort null to the beginning
  • sort null to the end

What you want will determine whether this logic goes into the sort algorithm, or a pre-sort transformation step.

Either way, data type matters when comparing values.

As an aside: return a - b is perhaps the crudest comparison, and is guaranteed to produce bad results if even one item is not a number. As a general rule, I use this version, which is a bit more verbose but is more resilient against the occasional odd value (which collect at the beginning and end of the list):

// not amazing, but superior to `return a - b`
.sort(( a, b ) => {
  if(a < b) return -1
  if(b < a) return 1
  return 0
})
user_8144630
Related