How to move values into correct column in R?

Question

Beginner here: I have a dataframe that looks like this:

Name   Col_1     Col_2          Col_3      Col_4
abc    Website   https://LINK1  Twitter    https://LINK4
def    Email     https://LINK2  Facebook   https://LINK5
ghj    Document  https://LINK3  Website    https://LINK6

I want to create columns for the values in Col_1 and Col_3 (aka Website, Email, Document, Twitter and Facebook) move the LINKS in Col_2 and Col_4 into the respective columns. The output should look like this:

Names  Website        Email           Document        Twitter        Facebook
abc    https://LINK1  NA              NA              https://LINK4  NA
def    NA             https://LINK2   NA              NA             https://LINK5
ghj    https://LINK6  NA              https://LINK3   NA             NA

How can I achieve this? Thank you!

Answer 1

Here is an option using two times pivot_wider and coalesce once the duplicated website columns like this:

library(dplyr)
library(tidyr)
df %>%
  pivot_wider(names_from = Col_1, values_from = Col_2) %>%
  pivot_wider(names_from = Col_3, values_from = Col_4, names_repair = "unique") %>%
  mutate(Website = coalesce(`Website...2`, `Website...7`)) %>%
  select(-c(`Website...2`, `Website...7`)) 
#> New names:
#> • `Website` -> `Website...2`
#> • `Website` -> `Website...7`
#> # A tibble: 3 × 6
#>   Name  Email         Document      Twitter       Facebook      Website      
#>   <chr> <chr>         <chr>         <chr>         <chr>         <chr>        
#> 1 abc   <NA>          <NA>          https://LINK4 <NA>          https://LINK1
#> 2 def   https://LINK2 <NA>          <NA>          https://LINK5 <NA>         
#> 3 ghj   <NA>          https://LINK3 <NA>          <NA>          https://LINK6

^{Created on 2023-01-02 with reprex v2.0.2}