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 *)
Improve this question
$\endgroup$

4 Answers 4

Reset to default
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

Improve this answer
$\endgroup$
6
  • 1
    $\begingroup$ +1. Similarly, ds[Apply[Association], #[ch] -> KeyDrop[#, ch]&] $\endgroup$ Commented Nov 19, 2020 at 4:50
  • 2
    $\begingroup$ This appears the fastest answer despite converting to Normal and then Dataset $\endgroup$ Commented 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$ Commented Nov 19, 2020 at 14:10
  • $\begingroup$ @WReach I updated my answer with your suggestion. (It is also faster.) $\endgroup$ Commented 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$ Commented 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

Improve this answer
$\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$ Commented Nov 19, 2020 at 10:13
  • 1
    $\begingroup$ @IntroductionToProbability, could you post an example that you used for timing comparisons? $\endgroup$ Commented Nov 19, 2020 at 10:28
  • $\begingroup$ @kglt sure thing $\endgroup$ Commented 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$ Commented Nov 19, 2020 at 11:14
  • $\begingroup$ @SjoerdSmit This is elegant, would you be able to submit it as an answer? $\endgroup$ Commented 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.

Improve this answer
$\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$ Commented 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

Improve this answer
$\endgroup$

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.