combine values from different rows of same column when repeat values occur in a different column

I have a data frame as below. I am working in R.

site lat long site_code
a 12 34 a1
b 23 23 b3
c 34 23 c4
d na na na
a na na na
c na na na

I want a data frame as below. I want to keep the unique list of sites but not lose the lat and long for each site. I want to combine the lat and long when site = site

site lat long site code
a 12 34 a1
b 23 23 b3
c 34 23 c4
d na na na

>Solution :

We may convert the na to NA and then do a group by ordering of non_NA elements and slice the first row

library(dplyr)
df1 %>%
   na_if("na") %>%
   group_by(site) %>%
   mutate(across(everything(), ~ .x[order(is.na(.x))])) %>%
   slice_head(n = 1) %>%
   ungroup

-output

# A tibble: 4 × 4
  site  lat   long  site_code
  <chr> <chr> <chr> <chr>    
1 a     12    34    a1       
2 b     23    23    b3       
3 c     34    23    c4       
4 d     <NA>  <NA>  <NA>    

data

df1 <- structure(list(site = c("a", "b", "c", "d", "a", "c"), lat = c("12", 
"23", "34", "na", "na", "na"), long = c("34", "23", "23", "na", 
"na", "na"), site_code = c("a1", "b3", "c4", "na", "na", "na"
)), class = "data.frame", row.names = c(NA, -6L))

Leave a Reply