CODEWITHSUNDEEP
rdplyrtidyverse
lim-lim
lim-lim

Reputation: 17

Dplyr:: how to find sums for rows that partially share their name and keep them in the entire tibble?

In the mtcars sample there are partially matching car names (in Mercedes there are Merc 240D, Merc 230, Merc 280, Merc 280C, Merc 450SE, Merc 450SL, Merc 450SLC etc). I would like to group them with the beginning of their name like Merc 2* and Merc 4*, count the sum of their hp by these groups and transform all the mentioned rows into rows called Merc 2 and Merc 4 leaving other non-Mercedes car rows in mtcars unmodified. What is the best way of doing this?

Actually the only solution I have to date is:

    mtcars %>%
      rownames_to_column(var = "cars") %>%
      select(cars, hp) %>%
      mutate(hp = if_else(cars=="Merc 450SLC",
      sum(hp[which(.$cars == "Merc 450SL")], hp[which(.$cars == "Merc450SE")], hp[which(.$cars == "Merc 450SLC")]),
      hp))

Many thanks

Upvotes: 0

Views: 38

Answers (1)

Weihuang Wong
Weihuang Wong

Reputation: 13128

You can use regex to change the names of the cars:

library(dplyr)
library(tibble)

mtcars %>%
  rownames_to_column(var = "cars") %>%
  mutate(cars = gsub("^(Merc [0-9]).*$", "\\1", cars)) %>%
  group_by(cars) %>%
  summarise(hp = sum(hp)) %>%
  as.data.frame
#                   cars  hp
# 1          AMC Javelin 150
# 2   Cadillac Fleetwood 205
# 3           Camaro Z28 245
# 4    Chrysler Imperial 230
# 5           Datsun 710  93
# 6     Dodge Challenger 150
# 7           Duster 360 245
# 8         Ferrari Dino 175
# 9             Fiat 128  66
# 10           Fiat X1-9  66
# 11      Ford Pantera L 264
# 12         Honda Civic  52
# 13      Hornet 4 Drive 110
# 14   Hornet Sportabout 175
# 15 Lincoln Continental 215
# 16        Lotus Europa 113
# 17       Maserati Bora 335
# 18           Mazda RX4 110
# 19       Mazda RX4 Wag 110
# 20              Merc 2 403
# 21              Merc 4 540
# 22    Pontiac Firebird 175
# 23       Porsche 914-2  91
# 24      Toyota Corolla  65
# 25       Toyota Corona  97
# 26             Valiant 105
# 27          Volvo 142E 109

Upvotes: 1

Related Questions