I've got a list of names of phone numbers, which I want to group by name, and bring them from a long format to a wide one, with the phone number filling across the columns
Name Phone_Number John Doe 0123456 John Doe 0123457 John Doe 0123458 Jim Doe 0123459 Jim Doe 0123450 Jane Doe 0123451 Jill Doe 0123457 Name Phone_Number1 Phone_Number2 Phone_Number3 John Doe 0123456 0123457 0123458 Jim Doe 0123459 0123450 NA Jane Doe 0123451 NA NA Jill Doe NA NA NA
library(dplyr)
library(tidyr)
library(data.table)
df <- data.frame(Name = c("John Doe", "John Doe", "John Doe", "Jim Doe", "Jim Doe", "Jane Doe", "Jill Doe" ),
Phone_Number = c("0123456", "0123457","0123458", "0123459", "0123450","0123451", NA))
df1 <- data.frame(Name = c("John Doe","Jim Doe", "Jane Doe", "Jill Doe" ),
Phone_Number1 = c("0123456", "0123459", "0123451", NA),
Phone_Number2 = c("0123457", "0123450", NA, NA),
Phone_Number3 = c("0123458", NA, NA, NA))
I've tried a range of permutations, but what I'm doing wrong just isn't clicking. I'm guessing it's to do with how to specify they key/value pairs properly. The closest I've got is the with the code below:
tidyr::spread
df %>%
group_by(Name) %>%
mutate(id = row_number()) %>%
spread(Name, Phone_Number) %>%
select(-id)
data.table::dcast
df%>%
dcast(Name + Phone_Number ~ Phone_Number, value.var = "Phone_Number")
You don't want to add a row number (index for the whole data) but instead add the group index with the helper function n(), which represents the number of observations in each group in a grouped_df. Then the spreading should go smoothly...
df %>% group_by(Name) %>%
mutate(group_index = 1:n() %>% paste0("phone_", .)) %>%
spread(group_index, Phone_Number)
# A tibble: 4 x 4
# Groups: Name [4]
Name phone_1 phone_2 phone_3
<fctr> <fctr> <fctr> <fctr>
1 Jane Doe 0123451 <NA> <NA>
2 Jill Doe <NA> <NA> <NA>
3 Jim Doe 0123459 0123450 <NA>
4 John Doe 0123456 0123457 0123458