Time-series box plot using precomputed quantiles with R and Plotly

Question

I'm trying to create a boxplot visualization with R and plotly. Data is broken up into dates and factor levels and I have precomputed quantiles (at database side). Can't get plotly to group boxes by date...

My data table:

df <- structure(list(ts_bucket = structure(c(18691, 18682, 18679, 18677, 
18683, 18678, 18684, 18676, 18679, 18685, 18676, 18687, 18680, 
18678, 18691, 18684, 18695, 18686, 18693, 18690, 18692, 18680, 
18688, 18678, 18696, 18689, 18690, 18682, 18681, 18677, 18684, 
18696, 18683, 18697, 18683, 18681, 18679), class = "Date"),
direction = structure(c(4L, 1L, 3L, 3L, 3L, 1L, 2L, 1L, 1L, 3L, 3L, 3L,
3L, 3L, 1L, 1L, 1L, 3L, 3L, 4L, 3L, 1L, 3L, 2L, 1L, 3L, 3L, 3L, 3L, 1L,
3L, 3L, 1L, 3L, 2L, 1L, 2L), .Label = c("A", "B", "C", "D"),
class = "factor"), speed_qq_1 = c(35074856.0636453, 
5572739.29896457, 4949.00019700353, 2262.422961163, 317.185405380422, 
6116316.78164879, 914964.957132739, 5617337.44924684, 6012746.72963657, 
3752.59623536317, 2550.83882626045, 1107.30985421494, 2718.78215993215, 
2426.13471489644, 898455.086811819, 6625204.17452419, 22009846.3426728, 
3739.93928208145, 1474.84362560781, 7614530.8618137, 2068.59353268107, 
6586459.72219984, 4016.99754808994, 49010644.4142805, 28838766.4627051, 
6457.24588756896, 3874.83562381789, 9442.29259179366, 3136.43192102517, 
7029374.92889377, 1171.48958255544, 3239050.77836986, 106028751.783377, 
3194.72457876061, 286228.647413479, 5633624.43107491, 3788537.99278917
), speed_qq_2 = c(58434969.0499432, 6713592.05736426, 4949.00019700353, 
2789.43719885885, 1304.70364995432, 8787065.45925714, 992147.434353278, 
7160600.64834872, 7475335.25000602, 3752.59623536317, 2550.83882626045, 
1107.30985421494, 2811.54774485398, 2426.13471489644, 1776187.58467355, 
8242706.99526232, 28704801.9080504, 3739.93928208145, 1818.68800075085, 
8970781.77071988, 2068.59353268107, 7795940.06231112, 4016.99754808994, 
193429385.816011, 28963868.6771179, 6484.31088201772, 3874.83562381789, 
9442.29259179366, 3136.43192102517, 8560767.52821054, 1171.48958255544, 
3239050.77836986, 328163508.85003, 3194.72457876061, 579964.638411297, 
7034203.54172317, 3788537.99278917), speed_qq_3 = c(86758378.6063609, 
8187218.39535632, 4949.00019700353, 3448.20499597865, 2539.1014556717, 
11495728.215223, 1088625.53087895, 8677882.82094334, 9616012.88811428, 
3752.59623536317, 2550.83882626045, 1107.30985421494, 2927.50472600625, 
2426.13471489644, 58692374.1220261, 10146634.6481728, 70114644.955393, 
3739.93928208145, 2248.49346967966, 43539894.3129724, 2068.59353268107, 
8670615.8477866, 4016.99754808994, 237867862.201636, 29120246.445134, 
6518.14212507867, 3874.83562381789, 9442.29259179366, 3136.43192102517, 
10990607.1857129, 1171.48958255544, 3239050.77836986, 605831955.183345, 
3194.72457876061, 622314.783595397, 8911298.7354117, 3788537.99278917
), speed_qq_4 = c(1840213607817.27, 154247676.895601, 4949.00019700353, 
4106.97279309845, 3773.49926138908, 133763151.122471, 1185103.62740462, 
124108789.254772, 14064468.5221641, 3752.59623536317, 2550.83882626045, 
1107.30985421494, 3043.46170715853, 2426.13471489644, 144875560.171982, 
252399515.548593, 259399228.193974, 3739.93928208145, 2678.29893860847, 
85908924.8915396, 2068.59353268107, 113366716.236699, 4016.99754808994, 
255274097.819393, 29276624.21315, 6551.97336813962, 3874.83562381789, 
9442.29259179366, 3136.43192102517, 135119259.337972, 1171.48958255544, 
3239050.77836986, 883500401.516661, 3194.72457876061, 40841373.9051296, 
12902925.3084376, 3788537.99278917), speed_qq_5 = c(6256473430400.1, 
256984303.854871, 4949.00019700353, 4633.98703079429, 4761.01750596298, 
152861381.467807, 1262286.10462516, 339709224.091257, 166764431.381778, 
3752.59623536317, 2550.83882626045, 1107.30985421494, 3136.22729208035, 
2426.13471489644, 215994091.500092, 830854506.163353, 662007904.257696, 
3739.93928208145, 3022.14331375151, 105984979.087601, 2068.59353268107, 
171347751.636349, 4016.99754808994, 331299921.427781, 29401726.4275629, 
6579.03836258838, 3874.83562381789, 9442.29259179366, 3136.43192102517, 
151149592.214996, 1171.48958255544, 3239050.77836986, 1105635158.58331, 
3194.72457876061, 63620185.6380561, 168788174.040959, 3788537.99278917
)), row.names = c(NA, -37L), class = c("data.table", "data.frame"))

This is what I've tried so far:

 plot_ly(data = df,
          x = ~ts_bucket,
          name = ~ direction) %>%
    add_boxplot(
          lowerfence = ~ speed_qq_1,
          q1 = ~ speed_qq_2,
          median = ~ speed_qq_3,
          q3 = ~ speed_qq_4,
          upperfence = ~ speed_qq_5) %>%
    layout(
      yaxis = list(exponentformat = "SI",
                   title = "Transfer speed, B/s"),
      xaxis = list(title = "Date")
    )

Not quite what I want, orientation is wrong and boxes are not drawn by date...

Expected output should look like this (I know how to do this with ggplot! but plotly - no clue...):

I've got the desired result with ggplot:

ggplot(data=df,
  aes(
    x=ts_bucket,
    group=interaction(ts_bucket, direction),
    fill=direction,
    ymin=speed_qq_1,
    lower=speed_qq_2,
    middle=speed_qq_3,
    upper=speed_qq_4,
    ymax=speed_qq_5)) +
  geom_boxplot(stat="identity")

But applying ggplotly on it - I get no traces at all...

Answer 1

If you set x to be a factor as in x=~factor(ts_bucket), you should get the desired result. Note that I've set the y axis scale to log, to avoid direction="D" from dominating visually.

plot_ly(
data = df,
x = ~factor(ts_bucket),
color = ~ direction,
type="box",
lowerfence = ~ speed_qq_1,
q1 = ~ speed_qq_2,
median = ~ speed_qq_3,
q3 = ~ speed_qq_4,
upperfence = ~ speed_qq_5) %>%
layout(
    yaxis = list(exponentformat = "SI",type="log",title = "Transfer speed, B/s"),
    xaxis = list(title = "Date"),
    boxmode = "group")