Sorting Columns

Use the sort function in Python or the arrange function in R to create a new frame that is sorted by column(s) in ascending (default) or descending order. Note that when using sort, the original frame cannot contain any string columns.

If only one column is specified in the sort, then the final results are sorted according to that one single column either in ascending (default) or in descending order. However, if you specify more than one column in the sort, then H2O performs as described below:

Assuming two columns, X (first column) and Y (second column):

  • H2O will sort on the first specified column, so in the case of [0,1], the X column will be sorted first. Similarly, in the case of [1,0], the Y column will be sorted first.

  • H2O will sort on subsequent columns in the order they are specified, but only on those rows that have the same values as the first sorted column. No sorting will be done on subsequent columns if the values are not also duplicated in the first sorted column.

# Currently, this function only supports `all.x = TRUE`. All other permutations will fail.
library(h2o)
h2o.init()

# Import the smallIntFloats dataset
X <- h2o.importFile("https://s3.amazonaws.com/h2o-public-test-data/smalldata/synthetic/smallIntFloats.csv.zip")
X
         C1           C10
1     68379 -1.618668e+07
2  67108864  3.276800e+04
3     32768 -8.709456e+08
4        32  1.310720e+05
5 268435456 -2.910033e+01
6 105383117 -2.397206e+08

[180000 rows x 2 columns]

# Sort on the first column only in ascending order (default)
X_sorted1 <- h2o.arrange(X, C1)
X_sorted1
           C1           C10
1 -1073593184  7.474380e+05
2 -1073563127 -2.097152e+06
3 -1073521109  5.110769e+06
4 -1073416724  2.220942e+06
5 -1073361973 -5.707598e+00
6 -1073357712 -4.650334e+03

[180000 rows x 2 columns]

# Sort on both columns in descending order, specifying to sort on C1 first
X_sorted2 <- h2o.arrange(X, desc(C1), desc(C10))
X_sorted2
          C1         C10
1 1073593184  256.000000
2 1073521109 -128.000000
3 1073257966   15.616867
4 1073072648    1.884208
5 1072757094  441.816579
6 1072669626 -512.000000

[180000 rows x 2 columns]

# Sort on the second column in descending order
X_sorted3 <- h2o.arrange(X, desc(C10))
X_sorted3
         C1        C10
1 321417689 1073662860
2       448 1073574390
3        85 1073288384
4     -4096 1072908385
5        28 1072890306
6  -4194304 1072750253

[180000 rows x 2 columns]
import h2o
h2o.init()

# Import the smallIntFloats dataset
df1 = h2o.import_file("https://s3.amazonaws.com/h2o-public-test-data/smalldata/synthetic/smallIntFloats.csv.zip")
df1
              C1               C10
----------------  ----------------
 68379                -1.61867e+07
     6.71089e+07   32768
 32768                -8.70946e+08
    32            131072
     2.68435e+08     -29.1003
     1.05383e+08      -2.39721e+08
350191             21551.4
  -188                 2.39872e+07
   493               525.825
     9.31041e+07      -1.63828e+08

[180000 rows x 2 columns]

# Sort on the first column only in ascending order (default)
sorted_column_indices=[0]
df2 = df1.sort(0)
df2
          C1               C10
------------  ----------------
-1.07359e+09  747438
-1.07356e+09      -2.09715e+06
-1.07352e+09       5.11077e+06
-1.07342e+09       2.22094e+06
-1.07336e+09      -5.7076
-1.07336e+09   -4650.33
-1.07326e+09      -1.04858e+06
-1.07307e+09    8192
-1.07291e+09      -1.49017
-1.07291e+09   -9337.5

[180000 rows x 2 columns]

# Sort on both columns in descending order, specifying to sort on C1 first
df3 = df1.sort([0,1], ascending=[False, False])
df3
         C1                C10
-----------  -----------------
1.07359e+09      256
1.07352e+09     -128
1.07326e+09       15.6169
1.07307e+09        1.88421
1.07276e+09      441.817
1.07267e+09     -512
1.07233e+09     1444.14
1.07184e+09  -231812
1.07096e+09        2.00296e+07
1.07082e+09        5.36871e+08

[180000 rows x 2 columns]

# Sort on the second column in descending order
df4 = df1.sort(1, ascending=False)
df4
               C1          C10
-----------------  -----------
      3.21418e+08  1.07366e+09
    448            1.07357e+09
     85            1.07329e+09
  -4096            1.07291e+09
     28            1.07289e+09
     -4.1943e+06   1.07275e+09
      6.61688e+06  1.07254e+09
 -50127            1.07235e+09
-262144            1.07207e+09
     55            1.07175e+09

[180000 rows x 2 columns]