1
$\begingroup$

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

$\endgroup$
  • 1
    $\begingroup$ It would help if you could give a small example of input and output that you desire from it. $\endgroup$ – Mr.Wizard Dec 30 '14 at 3:17
  • $\begingroup$ Right. Input would be lists like these: {{"UNGA", 1, 10044, "GAOR-1-66", "1-Jan-46", 1946, 1, 1, "F", "United States", 3, "YES"}, {"UNGA", 1, 10044, "GAOR-1-66", "1-Jan-46", 1946, 1, 1, "L", "Canada", 3, "YES"}} And output would be, say, a list containing ones or zeroes to indicate coincidence or non-coincidence. $\endgroup$ – arebearit Dec 30 '14 at 3:45
  • 1
    $\begingroup$ Do you really have a (Mathematica) Dataset or is this (only) a list. If you have a Dataset there is a good chance to get a simple solution. $\endgroup$ – mgamer Dec 30 '14 at 7:45
  • $\begingroup$ I guess it is just a list of lists – in contrast to a native Mathematica dataset? Is that what you mean? $\endgroup$ – arebearit Dec 30 '14 at 15:32
  • $\begingroup$ Is the intent to pass two specific countries and get a result pertinent to those two? Or are you trying for an "all-pairs" type of result? There may be better or worse approaches depending on which of these scenarios (if either) you wish to address. $\endgroup$ – Daniel Lichtblau Dec 30 '14 at 15:35
0
$\begingroup$

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}}
$\endgroup$
0
$\begingroup$

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.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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