1

I have a dataset which looks like this:

Dataset

I want to create another dataset such that I get the count of A1flag for each userId, i.e., 5311->2, 5313->2.

I tried the following code

GroupBy[FEflag[Select[#A1flag == 1 &], {"userId", "A1flag"}], "userId",Length]

but, with this I lose the names of columns.

Anyone has a better suggestion?

Priyesh Sharma
  • 359
  • 1
  • 3
  • Consider adding a small test dataset to your question so we don't have to come up with our own :) – Carl Lange Apr 04 '19 at 17:22
  • A test dataset can be generated by FEflag = AssociationThread[{"userId", "A1flag"}, #] & /@ {{5311, 1}, {5311, 1}, {5313, 1}, {5313, 1}, {5313, 1}} // Dataset – Carl Lange Apr 04 '19 at 17:28
  • Values@GroupBy[FEflag[Select[#A1flag == 1 &], All], "userId", Total] should do it, but it will only work when the value of A1flag is 1, or it will count weird. – Carl Lange Apr 04 '19 at 17:31
  • Ok. I shall follow this from the next time. I wasn't aware of how to add datasets to the posts – Priyesh Sharma Apr 04 '19 at 17:32
  • @CarlLange A1flag has only 1 and 0. But the code you shared doesn't work – Priyesh Sharma Apr 04 '19 at 17:34
  • 1
    @Maria don't wait for the next time, please update the question with a minimal example and the expected result so that everything is clear. – Kuba Apr 05 '19 at 07:06

3 Answers3

1
FEflag = AssociationThread[{"userId", "A1flag"}, #] & /@
  {{5311, 1}, {5311, 1}, {5313, 1}, {5313, 1}, {5313, 1}, {5313, 0}} // Dataset

enter image description here

GroupBy[FEflag, First -> Last, Total]

enter image description here

The same result can be achieved more robustly with

GroupBy[FEflag, Key["userId"] -> Key["A1flag"], Total]

enter image description here

which makes fewer assumptions about the ordering of the columns.

I don't know a pretty way of keeping the name of the first column, but the following works by reconstructing a new Dataset from the results of GroupBy:

KeyValueMap[<|"userId" -> #1, "sum" -> #2|> &, 
  GroupBy[FEflag, Key["userId"] -> Key["A1flag"], Total]]

enter image description here

edit

Following @VictorK. 's solution, the above query can be simplified a bit:

KeyValueMap[<|"userId" -> #1, "sum" -> #2|> &,
  FEflag[GroupBy["userId"], Total, "A1flag"]]

enter image description here

Roman
  • 47,322
  • 2
  • 55
  • 121
  • Thanks. This works, but it removes the column names. I want to keep userId for the first column and rename the new count column with something else. – Priyesh Sharma Apr 04 '19 at 17:55
  • Thanks, it works :) – Priyesh Sharma Apr 04 '19 at 18:19
  • I just cleaned it up a bit, the new version is better. Still a hack though. – Roman Apr 04 '19 at 18:26
  • You can keep the column name by using Part with a list: GroupBy[FEflag, First -> Function[#[[{"A1flag"}]]], Total] – Sjoerd Smit Apr 04 '19 at 18:36
  • @SjoerdSmit do you know how to keep the userId as well, without doing a Dataset@KeyValueMap on the result of GroupBy? – Roman Apr 04 '19 at 18:46
  • Well, GroupBy always returns an association, so you'll always need to do some extra step to turn it into a list of associations. There is also the function GatherBy, which returns a list, but that's not very easy to use in this situation. Or you do GroupBy[FEflag, Function[#[[{"userId"}]]] -> Function[#[[{"A1flag"}]]], Total] and just accept that you get an association with associations for keys. – Sjoerd Smit Apr 04 '19 at 18:52
1

Another possibility is to repeat the "userId" field in the GroupBy RHS, and then use Values:

Values @ FEflag[
    GroupBy["userId"],
    <|"userId" -> "userId" @* First, "A1flag" -> Total @* Lookup["A1flag"]|>
]

enter image description here

Carl Woll
  • 130,679
  • 6
  • 243
  • 355
0

I'm surprised no one has mentioned the most straightforward solution that is mentioned on Dataset help page:

FEflag[GroupBy["userId"], Length, "A1flag"]

enter image description here

Victor K.
  • 5,146
  • 3
  • 21
  • 34