1
$\begingroup$

This question already has an answer here:

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?

$\endgroup$

marked as duplicate by Edmund, MarcoB, m_goldberg, Alex Trounev, bbgodfrey Apr 7 at 17:23

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • $\begingroup$ Consider adding a small test dataset to your question so we don't have to come up with our own :) $\endgroup$ – Carl Lange Apr 4 at 17:22
  • $\begingroup$ A test dataset can be generated by FEflag = AssociationThread[{"userId", "A1flag"}, #] & /@ {{5311, 1}, {5311, 1}, {5313, 1}, {5313, 1}, {5313, 1}} // Dataset $\endgroup$ – Carl Lange Apr 4 at 17:28
  • $\begingroup$ 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. $\endgroup$ – Carl Lange Apr 4 at 17:31
  • $\begingroup$ Ok. I shall follow this from the next time. I wasn't aware of how to add datasets to the posts $\endgroup$ – Annesha Bhoumik Apr 4 at 17:32
  • 1
    $\begingroup$ @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. $\endgroup$ – Kuba Apr 5 at 7:06
1
$\begingroup$

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

$\endgroup$
1
$\begingroup$
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

$\endgroup$
  • $\begingroup$ 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. $\endgroup$ – Annesha Bhoumik Apr 4 at 17:55
  • $\begingroup$ Thanks, it works :) $\endgroup$ – Annesha Bhoumik Apr 4 at 18:19
  • $\begingroup$ I just cleaned it up a bit, the new version is better. Still a hack though. $\endgroup$ – Roman Apr 4 at 18:26
  • $\begingroup$ You can keep the column name by using Part with a list: GroupBy[FEflag, First -> Function[#[[{"A1flag"}]]], Total] $\endgroup$ – Sjoerd Smit Apr 4 at 18:36
  • $\begingroup$ @SjoerdSmit do you know how to keep the userId as well, without doing a Dataset@KeyValueMap on the result of GroupBy? $\endgroup$ – Roman Apr 4 at 18:46
0
$\begingroup$

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

$\endgroup$
  • 1
    $\begingroup$ probably because <|5311 -> 2, 5313 -> 4|> is not a desired result. $\endgroup$ – Kuba Apr 5 at 7:05
  • $\begingroup$ indeed; I completely missed part of the requirement. $\endgroup$ – Victor K. Apr 6 at 23:14

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