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
Deserializing JSON When Sometimes Array and Sometimes Object
How to Seed a Random Class to Avoid Getting Duplicate Random Values
How to Animate a Line on a Canvas in C#
Dot Character '.' in MVC Web API 2 for Request Such as API/People/Staff.45287
Parsing Large JSON File in .Net
Check for Column Name in a SQLdatareader Object
Is It Bad to Not Unregister Event Handlers
Implementing a Log Viewer with Wpf
Loop Through All the Resources in a .Resx File
Capture a Keyboard Keypress in the Background
How to Get Difference Between Two Dates in Year/Month/Week/Day
Catch Exception That Is Thrown in Different Thread
How to Make an Event in the Usercontrol and Have It Handled in the Main Form
Two Different Dll with Same Namespace
Implementing Idisposable Correctly
Why Implement Interface Explicitly
How to Convert Struct System.Byte Byte[] to a System.Io.Stream Object in C#