CODEWITHSUNDEEP
rdplyrtidyrtibbletidytext
M.Qasim
M.Qasim

Reputation: 1878

Expand tibble of email dataset in R

I have a massive tibble of my email data which looks like the following:

library(dplyr)

emails <- tibble(
  from = c('[email protected]','[email protected]','[email protected]',
           '[email protected]','[email protected]'),
  to = list(
    c('[email protected]', 'employee.3xtra.co'),
    c('[email protected]', '[email protected]'),
    c('[email protected]'),
    c('[email protected]'),
    c('[email protected]','[email protected]','[email protected]')),
  
  cc = list(
    c('employee.2xtra.co', 'employee.4xtra.co', 'employee.6xtra.co'),
    c('employee.1xtra.co', 'employee.8xtra.co', 'employee.6xtra.co'),
    NA,
    c('employee.2xtra.co', 'employee.4xtra.co'),
    c('employee.2xtra.co', 'employee.6xtra.co'))
)

emails

# A tibble: 5 x 3
  from               to        cc       
  <chr>              <list>    <list>   
1 [email protected] <chr [2]> <chr [3]>
2 [email protected] <chr [2]> <chr [3]>
3 [email protected] <chr [1]> <lgl [1]>
4 [email protected] <chr [1]> <chr [2]>
5 [email protected] <chr [3]> <chr [2]>

I need your help to be able to expand each record for each combination. For example, what I want to achieve for row 1 is:

from                to                  cc
[email protected]  [email protected]  employee.2xtra.co
[email protected]  [email protected]  employee.4xtra.co
[email protected]  [email protected]  employee.6xtra.co
[email protected]  employee.3xtra.co   employee.2xtra.co
[email protected]  employee.3xtra.co   employee.4xtra.co
[email protected]  employee.3xtra.co   employee.6xtra.co

Thank you very much for your time.

Upvotes: 2

Views: 34

Answers (1)

www
www

Reputation: 39154

We can apply unnest twice.

library(dplyr)
library(tidyr)

emails2 <- emails %>%
  unnest(cols = "to") %>%
  unnest(cols = "cc")
head(emails2)
# # A tibble: 6 x 3
#   from               to                 cc               
#   <chr>              <chr>              <chr>            
# 1 [email protected] [email protected] employee.2xtra.co
# 2 [email protected] [email protected] employee.4xtra.co
# 3 [email protected] [email protected] employee.6xtra.co
# 4 [email protected] employee.3xtra.co  employee.2xtra.co
# 5 [email protected] employee.3xtra.co  employee.4xtra.co
# 6 [email protected] employee.3xtra.co  employee.6xtra.co

If you have more than two columns to expand, below is one approach. First identify the columns that are list. Store the column names in names_target, and then use a for loop to repeatedly apply the unnest function.

names_target <- emails %>%
  select(where(is.list)) %>%
  names()

temp <- emails

for (i in names_target){
  temp <- temp %>% unnest(cols = all_of(i))
}

identical(temp, emails2)
# [1] TRUE

Upvotes: 3

Related Questions