# Group data in table by 2nd column while consolidating values in 3rd column

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}}

• 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. Commented Mar 10, 2015 at 20:20

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


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)]


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"]]


Reduce the dataset to what is wanted

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


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}}

• You read my mind but I got stuck on the munging. Commented Mar 11, 2015 at 0:11
• 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 Commented Mar 11, 2015 at 0:32
• @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. Commented Mar 11, 2015 at 2:53
• @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"]]] Commented Mar 11, 2015 at 15:26
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}}*)

• 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? Commented Mar 10, 2015 at 22:34
• 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]. Commented Mar 10, 2015 at 23:33
• Please note this code works with any number of columns and also works on duplicate entries Commented Mar 10, 2015 at 23:36
• 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 Commented Mar 11, 2015 at 0:34
• @krieger_saan see the update. Commented Mar 11, 2015 at 1:22

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}} *)