Sorting Rows in a Data Table

Sort rows in data.table in decreasing order on string key `order(-x,v)` gives error on data.table 1.9.4 or earlier


Update

data.table v1.9.6+ now supports OP's original attempt and the following answer is no longer necessary.


You can use DT[order(-rank(x), y)].

   x y v
1: c 1 7
2: c 3 8
3: c 6 9
4: b 1 1
5: b 3 2
6: b 6 3
7: a 1 4
8: a 3 5
9: a 6 6

Sort rows of DataTable in particular order we want

Add an expression based column

dataExistingTable.Columns.Add("MonthNumber", typeof(int),
"IIF ([Column2] = 'dec', 12, (IIF ([Column2] = 'nov', 11, (IIF ([Column2] = 'oct', 10, (IIF ([Column2] = 'sep', 9, 8)))))))");

Then apply sort on expression based column

dataExistingTable.DefaultView.Sort = "MonthNumber desc";

Then DefaultView of the table will give your sorted rows.

// dataExistingTable.DefaultView;

How can I sort the rows of a data.table based on arbitrary order?

You need to change Rating to factor first, then use order:

dat$Rating <- factor(dat$Rating, levels = c("AAA","AA","A","BBB", "BB", "B", "CCC"))

dat[order(dat$Rating), ]
# Rating el es
# 3 AAA 34.07 0.06
# 2 AA 26.25 0.13
# 1 A 21.96 0.15
# 6 BBB 14.63 0.52
# 5 BB 2.24 0.07
# 4 B 0.84 0.07
# 7 CCC 70.48 0.04

Data:

tt <- "Rating  el  es
A 21.96 0.15
AA 26.25 0.13
AAA 34.07 0.06
B 0.84 0.07
BB 2.24 0.07
BBB 14.63 0.52
CCC 70.48 0.04"

dat <- read.table(text = tt, header = T)

Sorting a Data Table

After setting the sort expression on the DefaultView (table.DefaultView.Sort = "Town ASC, Cutomer ASC" ) you should loop over the table using the DefaultView not the DataTable instance itself

foreach(DataRowView r in table.DefaultView)
{
//... here you get the rows in sorted order
Console.WriteLine(r["Town"].ToString());
}

Using the Select method of the DataTable instead, produces an array of DataRow. This array is sorted as from your request, not the DataTable

DataRow[] rowList = table.Select("", "Town ASC, Cutomer ASC");
foreach(DataRow r in rowList)
{
Console.WriteLine(r["Town"].ToString());
}

How can I sort counted DataTable rows according to specific parameter

I believe the wisest choice is to act in the db side.

Assuming you're using SQL Server, the query should be:

SELECT *, COUNT(*) OVER (PARTITION BY Column1) AS c
FROM Table1
ORDER BY c

This query returns the data on your table "Table1", plus the column "c" that represents the count of the value of "Column1", per each row.

Finally, it sorts rows by the column "c", as you request.


EDIT

To complete this task, I will use a Common Table Expression:

-- Code before INSERT...

;WITH CTE1 AS (
SELECT *,
COUNT(*) OVER (PARTITION BY [Column1]) AS c
FROM @param
)
INSERT INTO [dbo].[myTable] (/* list of columns - must add the column c */)
SELECT [Column1],
[Column2],
[c],
-- aggregated columns
FROM CTE1
GROUP BY [Column2], [Column1], c

-- Code after INSERT...

In the Common Table Expression "CTE1" I select all the values in @param, adding a column "c" with the count per Column1.

Note: if you have 5 rows with the same value of Column1, but two different values in Column2, in myTable you will have two rows (because of the GROUP BY [Column2], [Column1]), both with c=5.

If you want instead obtain the count grouped by Column1 and Column2, you have to declare c as follows: COUNT(*) OVER (PARTITION BY [Column1], [Column2]) AS c.

I hope I was clear, If not I'm available to explain it in a different way.


EXAMPLE

CREATE TABLE myTable (col1 VARCHAR(50), col2 INT, col3 INT, c INT) 

CREATE TYPE myUserDefinedTableType AS TABLE (column1 VARCHAR(50), column2 INT, column3 INT)
DECLARE @param myUserDefinedTableType
INSERT INTO @param VALUES ('A', 1, 4), ('A', 2, 3), ('A', 2, 6), ('B', 2, 3)

;WITH CTE1 AS (
SELECT *, COUNT(*) OVER (PARTITION BY [Column1]) AS c
FROM @param
)
INSERT INTO [myTable]([col1], [col2], [col3], [c])
SELECT [column1], [column2],
-- aggregated columns
MAX([column3]),
-- count
[c]
FROM CTE1
GROUP BY [column2], [column1], c


Related Topics



Leave a reply



Submit