9
$\begingroup$

Suppose we have in ~/time-data/time-data.org the following data:

* Parent1
:LOGBOOK:
CLOCK: [2019-07-09 Tue 00:00]--[2019-07-09 Tue 00:20] =>  0:20
:END:
** Child1
:LOGBOOK:
CLOCK: [2019-07-10 Wed 00:02]--[2019-07-10 Wed 00:40] =>  0:38
:END:
** Child2
:LOGBOOK:
CLOCK: [2019-07-11 Thu 00:02]--[2019-07-11 Thu 06:40] =>  0:38
:END:

We then can use atheriel/org-clock-csv to to pull this data via

(org-clock-csv-to-file "~/time-data/time-data.csv" '("~/time-data/time-data.org"))

which populates time-data.csv with

task,parents,category,start,end,effort,ishabit,tags
Parent1,,,2019-07-09 00:00,2019-07-09 00:20,,,
Child1,Parent1,,2019-07-10 00:02,2019-07-10 00:40,,,
Child2,Parent1,,2019-07-11 00:02,2019-07-11 06:40,,,

so that in Mathematica we can run:

enter image description here

Question: How do we get a DateListPlot out of this that shows, i.e., hours spent per day?

EDIT: I fed everyone's answers through my actual data (which spans several months) and published them here. I get lots of errors and (mostly) unparsable graphs. I think these answers are getting me closer to something usable though!

Improve this question
$\endgroup$
  • 1
    $\begingroup$ If you do ds = SemanticImport["f.csv", HeaderLines->1], then DateListPlot[ds[All, "start"] that will get you somewhere. Also have a look at TimelinePlot - you could generate intervals using the start and ends. (And hello, fellow emacser) $\endgroup$ – Carl Lange Jul 9 '19 at 20:38
  • 1
    $\begingroup$ There is Gantt-chart making tool for org-mode in emacs, but I never made it work. So, I did/do similar imports and timeline plots. $\endgroup$ – Anton Antonov Jul 9 '19 at 21:00
  • $\begingroup$ Can you add your "time-data.csv" file? $\endgroup$ – M.R. Dec 10 '19 at 16:34
  • $\begingroup$ M.R.: See above. $\endgroup$ – George Dec 10 '19 at 17:08
5
+25
$\begingroup$

If Mathematica was perfect, DateHistogram[..., "Day", "Hour"] would work, making what you want a one-liner. I believe that a DateInterval function might be coming in the next version (12.1) which would presumably work with DateHistogram and TimelinePlot.

All that aside, let's see how to chart a temporal histogram across all of your tasks. First, let's import your dataset:

csv = "task,parents,category,start,end,effort,ishabit,tags
      Parent1,,,2019-07-07 00:00,2019-07-07 00:20,,,
      Child1,Parent1,,2019-07-8 00:02,2019-07-8 00:40,,,
      Child2,Parent1,,2019-07-9 00:02,2019-07-9 06:40,,,
      Parent2,,,2019-07-08 00:00,2019-07-08 00:20,,,
      Child21,Parent2,,2019-07-9 00:02,2019-07-9 00:40,,,
      Child22,Parent2,,2019-07-10 00:02,2019-07-10 06:40,,,
      Parent3,,,2019-07-09 00:00,2019-07-09 00:20,,,
      Child31,Parent3,,2019-07-10 00:02,2019-07-10 00:40,,,
      Child32,Parent3,,2019-07-11 00:02,2019-07-11 06:40,,,";
ds = ImportString[csv, {"CSV", "Dataset"}, HeaderLines -> 1];

enter image description here

Now with a single GroupBy[] command, we change the raw data into the form we need:

data = GroupBy[ds[All, <|"p" -> If[#parents == "", #task, #parents], 
     "d" -> (DateObject /@ {#"start", #"end"})|> &], First -> Last, 
     Map[{CurrentDate[#[[1]], "Hour"], DateDifference[#[[1]], #[[2]], "Hour"]} &]]

enter image description here

and then visualize it by simply calling:

Row @ {DateListPlot[data, Filling -> Axis, ImageSize -> Medium, PlotLegends -> None],
StackedDateListPlot[data, PlotTheme -> "Detailed", ImageSize -> Medium]}

enter image description here

Another (non-dataset based) way to do this is as follows:

ds = ImportString[csv, "CSV"];dates = Map[DateObject, ds[[2 ;;, {4, 5}]], {-1}];
dr = Flatten[DateRange[##, "Minute"] & @@@ dates];
DateHistogram[dr, "Day", DateReduction -> "Week", FrameLabel -> {None, "Minutes"}, Frame -> True, 
 LabelingFunction -> (Column@{Quantity[#/60., "Hours"], Quantity[#, "Seconds"]} &)]

enter image description here

Or we can discretize by "Hours":

dr = DeleteDuplicates[DateObject[#, "Hour"] & /@ Flatten[DateRange[##, "Hours"] & @@@ dates]];
DateHistogram[dr, "Day", DateReduction -> "Week", FrameLabel -> {None, "Hours"}, Frame -> True]

enter image description here

Yet another way to analyze it (with a different coding style to boot) is to read it as a graph and plot a weighted tree-map. To further break your data down by parent task, try this:

edges = Normal[(Reverse /@ Rule @@@ ds[[2 ;;, {1, 2}]]) /. "" -> "Root"];
TreePlot[edges, Top, "Root", VertexLabels -> "Name", DirectedEdges -> True]

enter image description here

taskParent[t_] := With[{parent = FirstCase[edges, Verbatim[Rule][p_, t] :> p]}, If[parent == "Root", t, parent]];
dr = DateRange[##, "Minute"] & @@@ dates;
groups = Flatten /@ GroupBy[Thread[{taskParent /@ ds[[2 ;;, 1]], dr}], First -> Last];
DateHistogram[Values[groups], "Day", ChartLayout -> "Stacked", ChartLegends -> Keys[groups], DateReduction -> "Week", FrameLabel -> {None, "Hours"}, PlotTheme -> "Marketing"]

enter image description here

Improve this answer
$\endgroup$
  • $\begingroup$ Does this fit your needs? $\endgroup$ – M.R. Dec 10 '19 at 19:10
  • $\begingroup$ M.R.: Thanks for your answer! See wolframcloud.com/obj/george.w.singer/Published/time-data I fed your functions into my actual dataset (which spans several months) and got a bunch of errors/unparsable graphs. $\endgroup$ – George Dec 16 '19 at 15:59
  • $\begingroup$ Can we wrap all of these categories into one, as a simplifying assumption? (i.e.: don't split up the time data into different categories, but instead just plot hours logged over time). $\endgroup$ – George Dec 16 '19 at 16:19
  • 1
    $\begingroup$ M.R.: I was able to build on your answer to get a simpler data/graph set for my purposes. Thanks!! $\endgroup$ – George Dec 16 '19 at 21:41
4
$\begingroup$

You may use the "Dataset" and "HeaderLines" Import options for "CSV" along with Dataset and Query.

Using a slightly modified csv from @M.R.

csv = "task,parents,category,start,end,effort,ishabit,tags
    Parent1,,,2019-07-07 00:00,2019-07-07 00:20,,,
    Child1,Parent1,,2019-07-8 00:02,2019-07-8 00:40,,,
    Child2,Parent1,,2019-07-9 00:02,2019-07-9 06:40,,,
    Parent2,,,2019-07-08 00:00,2019-07-08 00:20,,,
    Child21,Parent2,,2019-07-9 00:02,2019-07-9 00:40,,,
    Child22,Parent2,,2019-07-10 00:02,2019-07-10 06:40,,,
    Parent3,,,2019-07-09 00:00,2019-07-09 00:20,,,
    Child31,Parent3,,2019-07-10 00:02,2019-07-10 00:40,,,
    Child32,Parent3,,2019-07-11 00:02,2019-07-11 06:40,,,";

Import (using ImportString but its the exact same options for Import) as a Dataset with

ds = ImportString[csv, {"CSV", "Dataset"}, "HeaderLines" -> 1]

Mathematica graphics

Convert "start" and "end" to DateObjects and fill in "parents" for parent task to make grouping easier.

ds =
 Query[All, <|#, "parents" -> If[#parents == "", #task, #parents]|> &]@
  Query[All, Thread[{"start", "end"} -> DateObject]]@ds

Mathematica graphics

GroupBy "parents" then the CurrentDate "Day" of "start", calculate the DateDifference in "Hour"s between "start" and "end", Total the hours per start day.

dsHours =
 ds[
  GroupBy[{#parents &, CurrentDate[#start, "Day"] &}] /* KeySort,
  All,
  Total,
  DateDifference[#start, #end, "Hour"] &
  ]

Mathematica graphics

Then DateListPlot.

DateListPlot[dsHours, Filling -> Axis]

Mathematica graphics

or DateHistogram

DateHistogram[
 dsHours[All, {Keys, Values} /* Apply[WeightedData]], "Day",
 ChartLegends -> Automatic
 ]

Mathematica graphics

Hope this helps.

Improve this answer
$\endgroup$
4
$\begingroup$ 
csv = "task,parents,category,start,end,effort,ishabit,tags
      Parent1,,,2019-07-07 00:00,2019-07-07 00:20,,,
      Child1,Parent1,,2019-07-8 00:02,2019-07-8 00:40,,,
      Child2,Parent1,,2019-07-9 00:02,2019-07-9 06:40,,,
      Parent2,,,2019-07-08 00:00,2019-07-08 00:20,,,
      Child21,Parent2,,2019-07-9 00:02,2019-07-9 00:40,,,
      Child22,Parent2,,2019-07-10 00:02,2019-07-10 06:40,,,
      Parent3,,,2019-07-09 00:00,2019-07-09 00:20,,,
      Child31,Parent3,,2019-07-10 00:02,2019-07-10 00:40,,,
      Child32,Parent3,,2019-07-11 00:02,2019-07-11 06:40,,,";

dt = ImportString[csv, "CSV", "HeaderLines" -> 1] /. {a_, "", b__} :> {a, a, b};

dt2 = Values @ GroupBy[dt, #[[2]] &, 
   Labeled[Interval[DateObject[#, "Minute"] & /@ {#, #2}], #3, Above] & @@@ 
     #[[All, {4, 5, 1}]] &];

tlp = TimelinePlot[dt2, 
  PlotStyle -> Thread[Directive[{Red, Green, Blue}, CapForm["Round"], Thickness[.015]]], 
  AxesOrigin -> Bottom, ImageSize -> 800, AspectRatio -> 1/3]

enter image description here

edges = DirectedEdge @@@ DeleteCases[dt[[All, {2, 1}]], {a_, a_}];

vertices = VertexList[edges];

vcoords = Association @ 
   Cases[tlp[[1]], Text[v_, Offset[o_, vc_], ___] :> v[[1]] -> vc, All];

grph = Show @ Graph[vertices, edges, VertexShapeFunction -> None, 
    EdgeShapeFunction -> ({Arrowheads[{{.02, .8}}], 
        Arrow@GraphElementData[{"CurvedArc", "Curvature" -> -.00001}][##]} &), 
    VertexCoordinates -> (vcoords /@ vertices), AspectRatio -> 1/3];

Show[ tlp, Prolog -> grph[[1]]]

enter image description here

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