Combine two dataframes into one by intercalating the columns of each one of the two original dataframes

Question

So i have two dataframes, df1 and df2, both have the same number of columns (and with the same name) but different number of rows. I want to combine them into one big dataframe in which the first column is the first column of df1, the second column is the first column of df2, the third column is the second column of df1, the fourth column is the second column of df2, and so on.

Answer 1

Using the built in BOD data frame construct sample df1 and df2 inputs.

Then iterating over the columns convert the jth column of each to a ts series (since ts series can be cbind'ed even with different numbers of rows) and then cbind them and convert that to a data frame. Finally give it nicer names. No packages are used.

# test data
df1 <- BOD  # 6x2 data frame w Time and demand col names
df2 <- head(10 * BOD, 3) # 3x2 data frame w same names

nc <- ncol(df1)
out <- do.call("data.frame", lapply(1:nc, function(j) cbind(ts(df1[,j]), ts(df2[,j]))))
names(out) <- make.names(rep(names(df1), each = 2), unique = TRUE)

out

giving:

  Time Time.1 demand demand.1
1    1     10    8.3       83
2    2     20   10.3      103
3    3     30   19.0      190
4    4     NA   16.0       NA
5    5     NA   15.6       NA
6    7     NA   19.8       NA

Answer 2

A base solution using iris dataset.

First, rename the columns to have a the columns named in sequence. Second, having two data frames, df1 and df2, create a dummy variable in both of them that will serve as a key. Third, left-join df1 and df2 based on dummy(merge with all.x = TRUE argument). Fourth, remove dummy, Five, reorder your columns.

df <- iris
names(df) <- paste0("column",c("A", "B", "C", "D", "E"))
head(df)
df1 <- df[1:10,] #first data frame
df2 <- df[11:16,] #second data frame different number of rows
df1$dummy <- 1:nrow(df1) #Creating a dummy variable for merging
df2$dummy <- 1:nrow(df2) #Creating a dummy variable for merging

result <- base::merge(df1, df2, by  = "dummy", all.x = TRUE) #merging per dummy
result$dummy<- NULL # I don't need dummy anymore

result[,sort(names(result))] #Your result

#Output
   columnA.x columnA.y columnB.x columnB.y columnC.x columnC.y columnD.x columnD.y columnE.x columnE.y
1        5.1       5.4       3.5       3.7       1.4       1.5       0.2       0.2    setosa    setosa
2        4.9       4.8       3.0       3.4       1.4       1.6       0.2       0.2    setosa    setosa
3        4.7       4.8       3.2       3.0       1.3       1.4       0.2       0.1    setosa    setosa
4        4.6       4.3       3.1       3.0       1.5       1.1       0.2       0.1    setosa    setosa
5        5.0       5.8       3.6       4.0       1.4       1.2       0.2       0.2    setosa    setosa
6        5.4       5.7       3.9       4.4       1.7       1.5       0.4       0.4    setosa    setosa
7        4.6        NA       3.4        NA       1.4        NA       0.3        NA    setosa      <NA>
8        5.0        NA       3.4        NA       1.5        NA       0.2        NA    setosa      <NA>
9        4.4        NA       2.9        NA       1.4        NA       0.2        NA    setosa      <NA>
10       4.9        NA       3.1        NA       1.5        NA       0.1        NA    setosa      <NA>

Answer 3

# load packages
library(tidyverse)

# define function to allow cbinding DFs of different length
# taken from: https://stackoverflow.com/a/7962286
cbind.fill <- function(...){
  nm <- list(...) 
  nm <- lapply(nm, as.matrix)
  n <- max(sapply(nm, nrow)) 
  do.call(cbind, lapply(nm, function (x) 
    rbind(x, matrix(, n-nrow(x), ncol(x))))) 
}

# make some sample DFs
df1 <- data.frame(a = 1:3, b = 4:6)
df2 <- data.frame(a = 1:5, b = 6:10)

# merge them and reorder collate col positions
cbind.fill(df1, df2) %>% 
  as_tibble(.name_repair = "unique") %>%
  select(c(seq(1, ncol(.), 2), seq(2, ncol(.), 2)))
#> New names:
#> * a -> a...1
#> * b -> b...2
#> * a -> a...3
#> * b -> b...4
#> # A tibble: 5 x 4
#>   a...1 a...3 b...2 b...4
#>   <int> <int> <int> <int>
#> 1     1     1     4     6
#> 2     2     2     5     7
#> 3     3     3     6     8
#> 4    NA     4    NA     9
#> 5    NA     5    NA    10

^{Created on 2021-03-25 by the reprex package (v1.0.0)}

Answer 4

Here's a step by step guide using the iris dataset

# Use iris dataframe used for both df1 and df2
df1 <- iris 
df2 <- iris

tot_cols <- ncol(df1) + ncol(df2)
tot_rows <- nrow(df1) + nrow(df2)

# Create empty output data.frame with desired number of rows and cols
df <- data.frame(matrix(ncol=tot_cols, nrow=tot_rows))

# Assign columns to output data.frame
df[, c(seq(1, tot_cols, 2))] <- df1
df[, seq(2, tot_cols, 2)] <- df2

# Assign columns to output data.frame
colnames(df) <- c(rbind(colnames(df1), colnames(df2)))

# View output
head(df)

#   Sepal.Length Sepal.Length Sepal.Width Sepal.Width Petal.Length Petal.Length Petal.Width Petal.Width Species Species
# 1          5.1          5.1         3.5         3.5          1.4          1.4         0.2         0.2  setosa  setosa
# 2          4.9          4.9         3.0         3.0          1.4          1.4         0.2         0.2  setosa  setosa
# 3          4.7          4.7         3.2         3.2          1.3          1.3         0.2         0.2  setosa  setosa
# 4          4.6          4.6         3.1         3.1          1.5          1.5         0.2         0.2  setosa  setosa
# 5          5.0          5.0         3.6         3.6          1.4          1.4         0.2         0.2  setosa  setosa
# 6          5.4          5.4         3.9         3.9          1.7          1.7         0.4         0.4  setosa  setosa

Reference:

• See How to merge 2 vectors alternating indexes? for more info on why we use rbind().