ggplot: how to add multiple lines in a scatter plot

Question

I need to produce a scatter plot with lines that merge the values of each group.

This is an example

I have the values for 5 cities, one for each year.

This is a sample of my code.

                 Year       City_A     City_B  City_C City_D City_E  
 1                 2015          NA         NA     NA    NA    NA 
 2                 2016          NA         NA     NA    NA    NA 
 3                 2017        2085.      1956.  1949. 1780. 1798.
 4                 2018        2722.      1893.  2074. 1701. 1806.
 5                 2019        2675.      1979.  2058. 1624. 1770.
 6                 2020        2578.      2081.  2098. 1814. 1834.
 7                 2021        2315.      2310.  2162. 1740. 1874.
 8                 2022        2227.      2082.  2097. 1784. 1783.
 9                 2023        2549.      2060.  2072. 1714. 1948.
10                 2024        2252.      2073.  2669. 1673. 1688.
11                 2025        2266.      2074.  1767. 1897. 1656.
12                 2026        2660.      1821.  1738. 1432. 1851.
13                 2027        1449.      1556.  1452. 1260. 1299.

I'm trying to do that with this code,

dataset%>%
ggplot()+
  geom_point( aes(x=Year, y=City_A))+
  geom_line(aes(x=Year, y=City_A), color = 'blue')+
  geom_point( aes(x=Year, y=City_B))+
  geom_line(aes(x=Year, y=City_B), color = 'purple')+
  geom_point(aes(x=Year, y=City_C))+
  geom_line(aes(x=Year, y=City_C), color = 'red')+
  geom_point(aes(x=Year, y=City_D))+
  geom_line(aes(x=Year, y=City_D), color = 'orange')+
  geom_point(aes(x=Year, y=City_E))+
  geom_line(aes(x=Year, y=City_E), color = 'gold')

But I get this thing...

How can I solve?

Answer 1

We have to bring the data in long format. Next important step for such a graph is to group (here by city):

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

df %>% 
  pivot_longer(cols=-Year, names_to="city", values_to="value") %>% 
  ggplot(aes(x=factor(Year), y=value, group=city, color=city)) +
  geom_point() +
  geom_line() +
  scale_color_manual(values = c('blue', 'purple', 'red', 'orange', 'gold'))