Restrict sorting to a certain range

Question

Example data (revised to include negative values - see comment of @ydd)

data =
  <|"France" -> <|"18" -> -103.7`, "19" -> -103.9`, "20" -> -104.1`, 
     "21" -> -104.6`, "22" -> -105.`|>, 
   "Greece" -> <|"18" -> 96.6`, "19" -> 96.5`, "20" -> 96.4`, 
     "21" -> 96.`, "22" -> 94.1`|>, 
   "Italy" -> <|"18" -> -102.2`, "19" -> -101.1`, "20" -> -100.8`, 
     "21" -> -100.1`, "22" -> -99.7`|>, 
   "Portugal" -> <|"18" -> 97.3`, "19" -> 97.2`, "20" -> 97.4`, 
     "21" -> 97.4`, "22" -> 97.9`|>, 
   "Spain" -> <|"18" -> 100.4`, "19" -> 101.`, "20" -> 101.8`, 
     "21" -> 102.`, "22" -> 102.`|>, 
   "Total" -> <|"18" -> 88.4`, "19" -> 89.7`, "20" -> 90.7`, 
     "21" -> 90.7`, "22" -> 89.3`|>|>;

data // Dataset

I sometimes import data tables with column totals already appended. If I want to ReverseSort this data by the last column, the total row must of course stay in place. Therefore:

sorted = <|ReverseSortBy[Last] @ data[[;; -2]], KeyTake[Last @ Keys @ data] @ data|>;

sorted // Dataset

Although I can live with this not too complicated solution, I'm curious to know if there isn't a more direct approach, which wouldn't deconstruct and reassemble the matrix.

Answer 1

At first glance, SubsetMap appears to be perfect for the task. However, since it explicitly acts on a List of the elements (values) of the subset, keys remain in their original places:

SubsetMap[ReverseSortBy[Last]@*Echo, data, ;; -2] // Dataset

To work around this, we can Normalize the initial association into a list of key->value rules, then apply Association to the result. This also changes what ReverseSortBy sees, so we compose with Last to work on values again:

SubsetMap[ReverseSortBy[Last@*Last], Normal@data, ;; -2] // 
  Association // Dataset

Answer 2

You just need to ReverseSort the data and shift the total to the bottom:

RotateLeft[ReverseSortBy[data, Last], 1] // Dataset

---

add-on

Since I rely on "Total" being the largest (first in RevereSortBy) row, this is only guaranteed to work for positive values in data

---

For negative values, I can't really think of a better way yet to do it than yours. I think your method is quite clear and good already. But this is my attempt:

sorted = Join[ReverseSort@Most@data, Part[data, {-1}]];
sorted // Dataset

Answer 3

One approach, calling ReverseSort only on the relevant data:

(ReverseSort@Most@Query[All,"22"]@data//Keys//Append["Total"]//Query)@data//Dataset

Original Answer

Query[ReverseSort[Query[;;-2,-1]@data]//Keys//Append["Total"]]@data

Answer 4

rsort = Apply[Association] @* Through @* 
  {ReverseSortBy[{Key @ "22"}] @* KeyDrop["Total"], KeyTake["Total"]};


Dataset @ rsort @ data 

Query[rsort] @ Dataset @ data

data2 = data;
data2["France", "18"] = 102.0;
Dataset @ rsort @ data2

Answer 5

Is this what you want?:

data = <|"France" -> <|"18" -> 103.7, "19" -> 103.9, "20" -> 104.1, 
     "21" -> 104.6, "22" -> 105.2|>, 
   "Greece" -> <|"18" -> 96.6, "19" -> 96.5, "20" -> 96.4, 
     "21" -> 96.0, "22" -> 94.1|>, 
   "Italy" -> <|"18" -> 102.2, "19" -> 101.1, "20" -> 100.8, 
     "21" -> 100.1, "22" -> 99.7|>, 
   "Portugal" -> <|"18" -> 97.3, "19" -> 97.2, "20" -> 97.4, 
     "21" -> 97.4, "22" -> 97.9|>, 
   "Spain" -> <|"18" -> 100.4, "19" -> 101.5, "20" -> 101.8, 
     "21" -> 102.0, "22" -> 102.1|>, 
   "Total" -> <|"18" -> 500.2, "19" -> 499.7, "20" -> 500.5, 
     "21" -> 500.1, "22" -> 498.7|>|>;
data = Transpose@ReverseSortBy[Transpose@data, Last];
data // Dataset

Answer 6

Following the comment of @att I tried it with SubsetMap:

SubsetMap[ReverseSortBy[Last], data, 1 ;; -2] // Dataset

This sorted the Values as desired but not the Keys.

To also sort the Keys we must transform data into a List of Associations with this little helper function:

keysup = KeyValueMap[<|"" -> #1, #2|> &] @ # &;

To return to an Association of Associations we need:

keysdown = GroupBy[#, First -> Rest, Last] &;

Now, finally, the wanted result:

SubsetMap[ReverseSortBy[Last], keysup[data], 1 ;; -2] // keysdown // Dataset

Hmm, this restricted the sort range, but at what expense!