Sorting DataFrame that has null values in numerical columns

[mattfung]

Hello,

I found that calling DataFrame.OrderBy on a data frame that has null values in a numeric column throws an ArgumentException with MismatchedColumnLengths error. This occurs regardless of the column to sort by (ie. the column to sort by does not have to be the numeric column).

I believe this is because when the DataFrame clones new sorted columns, numeric columns with null values produce copied clones with length shorter than the original column.

Minimal Example to produce this error (see attached example.csv.txt) txt file because Github does not support csv files -_-

var df = DataFrame.LoadCsv(@"./example.csv.txt");
df.OrderBy("a");

example.csv.txt

Microsoft.Data.Analysis v0.3.0

Thanks

[RamonWill]

Hi,

It appears that there are two issues here.

1. If the column we want to sort contains nulls then, as @mattfung has alluded to, the .GetAscendingSortIndices() method will ignore any index that has a null value and the code will break when this mismatched column is added to the final dataframe.

2. The code will also break if the column we want to sort doesn't contain nulls but the other columns do contain nulls. This is because the clone Methods nullcount parameter in DataFrameColumn newColumn = oldColumn.Clone(sortIndices, !isAscending, oldColumn.NullCount); will append additional nulls to the end of the column.

The reason why this passed all the tests in TestOrderBy is because the null values are all on the same row for the dataframe used in the test. So any nulls that are removed from point 1 will be offset by the nulls added in point 2(only if the row contains all nulls).

I came up with the following amendments below (comments included) that will work with some test cases I created and the example provided by mattfung. I think it can definitely be optimized however it does rely on Linq and using more memory. Would like to know your thoughts @pgovind when you have time.

        private DataFrame Sort(string columnName, bool isAscending)
        {
            DataFrameColumn column = Columns[columnName];
+            int size = (int)column.Length;
              // put the sorted indices into a list
+            List<long?> sortIndicesNonNull = column.GetAscendingSortIndices().ToList();
              // get a list of all index values from 0 to the size of the target column.
+            List<long?> arr = Enumerable.Range(0, size).ToList().ConvertAll(n=>(long?)n);
              // using Linq method ".Except" get all indices that are missed from GetAscendingSortIndices() because their value was null
+            List<long?> nullValues = arr.Except(sortIndicesNonNull).ToList();

+            DataFrameColumn dfCol;
+           if (isAscending)
+            {
                  // if its ascending then add the null values at the end of the new column.
+                sortIndicesNonNull.AddRange(nullValues);
+                dfCol = new PrimitiveDataFrameColumn<long>("temp", sortIndicesNonNull);
+            }
+            else
+            {
                  // if its descending then reverse the null values. and then add the nullvalues to the front the of the new column because its clone will be inverted leaving all null values at the end.
+                nullValues.Reverse();
+                nullValues.AddRange(sortIndicesNonNull);
+                dfCol = new PrimitiveDataFrameColumn<long>("temp", nullValues);
+            }
            List<DataFrameColumn> newColumns = new List<DataFrameColumn>(Columns.Count);

            for (int i = 0; i < Columns.Count; i++)
            {
                DataFrameColumn oldColumn = Columns[i];
                // i removed the nullcount parameter because with this code it will add extra null values at the end.
+                DataFrameColumn newColumn = oldColumn.Clone(dfCol, !isAscending);

                Debug.Assert(newColumn.NullCount == oldColumn.NullCount);
                newColumns.Add(newColumn);
            }
            return new DataFrame(newColumns);

Kind Regards,
Ramon