Home > Mobile >  how to fill a data frame based on another table using loop
how to fill a data frame based on another table using loop

Time:11-26

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)

  • Related