How to write this dataframe to excel (xlsx)?

I have a dataframe of the following format:

What i want to do is write it to a .xlsx file as below (everything in one sheet):

Not fussed about the column names in the Excel file. Tried subsetting by name and using cbind() but it doesn't work as they have varying lengths. Any ideas on how to achieve this?

Solution

Here is one way -

Split the data by name in list of dataframes.
Get the max number of rows from the list.
Append NA's to the dataframe with less number of rows and combine them together.
Write to Excel with no column names.

df <- data.frame(name = c('car', 'car', 'van', 'bus', 'bus', 'bus'), 
                 index = c(1, 2, 1, -1, 0, 1), 
                 value = c(10, 20, 15, 20, 25, 25))

tmp <- split(df, df$name)
n <- 1:max(sapply(tmp, nrow))

writexl::write_xlsx(do.call(cbind, lapply(tmp, `[`, n, )), 
                    'result.xlsx', col_names = FALSE)

This is how it looks in Excel.