R combine rows and columns within a dataframe-CodePudding

I've looked around for a while trying to figure this out, but I just can't seem to describe my problem concisely enough to google my way out of it. I am trying to work with Michigan COVID stats where the data has Detroit listed separately from Wayne County. I need to add Detroit's numbers to Wayne County's numbers, then remove the Detroit rows from the data frame.

I have included a screen grab too. For the purposes of this problem, can someone explain how I can get Detroit City added to Dickinson, and then make the Detroit City rows disappear? Thanks.

library(tidyverse)
library(openxlsx)
cases_deaths <- read.xlsx("https://www.michigan.gov/coronavirus/-/media/Project/Websites/coronavirus/Cases-and-Deaths/4-20-2022/Cases-and-Deaths-by-County-2022-04-20.xlsx?rev=f9f34cd7a4614efea0b7c9c00a00edfd&hash=AA277EC28A17C654C0EE768CAB41F6B5.xlsx")[,-5]
# Remove rows that don't describe counties
cases_deaths <- cases_deaths[-c(51,52,101,102,147,148,167,168),]

Code chunk output picture

CodePudding user response：

You could do:

cases_deaths %>%
  filter(COUNTY %in% c("Wayne", "Detroit City")) %>%
  mutate(COUNTY = "Wayne") %>%
  group_by(COUNTY, CASE_STATUS) %>%
  summarize_all(sum) %>%
  bind_rows(cases_deaths %>%
            filter(!COUNTY %in% c("Wayne", "Detroit City")))
#> # A tibble: 166 x 4
#> # Groups:   COUNTY [83]
#>    COUNTY  CASE_STATUS  Cases Deaths
#>    <chr>   <chr>        <dbl>  <dbl>
#>  1 Wayne   Confirmed   377396   7346
#>  2 Wayne   Probable     25970    576
#>  3 Alcona  Confirmed     1336     64
#>  4 Alcona  Probable       395      7
#>  5 Alger   Confirmed     1058      8
#>  6 Alger   Probable       658      5
#>  7 Allegan Confirmed    24109    294
#>  8 Allegan Probable      3024     52
#>  9 Alpena  Confirmed     4427    126
#> 10 Alpena  Probable      1272     12
#> # ... with 156 more rows

^{Created on 2022-04-23 by the reprex package (v2.0.1)}