Pattern Matching in Lists

Question

I have a UN voting dataset – taken from a CSV file – where each row is in following format:

{venue, session number, resolution code, descriptor, full date, year, month, day, topic, country, vote code, vote}

I would like to indicate the vote coincidence between the US and another country for a given year and 'topic.' Sample (note that Botswana did not vote in res. '15294'):

UNGA2009 = {{"UNGA", 63, 15294, "...", "30-Jun-09", 2009, 6, 30, "B", "Argentina", 3, "YES"}, {"UNGA", 63, 15294, "...", "30-Jun-09", 2009, 6, 30, "B", "Cuba", 3, "YES"}, {"UNGA", 63, 15294, "...", "30-Jun-09", 2009, 6, 30, "B", "United States", 1, "NO"}, {"UNGA", 63, 15295, "...", "9-Sep-09", 2009, 9, 9, "F", "Argentina", 2, "ABSTAIN"}, {"UNGA", 63, 15295, "...", "9-Sep-09", 2009, 9, 9, "F", "Botswana", 2, "ABSTAIN"}, {"UNGA", 63, 15295, "...", "9-Sep-09", 2009, 9, 9, "F", "Cuba", 1, "NO"} , {"UNGA", 63, 15295, "...", "9-Sep-09", 2009, 9, 9, "F", "United States", 3, "YES"}, {"UNGA", 63, 15295, "...", "9-Sep-09", 2009, 9, 9, "H", "Argentina", 2, "ABSTAIN"}, {"UNGA", 63, 15295, "...", "9-Sep-09", 2009, 9, 9, "H", "Botswana", 2, "ABSTAIN"}, {"UNGA", 63, 15295, "...", "9-Sep-09", 2009, 9, 9, "H", "Cuba", 1, "NO"}, {"UNGA", 63, 15295, "...", "9-Sep-09", 2009, 9, 9, "H", "United States", 3, "YES"}}

With output looking something like this:

{15294, 2009, "B", "Argentina", 0}, {15294, 2009, "B", "Cuba", 0}, {15295, 2009, "F", "Argentina", .5}, {15295, 2009, "F", "Botswana", .5}, {15295, 2009, "F", "Cuba", 0}, {15295, 2009, "H", "Argentina", .5}, {15295, 2009, "H", "Botswana", .5}, {15295, 2009, "H", "Cuba", 0}

Where 0 is non-coincidence of the vote, 1 is coincidence, and .5 is partial coincidence (when one country abstains and the other votes either yes or no). Does anyone have any ideas on what the best method would be?

Thanks Again, Arebearit

Answer 1

In case you don't have V10, you can use a combination of GatherBy and Cases as follows:

First, define a helper function, vF, for aggregation of vote pairs:

vF[{"ABSTAIN", Except["ABSTAIN"]} | {Except["ABSTAIN"], "ABSTAIN"}] = .5;
vF[{x_, x_}] := 1;
vF[{x_, y_}] := 0;

and another helper function, rearrangeF, that rearranges the data for each resolution code, year and topic tuple. For each country except the reference country ("UnitedStates" in OP's example), rearrangeF replaces the vote of that country with the pair vF[{vote, refv}] where the refv is the vote of the reference country:

rearrangeF[ref_String: "United States"] := 
  With[{others = Cases[#, {__, Except[ref], _}], 
     refv = Cases[#, {__, ref, v_} :> v]}, 
    If[refv === {}, {}, Append[Most@#, vF@{Last@#, First@refv}] & /@ others]] &;

Next, take the relevant 5 columns of the data (columns 3, 6, 9, 10 and 12) and GatherBy the resulting data set by resolution code, year and topic:

data2 = GatherBy[UNGA2009[[All, {3, 6, 9, 10, 12}]], #[[;; 3]] &]

{{{15294, 2009, "B", "Argentina", "YES"}, {15294, 2009, "B", "Cuba", "YES"}, 
  {15294, 2009, "B", "United States", "NO"}}, 
 {{15295, 2009, "F", "Argentina", "ABSTAIN"}, {15295, 2009, "F", "Botswana", "ABSTAIN"}, 
  {15295, 2009, "F", "Cuba", "NO"}, {15295, 2009, "F", "United States", "YES"}}, 
 {{15295, 2009, "H", "Argentina", "ABSTAIN"}, {15295, 2009, "H", "Botswana", "ABSTAIN"},
  {15295, 2009, "H", "Cuba", "NO"}, {15295, 2009, "H", "United States",  "YES"}}}

Usage examples:

Example 1:

Join @@ rearrangeF[] /@ data2
{{15294, 2009, "B", "Argentina", 0}, {15294, 2009, "B", "Cuba",  0}, 
 {15295, 2009, "F", "Argentina", 0.5}, {15295, 2009, "F", "Botswana", 0.5}, 
 {15295, 2009, "F", "Cuba", 0}, {15295, 2009, "H", "Argentina", 0.5}, 
 {15295, 2009, "H", "Botswana", 0.5}, {15295, 2009, "H", "Cuba", 0}} 

Example 2:

Join @@ rearrangeFX["Botswana"] /@ data2
{{15295, 2009, "F", "Argentina", 1}, {15295, 2009, "F", "Cuba",  0.5},
 {15295, 2009, "F", "United States", 0.5}, {15295, 2009, "H", "Argentina", 1},
 {15295, 2009, "H", "Cuba", 0.5}, {15295, 2009, "H", "United States", 0.5}}

Example 3:

Join @@ rearrangeFX["Argentina"] /@ data2
{{15294, 2009, "B", "Cuba", 1}, {15294, 2009, "B", "United States", 0}, 
 {15295, 2009, "F", "Botswana", 1}, {15295, 2009, "F", "Cuba",  0.5},
 {15295, 2009, "F", "United States", 0.5}, {15295, 2009, "H", "Botswana", 1},
 {15295, 2009, "H", "Cuba", 0.5}, {15295, 2009, "H",  "United States", 0.5}}

Answer 2

Maybe I understood you right now. Give it a try with (the famous) Dataset ;-)

keys = {venue, session number, resolution code, descriptor, full date,
year, month, day, topic, country, vote code, vote};

First we make strings from the keys:

keys = ToString /@ keys

Now we have the data: (just your data)

data = {{"UNGA", 63, 15294, "...", "30-Jun-09", 2009, 6, 30, "B", 
"Argentina", 3, "YES"}, {"UNGA", 63, 15294, "...", "30-Jun-09", 
2009, 6, 30, "B", "Cuba", 3, "YES"}, {"UNGA", 63, 15294, "...", 
"30-Jun-09", 2009, 6, 30, "B", "United States", 1, "NO"}, {"UNGA",
 63, 15295, "...", "9-Sep-09", 2009, 9, 9, "F", "Argentina", 2, 
"ABSTAIN"}, {"UNGA", 63, 15295, "...", "9-Sep-09", 2009, 9, 9, 
"F", "Botswana", 2, "ABSTAIN"}, {"UNGA", 63, 15295, "...", 
"9-Sep-09", 2009, 9, 9, "F", "Cuba", 1, "NO"}, {"UNGA", 63, 15295,
 "...", "9-Sep-09", 2009, 9, 9, "F", "United States", 3, 
"YES"}, {"UNGA", 63, 15295, "...", "9-Sep-09", 2009, 9, 9, "H", 
"Argentina", 2, "ABSTAIN"}, {"UNGA", 63, 15295, "...", "9-Sep-09",
 2009, 9, 9, "H", "Botswana", 2, "ABSTAIN"}, {"UNGA", 63, 15295, 
"...", "9-Sep-09", 2009, 9, 9, "H", "Cuba", 1, "NO"}, {"UNGA", 63,
 15295, "...", "9-Sep-09", 2009, 9, 9, "H", "United States", 3, 
"YES"}};

Now we build Rules (with a function, just for convenience)

makeRule[keys_, values_] := Rule @@@ ({keys, values}\[Transpose])

Next step - making an Association

temp = Association @@@ (makeRule[keys, #] & /@ data);

And finally... a Dataset

ds = Dataset@temp

Now you can simply extract data for the Dataset, e.g. all "NO" votes:

ds[Select[#vote == "NO" &]]

or summarize the votes of the grouped countries

ds[GroupBy[#country &]][All, All, "vote"]

or, or or.... Take a look at the documentation of Dataset.