CODEWITHSUNDEEP
rdplyr
Forge
Forge

Reputation: 1677

show all other appearances by key in a new column of data frame

A dataframe with clients and purchases

id  name  department
1   joe    fruit
2   peter  meat
3   joe    warehouse
4   marge  fruit
5   marge  meat
6   joe    pharmacy

I need a dataframe putting a row for every coincidence of the customer this way:

joe  fruit  joe warehouse  
joe  fruit  joe  pharmacy
peter meat
marge fruit marge meat

For every customer.

Tried dplyr::join and merge but to no avail

Upvotes: 0

Views: 23

Answers (1)

Axeman
Axeman

Reputation: 35307

How about:

full_join(
  df %>% group_by(name) %>% slice(1),
  df %>% group_by(name) %>% slice(-1),
  'name'
)

Gives:

# A tibble: 4 x 5
# Groups:   name [?]
   id.x name  department.x  id.y department.y
  <int> <chr> <chr>        <int> <chr>       
1     1 joe   fruit            3 warehouse   
2     1 joe   fruit            6 pharmacy    
3     4 marge fruit            5 meat        
4     2 peter meat            NA NA

Or simply 

df2 <- df %>% group_by(name) %>% select(-id)
full_join(slice(df2, 1), slice(df2, -1), 'name')

# A tibble: 4 x 3
# Groups:   name [?]
  name  department.x department.y
  <chr> <chr>        <chr>       
1 joe   fruit        warehouse   
2 joe   fruit        pharmacy    
3 marge fruit        meat        
4 peter meat         NA

Upvotes: 2

Related Questions