I'd like to figure out the elapsed time between flag status changes.

Simplified example: a person can only be sad or happy. I'd like to know how long each mood was active until it changed.

I'm using common storage and a script to loop the records, which is very expensive. I'm looking for a general best practice. I'm open to use any technology in combination or as a replacement. I'm new to data science and probably miss the right terms. Related advices are very appreciated.

Index: user_mood_log

 |    changed_at    |  mood  | user |
 | yyyy-mm-dd 12:00 | sad    | John |
 | yyyy-mm-dd 15:00 | happy  | John |
 | yyyy-mm-dd 18:00 | happy  | John |
 |        ...       |   ...  |  ... |

Index: user_mood_log_daily

 |    date    |  sad  | happy | user |
 | 2019-05-22 |   3   |   21  | John |
 | 2019-05-21 |   5   |   19  | John |
 |    ...     |  ...  |   ... |  ... |


  1. The last record of yesterday (or even older) needs to be considered. Means I can't simply group by day.
  2. A mood might not change for several days.
  3. Duplicated mood logs are possible.

Any advice? Thanks in advance!

  • 1
    $\begingroup$ What is your definition of "common storage"? Exactly how is the data stored; database, text files, formatted text, binary files? $\endgroup$
    – Edmund
    May 25 '19 at 17:15
  • $\begingroup$ @Edmund I kept it "common" to not influence the answerer, as I'm willing to migrate to another storage. Currently I'm using Elasticsearch. $\endgroup$
    – Mr. B.
    May 25 '19 at 18:18
  • $\begingroup$ ElasticSearch is a search engine. What is the data store behind it in your implementation? $\endgroup$
    – Edmund
    May 26 '19 at 11:06
  • $\begingroup$ @Edmund These records are currently only stored in ElasticSearch. $\endgroup$
    – Mr. B.
    May 26 '19 at 15:21
  • $\begingroup$ ElasticSearch serializes data as JSON. $\endgroup$ May 26 '19 at 17:54

Python's pandas package has the capability to answer all those questions, assuming the data can fit into the computer's memory.

Pandas can process timeseries data, find time deltas, and remove duplicates.

  • $\begingroup$ Thank you. Do you have an example by any chance? $\endgroup$
    – Mr. B.
    May 25 '19 at 14:38

You may apply Wolfram Language to your project. There is a free Wolfram Engine for developers that plugs into many IDEs. It also has the Wolfram Client Library for Python to use these functions in Python.

I presume that there is a API to get the data as JSON or CSV or a stream of these or some other format. Depending on the API you can use functions from either the HTTP Requests & Responses guide or the Web Operations guide.

Since no data is provide I'll start by generating timed mood runs. To ensure there are runs I will use a DiscreteMarkovProcess with a transition matrix that slightly favours the current state over moving to another state.

MatrixForm[tm = {{0.6, 0.4}, {0.4, 0.6}}]

Mathematica graphics

Graph can take a DiscreteMarkovProcess so this can be visualised as

mood = DiscreteMarkovProcess[1, tm];

Mathematica graphics

or with a bit of formatting for clarity.

 Flatten@MapIndexed[Labeled[DirectedEdge @@ #2, #1] &, tm, {2}],
 VertexSize -> Medium,
 VertexStyle -> LightGreen,
 VertexLabels -> Thread[Range[2] -> Map[Placed[#, Center] &, {"Happy", "Sad"}]]

Mathematica graphics

RandomFunction will simulate this process and I will convert the integer values to text with ReplaceAll (/.) in order to have the same data types as in your ElasticSearch. DateRange will be use to create the timestamps 2 hours apart as in your example.

n = 20;
data = Transpose@
    With[{start = DateObject@{2019, 1, 1, 0}},
     DateRange[start, DatePlus[start, {2 n, "Hour"}], Quantity[2, "Hour"]]
    RandomFunction[mood, {0, n}]["Values"] /. Thread[Range@2 -> {"Happy", "Sad"}]

This gives the following Happy-Sad sequence viewed below with DateListPlot.

DateListPlot[KeySort@GroupBy[Last]@data /. Thread[{"Happy", "Sad"} -> Range@2],
 Joined -> False]

Mathematica graphics

Now that there is data the actual work can begin. The first 2 items are

data[[;; 2]]

Mathematica graphics

data can be SplitBy the Last value in each item. These runs can be Transposed so there is one row of DateObjects and one of the string values. Apply (@@@)ing MinMax to the dates and First to the values (as they are all the same for each run) gets the start and end points of each run.

runs = {MinMax@#1, First@#2} & @@@ Transpose /@ SplitBy[data, Last]

Mathematica graphics

I have decided that a run begins immediately after its prior run ends and ends on its last data point. Therefore, I need to include the previous run's end point to calculate the run time of the run. By Folding the list in pairs (FoldPairList) the DateDifference in "Hour" between the end of the previous and current can be calculated.

runTimes =
    {DateDifference[##, "Hour"] & @@ MinMax@Rest@Flatten@{##}[[All, 1]], Last@#2},
   } &,

Mathematica graphics

DateDifference returns a Quantity object of the time unit requested.

runTimes[[1, 1]] // InputForm
Quantity[8, "Hours"]

Quantity objects are known throughout the language so from here further processing or analysis can continue. For example BarChart visualisations like

BarChart[Labeled @@@ runTimes, ChartLabels -> Automatic, AxesLabel -> Automatic]

Mathematica graphics

Notice that the abbreviation for hours was Automatically placed as an AxesLabel.

Hope this helps.


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.