0

I have a question when I was trying to arrange the data. I have a data frame like below: 

ID price  location
1  10.2    A
2   9.0    B
2   9.0    C
3   8.5    F
3   8.5    G

For each unique ID, all the columns are the same except for the location. I want to pivot the table to be like this: 

ID price  location
1  10.2    A
2   9.0    B C
3   8.5    F G

I want to remove the duplicate IDs and move different location together in one column. I've tried pivot_wider() function, but it didn't work out. I'd appreciate it if someone can help. Thanks!

Stella
  • 65
  • 4

1 Answers1

1

You can use mutate and nest() 

library(tidyverse)

dfr%>%group_by(ID, price)%>%nest()%>%
   mutate(location = map(data, ~select(.x, location)))%>%
   select(ID, price, location)%>%ungroup()

# A tibble: 3 x 3
     ID price location        
  <dbl> <dbl> <list>          
1     1  10.2 <tibble [1 × 1]>
2     2   9   <tibble [2 × 1]>
3     3   8.5 <tibble [2 × 1]>

update:

Use this:

dfr%>%group_by(ID, price)%>%nest()%>%
  mutate(location = map(data, ~stringi::stri_paste(.x$location,collapse=',')))%>%
  unnest(location)%>%
  select(ID, price, location)%>%ungroup()

# A tibble: 3 x 3
     ID price location
  <dbl> <dbl> <chr>   
1     1  10.2 A       
2     2   9   B,C     
3     3   8.5 F,G

A much simpler way would be :

dfr%>%group_by(ID, price)%>%
  summarise(location = stringi::stri_paste(location,collapse=','))

# A tibble: 3 x 3
# Groups:   ID [3]
     ID price location
  <dbl> <dbl> <chr>       
1     1  10.2 A           
2     2   9   B,C         
3     3   8.5 F,G
Kay
  • 2,057
  • 3
  • 20
  • 29
  • how about simplifying to `dfr %>% group_by(ID, price) %>% nest()`? – stefan Apr 18 '20 at 17:45
  • Thanks! I just tried, but it looks like the location column is now tibbles? I used view() function to see to data frame, it looks the same as I done that before. Is there any way to change the column type to char? – Stella Apr 18 '20 at 17:48
  • See my updated answer. It all depends on what you want to do with the result - whether you want to keep some variables or not. In the answer that uses `summarise` try replacing `summarise` with `mutate` and see what happens. – Kay Apr 18 '20 at 18:49