CODEWITHSUNDEEP
rdplyrtidyversetidyr
user10974714
user10974714

Reputation:

Extract non-missing elements by rows and stack them

I have a data frame like this

df <- data.frame(id = 1:4,
  V1 = c("A", NA, "C", NA),
  V2 = c(NA, NA, NA, "E"),
  V3 = c(NA, "B", NA, "F"),
  V4 = c(NA, NA, "D", NA), stringsAsFactors = F)

#   id   V1   V2   V3   V4
# 1  1    A <NA> <NA> <NA>
# 2  2 <NA> <NA>    B <NA>
# 3  3    C <NA> <NA>    D
# 4  4 <NA>    E    F <NA>

How can I extract non-missing elements by rows and stack them into a column? My expected output is:

#   id value
# 1  1     A
# 2  2     B
# 3  3     C
# 4  3     D
# 5  4     E
# 6  4     F

Upvotes: 2

Views: 90

Answers (5)

Darren Tsai
Darren Tsai

Reputation: 35584

Try pivot_longer() or unite() + separate_rows().

library(tidyr)
library(dplyr)

# Method 1
df %>%
  pivot_longer(-id, values_drop_na = T) %>%
  select(-name)

# Method 2
df %>%
  unite(value, -id, na.rm = T) %>%
  separate_rows(value)

# # A tibble: 6 x 2
#      id value
#   <int> <chr>
# 1     1 A    
# 2     2 B    
# 3     3 C    
# 4     3 D    
# 5     4 E    
# 6     4 F

Upvotes: 3

Alfonso
Alfonso

Reputation: 713

How about combining complete.cases with reshape library?

library(reshape2)

df.temp <- melt(df, id.vars = "id")
df.temp[complete.cases(df.temp),-2]

results in

   id value
1   1     A
3   3     C
8   4     E
10  2     B
12  4     F
15  3     D

Upvotes: 1

tmfmnk
tmfmnk

Reputation: 39858

One base R solution could be:

na.omit(data.frame(df[1], stack(df[-1])[1]))

   id values
1   1      A
3   3      C
8   4      E
10  2      B
12  4      F
15  3      D

Upvotes: 1

Richard Telford
Richard Telford

Reputation: 9923

pivot_longer then filter

library(tidyverse)

df <- data.frame(id = 1:4,
                 V1 = c("A", NA, "C", NA),
                 V2 = c(NA, NA, NA, "E"),
                 V3 = c(NA, "B", NA, "F"),
                 V4 = c(NA, NA, "D", NA), stringsAsFactors = FALSE)

df %>% pivot_longer(-id, names_to = "name", values_to = "value") %>% 
  filter(!is.na(value)) %>% 
  select(-name)
#> # A tibble: 6 x 2
#>      id value
#>   <int> <chr>
#> 1     1 A    
#> 2     2 B    
#> 3     3 C    
#> 4     3 D    
#> 5     4 E    
#> 6     4 F

Created on 2020-03-02 by the reprex package (v0.3.0)

Upvotes: 0

Florian
Florian

Reputation: 1258

You can use dplyr and tidyr:

df %>% 
   tidyr::gather(-id, key = "key", value = "value") %>% 
   dplyr::filter(!is.na(value))

  id key value
1  1  V1     A
2  3  V1     C
3  4  V2     E
4  2  V3     B
5  4  V3     F
6  3  V4     D

Upvotes: 1

Related Questions