Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

Imagine the Shipments relation:

SP = Dataset[{
<|"SNO" -> "S1", "PNO" -> "P1", "QTY" -> 300|>,
<|"SNO" -> "S1", "PNO" -> "P2", "QTY" -> 200|>,
<|"SNO" -> "S1", "PNO" -> "P3", "QTY" -> 400|>,
<|"SNO" -> "S1", "PNO" -> "P4", "QTY" -> 200|>,
<|"SNO" -> "S1", "PNO" -> "P5", "QTY" -> 100|>,
<|"SNO" -> "S1", "PNO" -> "P6", "QTY" -> 100|>,
<|"SNO" -> "S2", "PNO" -> "P1", "QTY" -> 300|>,
<|"SNO" -> "S2", "PNO" -> "P2", "QTY" -> 400|>,
<|"SNO" -> "S3", "PNO" -> "P2", "QTY" -> 200|>,
<|"SNO" -> "S4", "PNO" -> "P2", "QTY" -> 200|>,
<|"SNO" -> "S4", "PNO" -> "P4", "QTY" -> 300|>,
<|"SNO" -> "S4", "PNO" -> "P5", "QTY" -> 400|>
}];

which is then Grouped By shipper (SNO):

grouped=SP[GroupBy[Key["SNO"]]]

What is a concise way to convert this into a List of Lists of Lists that contain only the Values? (I suppose I am trying to reproduce Gather-like behavior with datasets.)

(Thanks Murta:) I hope to see:

 {
 {{"P1",300},
  {"P2",200},
  {"P3",400},
  {"P4",200},
  {"P5",100}},
 {{"P1",300},
  {"P2",400}},
 {{"P2",200}},
 {{"P2",200},
  {"P4",300},
  {"P5",400}}
            }

Something like:

 {
 {"S1",{{"P1",300},
        {"P2",200},
        {"P3",400},
        {"P4",200},
        {"P5",100}}},
 {"S2",{{"P1",300},
        {"P2",400}}},
 {"S3",{{"P2",200}}},
 {"S4",{{"P2",200},
        {"P4",300},
        {"P5",400}}}
            }

might be even better!

share|improve this question
1  
Can you put the result that you want in your question? –  Murta Jul 22 at 0:54

3 Answers 3

Here is one option:

Normal@Values@SP[GroupBy[#, Key["SNO"], Values] &]

you get:

{{{"S1", "P1", 300}, {"S1", "P2", 200}, {"S1", "P3", 400}, {"S1", 
   "P4", 200}, {"S1", "P5", 100}, {"S1", "P6", 100}}, {{"S2", "P1", 
   300}, {"S2", "P2", 400}}, {{"S3", "P2", 200}}, {{"S4", "P2", 
   200}, {"S4", "P4", 300}, {"S4", "P5", 400}}}

or:

Normal@Normal@SP[GroupBy[#, Key["SNO"], Values] &]/. Rule -> List

for the second case:

{{"S1", {{"S1", "P1", 300}, {"S1", "P2", 200}, {"S1", "P3", 
    400}, {"S1", "P4", 200}, {"S1", "P5", 100}, {"S1", "P6", 
    100}}}, {"S2", {{"S2", "P1", 300}, {"S2", "P2", 
    400}}}, {"S3", {{"S3", "P2", 200}}}, {"S4", {{"S4", "P2", 
    200}, {"S4", "P4", 300}, {"S4", "P5", 400}}}}

You can also do it without Dataset:

ass = {<|"SNO" -> "S1", "PNO" -> "P1", "QTY" -> 300|>, <|
    "SNO" -> "S1", "PNO" -> "P2", "QTY" -> 200|>, <|"SNO" -> "S1", 
    "PNO" -> "P3", "QTY" -> 400|>, <|"SNO" -> "S1", "PNO" -> "P4", 
    "QTY" -> 200|>, <|"SNO" -> "S1", "PNO" -> "P5", "QTY" -> 100|>, <|
    "SNO" -> "S1", "PNO" -> "P6", "QTY" -> 100|>, <|"SNO" -> "S2", 
    "PNO" -> "P1", "QTY" -> 300|>, <|"SNO" -> "S2", "PNO" -> "P2", 
    "QTY" -> 400|>, <|"SNO" -> "S3", "PNO" -> "P2", "QTY" -> 200|>, <|
    "SNO" -> "S4", "PNO" -> "P2", "QTY" -> 200|>, <|"SNO" -> "S4", 
    "PNO" -> "P4", "QTY" -> 300|>, <|"SNO" -> "S4", "PNO" -> "P5", 
    "QTY" -> 400|>};

You get:

Normal@GroupBy[ass, Key["SNO"], Values] /. Rule -> List

{{"S1", {{"S1", "P1", 300}, {"S1", "P2", 200}, {"S1", "P3", 
    400}, {"S1", "P4", 200}, {"S1", "P5", 100}, {"S1", "P6", 
    100}}}, {"S2", {{"S2", "P1", 300}, {"S2", "P2", 
    400}}}, {"S3", {{"S3", "P2", 200}}}, {"S4", {{"S4", "P2", 
    200}, {"S4", "P4", 300}, {"S4", "P5", 400}}}}
share|improve this answer
    
There are very nice and clever. Would it be possible to modify your answer so that the "SNO" (e.g. "S1") is not in the {"S1","P1",300} part? –  Eric Brown Jul 22 at 15:16
    
For Association form you can use this: Normal@GroupBy[ass, Key["SNO"], Values@*Query[All, {"PNO", "QTY"}]]/.Rule -> List? –  Murta Jul 22 at 22:40
    
@EricBrown, see my answer using KeyDrop. –  alancalvitti Jul 23 at 3:21
    
@Murta +1 you could use: List @@@ Normal @ Normal @ SP[GroupBy[Key["SNO"] -> ({#PNO, #QTY} &)]] to eliminate the "SNO" key. –  WReach Jul 26 at 21:55
grouped = 
 SP[GroupBy[Key["SNO"]], KeyDrop["SNO"]][All, All, Values] // Normal //
   Normal // Column

Gives your second list if you don't mind the Rule at first Level:

S1->{{P1,300},{P2,200},{P3,400},{P4,200},{P5,100},{P6,100}}
S2->{{P1,300},{P2,400}}
S3->{{P2,200}}
S4->{{P2,200},{P4,300},{P5,400}}
share|improve this answer
    
+1 You could reduce the SP[...][...] expression to SP[GroupBy[Key["SNO"]], KeyDrop["SNO"] /* Values]. –  WReach Jul 26 at 21:57
    
@WReach, thanks, w/ practice am using composition more. –  alancalvitti Jul 30 at 18:05

Perhaps

Module[{grouped, keys},
  grouped = SP[GroupBy[Key["SNO"]]];
  keys = grouped[Keys] // Normal;
  {#, grouped[#][All, {"PNO", "QTY"}][All, Values] // Normal} & /@ keys]
{{"S1", {{"P1", 300}, {"P2", 200}, {"P3", 400}, {"P4", 200}, {"P5", 100}, {"P6", 100}}}, 
 {"S2", {{"P1", 300}, {"P2", 400}}}, 
 {"S3", {{"P2", 200}}}, 
 {"S4", {{"P2", 200}, {"P4", 300}, {"P5", 400}}}}

Edit

Perhaps a little better.

Module[{grouped, keys},
  grouped = SP[GroupBy[Key["SNO"]]];
  keys = grouped[Keys] // Normal;
  {#, grouped[#][All, {"PNO", "QTY"}] // Normal // Values} & /@ keys]
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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