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.
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