CODEWITHSUNDEEP
rdataframer-xlsx
samsamara
samsamara

Reputation: 4750

How to write this dataframe to excel (xlsx)?

I have a dataframe of the following format:

enter image description here

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

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?

Upvotes: 0

Views: 627

Answers (2)

Nadir Latif
Nadir Latif

Reputation: 3763

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)

Upvotes: 0

Ronak Shah
Ronak Shah

Reputation: 388982

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.

enter image description here

Upvotes: 4

Related Questions