How to collapse duplicate rows/columns when using pivot_wider?

bjones22 0
Viewed 30

I am working with a dataset that includes case numbers and employee names. Sometimes multiple employee names are tied to a single case number, or an employee may show attached to multiple case numbers. This shows on a series of rows like so:

Case.Number Employee Name
001 Name 1
001 Name 2
002 Name 3
003 Name 2

I'm using pivot_wider to rearrange this dataset from long to wide, but as this is a very large dataset it generates 100+ additional columns to account for all the Employee Names.

Is there a way to instead have only a few additional columns generated, into which any Employee Names associated with a Case.Number will populate? Intended output something like this:

Case.Number Employee Name 1 Employee Name 2 Employee Name 3
001 Name 1 Name 2 NA
002 Name 3 NA NA
003 Name 2 NA NA

Is this possible?

tidyverse r
1 Answers

I believe this is what you would like.

library(dplyr)
library(tidyr)

Case.Number   <- c('001', '001', '001', '001', '002', '002', '003', '003')
Employee.Name <- c('Frank', 'Bosu', 'Clara', 'Zulu', 'Frank', 'Zulu', 'Clara', 'Zulu')

frm1 <- data.frame(cbind(Case.Number,Employee.Name))
frm2 <- frm1 %>% 
  ## Each Employee Name should appear only once per Case?
  distinct(Case.Number, Employee.Name) %>% 
  group_by(Case.Number) %>% 
  mutate(Name_Count = row_number()) %>% 
  mutate(Name_Prefix = paste0('Name ', Name_Count)) %>% 
  ungroup()

# Notice drop of Name_Count column, necessary to avoid extra rows
pivot_wider(frm2 %>% select(-Name_Count),
            names_from  = Name_Prefix,
            names_sort  = TRUE,
            values_from = Employee.Name)

Shows names in Name columns per Case using pivot_wider function

@akrun's solution in the comment above used data.table::rowid(). Same result.

frm2 %>% 
  select(-Name_Count, -Name_Prefix) %>% 
  mutate(rn = data.table::rowid(Case.Number)) %>% 
  pivot_wider(names_from = rn, 
              names_prefix = 'Employee Name ',
              values_from = Employee.Name)
Rick Pack0
Related