Merge rows and values in R based on condition

user_15088972 0
Viewed 229

I have the following sample data:

# data
school = c('ABC University','ABC Uni','DFG University','DFG U')
applicant = c(2000,3100,210,2000)
students = c(100,2000,300,4000)
df = data.frame(school,applicant,students)

I want to merge to this:

|school        |appliant| students |
-----------------------------------
|ABC University| 5100   | 2100     |
|DFG University| 2210   | 4300     |

I ran this code:

df$school[df$school == 'ABC Uni'] = 'ABC University'

But it gives me ABC University twice instead of merging them together.

dataframe r merge
2 Answers

This really depends on the rest of your strings, but you could take a look into grep and use ^ for begins with.

df[grep('^ABC U', df$school), 'school'] <- 'ABC University'
df[grep('^DFG U', df$school), 'school'] <- 'DFG University'

And the aggregate as usual.

aggregate(cbind(applicant, students) ~ school, df, sum)
#           school applicant students
# 1 ABC University      5100     2100
# 2 DFG University      2210     4300
user_65740380

Here is dplyr stringr solution:

library(dplyr)
library(stringr)
df %>% 
    mutate(school = str_replace_all(school, c(
        "^ABC Uni$" = "ABC University",
        "^DFG U$" = "DFG University"))) %>% 
    group_by(school) %>% 
    summarise(across(c(applicant, students), sum))

output:

  school         applicant students
  <chr>              <dbl>    <dbl>
1 ABC University      5100     2100
2 DFG University      2210     4300
user_133216470
Related