Line plot with multible lines with data from Excel

user_9827719 0
Viewed 41

I have a table in Excel that show which weapon where used to commit murder per year.

I want to read the data and plot it as a line plot with multiple lines:

enter image description here

However my code only gives me a gibberish graph:

This is my code:

library("readxl")
library(data.table)
library(ggplot2)

# Read excel data
res <- as.data.frame(readxl::read_excel("murders_per_modus_veapon.xlsx", sheet = 1))
res$r = c('Unknown','Knife/stabbing weapon','Axe','Firearms','Suffocation','Blunt violence','Other')
res = reshape::melt(res)

# Plot data
ggplot(res, aes(x=variable,y=value))+ geom_line()
ggplot2 r
3 Answers

You need to convert your variable column from factor to date. See below;

library(reshape2)
library(dplyr)
library(lubridate)
library(ggplot2)


reshape2::melt(res, value.name = "MR", variable.name = "Year") %>% 
  mutate(Year = make_date(as.character(Year), 1, 1)) %>% 
 ggplot(., aes(x=Year, y=MR, color = Modus)) + 
   geom_line() +
   scale_x_date(date_breaks = "1 year",date_labels = "%Y")
#> Using Modus as id variables

Created on 2021-05-25 by the reprex package (v2.0.0)

Data:

read.table(text = "Modus    2018    2019    2020
                   Unknown  2   0   0
                   Knife_stabbing_weapon    8   14  16
                   Axe  1   1   0
                   Firearms 3   3   2
                   Suffocation  2   5   6
                   Blunt_violence   8   3   4
                   Other    1   1   0", 
            header = T, stringsAsFactors = F, check.names = F) -> res

You can still run this part from your own code to import the data, and then use mine for plotting;

res <- as.data.frame(readxl::read_excel("murders_per_modus_veapon.xlsx", sheet = 1)) 

res$r = c('Unknown','Knife/stabbing weapon','Axe','Firearms',
          'Suffocation','Blunt violence','Other')
user_64614620

Since you have not provided reproducible data, I created tiny data that may look like a small subset of your original xlsx data:

df = data.frame(weapon = c("Unknown", "knife", "axe"),
                x2018 = c(2, 8, 1),
                x2019 = c(0, 14, 1),
                x2020 = c(0, 16, 0))

Then, I make the data tidy using dplyr and tidyr. Finally I produce a line plot that you may be looking for:

df %>%
        pivot_longer(cols = 2:4, names_to = "year", values_to = "amount") %>%
        mutate(year = gsub("x", "", year)) %>%
        ggplot(aes(as.numeric(year), amount, col = weapon)) +
        geom_line() +
        scale_x_continuous(breaks = c(2018, 2019, 2020))

enter image description here

user_148022850

This could be an option. Thanks to M-- for the data.

library(ggplot2)
library(ggrepel)
df <- df %>% 
  pivot_longer(
    cols = c("2018", "2019", "2020") 
  ) %>% 
  mutate(label = if_else(name == max(name), as.character(Modus), NA_character_)) %>% 
  mutate(name = as.factor(name))

p <- ggplot(df, aes(x=name, y=value, colour=Modus, group=Modus)) +
  geom_point () +
  geom_line(size = 0.8) +
  theme_bw()

p + geom_label_repel(aes(label = label),
                       nudge_x = 1,
                       na.rm = TRUE) +
  theme(legend.position = "none")

enter image description here

user_133216470
Related