How to plot sparse timeseries data (preferably using Google Sheets)?

Question

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).

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?

Answer 1

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.

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