1
$\begingroup$

I currently have a list in the format:

 {Group, State, num}

{{a, CA, .5},
 {a, AK, .2},
 {a, MX, .1},
 {b, CA, .8},
 {b, AK, .8},
 {b, MX, .1},
 {b, HI, .7}}

I would like to ignore the first (Group) entry and form a list whose elements contain the State followed by all the associated num values, yielding, for instance:

State // Group1 # // Group2 # 

{{CA, .5, .8},
 {AK, .2, .8}, 
 {MX, .1, .1},
 {HI, 0, .7}}
$\endgroup$
1
  • $\begingroup$ Welcome to Mathematica.SE! I suggest that: 1) You take the introductory Tour now! 2) When you see good questions and answers, vote them up by clicking the gray triangles, because the credibility of the system is based on the reputation gained by users sharing their knowledge. Also, please remember to accept the answer, if any, that solves your problem, by clicking the checkmark sign! 3) As you receive help, try to give it too, by answering questions in your area of expertise. $\endgroup$
    – bbgodfrey
    Mar 10 '15 at 20:20
1
$\begingroup$

This assumes your "group" names and "state" names are all unique.. ( aside, I'd prefer to use strings for the state and group names, but this does work )

 l = {{a, CA, .5}, {a, AK, .2}, {a, MX, .1}, {b, CA, .8}, {b, 
       AK, .8}, {b, MX, .1}, {b, HI, .7}};
 gpton = Flatten[MapIndexed[{#1 -> First@#2} &, Union[l[[All, 1]]] ], 1];
 ston = Flatten[MapIndexed[{#1 -> First@#2} &, Union[l[[All, 2]]] ], 1];
 (m = SparseArray[Join[ {#[[2]], 1} -> #[[1]] & /@ ston,
      Map[  {#[[2]], #[[1]] + 1 } -> #[[3]]  &, l /. gpton /. ston]]]) // MatrixForm

enter image description here

$\endgroup$
1
$\begingroup$

Since this looked to me a lot like an operation on a dataset, I thought -- just for fun -- I'd try that approach. This will be a step-by-step presentation because there is no way I can write this kind of code in a single unreadable expression, such as are often admired in our community.

Raw data to data set.

keys = {"grp", "state", "num"};
vals = 
  {{a, "CA", .5}, {a, "AK", .2}, {a, "MX", .1}, {b, "CA", .8}, 
   {b, "AK", .8}, {b, "MX", .1}, {b, "HI", .7}};
ds1 = Dataset[AssociationThread @@@ (Rule[keys, #] & /@ vals)]

ds1

Regularize the records to make processing easy. Items in the "num" column are wrapped by List so Catenate can be applied later. The missing "HI" record is appended. The dataset is sorted by "state". (What state does "MX" represent?)

ds2 = Append[
        ds1[All, {"num" -> List}], 
        AssociationThread[keys -> {a, "HI", {0.}}]] // SortBy[Key["state"]]

ds2

Reduce the dataset to what is wanted

ds3 = ds2[GroupBy["state"], Catenate, "num"]

ds3

Munge the transformed dataset into the list form requested.

byState = (ds3 // Normal // Normal) /. Rule[x_, {y_, z_}] :> {x, y, z}
{{"AK", 0.2, 0.8}, {"CA", 0.5, 0.8}, {"HI", 0., 0.7}, {"MX", 0.1, 0.1}}
$\endgroup$
4
  • $\begingroup$ You read my mind but I got stuck on the munging. $\endgroup$ Mar 11 '15 at 0:11
  • $\begingroup$ I'm having trouble creating variable ds3. I get a two column dataset with state[<|<<1>>|>] in each row for the first column. Also looks like a ds4 is used but not initialized $\endgroup$ Mar 11 '15 at 0:32
  • $\begingroup$ @krieger_saan. My apologies. There was a typo in my answer. There is no ds4. I have corrected the definition of byState to use ds3, not ds4. $\endgroup$
    – m_goldberg
    Mar 11 '15 at 2:53
  • $\begingroup$ @m_goldberg Looks like my other problem was with the ds3 = ds2[GroupBy["state"], Catenate, "num"]-- example works when changed to ds2[GroupBy[Key["state"]]] $\endgroup$ Mar 11 '15 at 15:26
0
$\begingroup$
l = {{a, CA, .5}, {a, AK, .2}, {a, MX, .1}, {b, CA, .8}, {b,AK, .8}, {b, MX, .1}, {b, HI, .7}};
mx = Max@(Length /@ Gather[l[[;; , 2]]]);
dd = Merge[Dataset[Rule @@@ l[[;; , 2 ;;]]], Identity];
Flatten /@ (List @@@ Normal[Normal[dd[;; , (PadLeft[#, mx, 0] &)]]])

(* {{CA, 0.5, 0.8}, {AK, 0.2, 0.8}, {MX, 0.1, 0.1}, {HI, 0, 0.7}}*)

Based on your comment, here is one way of doing it:

l = {{a, CA, .5}, {a, AK, .2}, {a, MX, .1}, {b, CA, .8}, {b, 
    AK, .8}, {b, MX, .1}, {b, HI, .7}, {c, HI, .7}};
l2 = Flatten[
   Thread[List[#, Union@l[[;; , 2]], 0]] & /@ Union@l[[;; , 1]], 1];
l3 = SortBy[DeleteDuplicates[Join[l, l2], #[[;; 2]] === #2[[;; 2]] &],First];
dd = Merge[Dataset[Rule @@@ l3[[;; , 2 ;;]]], Identity];
Flatten /@ (List @@@ Normal[Normal[dd]])
(*{{AK, 0.2, 0.8, 0}, {CA, 0.5, 0.8, 0}, {HI, 0, 0.7, 0.7}, {MX, 0.1,0.1, 0}}*)
$\endgroup$
5
  • $\begingroup$ I tried this on my non-abstracted dataset, and I found that the col order does not keep. For example in the above, if you change the HI group spec to 'a' it will not move from the right col. Any pointers for a fix? $\endgroup$ Mar 10 '15 at 22:34
  • $\begingroup$ I am not sure what you mean by your comment but if you mean that the result should be sorted according to the first column then you can do that from the beginning l=SortBy[yourlist, First]. $\endgroup$ Mar 10 '15 at 23:33
  • $\begingroup$ Please note this code works with any number of columns and also works on duplicate entries $\endgroup$ Mar 10 '15 at 23:36
  • $\begingroup$ When the declaration for {b, HI, .7} changes to {a, HI, .7}- the result of {HI, 0, 0.7} should swap to {HI, 0.7, 0}. Apologies for not being clear $\endgroup$ Mar 11 '15 at 0:34
  • $\begingroup$ @krieger_saan see the update. $\endgroup$ Mar 11 '15 at 1:22
0
$\begingroup$

Just for giggles:

l = {{a, CA, .5}, {a, AK, .2}, {a, MX, .1}, {b, CA, .8},
     {b, AK, .8}, {b, MX, .1}, {b, HI, .7}};

Total /@ GatherBy[Replace[l, {{a, x_, y_} :> {x, y, 0}, {b, x_, y_} :> {x, 0, y}}, {1}],
                  First] /. Times[_, b_] :> b

(* {{CA, 0.5, 0.8}, {AK, 0.2, 0.8}, {MX, 0.1, 0.1}, {HI, 0, 0.7}} *)
$\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.