How can I best plot timestamps by frequency-to-date?

Question

I have a site called Sudomemo. I record when I first see a user in the database; and I've exported it in JSON format, located here.

http://www.sudomemo.net/statistics/firstSeenDump.php

I've done this:

dates = Import["http://www.sudomemo.net/statistics/firstSeenDump.php", "JSON"]

I want to plot, since February, how the user-join rate increases/decreases, over the course of the year.

Thinking about it mathematically: Since the derivative of a function shows the slope of a function, and the second derivative shows the concavity/how quickly the slope is changing, I should graph the second derivative of the timestamps over the year. I think I have the right idea... I'm still learning about derivatives in Calculus :P

The only issue is, I have no idea how to go about doing that!

I'm pretty new to Mathematica, and thought this would make a fantastic chart. Not only that, but I've installed Mathematica 10 on my server, and I'd love to be able to present cool graphs :D some interesting statistics to interested users.

Right now, I've simply used the json_encode() PHP function to produce the timestamps like this:

["2014-01-29 16:49:00","2014-01-29 16:49:00","2014-01-29 16:49:00"...

I can change the array format to something different if that'd be better suited for this.

Would anyone be able to point me in the right direction?

Note: The first hundred values or so are all 2014-01-29 16:49:00 because that's when I first started recording the "first seen" timestamps.

http://www.sudomemo.net/statistics/firstSeenDump.php?nospike doesn't show the ones from January 29th 16:49:00.

Answer 1

You're about to realize that derivatives in the real world are a pain. You have to aggregate data as much as possible and average it a lot until you get a "textbook-quality second-derivative!

dates = DateList /@ Import["http://www.sudomemo.net/statistics/firstSeenDump.php", "JSON"]

Grouping by days and counting:

datesDays = Tally@dates[[All, ;; 3]]; 
datesDays // DateListPlot

Very noisy, as every real world data... and when we differentiate noise we make it stronger.

MapThread[{#1[[1]], (#2[[2]] - #1[[2]])/DayCount[#1[[1]], #2[[1]]]} &
    , {Most@#, Drop[#, 1]}] &@dayCounts // DateListPlot

We'd get a similar plot if we differentiated it once more.

Some MovingAverage filtering

datesWeeks = Thread[{datesDays[[;; -7, 1]], MovingAverage[datesDays[[All, 2]], 7]}];
datesMonths = Thread[{datesDays[[;; -30, 1]], MovingAverage[datesDays[[All, 2]], 30]}];
GraphicsGrid@{DateListPlot /@ {%%, %}}

But even the well-behaved 30-day-average data becomes too noisy under a single differentiation:

MapThread[{#1[[1]], (#2[[2]] - #1[[2]])/DayCount[#1[[1]], #2[[1]]]} & , {Most@#, Drop[#, 1]}] &@datesMonths // DateListPlot[#, PlotRange -> All] &

You could average the result before taking another derivative, but remember that every averaging step adds more lag to your system... Old data is just as actionable as noisy data!

Just for kicks, second derivative, with and without averaging

Using the same "methodology" as before:

diffMonthAvg = Thread[{diffMonth[[;; -30, 1]], MovingAverage[diffMonth[[All, 2]], 30]}];

MapThread[{#1[[1]], (#2[[2]] - #1[[2]])/DayCount[#1[[1]], #2[[1]]]} &, {Most@#, Drop[#, 1]}] &@diffMonth;
MapThread[{#1[[1]], (#2[[2]] - #1[[2]])/DayCount[#1[[1]], #2[[1]]]} &, {Most@#, Drop[#, 1]}] &@diffMonthAvg;
GraphicsGrid@{DateListPlot[#, PlotRange -> {All, {-3, 3}}] & /@ {%%, %}}

You can also apply the moving average to the second derivative, but it's late and we all know that squinting hard achieves the same result!

Answer 2

You have the import.

dates = Import["http://www.sudomemo.net/statistics/firstSeenDump.php", "JSON"]

Gather them up by day and count how many in each day.

dailyGather = GatherBy[dates, Take[DateList[#], 3] &];
dailyVisits = {Take[DateList[dailyGather[[#, 1]]], 3],Length[dailyGather[[#]]]} & 
 /@ Range[First@Dimensions[dailyGather]];

Plot the visits.

DateListPlot[dailyVisits, PlotRange -> All]

Calc and plot the pace of visits.

paceOfVisits = MovingMap[#[[2]] - #[[1]] &, dailyVisits, 2];
DateListPlot[paceOfVisits, PlotRange -> All]

Calc and plot the change in pace of visits.

changeOfPaceOfVisits = MovingMap[#[[2]] - #[[1]] &, paceOfVisits, 2];
DateListPlot[changeOfPaceOfVisits, PlotRange -> All]

Look at smoothing the change in pace with average.

Manipulate[
 DateListPlot[MovingAverage[changeOfPaceOfVisits, averageN], 
  PlotRange -> {Automatic, {-100, 100}}], {averageN, 3, 25, 1}]

Update

You can try more smoothers with MovingMap instead of MovingAverage in the Manipulate.

Manipulate[
 DateListPlot[MovingMap[smoother, changeOfPaceOfVisits, averageN], 
  PlotRange -> {Automatic, {-100, 100}}], 
 {smoother, {Mean, Median, Quantile[#, .8] & -> "80% Quantile", 
   TrimmedMean[#, .1] & -> "10% Trimmed Mean"}, SetterBar}, 
 {averageN, 3, 30, 1}]