Read List excel in R & then perform operation (two)

user_12070334 0
Viewed 59

I already created a similar question here, though now the task is to pick two entries from each column as output suggest

enter image description here

Sample dataframe

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

we can use split and unlist:

library(purrr)
library(dplyr)

df %>% split(c(1,1,2,2,2)) %>%
        unlist()%>%
        keep(!is.na(.))

1.A1 1.A2 1.B1 1.B2 1.C1 1.C2 1.D1 1.D2 2.A1 2.A2 2.B1 2.B2 2.C1 2.C2 2.C3 
"A1" "A2" "B1" "B2" "C1" "C2" "D1" "D2" "A3" "A4" "B3" "B4" "C3" "C4" "C5"

Or simply

df %>% split(seq_len(nrow(.))>2) %>% unlist() %>% discard(is.na) %>% unname

 [1] "A1" "A2" "B1" "B2" "C1" "C2" "D1" "D2" "A3" "A4" "B3" "B4" "C3" "C4" "C5"
user_139723330

We could reshape to 'long' format with pivot_longer, create a flag column based on the row sequence after grouping by 'name', then do the arrange on the 'flag' and the 'name' column, and remove the columns not needed

library(dplyr)
library(tidyr)
df %>% 
  pivot_longer(cols = everything(), values_to = 'Output', 
        values_drop_na = TRUE)  %>% 
  group_by(name) %>% 
  mutate(flag = row_number() > 2) %>% 
  ungroup %>% 
  arrange(flag, name) %>% 
  select(-name, -flag)

-output

# A tibble: 15 x 1
   Output
   <chr> 
 1 A1    
 2 A2    
 3 B1    
 4 B2    
 5 C1    
 6 C2    
 7 D1    
 8 D2    
 9 A3    
10 A4    
11 B3    
12 B4    
13 C3    
14 C4    
15 C5

Or may also use group_split

library(purrr)
df %>%
    group_split(rn = row_number() > 2, .keep = FALSE) %>%
    map(~ .x %>%
         unlist %>% 
         na.omit) %>% 
    unlist %>% 
    tibble(output = .)

For the second case, we need a condition in the 'flag' creation

df %>% 
  pivot_longer(cols = everything(), values_to = 'Output', 
        values_drop_na = TRUE) %>% 
  group_by(name) %>% 
  mutate(flag = case_when(name == 'D' ~ row_number() > 1, 
        TRUE ~ row_number() > 2)) %>% 
  ungroup %>% 
  arrange(flag, name) %>% 
  select(-name, -flag)

-output

# A tibble: 15 x 1
   Output
   <chr> 
 1 A1    
 2 A2    
 3 B1    
 4 B2    
 5 C1    
 6 C2    
 7 D1    
 8 A3    
 9 A4    
10 B3    
11 B4    
12 C3    
13 C4    
14 C5    
15 D2
user_37322710

This may works

df %>%
  mutate(E = is.na(.)%>% rowSums()) %>%
  melt(id.vars = "E")  %>%
  arrange(E) %>%
  na.omit %>% 
  select(value)

   value
1     A1
2     A2
3     B1
4     B2
5     C1
6     C2
7     D1
8     D2
9     A3
10    A4
11    B3
12    B4
13    C3
14    C4
19    C5

Edit

dummy %>%
  mutate(E = is.na(.)%>% rowSums()) %>%
  melt(id.vars = "E")  %>%
  na.omit %>%
  arrange(E) %>%
  mutate(FF = max(E)) %>%
  group_by(variable) %>%
  mutate( E = ifelse(variable == "D" & !(value == first(value)), FF - 0.5, E)) %>%
  arrange(E) %>% select(value)

   variable value
   <fct>    <chr>
 1 A        A1   
 2 A        A2   
 3 B        B1   
 4 B        B2   
 5 C        C1   
 6 C        C2   
 7 D        D1   
 8 A        A3   
 9 A        A4   
10 B        B3   
11 B        B4   
12 C        C3   
13 C        C4   
14 D        D2   
15 C        C5
user_167291750
Related