Converting datasets to nested lists

Question

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!

I notice that you never Accepted an answer to this question. Does something remain unanswered or unsatisfactory? What can be done to improve the answers given? — Mr.Wizard, Jan 22 '15 at 04:37

Murta · Answer 1 · 2014-09-28T13:09:44.797

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

Update for your last form:

Normal@Normal@SP[GroupBy[#, Key["SNO"], Values@#[[All,{"PNO","QTY"}]]&] &]/. Rule -> List

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

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 '14 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 '14 at 22:40
@Murta +1 you could use: List @@@ Normal @ Normal @ SP[GroupBy[Key["SNO"] -> ({#PNO, #QTY} &)]] to eliminate the "SNO" key. — WReach, Jul 26 '14 at 21:55

score 3 · Answer 2 · answered Jul 22 '14 at 01:07

3

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

answered Jul 22 '14 at 01:07

alancalvitti

15,143
3
27
92

+1 You could reduce the SP[...][...] expression to SP[GroupBy[Key["SNO"]], KeyDrop["SNO"] /* Values]. – WReach Jul 26 '14 at 21:57
@WReach, thanks, w/ practice am using composition more. – alancalvitti Jul 30 '14 at 18:05

m_goldberg · Accepted Answer · 2014-07-23T02:30:30.453

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]

score 0 · Answer 4 · edited Oct 19 '23 at 12:06

0

This would also work:

grouped = Normal[SP[GroupBy[Key["SNO"]], All, Rest][Values, Values]]

edited Oct 19 '23 at 12:06

creidhne

5,055
4
20
28

answered Oct 19 '23 at 11:08

Thomas Grebel

1
1

Converting datasets to nested lists

4 Answers4

Edit