Home > Net >  How to bind more than 2 dataframes with different column number in R
How to bind more than 2 dataframes with different column number in R

Time:08-09

I want to bind 4 dataframes. One of them, the "B8A_EVI_EOS_KRR05" has 11 rows while the others have 19. I've used cbind but I get a repetition of "B8A_EVI_EOS_KRR" first rows after the 11th row.

I want to be able to bind the 4 dataframes by ID (choose the ID of B8A_NDVI_EOS_KRR, B8A_NIRv_EOS_KRR or B8A_kNDVI_EOS_KRR) and fill the empty cells with NA.

My 4 dataframes look like this:

dput(B8A_EVI_EOS_KRR05)
structure(list(ID = c("AUR", "AUR", "AUR", "AUR", "AUR", "AUR", 
"P1", "P14", "P15", "P17", "P2"), D_EOS = structure(c(17067, 
17353, 17712, 18082, 18360, 18516, 17714, 17007, 16987, 16988, 
17715), class = "Date"), EVI_EOS = structure(c(17042, 17344, 
17813, 18107, 18385, 18548, 17705, 17144, 17027, 17003, 17827
), class = "Date")), row.names = c(NA, -11L), class = c("data.table", 
"data.frame"), .internal.selfref = <pointer: 0x0000000002745260>)

dput(B8A_NDVI_EOS_KRR05)
structure(list(ID = c("AUR", "AUR", "AUR", "AUR", "AUR", "AUR", 
"LAM", "LAM", "LAM", "LAM", "LAM", "LAM", "P0", "P1", "P14", 
"P15", "P17", "P2", "P3"), D_EOS = structure(c(17067, 17353, 
17712, 18082, 18360, 18516, 17002, 17123, 17414, 17722, 18148, 
18446, 17359, 17714, 17007, 16987, 16988, 17715, 17716), class = "Date"), 
    NDVI_EOS = structure(c(17071, 17379, 17814, 18095, 18384, 
    18577, 16996, 17248, 17501, 17715, 18176, 18461, 17393, 17705, 
    17076, 16994, 17050, 17829, 17755), class = "Date")), row.names = c(NA, 
-19L), class = c("data.table", "data.frame"), .internal.selfref = <pointer: 0x0000000002745260>)

dput(B8A_NIRv_EOS_KRR05)
structure(list(ID = c("AUR", "AUR", "AUR", "AUR", "AUR", "AUR", 
"LAM", "LAM", "LAM", "LAM", "LAM", "LAM", "P0", "P1", "P14", 
"P15", "P17", "P2", "P3"), D_EOS = structure(c(17067, 17353, 
17712, 18082, 18360, 18516, 17002, 17123, 17414, 17722, 18148, 
18446, 17359, 17714, 17007, 16987, 16988, 17715, 17716), class = "Date"), 
    NIRv_EOS = structure(c(17077, 17385, 17810, 18096, 18385, 
    18574, 17085, 17085, 17494, 17709, 18179, 18534, 17387, 17705, 
    17062, 16997, 17027, 17822, 17749), class = "Date")), row.names = c(NA, 
-19L), class = c("data.table", "data.frame"), .internal.selfref = <pointer: 0x0000000002745260>)

dput(B8A_kNDVI_EOS_KRR05)
structure(list(ID = c("AUR", "AUR", "AUR", "AUR", "AUR", "AUR", 
"LAM", "LAM", "LAM", "LAM", "LAM", "LAM", "P0", "P1", "P14", 
"P15", "P17", "P2", "P3"), D_EOS = structure(c(17067, 17353, 
17712, 18082, 18360, 18516, 17002, 17123, 17414, 17722, 18148, 
18446, 17359, 17714, 17007, 16987, 16988, 17715, 17716), class = "Date"), 
    kNDVI_EOS = structure(c(17074, 17380, 17812, 18093, 18385, 
    18569, 16997, 17247, 17487, 17715, 18177, 18454, 17369, 17775, 
    17078, 16991, 17028, 17770, 17742), class = "Date")), row.names = c(NA, 
-19L), class = c("data.table", "data.frame"), .internal.selfref = <pointer: 0x0000000002745260>)`

On the left is an example of how the dataframes looks like when I use cbind and on the right it is shown the desired output.

enter image description here

CodePudding user response:

We may nest the datasets in a list, do a join and then unnest

library(purrr)
library(dplyr)
library(tidyr)
mget(ls(pattern = "^B8A_.*_EOS_KRR05$")) %>% 
   imap(~ .x %>%
     nest(data = -ID) %>% 
     rename(!! .y := data)) %>%
   reduce(full_join, by = "ID") %>% 
   unnest(where(is.list), names_sep = "_")

-output

# A tibble: 19 × 9
   ID    B8A_EVI_EOS_KRR05_D… B8A_EVI_EOS_KRR… B8A_kNDVI_EOS_K… B8A_kNDVI_EOS_K… B8A_NDVI_EOS_KR… B8A_NDVI_EOS_KR… B8A_NIRv_EOS_KR… B8A_NIRv_EOS_KR…
   <chr> <date>               <date>           <date>           <date>           <date>           <date>           <date>           <date>          
 1 AUR   2016-09-23           2016-08-29       2016-09-23       2016-09-30       2016-09-23       2016-09-27       2016-09-23       2016-10-03      
 2 AUR   2017-07-06           2017-06-27       2017-07-06       2017-08-02       2017-07-06       2017-08-01       2017-07-06       2017-08-07      
 3 AUR   2018-06-30           2018-10-09       2018-06-30       2018-10-08       2018-06-30       2018-10-10       2018-06-30       2018-10-06      
 4 AUR   2019-07-05           2019-07-30       2019-07-05       2019-07-16       2019-07-05       2019-07-18       2019-07-05       2019-07-19      
 5 AUR   2020-04-08           2020-05-03       2020-04-08       2020-05-03       2020-04-08       2020-05-02       2020-04-08       2020-05-03      
 6 AUR   2020-09-11           2020-10-13       2020-09-11       2020-11-03       2020-09-11       2020-11-11       2020-09-11       2020-11-08      
 7 P1    2018-07-02           2018-06-23       2018-07-02       2018-09-01       2018-07-02       2018-06-23       2018-07-02       2018-06-23      
 8 P14   2016-07-25           2016-12-09       2016-07-25       2016-10-04       2016-07-25       2016-10-02       2016-07-25       2016-09-18      
 9 P15   2016-07-05           2016-08-14       2016-07-05       2016-07-09       2016-07-05       2016-07-12       2016-07-05       2016-07-15      
10 P17   2016-07-06           2016-07-21       2016-07-06       2016-08-15       2016-07-06       2016-09-06       2016-07-06       2016-08-14      
11 P2    2018-07-03           2018-10-23       2018-07-03       2018-08-27       2018-07-03       2018-10-25       2018-07-03       2018-10-18      
12 LAM   NA                   NA               2016-07-20       2016-07-15       2016-07-20       2016-07-14       2016-07-20       2016-10-11      
13 LAM   NA                   NA               2016-11-18       2017-03-22       2016-11-18       2017-03-23       2016-11-18       2016-10-11      
14 LAM   NA                   NA               2017-09-05       2017-11-17       2017-09-05       2017-12-01       2017-09-05       2017-11-24      
15 LAM   NA                   NA               2018-07-10       2018-07-03       2018-07-10       2018-07-03       2018-07-10       2018-06-27      
16 LAM   NA                   NA               2019-09-09       2019-10-08       2019-09-09       2019-10-07       2019-09-09       2019-10-10      
17 LAM   NA                   NA               2020-07-03       2020-07-11       2020-07-03       2020-07-18       2020-07-03       2020-09-29      
18 P0    NA                   NA               2017-07-12       2017-07-22       2017-07-12       2017-08-15       2017-07-12       2017-08-09      
19 P3    NA                   NA               2018-07-04       2018-07-30       2018-07-04       2018-08-12       2018-07-04       2018-08-06      
  • Related