I have a data frame like below: data:
df<-tibble(id=c("ls1","ls1","ls2","ls4"),
symbol=c("a","a","b","df"),
var=c("-","gh","gh","lm"))
I want to convert to another data frame like below:
ls1 lsp10 ls02 ls6
a _ gh _ _
a _ _ _ _
b _ _ gh _
df _ _ _ lm
To this end I am using the code below loop but it is not working?
for(i in 1:nrow(data)) {
for(j in 1:nrow(data)) {
if(identical(data[1,1], data[1,1]) && identical(data[1,2], data[1,2]) && data[1,3] = data[1,3]){
data[i,3] <- paste0(data[i,3],";",data[j,3])
data<- data[-j,]
}}}
data file doesn't change! Any idea?
CodePudding user response:
One option using dplyr
and tidyr
Includes row_id helper variable to make explicit symbol variable where the same value appears on separate rows; this can easily be removed, if required.
Updated to include - for NAs
library(tibble)
library(dplyr)
library(tidyr)
data <- tribble(
~id, ~symbol, ~var,
"ls1", "a", "-",
"lsp10", "a", "gh",
"ls02", "b", "gh",
"ls6", "df", "lm")
data %>%
mutate(row_id = row_number()) %>%
na_if("-") %>%
pivot_wider(names_from = id, values_from = var) %>%
mutate(across(everything(), ~replace_na(., "-")))%>%
select(row_id, everything())
#> # A tibble: 4 x 6
#> row_id symbol ls1 lsp10 ls02 ls6
#> <chr> <chr> <chr> <chr> <chr> <chr>
#> 1 1 a - - - -
#> 2 2 a - gh - -
#> 3 3 b - - gh -
#> 4 4 df - - - lm
Created on 2021-11-26 by the reprex package (v2.0.1)