# How to convert a Dataset into an indexed dataset / association-of-associations given a column header?

Given a dataset as such

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?

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|>};
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 *)
data = RandomReal[{-1, 1}, {100000, 26}];


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


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


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


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

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



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

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

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



• +1. Similarly, ds[Apply[Association], #[ch] -> KeyDrop[#, ch]&] Commented Nov 19, 2020 at 4:50
• This appears the fastest answer despite converting to Normal and then Dataset Commented Nov 19, 2020 at 10:31
• I went with a similiar riff on what Anton did: f[ds_, col_] := Dataset[AssociationThread[ Normal@ds[[All, col]] -> Normal[KeyDrop[ds, col]]]] Commented Nov 19, 2020 at 14:10
• @WReach I updated my answer with your suggestion. (It is also faster.) Commented Nov 20, 2020 at 13:22
• 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. Commented Nov 23, 2020 at 11:02
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]]


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


and

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

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


• 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 Commented Nov 19, 2020 at 10:13
• @IntroductionToProbability, could you post an example that you used for timing comparisons?
– kglr
Commented Nov 19, 2020 at 10:28
• @kglt sure thing Commented Nov 19, 2020 at 10:43
• @IntroductionToProbability This method can be sped up a little with ds[GroupBy[#, #letter & -> KeyDrop["letter"], First] &] Commented Nov 19, 2020 at 11:14
• @SjoerdSmit This is elegant, would you be able to submit it as an answer? Commented Nov 19, 2020 at 11:34

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.

• 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. Commented Nov 20, 2020 at 10:02
AssociationThread[data[[;; , 1]] -> data[[;; , 2 ;;]]] // Dataset


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