CODEWITHSUNDEEP
rlistdplyrtidyverse
Vaibhav Singh
Vaibhav Singh

Reputation: 1209

Read List excel in R & then perform operation

I am not sure how to replicate the code or list in R, hopefully my image will be able able to convey the problem. I have few columns in excel (uneven length), I want to create a final column which takes 1 value from each column & create a list. (If you can include code on how to read such data in R from an excel that would be helpful too.)

Desired output

Adding data structure

df <- structure(list(A = c("A1", "A2", "A3", NA, NA), B = c("B1", "B2", 
NA, NA, NA), C = c("C1", "C2", "C3", "C4", "C5"), D = c("D1", 
"D2", "D3", NA, NA)), row.names = c(NA, -5L), class = c("tbl_df", 
"tbl", "data.frame"))

Upvotes: 0

Views: 144

Answers (3)

akrun
akrun

Reputation: 887951

We can do this easily in base R

na.omit(stack(df)[order(c(row(df))),][1])

Or using melt

library(data.table)
melt(data.table::transpose(setDT(df)), measure = 1:5, na.rm = TRUE)[, 2, with = FALSE]

Upvotes: 1

jpdugo17
jpdugo17

Reputation: 7116

with tidyr:

library(tidyverse)

df <- structure(list(A = c("A1", "A2", "A3", NA, NA), B = c("B1", "B2", 
                                                            NA, NA, NA), C = c("C1", "C2", "C3", "C4", "C5"), D = c("D1", 
                                                                                                                    "D2", "D3", NA, NA)), row.names = c(NA, -5L), class = c("tbl_df", 
                                                                                                                                                                            "tbl", "data.frame"))
pivot_longer(df, cols = c('A', 'B', 'C', 'D')) %>% na.omit()
#> # A tibble: 13 x 2
#>    name  value
#>    <chr> <chr>
#>  1 A     A1   
#>  2 B     B1   
#>  3 C     C1   
#>  4 D     D1   
#>  5 A     A2   
#>  6 B     B2   
#>  7 C     C2   
#>  8 D     D2   
#>  9 A     A3   
#> 10 C     C3   
#> 11 D     D3   
#> 12 C     C4   
#> 13 C     C5

Created on 2021-06-01 by the reprex package (v2.0.0)

Upvotes: 2

Ronak Shah
Ronak Shah

Reputation: 389325

Transpose the data, convert to vector and drop NA values.

result <- data.frame(Final = na.omit(c(t(df))))
result

#   Final
#1     A1
#2     B1
#3     C1
#4     D1
#5     A2
#6     B2
#7     C2
#8     D2
#9     A3
#10    C3
#11    D3
#12    C4
#13    C5

Upvotes: 2

Related Questions