# 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]&] – WReach Nov 19 '20 at 4:50
• This appears the fastest answer despite converting to Normal and then Dataset – IntroductionToProbability Nov 19 '20 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]]]] – kickert Nov 19 '20 at 14:10
• @WReach I updated my answer with your suggestion. (It is also faster.) – Anton Antonov Nov 20 '20 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. – IntroductionToProbability Nov 23 '20 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 – IntroductionToProbability Nov 19 '20 at 10:13
• @IntroductionToProbability, could you post an example that you used for timing comparisons? – kglr Nov 19 '20 at 10:28
• @kglt sure thing – IntroductionToProbability Nov 19 '20 at 10:43
• @IntroductionToProbability This method can be sped up a little with ds[GroupBy[#, #letter & -> KeyDrop["letter"], First] &] – Sjoerd Smit Nov 19 '20 at 11:14
• @SjoerdSmit This is elegant, would you be able to submit it as an answer? – IntroductionToProbability Nov 19 '20 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. – IntroductionToProbability Nov 20 '20 at 10:02