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 looking for a general best practice . I'm open to change the way I create my logs. I'm new to data science and probably miss the right terms. Related advices are very appreciated.
| 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 | | ... | ... | ... |
- The last record of yesterday (or even older) needs to be considered. Means I can't simply group by day.
- A mood might not change for several days.
- Duplicated mood logs are possible.
- I could want to change the grouping by hours or months or others on the fly
- It could have more than 2 moods
- Number of logs are somewhat big
Any advice? Thanks in advance!