Merge Columns with different number of rows into one data frame in R

Question

I am trying to dynamically find the number of columns which is imported from an excel sheet and add that to another data frame. But this has to be done dynamically and the numbers of rows in both data frame will be different so cbind() is not working. I am not able to do that. Can someone help me understand, what is it that I am doing wrong.

The data frame I am working with is given below:

dput(Book1)
structure(list(`Row Labels` = structure(c(1667260800, 1669852800, 
1672531200, 1675209600, 1677628800, 1680307200, 1682899200, 1685577600, 
1688169600), class = c("POSIXct", "POSIXt"), tzone = "UTC"), 
    X1 = c(1, 2, 2, 3, 3, 4, 4, 5, 5), X2 = c(2, 3, 2, 3, 2, 
    3, 2, 3, 2), X3 = c(3, 4, 3, 4, 3, 4, 3, 4, 3)), class = c("tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -9L))
dput(Book2)
structure(list(`Row Labels` = structure(c(1667260800, 1669852800, 
1672531200, 1675209600, 1677628800), class = c("POSIXct", "POSIXt"
), tzone = "UTC"), X6 = c(1, 1, 1, 1, 1), X7 = c(1, 1, 1, 1, 
1), X8 = c(1, 1, 1, 1, 1), X9 = c(1, 1, 1, 1, 1)), class = c("tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -5L))

The code which I have written is:

library(readxl)
Book1 <- read_excel("C:/X/X/X/X/Book1.xlsx",sheet = "Sheet1")
View(Book1)
Book2 <- read_excel("C:/X/X/X/X/Book1.xlsx",sheet = "Sheet2")

dput(Book1)
dput(Book2)
ncol(Book2)
colnames(Book2)

cbind(Book1,Book2)

The Expected Output is:

The Error I am getting while using cbind is :

Use `dplyr`: left_join(tableA,tableB,by=”Row Labels”) – Minnow Nov 14 '22 at 16:35 — Minnow, Nov 14 '22 at 16:35

score 1 · Accepted Answer · answered Nov 14 '22 at 16:36

You can use full_join

> Book1 %>% 
+   full_join(Book2, by = "Row Labels")
# A tibble: 9 × 8
  `Row Labels`           X1    X2    X3    X6    X7    X8    X9
  <dttm>              <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 2022-11-01 00:00:00     1     2     3     1     1     1     1
2 2022-12-01 00:00:00     2     3     4     1     1     1     1
3 2023-01-01 00:00:00     2     2     3     1     1     1     1
4 2023-02-01 00:00:00     3     3     4     1     1     1     1
5 2023-03-01 00:00:00     3     2     3     1     1     1     1
6 2023-04-01 00:00:00     4     3     4    NA    NA    NA    NA
7 2023-05-01 00:00:00     4     2     3    NA    NA    NA    NA
8 2023-06-01 00:00:00     5     3     4    NA    NA    NA    NA
9 2023-07-01 00:00:00     5     2     3    NA    NA    NA    NA

Merge Columns with different number of rows into one data frame in R

1 Answers1