CODEWITHSUNDEEP
rmergedata-cleaning
rise of a phoenix
rise of a phoenix

Reputation: 43

Convert multiple columns with NA's to rows

I have a big data frame (with ~100 columns):

col1| col2| col3| col4| col5| col6| col7
-----------------------------------------
 A     1     2     NA    NA    NA    NA     
 A     3     4     NA    NA    NA    NA    
 B     NA    NA     1     3    NA    NA     
 B     NA    NA     2     3    NA    NA
 C     NA    NA    NA    NA    2     5
 C     NA    NA    NA    NA    3     2

I want to transform it to the following data frame:

col1| merged_col1|
--------------------
 A       1
 A       2
 A       3 
 A       4
 B       1
 B       3
 B       2
 B       3
 C       2
 C       5
 C       3
 C       2

I want to combine the columns omitting all the NA's to corresponding rows. Then, I would like to convert all but the first to rows. How can I do that in R?

Upvotes: 0

Views: 64

Answers (2)

Kristijan Tornič
Kristijan Tornič

Reputation: 19

For loops are not exactly best thing to use in R but you can try something like this anyway:

new.df <- data.frame()

index <- 1

for(i in 1:nrow(old.df)){
  for(j in 2:ncol(old.df)){
    if (!is.na(old.df[i,j])){
      new.df[index,1] <- old.df[i,1]
      new.df[index,2] <- old.df[i,j]
      index <- index + 1
    }
  }
}

Upvotes: -1

udden2903
udden2903

Reputation: 783

library(tidyverse)

df <- data.frame(col1 = c("A", "A", "B", "B", "C", "C"),
           col2 = c(1, 3, NA, NA, NA, NA),
           col3 = c(2, 4, NA, NA, NA, NA),
           col4 = c(NA, NA, 1, 2, NA, NA),
           col5 = c(NA, NA, 3, 3, NA, NA),
           col6 = c(NA, NA, NA, NA, 2, 3),
           col7 = c(NA, NA, NA, NA, 5, 2))

df_long <- df %>% gather(col, merged_col1, c(2:7), na.rm = T) %>% select(-col) %>% arrange(col1, merged_col1)

Upvotes: 2

Related Questions