5
$\begingroup$

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

enter image description here

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

enter image description here

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.

$\endgroup$
1
  • 2
    $\begingroup$ SubsetMap? $\endgroup$
    – att
    Sep 4, 2023 at 20:17

6 Answers 6

3
$\begingroup$

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

A sorted dataset... except the labels haven't moved

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

A properly sorted dataset

$\endgroup$
2
  • $\begingroup$ I get exactly the same result with ReverseSortBy[Last] (omitting the composed @*Last $\endgroup$
    – eldo
    Sep 5, 2023 at 8:41
  • 3
    $\begingroup$ @eldo that's because in this case, the rows (and all columns) sort the same as the last column. $\endgroup$
    – att
    Sep 5, 2023 at 8:43
6
$\begingroup$

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

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

enter image description here


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

enter image description here

$\endgroup$
2
  • 1
    $\begingroup$ Exactly, I should have included negative values - will revise the question - +1 anyway $\endgroup$
    – eldo
    Sep 4, 2023 at 17:08
  • $\begingroup$ Ok, I added a method for negative values. I'm worried it doesn't really add much though to the method you've already got in your question $\endgroup$
    – ydd
    Sep 4, 2023 at 19:27
3
$\begingroup$

One approach, calling ReverseSort only on the relevant data:

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

enter image description here

Original Answer

Query[ReverseSort[Query[;;-2,-1]@data]//Keys//Append["Total"]]@data
$\endgroup$
1
  • 1
    $\begingroup$ Yes, this is the way to go! Can be shortened to Query[ReverseSort[data[[;; -2]]] // Keys // Append["Total"]]@data // Dataset $\endgroup$
    – eldo
    Sep 5, 2023 at 8:29
3
$\begingroup$
rsort = Apply[Association] @* Through @* 
  {ReverseSortBy[{Key @ "22"}] @* KeyDrop["Total"], KeyTake["Total"]};


Dataset @ rsort @ data 

enter image description here

Query[rsort] @ Dataset @ data

enter image description here

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

enter image description here

$\endgroup$
4
  • $\begingroup$ Extremely nice! But it would give a wrong result if , for example, we would change the value for France in column 18 from -103.7 to 102.0. See comments following @atts solution. $\endgroup$
    – eldo
    Sep 5, 2023 at 10:29
  • $\begingroup$ This is of course my mistake, not yours, because I should have included this possibility in the example data. $\endgroup$
    – eldo
    Sep 5, 2023 at 10:37
  • $\begingroup$ @eldo, please try the updated version., $\endgroup$
    – kglr
    Sep 5, 2023 at 10:44
  • $\begingroup$ Now it's perfect :) $\endgroup$
    – eldo
    Sep 5, 2023 at 10:48
2
$\begingroup$

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

enter image description here

$\endgroup$
1
  • 2
    $\begingroup$ No, the table should be reverse sorted by the last column, not by the last row. Exactly like the second table in my question. $\endgroup$
    – eldo
    Sep 4, 2023 at 16:24
2
$\begingroup$

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

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

enter image description here

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

enter image description here

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

$\endgroup$
2
  • 1
    $\begingroup$ Ah, I didn't notice the issue with the keys. Might be more elegant to have keysup=Association, keysdown=Normal, and ReverseSortBy[Last@*Last] instead. $\endgroup$
    – att
    Sep 5, 2023 at 7:47
  • $\begingroup$ If you would write this as an answer, I would certainly accept it :) $\endgroup$
    – eldo
    Sep 5, 2023 at 7:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.