Creating a line chart with monthly data unerneath the x axis

Question

I'm trying to create a chart as under:

The data set is as below:

+-----------+------+------------+------------+------------+
|           | Week | S19 retail | S20 target | S20 retail |
+-----------+------+------------+------------+------------+
| "November |      |            |            |            |
| +41.7%    |      |            |            |            |
| +56.5%"   |   45 |        134 |        216 |        152 |
|           |   46 |         89 |        129 |        142 |
|           |   47 |         93 |        132 |        133 |
|           |   48 |         94 |        166 |        154 |
| "December |      |            |            |            |
| +29%      |      |            |            |            |
| +26.3%"   |   49 |         97 |        166 |        182 |
|           |   50 |        108 |        166 |        161 |
|           |   51 |        108 |        153 |        132 |
|           |   52 |         99 |         83 |        121 |
|           |    1 |        140 |        129 |        116 |
| "January  |      |            |            |            |
| +2.4%     |      |            |            |            |
| -1.5%"    |    2 |        126 |         89 |        108 |
|           |    3 |        154 |        141 |        169 |
|           |    4 |        238 |        228 |        214 |
|           |    5 |        531 |        575 |        583 |
| "February |      |            |            |            |
| -20.9%    |      |            |            |            |
| +50.6%"   |    6 |        152 |        217 |        112 |
|           |    7 |        178 |        245 |        142 |
|           |    8 |        221 |        356 |        182 |
|           |    9 |        331 |        510 |        262 |
+-----------+------+------------+------------+------------+

I was able to generate the line charts using plotly function as below:

df<-read_excel("data.xlsx", sheet=4, skip=2, range = cell_cols("B:E"))

df<- reshape2::melt(df, id.vars = "Week")

plotly::plot_ly(df, x = ~Week, y = ~value, name = df$variable, type = 'scatter', mode = 'lines') 

I'm having a hard time adding the monthly data underneath the weekly one as the one in the image attached

the dataset

structure(list(Month = structure(c(10L, 10L, 10L, 10L, 3L, 3L, 
3L, 3L, 3L, 5L), .Label = c("April", "August", "December", "February", 
"January", "July", "June", "March", "May", "November", "October", 
"September"), class = "factor"), Growth.vs..LY = structure(c(0.417073170731707, 
0.417073170731707, 0.417073170731707, 0.417073170731707, 0.289855072463768, 
0.289855072463768, 0.289855072463768, 0.289855072463768, 0.289855072463768, 
0.0238322211630124), formattable = list(formatter = "formatC", 
    format = list(format = "f", digits = 2L), preproc = "percent_preproc", 
    postproc = "percent_postproc"), class = c("formattable", 
"numeric")), Growth.Target = structure(c(0.565291083370416, 0.565291083370416, 
0.565291083370416, 0.565291083370416, 0.262542921791554, 0.262542921791554, 
0.262542921791554, 0.262542921791554, 0.262542921791554, -0.014666718172979
), formattable = list(formatter = "formatC", format = list(format = "f", 
    digits = 2L), preproc = "percent_preproc", postproc = "percent_postproc"), class = c("formattable", 
"numeric")), Week = c(45, 46, 47, 48, 49, 50, 51, 52, 1, 2), 
    S19.retail = c(134, 89, 93, 94, 97, 108, 108, 99, 140, 126
    ), S20.target = c(215.585342528395, 128.601960044626, 131.63685762905, 
    165.9451839798, 166.053984112215, 165.989660341896, 153.423458124949, 
    82.5880885087337, 128.868501741144, 88.8593114889462), S20.retail = c(152, 
    142, 133, 154, 182, 161, 132, 121, 116, 108)), row.names = c(NA, 
10L), class = "data.frame")

Answer 1

You can reach somethinh close to the output in your question using next code. Please next time include your data using dput() as suggested by @BenNorris:

library(tidyverse)
#Code
df %>% fill(Var) %>% pivot_longer(-c(Var,Week)) %>%
  mutate(Var=gsub(' ','\n',trimws(Var))) %>%
  mutate(Var=factor(Var,levels = unique(Var),ordered = T)) %>%
  ggplot(aes(x=factor(Week),y=value,color=name,group=name))+
  geom_line()+
  geom_point()+
  facet_wrap(.~Var,scales = 'free_x',nrow = 1,strip.position = 'bottom')+
  theme_bw()+
  theme(strip.placement = 'outside',
        strip.background = element_blank(),
        legend.position = 'bottom')+xlab('')+
  labs(color='')

Output:

Some data used:

#Data
df <- structure(list(Var = c("November 41.7% +56.5%", NA, NA, NA, "December 29% +26.3%", 
NA, NA, NA, NA, "January 2.4% -1.5%", NA, NA, NA, "February -20.9% +50.6%", 
NA, NA, NA), Week = c(45, 46, 47, 48, 49, 50, 51, 52, 1, 2, 3, 
4, 5, 6, 7, 8, 9), `S19 retail` = c(134, 89, 93, 94, 97, 108, 
108, 99, 140, 126, 154, 238, 531, 152, 178, 221, 331), `S20 target` = c(216, 
129, 132, 166, 166, 166, 153, 83, 129, 89, 141, 228, 575, 217, 
245, 356, 510), `S20 retail` = c(152, 142, 133, 154, 182, 161, 
132, 121, 116, 108, 169, 214, 583, 112, 142, 182, 262)), row.names = c(NA, 
-17L), class = "data.frame")

If more customization are required you can use this:

#Code 2
df %>% fill(Var) %>% pivot_longer(-c(Var,Week)) %>%
  mutate(Var=gsub(' ','\n',trimws(Var))) %>%
  mutate(Var=factor(Var,levels = unique(Var),ordered = T)) %>%
  ggplot(aes(x=factor(Week),y=value,color=name,group=name))+
  geom_line()+
  geom_point()+
  facet_wrap(.~Var,scales = 'free_x',nrow = 1,strip.position = 'bottom')+
  theme_bw()+
  theme(strip.placement = 'outside',
        strip.background = element_blank(),
        legend.position = 'bottom',
        panel.border=element_blank())+xlab('')+
  labs(color='')

Output:

With the shared data, you have to build the label for facets like this:

#Create label and plot
ndf %>% mutate(Lab=paste0(Month,'\n',100*(round(Growth.vs..LY,3)),'%','\n',
                          100*round(Growth.Target,3),'%')) %>%
  select(-c(Month,Growth.vs..LY,Growth.Target)) %>%
  pivot_longer(-c(Lab,Week)) %>%
  mutate(Lab=factor(Lab,levels = unique(Lab),ordered = T)) %>%
  ggplot(aes(x=factor(Week),y=value,color=name,group=name))+
  geom_line()+
  geom_point()+
  facet_wrap(.~Lab,scales = 'free_x',nrow = 1,strip.position = 'bottom')+
  theme_bw()+
  theme(strip.placement = 'outside',
        strip.background = element_blank(),
        legend.position = 'bottom',
        panel.border=element_blank())+xlab('')+
  labs(color='')

Output: