Move data from specific row from one column to the adjacent row to the next column in R-CodePudding

I have a data frame with three columns:


df <- data.frame(col1=c('71711', '71711',  '71711', 'Comment 4', '71711', 'Comment 6'),
                  col2=c('Comment 1','Comment 2','Comment 3', '24','Comment 5','26'),
                  col3 = c('21','22','23',NA,'25',NA))

Desired output:

Col1    Col2   Col3
71711   Comment 1   21
71711   Comment 2   22
71711   Comment 3   23
71711   Comment 4   24
71711   Comment 5   25
71711   Comment 6   26

I tried the following but it replaces desired values with numeric one:

for (i in 1:nrow(df))
  {
  
  if  (any(sapply(df$col2, is.numeric)) == "True" )
       df$col3[i] <- df$col2[i]
       df$col1[i] <- df$col2[i]

}

Thanks

CodePudding user response：

You can find rows which have NA in col3 and just 'shift' values:

indices <- which(is.na(df$col3))
df[indices,] <- append(list(paste0("ID", indices)), df[indices,-3])

CodePudding user response：

Using apply:

df[] <- t(apply(df, 1, function(x){
  c(x[is.na(x)], x[!is.na(x)])
}))

df$col1 <- paste0("ID", 1:nrow(df))

output

  col1      col2 col3
1  ID1 Comment 1   21
2  ID2 Comment 2   22
3  ID3 Comment 3   23
4  ID4 Comment 4   24
5  ID5 Comment 5   25
6  ID6 Comment 6   26

CodePudding user response：

Here is another option. First, we collapse the data back to a string, then we extract the data into the proper columns, then we fill in the missing IDs in col1:

library(tidyverse)

df |>
  transmute(txt = pmap_chr(list(col1, col2, col3), 
                        ~ paste(c(...)[!is.na(c(...))], collapse = "---")))|>
  extract(col = txt, 
          into = c("col1", "col2", "col3"), 
          regex = "(ID\\d )?(?:---)?(Comment\\s\\d )---(\\d )$", 
          remove = TRUE, 
          convert = TRUE) |>
  mutate(col1 = paste0("ID", row_number()))
#>   col1      col2 col3
#> 1  ID1 Comment 1   21
#> 2  ID2 Comment 2   22
#> 3  ID3 Comment 3   23
#> 4  ID4 Comment 4   24
#> 5  ID5 Comment 5   25
#> 6  ID6 Comment 6   26