How to pivot table in r with multiple (or duplicate) text data?

user_1828605 0
Viewed 670

This is a toy example created based on the data of over 30 mil rows. 

df <- structure(list(id = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L), item = c("Job", "DOB", "organization", "info", "Job", 
"DOB", "organization", "info", "Job", "DOB", "organization", 
"info"), value = c("Assistant", "27395", "ABC", "Inspire others", 
"Project manager", "27395", "CDE", "Inspire others", "Project manager", 
"27395", "CDE", "Inspire others")), class = "data.frame", row.names = c(NA, 
-12L))

I want to create the table with one row per id, but I'm running into an issue with it. 

table %>% pivot_wider(names_from = item, values_from = value)

The code above gives me the following result: 

# A tibble: 1 x 5
     id Job       DOB       organization info     
  <int> <list>    <list>    <list>       <list>   
1     1 <chr [3]> <chr [3]> <chr [3]>    <chr [3]>

So, I tried to combine them as a text with values_fn = list(value = paste), but I get the following error: 

Error in `$<-.data.frame`(`*tmp*`, "val", value = c("Assistant", "Project manager", : replacement has 12 rows, data has 4

What's the best way to tackle such problems where the duplicate entries may be a mixture of character and numeric?

The output I want is the following: 

id  Job                         DOB     organization info
1   Assistant, Project manager  27395   ABC, CDE    Inspire others

the Job has multiple values so, it should be separated by a comma (,), and DOB is the same value so it should remain the same. Similarly, comma separated for organization and single item for info.

dplyr pivot-table r
1 Answers

We need a sequence column to make the rows unique and then use pivot_wider. In the wide format, then we do summarise_at to paste the elements

library(dplyr)
library(data.table)
library(tidyr)
library(stringr)
df %>% 
    mutate(rn = rowid(item)) %>%  
    pivot_wider(names_from = item, values_from = value) %>%
    select(-rn) %>%
    group_by(id) %>%
    summarise_at(vars(-group_cols()), ~ str_c(unique(.), collapse = ", "))
# A tibble: 1 x 5
#     id Job                        DOB   organization info          
#  <int> <chr>                      <chr> <chr>        <chr>         
#1     1 Assistant, Project manager 27395 ABC, CDE     Inspire others

Or another option is to make use of values_fn from pivot_wider

df %>% 
  pivot_wider(names_from = item, values_from = value, 
        values_fn = list(value =  ~ toString(unique(.))))
# A tibble: 1 x 5
#     id Job                        DOB   organization info          
#  <int> <chr>                      <chr> <chr>        <chr>         
#1     1 Assistant, Project manager 27395 ABC, CDE     Inspire others
user_37322710
Related