list sorting task

Question

I am trying to understand how to sort by multiple columns in Mathematica.

Here is a simple data set consisting of results of sports matches. Each element represents a game, and contains fields for date, first team's name, first team's score, second team's name, second team's score, and game location (home, away, or neutral). Some elements may be duplicates, or they may disagree on who played whom, or where a game was played. (Assumeing one team could only play one game per day.)

I would like to be able to evaluate each game and flag each having a conflict as "FALSE" or not having a conflict as "TRUE".

The score list is called gameList:

{{DateObject[{2020,1,1}],"Alpha",1,"Bravo",2,"away"},
 {DateObject[{2020,1,1}],"Bravo",2,"Alpha",1,"neutral"},
 {DateObject[{2020,1,1}],"Charlie",3,"Delta",4,"home"},
 {DateObject[{2020,1,1}],"Delta",4,"Charlie",3,"away"},
 {DateObject[{2020,1,2}],"Echo",5,"Foxtrot",6,"home"},
 {DateObject[{2020,1,2}],"Foxtrot",5,"Echo",6,"away"},
 {DateObject[{2020,1,2}],"Gulf",7,"Hotel",10,"home"},
 {DateObject[{2020,1,3}],"India",9,"Juliet",2,"home"},
 {DateObject[{2020,1,3}],"Kilo",10,"India",9,"away"},
 {DateObject[{2020,1,3}],"Lima",11,"Mike",12,"neutral"}}

Here is some code that flips (if necessary) team and score pairs so that the team whose name starts earlier in the alphabet comes first in each game record. In the event the names and scores are reordered, the game location is also changed from home to away or vice versa.

alphaSortKey = 
  Table[AlphabeticOrder[gameList[[i, 2]], gameList[[i, 4]]], {i, 
    Length[gameList]}];
gameList = 
  Table[Append[gameList[[i]], alphaSortKey[[i]]], {i, 
    Length[gameList]}];

regular = Cases[gameList, {_, _, _, _, _, _, 1}];
regular = Table[Take[regular[[i]], 6], {i, Length[regular]}];

needTeamsFlip = Cases[gameList, {_, _, _, _, _, _, -1}];
holderLoca = Table[needTeamsFlip[[i, 6]], {i, Length[needTeamsFlip]}];
holderLoca = 
  Table[If[holderLoca[[i]] == "home", holderLoca[[i]] = "away", 
    If[holderLoca[[i]] == "away", holderLoca[[i]] = "home", 
     holderLoca[[i]] = "neutral"]], {i, Length[needTeamsFlip]}];
needTeamsFlip = 
  Table[Take[needTeamsFlip[[i]], 5], {i, Length[needTeamsFlip]}];
needTeamsFlip = 
  Table[Append[needTeamsFlip[[i]], holderLoca[[i]]], {i, 
    Length[needTeamsFlip]}];
holderTeam = Table[needTeamsFlip[[i, 2]], {i, Length[needTeamsFlip]}];
holderScor = Table[needTeamsFlip[[i, 3]], {i, Length[needTeamsFlip]}];
Table[needTeamsFlip[[i, 3]] = needTeamsFlip[[i, 5]], {i, 
   Length[needTeamsFlip]}];
Table[needTeamsFlip[[i, 2]] = needTeamsFlip[[i, 4]], {i, 
   Length[needTeamsFlip]}];
Table[needTeamsFlip[[i, 4]] = holderTeam[[i]], {i, 
   Length[needTeamsFlip]}];
Table[needTeamsFlip[[i, 5]] = holderScor[[i]], {i, 
   Length[needTeamsFlip]}];
needTeamsFlip = 
  Table[Take[needTeamsFlip[[i]], 6], {i, Length[needTeamsFlip]}];


ordered = 
  DeleteDuplicates[Flatten[Join[{needTeamsFlip}, {regular}], 1]];
Column[ordered = SortBy[ordered, {#[[1]] &, #[[2]] &}]]

ordered = 
  DeleteDuplicates[Flatten[Join[{needTeamsFlip}, {regular}], 1]];

But

 SortBy[ordered, {#[[1]] &, #[[2]] &, #[[4]] &}]

returns an error.

The desired output would be:

{{DateObject[{2020,1,1}],"Alpha",1,"Bravo",2,"neutral",FALSE},
{DateObject[{2020,1,1}],"Alpha",1,"Bravo",2,"away",FALSE},
{DateObject[{2020,1,1}],"Charlie",3,"Delta",4,"home",TRUE},
{DateObject[{2020,1,2}],"Echo",6,"Foxtrot",5,"home",FALSE},
{DateObject[{2020,1,2}],"Echo",5,"Foxtrot",6,"home",FALSE},
{DateObject[{2020,1,2}],"Gulf",7,"Hotel",8,"home",TRUE},
{DateObject[{2020,1,3}],"India",9,"Kilo",10,"home",FALSE},
{DateObject[{2020,1,3}],"India",9,"Juliet",10,"home",FALSE},
{DateObject[{2020,1,3}],"Lima",11,"Mike",12,"neutral",TRUE}}

Thanks in advance for suggestions!

Answer 1

sorted = gameList;
orderings = Ordering /@ gameList[[All, {2, 4}]];
sorted[[All, {2, 4}]] = Sort /@ sorted[[All, {2, 4}]];
sorted[[All, {3, 5}]] = MapThread[#[[#2]] &, {sorted[[All, {3, 5}]], orderings}];
sorted[[All, 6]] = MapThread[If[#2 == {1, 2}, #, # /. {"home" -> "away", 
       "away" -> "home"}] &, {sorted[[All, 6]], orderings}];
sorted = DeleteDuplicates@sorted;

dateteamcounts = Counts[Join @@ (sorted[[All, {1, #}]] & /@ {2, 4})]; 

desired = Join[sorted, 
    {Max[dateteamcounts /@ {#[[{1, 2}]], #[[{1, 4}]]}] == 1} & /@ sorted, 2];

Grid[desired, Dividers -> All]

Update:

An alternative approach:

ClearAll[sORT, dESIRED]
sORT = DeleteDuplicates[# /. {a_, b_, c_, d_, e_, f_} /; OrderedQ[{d, b}] :>
   {a, d, e, b, c, f /. {"home" -> "away", "away" -> "home"}}] &; 

dESIRED = Module[{s = sORT @ #,  k = Function[x, x[[{1, #}]] & /@ {2, 4}]},
   Join[s, {Max[Counts[Join @@ k /@ s] /@ k[#]] == 1} & /@ s, 2]] &;

dESIRED @ gameList == desired

True