SortBy behaving strangely with missing data

Question

I'm trying to create a grid that shows the largest data value from a list.

What I have mostly works- the problem comes when there is missing data.

You can't really tell here, but all of the missing data is at the bottom.

Here you can see all the missing data is at the top...

I'm using SortBy to handle the sorting, and just can't seem to get the function to work. For instance, this works just like you'd expect:

SortBy[ finalData, { #[[2]] } &]

However, this does not:

SortBy[ finalData, { Function[ #[[2]] ] } &]

It also doesn't work when you throw in a Return either.

I've tried to extend that to a conditional If[ NumberQ[ ... ], #[[2]] and so forth, but if I can't get the most basic example working, I obviously can't the more complicated one working.

Code, for reference:

(* Utility functions *)
removeMissingData[ data_ ] := 
 DeleteCases[ data, _?( ! FreeQ[ #, _Missing ] & ) ]

divideStatsData[ data1_, data2_ ] := ( 
  Module[ { data }, (* Module makes declared variables local *)
   data = { #, CountryData[ #, data1 ], CountryData[ #, data2 ] } & /@ 
     CountryData[];
   (* Missing data / missing data = 1 *)
   data = removeMissingData[ data ];
   { #[[1]], #[[2]] / #[[3]] } & /@ data
   ] 
  )

extractData[ data_ ] := 
 QuantityMagnitude @ Flatten[ Transpose[ data ][[ 2 ]] ]

drawMapWithTooltips[ data_ ] := ( Module[ { rawData, min, max },
   rawData = extractData[ data ];
   { min, max } = { Min[ rawData ], Max[ rawData ] };
   GeoRegionValuePlot[ 
    data,  
    PlotRange -> { min, max },
    GeoLabels -> ( 
      Tooltip[ 
        #1, (* Location *)
        Extract[ 
         Extract[  (* First extract removes ugly braces, 
          second actually gets information *)
          data, 
          Position[ data, #2, 2 ] + { { 0, 1 } } (* 
          Search through table by country (#2) *)
          (* Could just recalculate the value again... *)
          ], 1 ]
        ] &
      )
    ]
   ] 
  )

sortFromGreatestToLeast[ data_ ] := Sort[ data, #1[[2]] > #2[[2]] &]

arableLandPerPopulation = divideStatsData[ "ArableLandArea", "Population" ];
drawMapWithTooltips[ arableLandPerPopulation ]

currentAccountBalancePerGDP = divideStatsData[ "CurrentAccountBalance", "GDP" ];
drawMapWithTooltips[ currentAccountBalancePerGDP ]

governmentSurplusPerGDP = divideStatsData[ "GovernmentSurplus", "GDP" ];
drawMapWithTooltips[ governmentSurplusPerGDP ]

governmentDebtPerGDP = divideStatsData[ "GovernmentDebt", "GDP" ];
drawMapWithTooltips[ governmentDebtPerGDP ]

militaryExpenditurePerPopulation = divideStatsData[ "MilitaryExpenditures", "Population" ];
drawMapWithTooltips[ militaryExpenditurePerPopulation ]

militaryExpenditurePerGovernmentDebt = divideStatsData[ "MilitaryExpenditures", "GovernmentDebt" ];
drawMapWithTooltips[ militaryExpenditurePerGovernmentDebt ]


combine[ data_, names_ ] := (
  countries = CountryData[];
  return = { Join[ { "Country" }, names ] };
  statsFinal = {};
  For[ i = 1, i <= Length[ data ], i++,
   new = { # };
   stats = ( 
       temp = Position[ data[[ i ]], # ]; 
       temp = If[ 
         Length[ temp ] > 0, 
         Extract[ Extract[ data[[ i ]], temp + {{ 0, 1 }} ], 1 ], 
         Missing["NotAvailable"]
         ];
       temp
       ) & /@ CountryData[];
   AppendTo[ statsFinal, stats ];
   ];
  For[ i = 1, i <= Length[ CountryData[] ], i++,
   new = { CountryData[ countries[[i]], "Name" ] };
   For[ ii = 1, ii <= Length[ statsFinal ], ii++,
    AppendTo[ new, statsFinal[[ii]][[i]] ]
    ];
   AppendTo[ return, new ];
   ];
  return
  )

finalData = combine[ 
   { militaryExpenditurePerGovernmentDebt, 
    militaryExpenditurePerPopulation, governmentDebtPerGDP, 
    governmentSurplusPerGDP, currentAccountBalancePerGDP, 
    arableLandPerPopulation },
   { "militaryExpenditurePerGovernmentDebt", 
    "militaryExpenditurePerPopulation", "governmentDebtPerGDP", 
    "governmentSurplusPerGDP", "currentAccountBalancePerGDP", 
    "arableLandPerPopulation" } 
   ];

    Manipulate[ 
 Grid[ 
  Reverse[
   Join[
    SortBy[ 
     Take[ finalData, { 2, Length[ finalData ] } ], 
     Function[ x, 
      x[[ Extract[Extract[Position[finalData[[1]], top], 1], 1] ]] ]
     ],
    Take[ finalData, 1 ]
    ]
   ],
  Frame -> All
  ], {
  top, {
   "militaryExpenditurePerGovernmentDebt", 
   "militaryExpenditurePerPopulation", 
   "governmentDebtPerGDP", 
   "governmentSurplusPerGDP", 
   "currentAccountBalancePerGDP", 
   "arableLandPerPopulation"
   } }
 ]

So I guess the question is really: How can I organize columns of data with missing data involved?

Answer 1

NA = Missing["NotAvailable"];
SM = 10^-9.;

The situation arises when you make a reverse sort like this one

data = {{"Libya", 1, 2, NA, 3}, {"Belgium", NA, 10, 30 , 20}, {"Egypt", NA, NA, 8, 7}, {"USA", 21, 18, 18, 17}};

Reverse @ SortBy[data, #[[2]] &] // TableForm

Solution: Temporarily replace missing data with a number small enough to not occur in your data.

Reverse @ SortBy[data /. NA :> SM, #[[2]] &] /. SM :> NA // TableForm

Answer 2

Using eldo's example data:

data = {{"Libya", 1, 2, NA, 3}, {"Belgium", NA, 10, 30, 20}, 
  {"Egypt", NA, NA, 8, 7}, {"USA", 21, 18, 18, 17}};

ReverseSortBy[{#[[2]] &} /* DeleteCases[NA]] @ data // TableForm