How to plot different indices keeping one fixed in R

Question

I have a data frame with house price indices (sales and rents) with 3 different versions of the HPI and the median price index. So, I have a variable "Value" with the value of the index, "Index" with either HPI1, HPI2, HPI3 or Median, for the type of index, "Operation" for sale or rent and Month. It looks something like this, but for a longer period of time.

Index	Value	Operation	Month
HPI1	0.9	Sale	01/2020
HPI1	1.1	Rent	02/2020
HPI2	0.89	Sale	01/2020
HPI2	1.12	Rent	02/2020
HPI3	0.85	Sale	01/2020
HPI3	1.22	Rent	02/2020
Median	0.91	Sale	01/2020
Median	1.02	Rent	02/2020

I need to plot each HPI with the Median, by operation. this means I need three plots in one figure, the first one will be HPI1 + Median, the second would be HPI2 + Median, and the third one would be HPI3 + Median. I'm using ggplot with a loop, but I've only managed to do 1 plot with the four lines. I don't know how to repeat the plot by HPI but keeping Median. Any ideas?

Thank you very much!

I am currently using this code (RES is the data frame)

for(z in operation  
ggplot(RES[operation==z,],aes(x=interaction(month,year,sep = " "),y=Value,group=index),alpha=0.02) + geom_line(aes(linetype=index)) + geom_point() + scale_linetype_manual(values=c("dotted", "solid")) + theme(legend.position='bottom',legend.title=element_blank(),axis.text.x = element_text(angle = 90))+ geom_hline(aes(yintercept=1,colour="red"),show.legend = F)+facet_wrap(~operacion,scales = "free",ncol = 2)
}

Answer 1

Another approach using foreach loop

Note: I used sample data provided in @Edo answer

Initial library load & prepare the legend for later used

library(ggplot2) # For obvious reason - graph
library(dplyr) # For manipulate data a bit
library(foreach) # Foreach Loop
library(cowplot) # For manipulate graphs

# Define the line type Solid for variables and dotted line for Median
line_type <- c("solid", "1111")
names(line_type) <- c("Index (HPI1, HPI2, HPI3)", "Median")

# Generate dummy plot just to extract legend
plot_for_legend <- ggplot(data = RES %>%
    mutate(Index = if_else(Index == "Median", "Median",
  # If you have more Index than HPI1, HPI2, HPI3, can just simply remove
  # the text in parentheses and leave it as Index - make sure you change
  # the line_type variable accordingly and adjust all the places needed.
                           "Index (HPI1, HPI2, HPI3)")),
  aes(x = Month, y = Value, group = Index)) +
  geom_line(aes(linetype = Index)) +
  scale_linetype_manual(values = line_type) +
  theme(legend.position = 'bottom', legend.title = element_blank(),
  axis.text.x = element_text(angle = 90))
# Extracting legend using get_legend from cowplot package
legend_area <- get_legend(plot_for_legend)

Using foreach loop to iterate through each operation and graph them

operation <- unique(RES[["Operation"]])  
operation_plot <- foreach(z = operation) %do% {
  # filter data to current operation Rent/ Sale
  operation_data <- RES %>% filter(Operation == z)
  median_data <- operation_data %>% filter(Index == "Median")
  
  median_line_point <- geom_line(data = median_data,
    aes(x = Month, y = Value, group = Index, linetype = "Median"), 
    alpha=1)
  median_point <- geom_point(data = median_data,
    aes(x = Month, y = Value))
  
  list_index <- unique(operation_data[["Index"]])
  list_index <- list_index[list_index != "Median"]
  
  list_plot <- foreach(i_index = list_index) %do% {
    ggplot(data = operation_data %>% filter(Index == i_index),
      aes(x = Month, y = Value, group = Index, 
          # in case you change the line_type make sure to change the name 
          # here accordingly
          linetype ="Index (HPI1, HPI2, HPI3)"), 
      alpha=1) +
      geom_line(linetype = "solid") +
      geom_point() +
      median_line_point + median_point +
      scale_linetype_manual(values = line_type) +
      geom_hline(aes(yintercept = 1,colour = "red"), show.legend = F) +
      ylab(i_index) + 
      theme(legend.position = "none")
  }
  # put all the plot in the list into one
  current_plot <- plot_grid(plotlist = list_plot, ncol = 2)

  # Generate title for operation
  title <- ggdraw() + 
    draw_label(
      sprintf("%s operation", z),
      fontface = 'bold',
      x = 0,
      hjust = 0
    ) +
    theme(
      # add margin on the left of the drawing canvas,
      # so title is aligned with left edge of first plot
      plot.margin = margin(0, 0, 0, 7)
    )

  # Combine title, main plot & legend into one
  current_plot_w_legend <- plot_grid(title, current_plot, legend_area, nrow = 3, rel_heights = c(.5, 10, 1))
}
# Set names of the plot variables for easier access
names(operation_plot) <- operation

With some sample data here is the output

Answer 2

This is a possible solution. It's scalable in case of many HPI. It's fully based on tidyverse. The idea is to set Median next to each HPIn by using two pivot commands from tidyr. You can get multiple plots in one image with facet_grid or facet_wrap.

SOLUTION

library(dplyr)
library(tidyr)
library(ggplot2)


df %>% 
 
 # transform in date
 mutate(Month = as.Date(paste0("01/", Month), format = "%d/%m/%Y")) %>% 
 
 # reshape data
 pivot_wider(names_from = Index, values_from = Value) %>% 
 pivot_longer(starts_with("HPI"), names_to = "Index", values_to = "Value") %>% 
 
 # plot by HPI
 ggplot(aes(x = Month)) +
 geom_line(aes(y = Value, colour = Index)) +
 geom_line(aes(y = Median, colour = "Median")) +
 geom_point(aes(y = Value, colour = Index)) +
 geom_point(aes(y = Median, colour = "Median")) +
 scale_x_date(date_labels = "%m %Y", date_breaks = "1 month") +
 facet_grid(Index~Operation)

The legend is redundant. If you don't want it: remove color = "Median" in the second geom_line and add show.legend = FALSE in the first geom_line. Or you can add + theme(legend.position = "none") at the end.

DATA

# (I just tripled your data)
df <- tibble::tribble(
 ~Index,    ~Value, ~Operation, ~Month,
 "HPI1",    0.9,    "Sale", "01/2020",
 "HPI1",    1.1,    "Rent", "02/2020",
 "HPI2",    0.89,   "Sale", "01/2020",
 "HPI2",    1.12,   "Rent", "02/2020",
 "HPI3",    0.85,   "Sale", "01/2020",
 "HPI3",    1.22,   "Rent", "02/2020",
 "Median",  0.91,   "Sale", "01/2020",
 "Median",  1.02,   "Rent", "02/2020",
 "HPI1",    0.9,    "Sale", "02/2020",
 "HPI1",    1.1,    "Rent", "03/2020",
 "HPI2",    0.89,   "Sale", "02/2020",
 "HPI2",    1.12,   "Rent", "03/2020",
 "HPI3",    0.85,   "Sale", "02/2020",
 "HPI3",    1.22,   "Rent", "03/2020",
 "Median",  0.91,   "Sale", "02/2020",
 "Median",  1.02,   "Rent", "03/2020",
 "HPI1",    0.9,    "Sale", "03/2020",
 "HPI1",    1.1,    "Rent", "04/2020",
 "HPI2",    0.89,   "Sale", "03/2020",
 "HPI2",    1.12,   "Rent", "04/2020",
 "HPI3",    0.85,   "Sale", "03/2020",
 "HPI3",    1.22,   "Rent", "04/2020",
 "Median",  0.91,   "Sale", "03/2020",
 "Median",  1.02,   "Rent", "04/2020")