How do you merge rows with common values together in R

Question

my data looks like

df <- data.frame(Role = c("a","a","b", "b", "c", "c"), Men = c(1,0,3,1,2,4), Women = c(2,1,1,4,3,1))

and when I try to plot a bar graph after I melt this data it only graphs the first value of each common "Role", I want it to add the sum of the values for each role and gender. So men would become c(1+0,3+1,2+4)

`df %>% group_by(Role) %>% summarize(across(c(Men, Women), sum))` — Phil, Sep 20 '20 at 03:54

score 2 · Accepted Answer · answered Sep 20 '20 at 03:55

If you want this for plotting purpose, it is better to get data in long format and then summarise them for each Role and name.

library(dplyr)

df %>%
  tidyr::pivot_longer(cols = -Role) %>%
  group_by(Role, name) %>%
  summarise(value = sum(value))

#  Role  name  value
#  <chr> <chr> <dbl>
#1 a     Men       1
#2 a     Women     3
#3 b     Men       4
#4 b     Women     5
#5 c     Men       6
#6 c     Women     4

score 0 · Answer 2 · answered Sep 20 '20 at 04:46

Base R (+ ggplot2):

library(ggplot2)
ggplot(reshape(aggregate(.~Role, df, sum), 
        direction = "long",
        varying = list(names(df)[names(df) %in% c("Men", "Women")]),
        v.names = "Value",
        idvar = "Role",
        timevar = "Sex",
        times = c("Men", "Women"), 
        new.row.names = 1:((sum(names(df) != "Role")) * nrow(df))), 
       aes(Role, Value)) + 
  geom_bar(aes(fill = Sex), position = "dodge", stat = "identity")

Data:

df <- data.frame(Role = c("a","a","b", "b", "c", "c"), Men = c(1,0,3,1,2,4), Women = c(2,1,1,4,3,1))

How do you merge rows with common values together in R

2 Answers2