Home > database >  How to expand rows in a tibble based on a list of values
How to expand rows in a tibble based on a list of values

Time:11-20

I have this row

  first coor  second
  <chr> <chr> <chr> 
1 first ALL   second

I want to expand this row into multiple rows and replace the coor column based on the following values

coor = c("ESP", "FRA", "GER")

So that I get this in the end

  first coor  second
  <chr> <chr> <chr> 
1 first ESP   second
2 first FRA   second
3 first GER   second

Any ideas?

CodePudding user response:

We may create a list column and unnest it

library(dplyr)
library(tidyr)
dat %>%
    mutate(coor = list(.env$coor)) %>% 
    unnest(coor)

-output

# A tibble: 3 × 3
  first coor  second
  <chr> <chr> <chr> 
1 first ESP   second
2 first FRA   second
3 first GER   second

Or another option is crossing on the subset of data with 'coor' vector

crossing(dat %>%
           select(-coor), coor)
# A tibble: 3 × 3
  first second coor 
  <chr> <chr>  <chr>
1 first second ESP  
2 first second FRA  
3 first second GER  

data

dat <- tibble(first = "first", coor = "ALL", second = "second")
coor <- c("ESP", "FRA", "GER")

CodePudding user response:

Another option is:

  1. Rename the vector
  2. use slice to expand dataframe to the length of vector
  3. fill coor with vector
library(dplyr)

vec_coor <- coor

dat %>% 
  slice(rep(1:n(), each = length(vec_coor))) %>% 
  mutate(coor = vec_coor)
 first coor  second
  <chr> <chr> <chr> 
1 first ESP   second
2 first FRA   second
3 first GER   second

CodePudding user response:

Yet another solution:

library(tidyverse)

df <- data.frame(
  stringsAsFactors = FALSE,
             first = c("first"),
              coor = c("ALL"),
            second = c("second")
      )

df %>% 
  complete(first, coor=c("ESP", "FRA", "GER"), second) %>% filter(!coor == "ALL")

#> # A tibble: 3 × 3
#>   first coor  second
#>   <chr> <chr> <chr> 
#> 1 first ESP   second
#> 2 first FRA   second
#> 3 first GER   second

CodePudding user response:

Here is another option. We can create a lookup table and apply full_join. dat and coor are from akrun's answer.

library(tidyverse)

lookup <- data.table(
  coor = rep("ALL", length(coor)),
  coor_new = coor
)

dat2 <- dat %>%
  full_join(lookup, by = "coor") %>%
  select(first, coor = coor_new, second)

dat2
# # A tibble: 3 x 3
#   first coor  second
#   <chr> <chr> <chr> 
# 1 first ESP   second
# 2 first FRA   second
# 3 first GER   second
  • Related