Search code examples
rggplot2dplyrggplotly

How to arrange values for line and point chart

With the help of this code i can get the total of each line chart but i dont know how to group the values for respective year range and also how to arrange the line to start from zero if there is no data

can anyone let me know how to acheive this...Thanks in advance expected outoutcurrent output

x = structure(list(Sector = c("Agri", "Agri", "Agri", 
    "Agri", "Agri", "Education", 
    "Education ", "Education", "Education", 
    "Education", "Energy ", "Energy ", 
    "Energy", "wealth", "wealth", "wealth", "wealth", 
    "wealth"), year_range = c("2017-2018", "2018-2019", "2019-2020", 
    "2020-2021", "2021-2022", "2017-2018", "2018-2019", "2019-2020", 
    "2020-2021", "2021-2022", "2019-2020", "2020-2021", "2021-2022", 
    "2017-2018", "2018-2019", "2019-2020", "2020-2021", "2021-2022"
    ), Month = structure(c(12L, 12L, 12L, 12L, 5L, 12L, 12L, 12L, 
    12L, 5L, 12L, 12L, 5L, 12L, 12L, 12L, 12L, 5L), .Label = c("Apr", 
    "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", "Jan", 
    "Feb", "Mar"), class = "factor"), total = c(1, 3, 9, 13, 13, 
    1, 6, 3, 1, 3, 1, 6, 9, 9, 12, 10, 4, 4)), row.names = c(NA, 
    -18L), class = c("data.table", "data.frame"), sorted = c("Sector", 
    "year_range")


y =  structure(list(year_range = c("2019-2020", "2019-2020", "2019-2020", 
"2019-2020", "2020-2021", "2020-2021", "2020-2021", "2020-2021", 
"2020-2021", "2020-2021", "2020-2021", "2021-2022", "2021-2022", 
"2021-2022"), total = c(0, 9, 10, 22, 0, 2, 14, 27, 30, 22, 13, 
0, 39, 15)), row.names = c(NA, -14L), groups = structure(list(
    year_range = c("2019-2020", "2020-2021", "2021-2022"), .rows = structure(list(
        1:4, 5:11, 12:14), ptype = integer(0), class = c("vctrs_list_of", 
    "vctrs_vctr", "list"))), row.names = c(NA, -3L), class = c("tbl_df", 
"tbl", "data.frame"), .drop = TRUE), class = c("grouped_df", 
"tbl_df", "tbl", "data.frame"))

Plan <- "#288D55"
x_labels = list(tickangle = -45,tickfont = list(family = "Arial",size = 10,color = "black",face="bold"))
y_labels = list(tickfont = list(family = "Arial",size = 10,color = "black",face="bold"))


    ggplotly(
  x %>%
    group_by(year_range) %>%
    mutate(total_year_range = sum(total)) %>%
    ungroup() %>%
    ggplot(aes(x = as.character(year_range), y = total, text = total_year_range))+
    geom_col(aes(fill = Sector))+theme_classic()+
    geom_line(data = y %>%
                mutate(total_year_range = sum(total)), aes(x=as.character(year_range), y= total, group=1,color="Plan"),size=0.8)+
    scale_colour_manual(name="",values=Plan)+
    geom_point(data = y %>%
                 mutate(total_year_range = sum(total)), aes(x=as.character(year_range), y=total),color="#288D55")+
    theme(axis.line.y = element_blank(),axis.ticks = element_blank(),legend.position = "bottom")+
    labs(x="", y="No.of total companies", fill="")+
    theme(axis.title.y =element_text(size=8))+
    scale_fill_manual(values=c("#1F7A3F","#0AAA4D","#70B821","#9BDFAF"))+
    scale_y_continuous(labels = function(x) format(x, scientific = FALSE),expand = expansion(mult = c(0,.3))),tooltip = c("text"))%>% 
  layout(legend = list(orientation = "h", x = 0.1, y = -0.2,font=list( family='Arial', size=10, color='black')),xaxis = x_labels,yaxis = y_labels)%>%
  config(displaylogo = FALSE,modeBarButtonsToRemove = list('sendDataToCloud', 'autoScale2d', 'resetScale2d', 'toggleSpikelines','hoverClosestCartesian', 'hoverCompareCartesian','zoom2d','pan2d','select2d','lasso2d','zoomIn2d','zoomOut2d'))
Solution

  • One note: There were two additional spaces for the Sector variable in your sample x which wasn't giving the right answer. So, I rechecked and removed them and then proceeded.

    You're not getting any 0 for the first two bars because there isn't any 0 in the data frame for those years. So I have run a simple loop and created the two rows for the y data frame

    i<-1
j<-1
year_range <- c()
while (i <= nrow(x)) {
  if(!(x$year_range[i] %in% y$year_range)){
    year_range[j] <- x$year_range[i]
    j <- j+1
  }
  i <- i+1
}

year_range <- unique(year_range)
total <- rep(0, length(year_range))

y_temp <- data.frame(cbind(year_range, total))
y_temp$total<-as.numeric(y_temp$total)
y <- rbind(y, y_temp)

    Now, draw the plot, and you'll have what you desire. Here I have given an example on a simple ggplot, but you can customize it as you wish:

    ggplot()+
  geom_col(data=x%>%
             group_by(year_range)%>%
             mutate(total_year_range = sum(total)) %>%
             ungroup(), aes(x=year_range, y=total, fill=Sector))+
  geom_line(data=y%>%
              group_by(year_range)%>%
              mutate(total_year_range = sum(total))%>%
              distinct(year_range, .keep_all = T), aes(x=year_range, y=total_year_range, group = 1))+
  geom_point(data=y%>%
               group_by(year_range)%>%
               mutate(total_year_range = sum(total))%>%
               distinct(year_range, .keep_all = T), aes(x=year_range, y=total_year_range))+
  xlab('Range of the year')+
  ylab('Total number of companies')+
  theme_bw()+
  theme(legend.position = 'top')

    Output

    Let me know if this solves your purpose.