14
$\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 '20 at 23:21
  • $\begingroup$ Is it normally sufficient to provide a subset of the dataset in an association of associations? $\endgroup$ – BBirdsell Mar 15 '20 at 1:39
11
$\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 '20 at 1:40
  • 1
    $\begingroup$ Or you can simply say ds[GroupBy["Date"], Total, "Hours"] and it will work! $\endgroup$ – Victor K. Mar 15 '20 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, privacy policy and cookie policy

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