Search code examples
rdatetimetidyversefacet

Unable to filter and plots data from the last six days in R with tidyverse

I have a raspberrypi that tests my internet speed and generates a table which is imported as a data frame df. Here is a snippet of the data frame. This snippet contains data for 9 days:

df <- data.frame(
  Date = rep(
    c(
      "2023/11/05", "2023/11/06", "2023/11/07", "2023/11/08", "2023/11/09",
      "2023/11/10", "2023/11/11", "2023/11/12"
    ),
    c(23L, 35L, 27L, 37L, 35L, 43L, 37L, 28L)
  ),
  Time = c(
    "11:20:29", "11:40:29", "12:15:02", "12:20:29", "12:40:30", "14:00:05",
    "14:20:28", "14:40:28", "15:00:05", "15:20:29", "15:40:29", "17:20:28",
    "17:40:30", "18:00:07", "18:20:34", "18:40:28", "19:00:10", "20:40:28",
    "21:00:06", "21:20:28", "21:40:33", "22:00:11", "22:20:33", "00:00:11",
    "00:20:30", "03:20:33", "03:40:35", "04:00:05", "04:20:35", "04:40:37",
    "05:00:10", "05:20:38", "05:40:39", "06:00:05", "06:20:35", "06:40:28",
    "07:00:05", "16:40:34", "17:00:05", "17:20:28", "17:40:28", "18:00:05",
    "18:20:47", "18:40:30", "19:00:05", "19:20:28", "19:40:28", "20:00:05",
    "20:20:30", "20:40:29", "21:00:05", "21:20:29", "21:40:29", "22:00:05",
    "22:20:43", "22:40:27", "23:00:10", "23:20:31", "07:40:32", "08:00:05",
    "08:20:33", "08:40:29", "09:00:06", "09:20:28", "09:40:28", "10:00:06",
    "10:21:01", "10:40:29", "11:00:05", "11:20:31", "11:40:28", "17:40:35",
    "18:00:06", "18:20:50", "18:40:33", "19:00:06", "19:20:29", "19:40:29",
    "20:00:05", "20:20:34", "20:40:28", "21:00:06", "21:20:29", "21:40:35",
    "22:00:06", "02:00:53", "02:20:28", "02:40:28", "03:00:06", "03:20:41",
    "03:40:32", "04:00:06", "04:20:31", "04:40:28", "05:00:06", "05:20:32",
    "08:40:30", "09:00:05", "09:20:31", "09:40:41", "10:00:06", "10:20:58",
    "12:00:11", "12:21:14", "12:40:42", "13:00:06", "13:20:38", "13:40:40",
    "15:20:41", "15:40:29", "16:00:06", "16:20:28", "16:40:37", "17:00:05",
    "18:40:31", "19:00:06", "22:00:05", "22:20:28", "22:40:28", "23:00:06",
    "23:20:32", "23:40:28", "00:00:07", "00:20:28", "00:40:28", "01:00:06",
    "01:20:28", "01:40:30", "11:20:36", "11:40:39", "12:00:10", "12:20:28",
    "12:40:33", "13:00:06", "13:20:29", "13:40:36", "14:00:06", "14:20:47",
    "14:40:31", "15:00:06", "15:20:34", "15:40:32", "16:00:05", "16:20:32",
    "16:40:32", "17:00:05", "17:20:28", "17:40:29", "18:00:06", "21:20:42",
    "21:40:29", "22:00:06", "22:20:50", "22:40:34", "23:00:06", "23:20:34",
    "23:40:28", "00:00:07", "00:20:29", "00:40:31", "01:00:55", "01:20:28",
    "01:40:40", "02:00:06", "02:20:29", "07:20:31", "07:40:34", "08:00:06",
    "08:20:28", "08:40:30", "09:00:06", "09:20:37", "09:40:28", "10:00:06",
    "10:20:29", "10:40:29", "11:00:55", "14:00:05", "14:20:38", "14:40:28",
    "15:00:05", "15:20:30", "15:40:28", "16:00:11", "16:20:32", "16:40:32",
    "17:00:05", "17:20:34", "17:40:32", "18:00:10", "18:20:28", "18:40:42",
    "21:20:29", "21:40:33", "22:00:14", "22:20:29", "22:40:28", "23:00:12",
    "23:20:29", "23:40:28", "00:00:07", "00:20:28", "00:40:43", "01:00:06",
    "01:20:34", "01:40:32", "02:00:05", "06:40:29", "07:00:05", "07:20:34",
    "07:40:29", "08:00:06", "08:20:29", "08:40:28", "09:00:05", "09:20:28",
    "09:40:39", "10:00:05", "10:20:34", "10:40:45", "11:00:05", "11:20:29",
    "11:40:28", "16:40:38", "17:00:06", "17:20:31", "17:40:28", "18:00:10",
    "18:20:37", "18:40:29", "19:00:06", "19:20:28", "19:40:29", "20:00:05",
    "20:20:29", "23:20:28", "23:40:32", "00:00:07", "00:20:34", "00:40:36",
    "01:00:06", "01:20:38", "01:40:28", "02:00:05", "02:20:29", "02:40:32",
    "03:00:05", "03:20:28", "03:40:28", "04:00:06", "06:40:31", "07:00:05",
    "07:20:35", "07:40:31", "08:00:05", "08:20:28", "08:40:28", "09:00:06",
    "09:20:29", "09:40:31", "10:00:06", "10:20:28", "10:40:29", "11:00:06",
    "11:20:28"
  ),
  Download = c(
    32356880.4919748, 34844147.1619465, 25854402.0044366, 34685894.4604624,
    33880009.8095178, NA, 28515617.287205, 24165718.0884807, NA, 19189661.0520715,
    33503774.2230896, 33406683.9398853, 26882502.6926029, NA, 32949400.0700374,
    35382889.6266268, NA, 26281843.8983273, NA, 28278922.606605, 32988505.6743543,
    NA, 32453300.7681058, NA, 20831588.7660831, 23864911.722864, 34514975.2087827,
    NA, 33616291.5719487, 35034481.6618909, NA, 32635268.6349418,
    33481078.0543706, NA, 34041301.937608, 29215041.742742, NA, 33488738.7128042,
    NA, 33510549.3432005, 33728930.4061252, NA, 1713097.41347917,
    9724313.48658741, NA, 34846013.3603166, 32180494.8193966, NA,
    33997816.8080293, 34745137.9449657, NA, 34446734.2024725, 20398123.275499, NA,
    17741220.2203676, 33886858.3035566, NA, 22570348.0562903, 28418707.3972164,
    NA, 26129117.3632178, 31800054.1200399, NA, 22634437.3927486,
    30881879.6404154, NA, 20386064.1090521, 25838235.4907137, NA,
    32270299.9562004, 31711865.0538831, 34118780.3587555, NA, 1654677.50565932,
    21491385.0805615, NA, 32692616.8982046, 31334467.2139728, NA,
    34102250.2816168, 34501404.6215278, NA, 18725194.8179493, 23332550.7052299,
    NA, 24674675.3844847, 33976568.1732382, 34572619.2923998, NA,
    26874566.8806066, 22868468.6898417, NA, 22435560.3406494, 29699316.5063209,
    NA, 22775963.1688915, 31905653.178521, NA, 31844485.913689, 8784693.90718776,
    NA, 33369268.1415165, NA, 33891404.4079249, 26123643.7238337, NA,
    30568024.190227, 27081959.588544, 20119425.5023407, 31026484.3423925, NA,
    29615758.9187347, 31029119.3994925, NA, 19889012.2808943, NA, NA,
    35934783.257505, 28947470.2047798, NA, 33570034.0931698, 34411975.6957302, NA,
    34023966.2475504, 27933431.8112887, NA, 26702022.7277994, 25572113.8265586,
    17006779.6824735, 28152159.4507148, NA, 33105623.4450435, 33504895.1276328,
    NA, 33757583.1477329, 25316104.7954374, NA, 34232725.0727732,
    30334118.5201595, NA, 32140687.5877763, 34158261.5662352, NA,
    15528184.8040746, 21559019.9133585, NA, 35749838.9238888, 25907648.4753666,
    NA, 17212687.7205647, 32805808.6298565, NA, 23560517.1362528,
    25582977.3548257, NA, 17474771.7922046, 29180072.4674036, NA,
    29669058.3987633, 25110102.4269606, 23411861.2520635, 30994652.9217347,
    24016185.5761036, NA, 34085528.2342506, 23497232.1929135, 23106812.8660693,
    NA, 35518757.9912117, 29486582.3195707, NA, 34136217.0095687,
    35049561.9230451, NA, 31476821.841245, 34564531.0295454, 30557185.5131296, NA,
    22156595.8710915, 35088357.8530381, NA, 34701814.8390195, 32053510.6383616,
    NA, 22399302.8148244, 34694920.6905685, NA, 32785033.2216692,
    33212487.7313374, NA, 28371218.587676, 20986699.2100242, 32018065.2988271,
    19702912.1685943, NA, 33204208.100021, 34317343.8227017, NA, 33114943.226394,
    15679683.9814008, NA, 23656485.1221866, 32848211.90291, NA, 18117334.621262,
    25581876.9408963, NA, 33359668.9919326, NA, 29784924.2491525,
    27044245.1313237, NA, 28691722.8983229, 33704266.0132147, NA,
    35155059.3502496, 16235763.0723252, NA, 33984778.6559746, 23432004.8471781,
    NA, 26815681.9974301, 28757808.0320259, 28081069.7426119, NA,
    25161817.1232983, 27577953.343721, NA, 23154469.0115521, 34061346.8001827, NA,
    26316209.2678151, 29360447.6353315, NA, 34027977.2646326, 26949895.33491,
    27591214.7475204, NA, 27604357.33268, 34563897.3864329, NA, 35726715.2432283,
    35306336.5979909, NA, 27598767.5636218, 20854802.415333, NA, 30467995.1808738,
    28930655.0495666, NA, 30189572.1778762, NA, 20451997.6555555,
    30646535.6729347, NA, 34717157.9023558, 33805025.3019924, NA,
    31947751.1338843, 33971994.3349009, NA, 33817041.6884924, 30431265.3305236,
    NA, 32608968.1607516
  ),
  Upload = c(
    22584835.4652142, 35391867.0430053, 34974007.2324695, 36934171.7777631,
    28675944.1396248, NA, 14826978.8769683, 24097465.9909863, NA,
    19574222.6730045, 28684709.621158, 29134826.519879, 28934188.6258622, NA,
    29719623.8610261, 6875354.04133675, NA, 26938260.2592279, NA,
    18901935.0502355, 12903899.238181, NA, 21608743.7018642, NA, 23054669.6240831,
    27292506.0350409, 32020277.1235767, NA, 32512858.6846428, 31522433.4113873,
    NA, 32766983.6205763, 29658288.881593, NA, 32660886.3868791, 28529099.8834329,
    NA, 6348624.14440707, NA, 29122314.1619112, 22717417.9781938, NA,
    5719914.93573861, 10556746.9486976, NA, 27325786.1022904, 13643830.2013121,
    NA, 30241106.4007472, 20579249.3181621, NA, 32231317.2455832,
    6090022.91626732, NA, 14087168.2908845, 26993008.0944394, NA,
    17483261.4744783, 28768062.5772198, NA, 13832336.8361317, 29855691.7842512,
    NA, 17611764.6959108, 24025202.1980015, NA, 13813060.28282, 28356776.0825849,
    NA, 18282507.7828267, 14149831.2638651, 34728470.8284993, NA, 2039077.9667497,
    8110086.06298634, NA, 8203610.83929742, 12039152.0513209, NA,
    29652555.0384662, 34425770.4673345, NA, 9160496.55051109, 22623830.2768646,
    NA, 16222849.920783, 28406888.554154, 32989689.1801105, NA, 17573265.1945185,
    12625820.0401113, NA, 24418082.6323506, 28816056.5218465, NA,
    24093494.0942859, 8692085.25591475, NA, 26324043.4268891, 15002746.0881793,
    NA, 16123755.9000061, NA, 35851563.6434097, 24636532.8728458, NA,
    22372720.6881946, 29941586.4250691, 14354225.3407577, 31780336.1399207, NA,
    19729115.6675094, 20336007.9024978, NA, 10338474.437649, NA, NA,
    27740839.3034115, 28907845.5763616, NA, 14889670.341333, 15544392.5274731, NA,
    27863548.6942018, 28422203.1896762, NA, 25205951.255052, 26433935.983247,
    15814374.2958366, 28698585.8402174, NA, 20705870.4917653, 27558302.6081748,
    NA, 6634857.46415233, 16402582.6448983, NA, 10472445.8988733,
    11847444.6474791, NA, 22964159.2539319, 12378702.4908133, NA,
    20651913.7255339, 22545521.4688346, NA, 29094021.9185202, 25842626.3051625,
    NA, 11081035.3513377, 11889654.7264496, NA, 14615624.4540906,
    13198083.0662914, NA, 13607410.9970851, 21226655.5237407, NA,
    28394181.3462912, 28419931.0077081, 23239426.3900961, 32326641.7568887,
    21176778.8892413, NA, 28449764.0245833, 14120931.2944265, 28713525.2492599,
    NA, 34585827.0727595, 20847280.9840437, NA, 34831806.6919995,
    34566402.2725826, NA, 29848246.3499657, 36752811.4412478, 12805703.3541025,
    NA, 17546971.1751116, 28721595.3579839, NA, 10775995.9464392, 27767867.835873,
    NA, 14114011.9423276, 14308923.760321, NA, 23879677.2969221, 15903605.1373542,
    NA, 20716077.020712, 13857666.0228365, 14012076.2808984, 10676329.8786397, NA,
    18428504.1492559, 28836201.7918765, NA, 31223777.3031438, 28712298.9536815,
    NA, 29278045.8990801, 13317526.0108751, NA, 15174755.746223, 24139647.5168277,
    NA, 17421449.9810667, NA, 23390338.6224689, 20601644.3736951, NA,
    18399794.9429363, 28556830.9637365, NA, 35310221.1203057, 19580813.2683059,
    NA, 36486566.5344187, 14682667.7627703, NA, 19231280.2025018,
    29240849.2011756, 10331330.4685136, NA, 23640193.3433305, 28733483.4050978,
    NA, 10365418.4377946, 28695540.256127, NA, 26757283.2350158, 26621862.064504,
    NA, 18154637.3473288, 23541243.5173611, 26400009.2986855, NA,
    13737562.7563817, 21799222.8868496, NA, 35271792.0177818, 30825074.7948737,
    NA, 25892498.421024, 12698156.5638019, NA, 30071390.5086304, 28974244.7686775,
    NA, 14990196.6383106, NA, 24939527.9555589, 13867765.6203342, NA,
    28362818.7978243, 29755838.3316087, NA, 26087358.0804634, 11178514.14991, NA,
    20087488.1204561, 31343177.6545353, NA, 27513624.8306468
  )
)

The code I am trying to use is given below:

df%>%
  select(Date,Time, Upload, Download)%>%
  pivot_longer(cols = c("Upload", "Download"),
               names_to = "Parameter", values_to = "Value")%>%
  mutate(Date = as.POSIXct(Date, format = "%Y/%m/%d"),
         Time = as.POSIXct(Time, format = "%H:%M:%S"))%>%
  drop_na()%>%
  filter(Date >= (max(as.Date(Date)) - 5))%>%
  ggplot(aes(x=Time, y=(Value/8000000), colour=Parameter))+
  geom_line()+
  geom_point()+
  facet_wrap(~Date, ncol = 3, nrow = 2)+
  scale_colour_manual(values = c("blue","orange"))+
  scale_x_datetime(date_breaks = "4 hours",
                   date_labels = "%H:%M")+
  xlab("Time")+
  ylab("Speed (Mbyte/s)")+
  ylim(c(0,6))+
  theme_bw()+
  theme(strip.background = element_blank(),
        legend.position = "bottom",
        panel.grid = element_line(linetype = "dashed"))+
  geom_text(data= df%>%
  select(Date,Time, Upload, Download)%>%
  mutate(Date = as.POSIXct(Date, format = "%Y/%m/%d"),
         Time = as.POSIXct(Time, format = "%H:%M:%S"))%>%
  group_by(Date)%>%
    drop_na()%>%
      filter(Date >= (max(as.Date(Date)) - 5))%>%
  summarise(Dwl=mean(Download)/8000000,
            sd_D=sd(Download)/8000000,
            Upl=mean(Upload)/8000000,
            sd_U=sd(Upload)/8000000),
            aes(x=as.POSIXct("20:00", format = "%H:%M"),
                y=5.75,
                label=paste0("Dld = ",round(Dwl,2),"±",round(sd_D,2),
                             "\n Uld = ",round(Upl,2),"±",round(sd_U,2))),
            colour="black", inherit.aes=TRUE, size=3)

ggsave("Speedtest_Figure.png")

However, I get the error:

 `Error in `wrap_dims()`:
! Need 9 panels, but together `nrow` and `ncol` only provide 6
ℹ Please increase `ncol` and/or `nrow`

This doesn't make sense to me because when I run the command until filter(Date >= (max(as.Date(Date)) - 5)), I find the correct subset of the data on display.

What I have tried

When I then proceed to comment out the ncol= 3, nrow=2 arguments in geom_facet(), it generates the plot, but with three empty facets.

The filter command seems to "filter", but not create the proper subset and "shrink" the dataset. I even tried adding subset() after the filter like: filter(Date >= (max(as.Date(Date)) - 5))%>% subset() %>%, but the problem persists.

Solution

  • The position of your group_by in the part generating the text is slight wrong.

    library(tidyverse)

df%>%
  select(Date,Time, Upload, Download)%>%
  mutate(Date = as.POSIXct(Date, format = "%Y/%m/%d"),
         Time = as.POSIXct(Time, format = "%H:%M:%S"))%>%
  group_by(Date)%>%
  drop_na() %>%
  filter(Date >= (max(as.Date(Date)) - 5)) %>%
  summarise(Dwl=mean(Download)/8000000,
            sd_D=sd(Download)/8000000,
            Upl=mean(Upload)/8000000,
            sd_U=sd(Upload)/8000000)
#> # A tibble: 8 × 5
#>   Date                  Dwl  sd_D   Upl  sd_U
#>   <dttm>              <dbl> <dbl> <dbl> <dbl>
#> 1 2023-11-05 00:00:00  3.79 0.574  3.09 1.04 
#> 2 2023-11-06 00:00:00  3.56 1.13   2.90 1.18 
#> 3 2023-11-07 00:00:00  3.35 1.01   2.43 1.24 
#> 4 2023-11-08 00:00:00  3.53 0.781  2.74 0.945
#> 5 2023-11-09 00:00:00  3.44 0.779  2.42 0.882
#> 6 2023-11-10 00:00:00  3.67 0.701  2.94 1.01 
#> 7 2023-11-11 00:00:00  3.50 0.610  2.84 0.883
#> 8 2023-11-12 00:00:00  3.84 0.557  2.97 0.941

    Vs correct six rows with:

    df%>%
  select(Date,Time, Upload, Download)%>%
  mutate(Date = as.POSIXct(Date, format = "%Y/%m/%d"),
         Time = as.POSIXct(Time, format = "%H:%M:%S"))%>%
  drop_na() %>%
  filter(Date >= (max(as.Date(Date)) - 5)) %>%
  group_by(Date)%>%
  summarise(Dwl=mean(Download)/8000000,
            sd_D=sd(Download)/8000000,
            Upl=mean(Upload)/8000000,
            sd_U=sd(Upload)/8000000)
#> # A tibble: 6 × 5
#>   Date                  Dwl  sd_D   Upl  sd_U
#>   <dttm>              <dbl> <dbl> <dbl> <dbl>
#> 1 2023-11-07 00:00:00  3.35 1.01   2.43 1.24 
#> 2 2023-11-08 00:00:00  3.53 0.781  2.74 0.945
#> 3 2023-11-09 00:00:00  3.44 0.779  2.42 0.882
#> 4 2023-11-10 00:00:00  3.67 0.701  2.94 1.01 
#> 5 2023-11-11 00:00:00  3.50 0.610  2.84 0.883
#> 6 2023-11-12 00:00:00  3.84 0.557  2.97 0.941

    So:

    df%>%
  select(Date,Time, Upload, Download)%>%
  pivot_longer(cols = c("Upload", "Download"),
               names_to = "Parameter", values_to = "Value")%>%
  mutate(Date = as.POSIXct(Date, format = "%Y/%m/%d"),
         Time = as.POSIXct(Time, format = "%H:%M:%S"))%>%
  drop_na()%>%
  filter(Date >= (max(as.Date(Date)) - 5))%>%
  ggplot(aes(x=Time, y=(Value/8000000), colour=Parameter))+
  geom_line()+
  geom_point()+
  facet_wrap(~Date, ncol = 3, nrow = 2)+
  scale_colour_manual(values = c("blue","orange"))+
  scale_x_datetime(date_breaks = "4 hours",
                   date_labels = "%H:%M")+
  xlab("Time")+
  ylab("Speed (Mbyte/s)")+
  ylim(c(0,6))+
  theme_bw()+
  theme(strip.background = element_blank(),
        legend.position = "bottom",
        panel.grid = element_line(linetype = "dashed"))+
  geom_text(data= df%>%
              select(Date,Time, Upload, Download)%>%
              mutate(Date = as.POSIXct(Date, format = "%Y/%m/%d"),
                     Time = as.POSIXct(Time, format = "%H:%M:%S"))%>%
              drop_na()%>%
              filter(Date >= (max(as.Date(Date)) - 5))%>%
              group_by(Date)%>%
              summarise(Dwl=mean(Download)/8000000,
                        sd_D=sd(Download)/8000000,
                        Upl=mean(Upload)/8000000,
                        sd_U=sd(Upload)/8000000),
            aes(x=as.POSIXct("20:00", format = "%H:%M"),
                y=5.75,
                label=paste0("Dld = ",round(Dwl,2),"±",round(sd_D,2),
                             "\n Uld = ",round(Upl,2),"±",round(sd_U,2))),
            colour="black", inherit.aes=TRUE, size=3)