R function for transposing data into new columns

Question

I have an issue that I have been trying to solve... it has been driving me insane. Essentially, my data produces duplicate rows for each additional "note" rather than ordering the notes in new columns. Essentially, I need this:

ID	Company	Note
1	srental	skiing rentals
1	srental	last used 3/12/14
2	hertz	car rental
3	nike	shoe orders
3	nike	last used 2/14/19
3	nike	www.nike.com

To turn into this:

ID	Company	Note 1	Note 2	Note 3
1	srental	skiing rentals	last used 3/12/14	NA
2	hertz	car rental	NA	NA
3	nike	shoe orders	last used 2/14/19	www.nike.com

I have tried and failed a few times now. Here is my closest attempt:

Pivot the data from long to wide format

data2 <- pivot_wider(data, names_from = Note, values_from = Note, names_prefix = "Note_")

Rename the columns to remove the "Note_" prefix

colnames(data2) <- gsub("Note_", "", seq(col(data2)))
names(data2)[names(data2) == "1"] <- "ID"
names(data2)[names(data2) == "2"] <- "Company"

Print the final result

data2

FINAL RESULT

I would very much appreciate any help!

Answer 1

Use row_number() by group, and then pivot wider:

pivot_wider(
  data %>% group_by(Company) %>% mutate(i=row_number()),
  names_from=i,
  values_from=Note,
  names_prefix = "Note "
)

Output:

     ID Company `Note 1`       `Note 2`          `Note 3`    
  <int> <chr>   <chr>          <chr>             <chr>       
1     1 srental skiing rentals last used 3/12/14 NA          
2     2 hertz   car rental     NA                NA          
3     3 nike    shoe orders    last used 2/14/19 www.nike.com

a more concise alternative, as suggested by @jpsmith:

pivot_wider(
  mutate(data, i=paste("Note",row_number()), .by=Company),
  names_from=i,
  values_from=Note,
)