Reading from Excel and plotting grouped barplot

Question

I have a Excel spread sheet with which looks at the relationship between victim and offender in murders. Either the murderer know the victim or not.

I want to plot it in a barplot, but I need to group each year. I am a bit green in R graphing so I did a paint image of what I want to have in my report:

I have the following code:

# Libraries
library("readxl")

res <- readxl::read_excel("muders_in_norway_with_relations.xlsx", sheet = 1)

dput(head(res[, 1:3])) 

# Plot bar
barplot(`Relation`~`2019`, res,
        main = "Murders in Norway with relations",
        xlab = "Year",
        ylab = "Murders")

which outputs this:

## structure(list(Relation = c("Relations", "No relation"), `2019` = c(26, 
## 3), `2020` = c(30, 1)), row.names = c(NA, -2L), class = c("tbl_df", 
## "tbl", "data.frame"))

Maby I am doing something wrong when I am reading the data into R?

is your "res" a dataframe object?

elielink
– elielink

2021-05-14 11:51:54 +00:00
Commented May 14, 2021 at 11:51 — elielink
– elielink, Commented May 14, 2021 at 11:51
res is from Excel. Im not sure what a dataframe object is.

Europa
– Europa

2021-05-14 11:58:28 +00:00
Commented May 14, 2021 at 11:58 — Europa
– Europa, Commented May 14, 2021 at 11:58

elielink · Accepted Answer · 2021-05-14 12:20:31Z

1

I did this:

library(data.table)    
library(ggplot2)
a = matrix(c(26,30,3,1),ncol=2,byrow = T)
a= as.data.frame(a)
a$r = c('relation', 'no relation')
colnames(a )=c('2019','2020','r')
a= melt(a)
ggplot(a, aes(fill=r,x=variable,y=value))+
  geom_bar(stat="identity", position=position_dodge(), width = 0.5)+
  theme_classic()+
  scale_fill_manual(values = c('blue','red'))

Maybe in your case you can do this:

library("readxl")
library(data.table)
library(ggplot2)
res <- as.data.frame(readxl::read_excel("murders_in_norway_with_relations.xlsx", sheet = 1))
res$r = c('relation','No relation')
res=reshape::melt(res)
ggplot(res, aes(fill=r,x=variable,y=value))+
geom_bar(stat="identity", position=position_dodge(), width = 0.5)+
theme_classic()+
scale_fill_manual(values = c('blue','red'))

Does it make the job?

edited May 14, 2021 at 12:20

answered May 14, 2021 at 11:43

elielink

1,2221 gold badge14 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Europa Over a year ago

Error in melt(a) : Could not find function melt. Also, I need to read the data from Excel.

elielink Over a year ago

Sorry melt is from the data.table package so you need to do library(data.table) before

elielink Over a year ago

And you can create the last column for my code coming from your dataframe using: res$r=rownames(res)

elielink Over a year ago

Does it work with the second code I added?

Europa Over a year ago

The melt gives error: Error in value [[3L]](cond) : The melt generic in data.table has been passed a data.frame, but data.table::melt currently only has a method for data.tables. Please confirm your input is a data.table, with SetDT(res) or as.data.table(res).

|

Collectives™ on Stack Overflow

Reading from Excel and plotting grouped barplot

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related