Binding dataframes with different length to an Excel file in separate columns

Question

The data frame are not having same number of rows. The datasets are uneven So how to add the data frame horizontally in separate column of single sheets?

Below is the code I am using to write separate data frame for sample data

 files <- list.files(pattern= '*.shp')
 
    a<- read_sf("fire_archive_M6_139248.shp")
    b<- read_sf("fire_nrt_M6_139248.shp")
     aa = as.data.frame(a$ACQ_DATE)
    bb = as.data.frame(b$ACQ_DATE)
    cc = as.data.frame(c$ACQ_DATE)
     library("writexl")
    setwd("D:/fire_work_cov/test3/csv")
    write_xlsx(aa,"2020a.xlsx")
    write_xlsx(bb,"2020b.xlsx")
    write_xlsx(cc,"2019.xlsx")

Below is the head of aa and bb (the length of full head is different for each data)

> head(aa)
  a$ACQ_DATE
1 2020-03-02
2 2020-03-02
3 2020-03-02
4 2020-03-02
5 2020-03-02
6 2020-03-03
> bb = as.data.frame(b$ACQ_DATE)
> head(bb)
  b$ACQ_DATE
1 2020-04-01
2 2020-04-01
3 2020-04-01
4 2020-04-01
5 2020-04-01
6 2020-04-01
> head(cc)
  c$ACQ_DATE
1 2019-03-01
2 2019-03-01
3 2019-03-01
4 2019-03-01
5 2019-03-02
6 2019-03-02

Please use `dput(head(aa))`, `dput(head(bb))` and `dput(head(cc))` and include this in your question so that we can see the shape of the data. — Andrew Chisholm, Jul 20 '20 at 11:08
Could you post the result of this command `dput(head(aa))`? This will allow others to more easily import your data. See this for more details https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — Andrew Chisholm, Jul 20 '20 at 13:00

M-- · Accepted Answer · 2020-07-21T22:08:44.960

1

You can use rowr::cbind.fill:

### Creating a sample dataset ###
df1 <- data.frame(col = c(1, 2, 3))
df2 <- data.frame(col = c(5, 6))
df3 <- data.frame(col = c(4, 4, 9, 10))
df <- list(df1, df2, df3)

### Loading required library ###
library(rowr) ## Not available for R 4.0.2

### binding the columns for the list of dataframes ###
#### using do.call to apply cbind.fill on a list of dataframes
df.e <- do.call(cbind.fill, c(df, fill=NA))

### writing to csv or excel file ###
#### setting NA-string to "" to have empty cells
#### setting writing row.names to false
write.csv(df.e, "D:\\test.csv", na = "", row.names = FALSE)

Or as @akrun suggested, in base we can do something like this:

mx <- max(sapply(df, nrow)) 

do.call(cbind, lapply(df, function(x) {rbind(x, x[seq_len(mx) > nrow(x),, drop = FALSE])}))

edited Jul 21 '20 at 22:08

answered Jul 20 '20 at 20:45

M--

25,431
8
61
93

1

`rowr` seems to be not available for R 4.0.2 – akrun Jul 21 '20 at 21:55
I think the `do.call(cbind.fill, c(df, list(fill = NA)))` should work. I couldn't test it because of the package not available – akrun Jul 21 '20 at 21:58
In base R, you could do `mx <- max(sapply(df, nrow)); do.call(cbind, lapply(df, function(x) {rbind(x, x[seq_len(mx) > nrow(x),, drop = FALSE])}))` – akrun Jul 21 '20 at 22:05
You could also add the base R version as the `rowr` is no longer in CRAN. thanks – akrun Jul 21 '20 at 22:05

Binding dataframes with different length to an Excel file in separate columns

1 Answers1