1
$\begingroup$

I have a table like the following, showing times my DSL modem lost connection to the ISP and how long until connection was restored (full data, if you're very bored):

Date Duration
01/10/2020 01:08:17 7
16/10/2020 02:21:00 2
31/10/2020 20:15:40 6

I'd like to have a quick way to plot this kind of data; today, I paste it to Google sheets, create a line chart, and it correctly puts points at the right distance from one another (i.e., 1 hour apart vs 1 day apart, on the X-axis).

Line chart of timeseries

This is pretty good and solves my immediate problem - I can see that there are more events lately. But I know this is the "wrong" way to do it; a line chart assumes the points are connected, but really they're just discrete events. What's the "right" way to do this in Google Sheets?

$\endgroup$
2
  • $\begingroup$ Are your durations seconds, minutes, hours, or fortnights? I hope you are fine with answers in R, because how to create a specific plot in a particular piece of software is off-topic here. $\endgroup$ – Stephan Kolassa Dec 9 '20 at 11:10
  • $\begingroup$ Hey, sorry for the slow reply. Durations in minutes. I'm fine with an answer in R, though if it's possible in Sheets I'd love to know. I'm new here, so may not understand what's off/on topic well - why did you emphasise specific plot? Isn't "plot timeseries of events with a metric" generic enough that asking how to plot it in Sheets is on-topic? Either way - thanks! $\endgroup$ – Yaniv Aknin Jan 10 at 23:03
1
$\begingroup$

Your plot is already useful. A different visualization would be a kind of a seasonplot (see here for more inspiration) showing the durations of outages against the hour of day.

outages

In such a plot, we can see a few days where outages bunched together (as in your plot), and in addition, we also see when in the day they occur: those at the beginning of November happened more in the early hours of the day, those later in November and in December happened either between 08:00 and 10:00, or between 17:00 and 23:00.

If you suspect weekly patterns (e.g., more/fewer outages on the weekend), you could put the 168 hours of a full week on the horizontal axis. (I suspect that the rectangles would look like very thin vertical bars then, and be hard to see - maybe impose some minimum thickness.)

R code below. I'm sorry, but how to draw a plot like this in a specific tool would be off-topic here, but this may already give you some inspiration, or you could ask elsewhere how to plot something like this in your favorite tool.

(dataset <- structure(list(Date = structure(c(1601507297, 1602807660, 1604171740, 
1604171957, 1604280564, 1604541931, 1604708318, 1605263031, 1605491728, 
1605574425, 1606410582, 1606583349, 1606603763, 1606718699, 1607287654, 
1607331721, 1607380229, 1607450045, 1607498543), class = c("POSIXct", 
"POSIXt"), tzone = ""), Duration = c(7, 2, 6, 5, 5, 28, 4, 4, 
9, 3, 17, 2, 11, 2, 3, 3, 4, 2, 5)), class = "data.frame", row.names = c(NA, 
-19L)))

x_starts <- as.POSIXlt(dataset$Date)$hour+as.POSIXlt(dataset$Date)$min/60+
    as.POSIXlt(dataset$Date)$sec/3600
x_ends <- x_starts+dataset$Duration/60
yy <- max(as.Date(dataset$Date))-as.Date(dataset$Date)

opar <- par(mai=c(.5,1.3,.1,.1))
    plot(c(0,24),c(0,diff(range(as.Date(dataset$Date)))),type="n",xlab="",ylab="",yaxt="n")
	# plot *all* dates, even if they overlap
	for ( ii in 1:nrow(dataset) ) {
        axis(2,at=max(as.Date(dataset$Date))-as.Date(dataset$Date[ii]),
            label=format(dataset$Date[ii],format="%Y-%m-%d"),las=1)
    }
    rect(xleft=x_starts,ybottom=yy-0.5,xright=x_ends,ytop=yy+0.5,col="black")
par(opar)

R code

$\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.