copy rows based on grepl and join as levelled list in dplyr

user_5571914 0
Viewed 36

I have the following dataframe

V1  V2
A20
Bxy
C3a
D   val1
D   val2
D   val3
A30
Bij
C4b
D   val4
D   val5

I wish to create level style dataframe as the output:

V1  V2  levels  
D   val1  A20-Bxy-C3a
D   val2  A20-Bxy-C3a
D   val3  A20-Bxy-C3a
D   val4  A30-Bij-C4b
D   val5  A30-Bij-C4b

I tried to use mutate and grepl, step-by-step as in:

df_level = df %>% mutate(levelA = grepl('^A',df$V1), 
                         levelB = grepl('^B',df$V1), 
                         levelC = grepl('^C',df$V1))

What I get is three columns with levelA, levelB, levelC with logicals.

How to copy the row value, if it matches the grep and finally join to make a consolidated levels?

dplyr r
1 Answers

You could try:

library(stringr)
library(dplyr)
library(tidyr)

df_levels <- df %>%
  filter(is.na(V2)) %>%
  select(V1) %>%
  mutate(name=str_extract(V1, "^."),
         id = rep(1:(nrow(.)/3), each=3)) %>%
  pivot_wider(names_from=name, values_from=V1) %>%
  select(-id)

which returns a data.frame of your levels:

# A tibble: 2 x 3
  A     B     C    
  <chr> <chr> <chr>
1 A20   Bxy   C3a  
2 A30   Bij   C4b

Now we transform your original data.frame

df %>%
  mutate(id = str_extract(V1, "^A.*")) %>%
  fill(id) %>%
  filter(V1 == "D")

to get

# A tibble: 5 x 3
  V1    V2    id   
  <chr> <chr> <chr>
1 D     val1  A20  
2 D     val2  A20  
3 D     val3  A20  
4 D     val4  A30  
5 D     val5  A30

Using the A-level as identification we join the data.frame of levels, therefore

df %>%
  mutate(id = str_extract(V1, "^A.*")) %>%
  fill(id) %>%
  filter(V1 == "D") %>%
  left_join(df_levels, by=c("id" = "A")) %>%
  mutate(levels = paste(id, B, C, sep="-")) %>%
  select(-id, -B, -C)

yields

# A tibble: 5 x 3
  V1    V2    levels     
  <chr> <chr> <chr>      
1 D     val1  A20-Bxy-C3a
2 D     val2  A20-Bxy-C3a
3 D     val3  A20-Bxy-C3a
4 D     val4  A30-Bij-C4b
5 D     val5  A30-Bij-C4b

Note: This is a very specific solution for your given test data. I'm not sure if this works for your real data.

user_125052510
Related