15
$\begingroup$

I have dataset of the following form. I have processed the dates to be DateObjects and now would like to merge the hours per the date because sometimes multiple types of tasks were done on the same day.

I was a bit surprised there wasn't a built-in function to do this but perhaps there is and I missed it in the docs? Right now I thought I would ask for help before going too far down this path because I can see this getting very convoluted very quickly. Is there a more Mathematica-centric way to do this? In Python with pandas I'd probably use a for-loop & append a list with the sums.

comparelist = Partition[Range[Length@data],2,1]
MatchQ[data[#[[1]],"Date"],data[#[[2]],"Date"]] &/@ comparelist

{False,False,False,False,False,False,False,False,False,False,False,False,False,False,False,False,False,True,True,False,False,False,False,False,False,False,False,False,False,False,False,True,False,False,False,False,False,False,True,False,False,False,False,True,False,False,False,False,False,False,False,False,False,True,False,False,False,False,False,False,False,False,True,False,False,False,False,True}
$\endgroup$
2
  • 1
    $\begingroup$ Please provide sample data if possible. $\endgroup$
    – Mr.Wizard
    Mar 14, 2020 at 23:21
  • $\begingroup$ Is it normally sufficient to provide a subset of the dataset in an association of associations? $\endgroup$
    – BBirdsell
    Mar 15, 2020 at 1:39

2 Answers 2

12
$\begingroup$
dr = DateObject /@ DateRange[{2020, 1, 1}, DatePlus[{2020, 1, 1}, Quantity[15, "Days"]]];

SeedRandom[1]
data = Transpose[{RandomChoice[dr, 30], 
   Round[ RandomReal[10, 30], .1], 
   RandomChoice[{"app", "graphics", "research"}, 30]}]; 

ds = Dataset[AssociationThread[{"Date", "Hours", "Task"}, #] & /@ data];

enter image description here

ds[GroupBy["Date"], All, {"Hours", "Task"}]

enter image description here

ds[GroupBy["Date"], All, "Hours"][All, Total]

enter image description here

Alternatively, you can use GroupBy on data:

GroupBy[data, First -> Rest, 
     Apply[{Total @ #, DeleteDuplicates @ #2} &] @* Transpose] // Dataset

enter image description here

GroupBy[data, First -> (#[[2]] &), Total] // Dataset

enter image description here

$\endgroup$
2
  • $\begingroup$ The first example is great because it keeps some of the data's structure. $\endgroup$
    – BBirdsell
    Mar 15, 2020 at 1:40
  • 1
    $\begingroup$ Or you can simply say ds[GroupBy["Date"], Total, "Hours"] and it will work! $\endgroup$
    – Victor K.
    Mar 15, 2020 at 7:26
6
$\begingroup$

While kglr's approach is correct, there is a more succinct one:

ds[GroupBy["Date"],Total,"Hours"]

The reason it works is because

ds[GroupBy["Date"],Total]

produces the total for every column in the grouped result, and "Hours" selects just the one we need.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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