DataFrame distinct with multiple columns in the selector is not working if the name of the columns is variable #155

Activity

1. marcodisa commented on Jan 13, 2023

   

   marcodisa

   on Jan 13, 2023

   Last edited by marcodisa

   Author

   More actions

   After many attempts I solved the issue using the javascript function constructor to create a function from a string

   var serie1_name = "filiale";
   var serie2_name = "CAP";
   const safeJoinChar = '$';
   
   var distinctFormula = new Function(serie1_name, serie2_name, safeJoinChar, "return value => [value."+ serie1_name + ", value." + serie2_name + "].join("+safeJoinChar+")");
   
   var distinct_join = df.distinct(distinctFormula());
   
   console.log(distinct_join.toString());
   

   Now it works perfectly!

   Anyway, is there a more clever solution?
2. ashleydavis commented on Jan 17, 2023

   

   ashleydavis

   on Jan 17, 2023

   Member

   More actions

   That's strange. You shouldn't have to use a string.

   Are you able to publish a runnable code example in GitHub of the broken version? Then I can take a look at it.
3. marcodisa commented on Jan 17, 2023

   

   marcodisa

   on Jan 17, 2023

   Last edited by marcodisa

   Author

   More actions

   Hi, thanks for the reply.
   Well, I need to run an automatic exploratory analysis on datasets that I don't know, no human will look at the dataset but it all has to be handled by an algorithm.
   For this reason I loop through all the columns and due to the fact I don't know their names in advance I need them to be variable.
   Moreover I need to compare the distinct count of every columns with the join distinct count of each columns with all the others, two columns at a time.

   Here is an example code and how I solved the "issue"

   const dataForge = require('data-forge');
   require('data-forge-fs');
   
   csvString = `col0,col1,col2,col3
   0,Aa,Ba,Aa
   1,Ab,Bb,Bb
   2,Ac,Bc,Cc
   3,Ad,Bd,Dd
   4,Ae,Be,Ee`
   
   const df = dataForge.fromCSV(csvString);
   var columns = df.getColumns();
   var series_distincts = [];
   const safeJoinChar = '$';
   
   for (const column of columns) {            //loop through all the columns
       var name = column.name;
       const count_distinct = column.series.distinct().filter(value => value != "").count();     //single column distinct count
       
       let element = {"name": name, "count_distinct": count_distinct};
       series_distincts.push(element);
   }
   //console.log(series_distincts);
   
   for (var i = 0; i < series_distincts.length; i++) {         //loop to compare the single distinct count and the join distinct count
   
       var serie1_name = series_distincts[i].name;
       var distinct1 = series_distincts[i].count_distinct;
       console.log("Column to compare with all the others in this iteration: " + serie1_name);
   
       for (var j = 0; j < series_distincts.length; j++) {
           if (i == j) {
               continue;           //don't need to compare a column with itself
           } else {
               var serie2_name = series_distincts[j].name;
               var distinct2 = series_distincts[j].count_distinct;
               console.log("Column compared: " + serie2_name);
   
               var distinctFormula = new Function(serie1_name, serie2_name, safeJoinChar, "return value => [value." + serie1_name + ", value." + serie2_name + "].join(" + safeJoinChar + ")");         //solution I found after many attempts
   
               var distinct_join = df.distinct(distinctFormula()).count();
   
               console.log(serie1_name + " count distinct: " + distinct1);
               console.log(serie2_name + " count distinct: " + distinct2);
               console.log(serie1_name + " + " + serie2_name + " join distinct count: " + distinct_join);
           }
       }
   
   }
   

   Obviously this dataset doesn't make sense, it's just an example of what the algorithm should do. In fact, the algorithm then reads the join distinct count and goes on with its tasks.

   Another "issue" that I experienced is that before executing a join distinct I have to remove all the spaces from the column names. (This step is not present in the example above but I do it in my algorithm).
   Is there a way to bypass dataframe renameSeries()?

   Hope this example helps to clarify what I meant in the original question, anyway now I solved it using the javascript function constructor but I'd like to know if there is a more clever solution.
4. ashleydavis commented on Mar 9, 2023

   

   ashleydavis

   on Mar 9, 2023

   Member

   More actions

   Hey @marcodisa any chance you could fork the repo then get your code working as a failing test? Then I can work towards fixing the code for you and making your test pass.