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

userwDza1 0
Viewed 13

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
r
1 Answers

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))
akrun0
Related