# Sorting with Missing values

I have the following data:

dt =
<|"Austria" -> <|"2018" -> 3848, "2019" -> 3965, "2020" -> 4099, "2021" -> 4662|>,
"Belgium" -> <|"2018" -> 3731, "2019" -> 3838, "2020" -> 3861, "2021" -> 4167|>,
"Denmark" -> <|"2018" -> 3701, "2019" -> 3796, "2020" -> Missing[_], "2021"-> Missing[_]|>,
"Finland" -> <|"2018" -> 3020, "2019" -> 3105, "2020" -> 3205, "2021" -> Missing[_]|>,
"Sweden" -> <|"2018" -> Missing[_], "2019" -> 3830, "2020" -> 3919,"2021" -> 4200|>,
"Estonia" -> <|"2018" -> 4834, "2019" -> 4917, "2020" -> 4997, "2021" -> Missing[_]|>|>;


ReverseSort by the last two years:

ReverseSortBy[dt, {Key["2021"], Key["2020"]}] // Dataset


Unfortunately, the missing values are placed in front of the others, and I could not find a Sort - specification to correct that. The solution I found is too complicated:

 move[in_, yr_] :=
Module[{dt, mi},
dt = ReverseSort @ Query[All, {yr}] @ in;
mi = MatchQ[#, KeyValuePattern[_ -> Missing[_]]] & /@ dt;
mi = Keys @ Select[Normal @ mi, Last];
<|KeyDrop[dt, mi], KeyTake[dt, mi]|>]

a = move[dt, #] & /@ {"2021", "2020"};
b = Query[All, {"2018", "2019"}] @ dt;


The expected result:

KeySort /@ Merge[{a, b}, Association] // Dataset


Better / shorter ways to do this?

• Didn't you yourself provide an answer a few years ago? :) Aug 6, 2023 at 12:50
• OMG. I also just find it now Aug 6, 2023 at 12:54
• Oops, shame on me
– eldo
Aug 6, 2023 at 13:00

ReverseSortBy[dt, DeleteMissing@{#"2021", #"2020"} &] // Dataset


Alternatively,

ReverseSortBy[dt, {#"2021", #"2020"} & /* DeleteMissing] // Dataset


same result

• More elegant solution 👏 Aug 6, 2023 at 13:41
• Excellent - but why does it function?
– eldo
Aug 6, 2023 at 13:47
• @eldo, SortBy[e,f] is equivalent to Sort[{f[#],#}&/@e][[All,-1]] (SortBy -- Properties and Relations). So ReverseSortBy[dt, {#"2021", #"2020"} &/*DeleteMissing] is actually doing the same thing as dt[[Reverse@Ordering@Map[DeleteMissing@{#"2021", #"2020"} &]@dt]]
– kglr
Aug 6, 2023 at 14:17
• If I replace 2020 with 2018, it seems that I can't obtain a reasonable result. Aug 6, 2023 at 14:19

According to the trick , we can temporarily replace missing data with a number small enough to not occur in your data. Then recover it after sort operation.

NA = Missing[_];
SM = 10^-9.;
ReverseSortBy[
dt /. NA -> SM, {Key["2021"], Key["2020"]}] /. {SM ->
NA} // Dataset