Create multiple columns in data frame based on another column

Question

I would like to update a data frame to add 10 columns with values based on another column

Starting with this

df <- data.frame(ID = 1:3, name = c("Bob", "Jim", "Fred"), endValue= c(3, 7, 4))

And ending with this

|ID|Name|endValue|A|B|C|D|E|F|G|H|I|J|
|1|Bob|3|Y|Y|Y|N|N|N|N|N|N|N|
|2|Jim|7|Y|Y|Y|Y|Y|Y|Y|N|N|N|
|3|Fred|4|Y|Y|Y|Y|N|N|N|N|N|N|

So each new record needs:

the original contents
Ten new columns
A conditional value based on whether the endValue is less than or equal to the column no

Any help welcome...

akrun · Answer 1 · 2021-04-27T16:18:16.710

One option is to create a list column by replicating the 'Y', 'N' for each value of 'endValue' column with the difference from 10 and then unnest it to wide

library(dplyr)
library(purrr)
library(tidyr)
df %>%
   mutate(new =  map(endValue, ~ rep(c("Y", "N"), c(.x, 10 - .x)))) %>% 
   unnest_wider(new) %>%
   rename_at(vars(starts_with('..')), ~ LETTERS[1:10])

-output

# A tibble: 3 x 13
#     ID name  endValue A     B     C     D     E     F     G     H     I     J    
#  <int> <chr>    <dbl> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
#1     1 Bob          3 Y     Y     Y     N     N     N     N     N     N     N    
#2     2 Jim          7 Y     Y     Y     Y     Y     Y     Y     N     N     N    
#3     3 Fred         4 Y     Y     Y     Y     N     N     N     N     N     N

Or use separate

library(stringr)
df %>%
  mutate(new = str_c(strrep('Y', endValue),
         strrep('N', 10 - endValue))) %>% 
  separate(new, into = LETTERS[1:10], sep="(?<=[A-Z])(?=[A-Z])")

score 2 · Accepted Answer · answered Apr 27 '21 at 16:33

Also using tidyverse family with pmap & pivot_wider

library(dplyr)
library(purrr)
library(tidyr)

df %>%
  # map each row to a defined function using pmap
  # this function create a long table with 10 rows per ID, name, endValue
  pmap(.f = function(...) { 
    x <- tibble(...)
    y <- tibble(
      ID = x$ID, name = x$name,
      endValue = x$endValue,
      LETTER = LETTERS[1:10],
      value = c(rep("Y", x$endValue), rep("N", 10 - x$endValue)))
  }) %>%
  # combine all the df together
  bind_rows() %>%
  # then pivot_wider to get final result
  pivot_wider(names_from = LETTER, values_from = value)
#> # A tibble: 3 x 13
#>      ID name  endValue A     B     C     D     E     F     G     H     I    
#>   <int> <chr>    <dbl> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
#> 1     1 Bob          3 Y     Y     Y     N     N     N     N     N     N    
#> 2     2 Jim          7 Y     Y     Y     Y     Y     Y     Y     N     N    
#> 3     3 Fred         4 Y     Y     Y     Y     N     N     N     N     N    
#> # … with 1 more variable: J <chr>

^{Created on 2021-04-27 by the reprex package (v2.0.0)}

Create multiple columns in data frame based on another column

2 Answers2