9
$\begingroup$

Given a dataset as such

Input

If "letter" is the header that is chosen, how do I convert it into an indexed dataset / association-of-associations?

i.e. How do I define f such that f[dataset_,columnHeader_] produces the following?

enter image description here

Please note GroupBy is close but fails as you are unable to use Part to work with the result to extract column data. eg:

data = {<|"letter" -> "a", "foo" -> 1, "bar" -> 2|>, <|"letter" -> "b", "foo" -> 3, "bar" -> 4|>, <|"letter" -> "c", "foo" -> 5, "bar" -> 6|>};
dataDS = Dataset[data];
dataDSg= GroupBy[dataDS, Key["letter"]];
dataDSg[All, "foo"] (* <- produces an error *)

Where as data in the format of an association-of-association works fine

data2 = <|"a" -> <|"foo" -> 1, "bar" -> 2|>, "b" -> <|"foo" -> 3, "bar" -> 4|>, "c" -> <|"foo" -> 5, "bar" -> 6|>|>;
data2DS = data2 // Dataset;
data2DS [All, "foo"] (* <- returns a dataset with 1,3,5 *)

Update

Some timing comparisons

(* make dataset to test *)
colHeader = CharacterRange["a", "z"];
colHeader[[1]] = "letter";
data = RandomReal[{-1, 1}, {100000, 26}];
table = Insert[data, colHeader, 1];
dataDS = Dataset[AssociationThread[table[[1]], #] & /@ table[[2 ;;]]];

Anton Antonov answer

f[ds_Dataset, ch_] := Dataset@Association@Normal@ds[All, #[ch] -> KeyDrop[#, ch] &]
fAns = f[dataDS, "letter"]; // RepeatedTiming (* 0.934 *)

kglr answer

f0 = GroupBy[##, Association@*KeyDrop[#2]] &;
f0ans = f0[dataDS, "letter"]; // RepeatedTiming (* 1.85 *)

f1 = #[GroupBy[#2] /* Map[Association@*KeyDrop[#2]]] &;
f1ans = f1[dataDS, "letter"]; // RepeatedTiming (* 1.714 *)

Sjoerd Smit answer

groupByKey[ds_, key_String] := GroupBy[ds, Function[Slot[key]] -> KeyDrop[key], First];
groupByKeyAns = groupByKey[dataDS, "letter"]; // RepeatedTiming (* 1.2 *)

some other timings that don't produce an answer but help to put the times above into context

GroupBy[dataDS, "letter"]; // RepeatedTiming (* 0.25 *)
Dataset[Normal[dataDS]]; // RepeatedTiming (* 0.38 *)
$\endgroup$

4 Answers 4

8
$\begingroup$
data = {<|"letter" -> "a", "foo" -> 1, "bar" -> 2|>, <|
    "letter" -> "b", "foo" -> 3, "bar" -> 4|>, <|"letter" -> "c", 
    "foo" -> 5, "bar" -> 6|>};
dataDS = Dataset[data];

ClearAll[f];
f[ds_Dataset, ch_] := ds[Apply[Association], #[ch] -> KeyDrop[#, ch] &];

f[dataDS, "letter"]

enter image description here

(Using the definition suggested by @WReach in the comments.)

First answer

data = {<|"letter" -> "a", "foo" -> 1, "bar" -> 2|>, <|
    "letter" -> "b", "foo" -> 3, "bar" -> 4|>, <|"letter" -> "c", 
    "foo" -> 5, "bar" -> 6|>};
dataDS = Dataset[data];

ClearAll[f];
f[ds_Dataset, ch_] := Dataset@Association@Normal@ds[All, #[ch] -> KeyDrop[#, ch] &];

f[dataDS, "letter"]

enter image description here

$\endgroup$
6
  • 1
    $\begingroup$ +1. Similarly, ds[Apply[Association], #[ch] -> KeyDrop[#, ch]&] $\endgroup$
    – WReach
    Nov 19, 2020 at 4:50
  • 2
    $\begingroup$ This appears the fastest answer despite converting to Normal and then Dataset $\endgroup$ Nov 19, 2020 at 10:31
  • $\begingroup$ I went with a similiar riff on what Anton did: f[ds_, col_] := Dataset[AssociationThread[ Normal@ds[[All, col]] -> Normal[KeyDrop[ds, col]]]] $\endgroup$
    – kickert
    Nov 19, 2020 at 14:10
  • $\begingroup$ @WReach I updated my answer with your suggestion. (It is also faster.) $\endgroup$ Nov 20, 2020 at 13:22
  • $\begingroup$ I missed WReach reply initially and will select this as the answer as it does not involve converting back to normal and is the fastest answer. $\endgroup$ Nov 23, 2020 at 11:02
5
$\begingroup$
ClearAll[f0]
f0 = GroupBy[##, Association @* KeyDrop[#2]] &;

Examples:

ds = Dataset @ {<|"letter" -> "a", "foo" -> 1, "bar" -> 2|>, 
        <|"letter" -> "b", "foo" -> 3, "bar" -> 4|>, 
        <|"letter" -> "c", "foo" -> 5, "bar" -> 6|>};

Row[{ds, f0[ds, "letter"], f0[ds, "foo"], f0[ds, "bar"]}, Spacer[10]]

enter image description here

You can also do:

ClearAll[f1]
f1 = #[GroupBy[#2] /* Map[Association @* KeyDrop[#2]]] &;

Row[{ds, f1[ds, "letter"], f1[ds, "foo"], f1[ds, "bar"]}, Spacer[10]]

enter image description here

and

ClearAll[f2]
f2 = #[GroupBy @ #2, All, First @ Normal @ Keys @ KeyDrop @ ##] &;

Row[{ds, f2[ds, "letter"], f2[ds, "foo"], f2[ds, "bar"]}, Spacer[10]]

enter image description here

$\endgroup$
6
  • $\begingroup$ I like this answer as it does not convert to Normal followed by Dataset and it also introduced me to the Composition (@*). However, Anton's method appears ~2x quicker $\endgroup$ Nov 19, 2020 at 10:13
  • 1
    $\begingroup$ @IntroductionToProbability, could you post an example that you used for timing comparisons? $\endgroup$
    – kglr
    Nov 19, 2020 at 10:28
  • $\begingroup$ @kglt sure thing $\endgroup$ Nov 19, 2020 at 10:43
  • 1
    $\begingroup$ @IntroductionToProbability This method can be sped up a little with ds[GroupBy[#, #letter & -> KeyDrop["letter"], First] &] $\endgroup$ Nov 19, 2020 at 11:14
  • $\begingroup$ @SjoerdSmit This is elegant, would you be able to submit it as an answer? $\endgroup$ Nov 19, 2020 at 11:34
5
$\begingroup$

Related to kglr's answer, here's a slight variation:

ds = Dataset @ {
    <|"letter" -> "a", "foo" -> 1, "bar" -> 2|>, 
    <|"letter" -> "b",  "foo" -> 3, "bar" -> 4|>,
    <|"letter" -> "c", "foo" -> 5, "bar" -> 6|>
};
groupByKey[ds_, key_String] := GroupBy[ds, Function[Slot[key]] -> KeyDrop[key], First];
groupByKey[ds, "letter"]

Of course, you have to be confident that the key values you're grouping by is actually unique, otherwise you'll be dropping rows.

$\endgroup$
1
  • $\begingroup$ Though slightly slower than Anton's answer I found this most helpful as it kept in between the operations within the "Dataset space", i.e. without reverting to Normal and Association. $\endgroup$ Nov 20, 2020 at 10:02
3
$\begingroup$
AssociationThread[data[[;; , 1]] -> data[[;; , 2 ;;]]] // Dataset

enter image description here

AssociationThread[data[[All, "foo"]] -> KeyDrop["foo"] /@ data] // Dataset

enter image description here

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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