Merging rows based on values in two columns?

[rvrgl]

Hi! I have a dataset that has several rows for each "id". I'd like to merge the rows so that all of the data for each unique "id" and "year" combination are contained in one row. Below is an example of my current data:

id<-c("A","A","A","A","A","A","B","B","B")
year<-c(1,1,1,2,2,2,1,1,1)
maytemp<-c(20,NA,NA,21,NA,NA,23,NA,NA)
juntemp<-c(NA,21,NA,NA,20,NA,NA,24,NA)
jultemp<-c(NA,NA,22,NA,NA,18,NA,NA,25)
df<-data.frame(id,year,maytemp,juntemp,jultemp)
View(df)

I am trying to get an output like this:

I'd appreciate any advice!

[sergio.costa]

The following approach works for your example data. If in your full data each combination of id and year have only one numeric value for maytemp, juntemp and jultemp (like in the example data), the solution will work fine as well.

df<-data.frame(id,year,maytemp,juntemp,jultemp)%>%
  group_by(id,year)%>%
  summarise(maytemp=sum(maytemp,na.rm = T),
            juntemp=sum(juntemp,na.rm = T),
            jultemp=sum(jultemp,na.rm = T))

[andresrcs]

You can save some lines using across()

library(dplyr)

df <- data.frame(
  stringsAsFactors = FALSE,
                id = c("A", "A", "A", "A", "A", "A", "B", "B", "B"),
              year = c(1, 1, 1, 2, 2, 2, 1, 1, 1),
           maytemp = c(20, NA, NA, 21, NA, NA, 23, NA, NA),
           juntemp = c(NA, 21, NA, NA, 20, NA, NA, 24, NA),
           jultemp = c(NA, NA, 22, NA, NA, 18, NA, NA, 25)
)

df %>% 
    group_by(id, year) %>% 
    summarise(across(c(maytemp:jultemp), sum, na.rm = TRUE))
#> `summarise()` has grouped output by 'id'. You can override using the `.groups` argument.
#> # A tibble: 3 x 5
#> # Groups:   id [2]
#>   id     year maytemp juntemp jultemp
#>   <chr> <dbl>   <dbl>   <dbl>   <dbl>
#> 1 A         1      20      21      22
#> 2 A         2      21      20      18
#> 3 B         1      23      24      25

^{Created on 2021-05-04 by the reprex package (v2.0.0)}

[rvrgl]

Yes, this works great, thanks a lot! I am marking the other answer as a solution because it requires fewer lines.

[rvrgl]

This is exactly what I needed. Many thanks!