How to write this dataframe to excel (xlsx)?

Question

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?

Answer 1

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.

Answer 2

Well another option is to convert the original data frame to a list containing unequal number of elements in each column. This list can then be converted to a data frame with padded columns.

The following code can be used:

# The original data frame
n <- c("car", "car", "van", "bus", "bus", "bus")
i <- c(1,2,1,-1,0,1)
v <- c(10,20,15,20,25,25)
df <- data.frame(name = n, index = i, value = v)

# The data frame is converted to a list
l <- list(
    "car" = rep("car", nrow(df[df$name == "car", ])), 
    "car_index" = df[df$name == "car", "index"], 
    "car_value" = df[df$name == "car", "value"], 
    "van" = rep("van", nrow(df[df$name == "van", ])), 
    "van_index" = df[df$name == "van", "index"], 
    "van_value" = df[df$name == "van", "value"], 
    "bus" = rep("bus", nrow(df[df$name == "bus", ])), 
    "bus_index" = df[df$name == "bus", "index"], 
    "bus_value" = df[df$name == "bus", "value"])

# The list column names
cols <- names(l)

# The maximum column length
max_len <- 0
for (col in cols){
    if (length(l[[col]]) > max_len)
        max_len <- length(l[[col]])
}

# Each column is padded
for (col in cols){
    l[[col]] <- c(l[[col]], rep("", max_len - length(l[[col]])))
}

# The list is converted to data frame
df_new <- as.data.frame(l)

# The data frame is written to xlsx file
writexl::write_xlsx(df_new, 'test.xlsx', col_names = FALSE)