8
$\begingroup$

After struggling for a few weeks, I have accumulated a few examples of challenging date-related operatations with datasets. Some I have partial solutions; others I cannot yet do. The resources on Datetime objects in datasets is thin on here (both are relatively new to Mathematica, so no surprise), so maybe this will fill in that gap.

For all of my data, we have events that occur at a datetime. We want to understand how the progress on given days compare; for example, when did task A occur on April 4, 2017 vs October 12, 2018? What is the distribution for Task A occurring throughout a day over 100 days?

  1. How can I plot a DateHistogram with a bin of, say, 20 minutes? I can do it by hour: DateHistogram[dataset, "Hour", DateReduction->"Day"]

  2. How can I utilize the DateHistogram plot style using the operator form? A relatively new functionality allows the syntax dataset[plotstyle,"Key"] (see this nice StackExchange thread). For example, I can implement:

    dataset[Groupby[Key["State"]] /* (PieChart[#, ChartLabels->Keys[#]]&), Length]

    I want to use this syntax with DateHistogram; I can implement the most basic version:

    dataset[DateHistogram,"Sent"]

    I want to add the DateReduction option, along the lines of:

    dataset[DateHistogram["Hour",DateReduction->"Day"],"Sent"]

    Unfortunately, the above example doesn't work and I can't find more documentation.

  3. How can I select objects in a certain time window? I figured out a method, but perhaps there is a more elegant solution. In the below example, I can plot the events that occur before 2016:

    eventsBefore2016 = dataset[Select[#Sent<DateObject[{2016,1,1}]&,"Sent"]; DateHistogram[eventsBefore2016,"Hour",DateReduction->"Day"]

    Can I make these two lines into one line of code, for example? Could I do it using the operate form (question 2)?

  4. How can I adapt a DistributionChart for a dataset of Datetime values? I don't example starter code for this one.

(Apologies for the generic code examples below; I can't share the actual data and my workplace blocks the tutorial datasets such as titanic. If this is a real sticking issue I can adapt this question with a tutorial dataset later at home.)

$\endgroup$
8
$\begingroup$

Q1:

You can use Quantity[20, "Minutes"] as the bin specification:

maildata = Databin["4UYrgYkd"];
DateHistogram[Values[maildata], Quantity[20, "Minutes"], DateReduction->"Day"]

enter image description here

Q2:

dataset[DateHistogram[#, "Hour", DateReduction->"Day"]&, "Sent"]

Q3:

Three possible ways:

dataset[DateHistogram[#, "Hour", DateReduction -> "Day"]& @*
 Select[# < DateObject[{2016, 1, 1}]&], "Sent"] 

dataset[Select[#Sent < DateObject[{2016, 1, 1}]&] /* 
  (DateHistogram[#,"Hour", DateReduction->"Day"]&), "Sent"]

dataset[DateHistogram[Select[# < DateObject[{2016, 1, 1}]&],
  "Hour", DateReduction -> "Day"]& , "Sent"] 

Q4:

Processing maildata to get time-stamps grouped by day:

groupedByDay = KeySortBy[System`DateObjectDump`$dowAssociation]@
   GroupBy[Values @ maildata, {DateValue[#, "DayName"] &}, 
    AbsoluteTime@DateList[{2019, 1, 1, ## & @@ #[[4 ;;]]}] & /@ # &];

Note: if your dates are DateObjects, change #[[4 ;;]] to #[[1, 4 ;;]].

We can use groupedByDay with DateHistogram, DistributionChart or BoxWhiskerChart:

DateHistogram[groupedByDay, 
    ChartLayout->"Stacked", ChartStyle->3,
    ChartLegends->Keys[groupedByDay]]

enter image description here

For DistributionChart and BoxWhiskerChart we need to compute the date ticks for the vertical axis:

ticks = System`DateListPlotDump`DateTicks[{{2019, 1, 1, 0, 0, 
     0.}, {2019, 1, 1, 23, 59, 0.}}, 10, {"Hour", ":", "Minute"}];

BoxWhiskerChart[groupedByDay, 
  ChartLabels -> Keys[groupedByDay], 
  FrameTicks->{{ticks, Automatic}, {Automatic, Automatic}}]

enter image description here

If you have to use DistributionChart:

DistributionChart[groupedByDay, 
 ChartLabels -> Keys[groupedByDay], 
 FrameTicks -> {{ticks, Automatic}, {Automatic, Automatic}}, 
 ChartElementFunction -> ChartElementDataFunction["SmoothDensity", 
     "ColorScheme" -> "DeepSeaColors"]]

enter image description here

Use

ChartElementFunction -> ChartElementDataFunction["GlassQuantile",
   "Quantile" -> Range[0,90,10],
   "QuantileStyle" -> (Directive[Thick, Hue[#/100]] & /@ Range[0,90,10]),
   "QuantileShading" -> True]

to get

enter image description here

Credit: The function System`DateObjectDump`$dowAssociation is from this answer by halirutan.

System`DateObjectDump`$dowAssociation

<|Monday -> 1, Tuesday -> 2, Wednesday -> 3, Thursday -> 4, Friday -> 5, Saturday -> 6, Sunday -> 7|>

Update: Working with Dataset input:

We first construct a data set using the timestamps in maildata.

maildata = Databin["4UYrgYkd"];
timestamps = Values[maildata] /. a : {_, __} /; Length[a] == 6 :> DateObject[a];

SeedRandom[1]
from = RandomChoice[{"A", "B", "C", "D"}, Length @ timestamps];
to = RandomChoice[{"A", "B", "C", "D"}, Length @ timestamps];
subject = StringRiffle /@ Partition[RandomWord["Noun", 2 Length @ timestamps], 2];
ds = Dataset[AssociationThread[{"from", "to", "subject", "sent"} -> #] & /@
   Thread[{from, to, subject, timestamps}]]

enter image description here

"abuse randomness"? There is something suspiciously non-random about the subject lines:)

Create a new data set showing DateHistograms in hourly bins of daily message traffic grouped by senders and recipients:

ds2 = Transpose @ ds[GroupBy["from"], GroupBy["to"], 
  Select[#sent < DateObject[{2010, 12, 15, 1, 0, 0}]&] /* 
   (DateHistogram[#, "Hour", DateReduction -> "Day"]&), "sent"];
Labeled[ds2, {"from", "to"}, {Left, Top}, LabelStyle -> 16, RotateLabel -> True]

enter image description here

Pull the date histogram of messages from "A" to "C":

ds2["A", "C"]

enter image description here

DistributionChart of message timestamps grouped by sender:

grouped = ds[GroupBy["from"], All, "sent"];
DistributionChart[Map[AbsoluteTime] /@ grouped, ChartLabels -> Automatic]

enter image description here

Group timestamps by hour and use BoxWhiskerChart to show the distributions of timestamps within in each hour:

grouped2 = ds[GroupBy[DateValue[#sent, "Hour"]&], All, "sent"];
BoxWhiskerChart[Map[Dot[{60, 1}, #] & /@ DateValue[#, {"Minute", "Second"}]&] @ 
  KeySort[grouped2] , ChartLabels -> Automatic, ImageSize -> Large]

enter image description here

$\endgroup$
  • $\begingroup$ This is a very nice answer! I have a couple things I'm having trouble implementing--one in Q3 and one in Q4. For Q3, there appears to be a ] missing. I think it should be after the &. When I add it here, it has a failure: "(#Sent<DateObject[{2016,1,1}]&)[DateObject[{2015,1,5,6,54,31.7},Instant,Gregorian,-6]] is expected to have an Association as the first argument." I'm not sure if I've misplaced the bracket or if something deeper is going on. I'm not familiar with the syntax. I tried to search @* but it doesn't show in the documentation--what is this operator? $\endgroup$ – KBL Jul 23 at 20:49
  • $\begingroup$ For Q4, I'm pretty lost on the syntax of your groupedByDay code. What would this grouping line look like for something like the age distribution by gender in the titanic dataset? Could you explain the code after AbsoluteTime? For my own data, different working groups perform tasks and we'd like to see how they fall throughout the day; we would like to see what a Group A day looks like versus a Group B day. I would expect it to be a much simpler grouping than the System`DateObjectDump; I can't test your example or examine this structure due to my Cloud access restrictions. $\endgroup$ – KBL Jul 23 at 21:00
  • 1
    $\begingroup$ @KBL, @* is infix for Composition. Re the error message, does eventsBefore2016 = dataset[Select[#Sent<DateObject[{2016,1,1}]&,"Sent"]; work for your dataset? I will see if can the titanic dataset has date column that i can use. The part of the code after AbsoluteTime takes {"Hour" , "Minute", "Second"} part of a date list prepends an arbitrary {"Year", "Month" , "Day"} ({2019,1,1} could be anything) to it so that input dates are grouped by {"Hour" , "Minute", "Second"}. $\endgroup$ – kglr Jul 23 at 23:33
  • 1
    $\begingroup$ @KBl, try if dataset[DateHistogram[#, "Hour", DateReduction -> "Day"]& @* Select[# < DateObject[{2016, 1, 1}]&], "Sent"] works. $\endgroup$ – kglr Jul 24 at 5:44
  • 1
    $\begingroup$ @KBL, change #[[4 ;;]] to #[[1,4 ;;]] in the last line of groupByDay . System`DateObjectDump`$dowAssociation is just <|Monday -> 1, Tuesday -> 2, Wednesday -> 3, Thursday -> 4, Friday -> 5, Saturday -> 6, Sunday -> 7|> $\endgroup$ – kglr Jul 25 at 6:33

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.