Home > OS >  How can I join elements (columns from dataframes) from two lists by row names using R?
How can I join elements (columns from dataframes) from two lists by row names using R?

Time:05-02

I need help please. I have two lists: the first contains ndvi time series for distinct points, the second contains precipitation time series for the same plots (plots are in the same order in the two lists).

I need to combine the two lists. I want to add the column called precipitation from one list to the corresponding ndvi column from the other list respecting the dates (represented here by letters in the row names) to a posterior analises of correlation between columns. However, both time series of ndvi and precipitation have distinct lenghts and distinct dates.

I created the two lists to be used as example of my dataset. However, in my actual dataset the row names are monthly dates in the format "%Y-%m-%d".

library(tidyverse)

set.seed(100)

# First variable is ndvi.mon1 (monthly ndvi)
ndvi.mon1 <- vector("list", length = 3)
for (i in seq_along(ndvi.mon1)) {
  aux <- data.frame(ndvi = sample(randu$x,
                                  sample(c(seq(1,20, 1)),1),
                                  replace = T))
  
  ndvi.mon1[i] <- aux
  ndvi.mon1 <- ndvi.mon1 %>% map(data.frame)
  rownames(ndvi.mon1[[i]]) <- sample(letters, size=seq(letters[1:as.numeric(aux %>% map(length))]) %>% length)
}

# Second variable is precipitation
precipitation <- vector("list", length = 3)
for (i in seq_along(ndvi.mon1)){
  prec_aux <- data.frame(precipitation = sample(randu$x*500,
                                       26,
                                       replace = T))
  row.names(prec_aux) <-  seq(letters[1:as.numeric(prec_aux %>% map(length))])
  
  precipitation[i] <- prec_aux
  precipitation <- precipitation %>% map(data.frame)
  rownames(precipitation[[i]]) <- letters[1:(as.numeric(precipitation[i] %>% map(dim) %>% map(first)))]  
}

Can someone help me please?

Thank you!!!

Marcio.

CodePudding user response:

Maybe like this?

library(dplyr)
library(purrr)

precipitation2 <- precipitation %>% 
  map(rownames_to_column) %>% 
  map(rename, precipitation = 2)

ndvi.mon2 <- ndvi.mon1 %>% 
  map(rownames_to_column) %>% 
  map(rename, ndvi = 2)


purrr::map2(ndvi.mon2, precipitation2, left_join, by = "rowname")

    [[1]]
   rowname     ndvi precipitation
1        k 0.354886      209.7415
2        x 0.596309      103.3700
3        r 0.978769      403.8775
4        l 0.322291      354.2630
5        c 0.831722      348.9390
6        s 0.973205      273.6030
7        h 0.949827      218.6430
8        y 0.443353       61.9310
9        b 0.826368        8.3290
10       d 0.337308      291.2110

CodePudding user response:

The below will return a list of data.frames, that have been merged, using rownames:

lapply(seq_along(ndvi.mon1), function(i) {
  merge(
    x = data.frame(date = rownames(ndvi.mon1[[i]]), ndvi = ndvi.mon1[[i]][,1]),
    y = data.frame(date = rownames(precipitation[[i]]), precip = precipitation[[i]][,1]),
    by="date"
  )
})

Output:

[[1]]
   date     ndvi   precip
1     b 0.826368   8.3290
2     c 0.831722 348.9390
3     d 0.337308 291.2110
4     h 0.949827 218.6430
5     k 0.354886 209.7415
6     l 0.322291 354.2630
7     r 0.978769 403.8775
8     s 0.973205 273.6030
9     x 0.596309 103.3700
10    y 0.443353  61.9310

[[2]]
  date     ndvi   precip
1    g 0.415824 283.9335
2    k 0.573737 311.8785
3    p 0.582422 354.2630
4    y 0.952495 495.4340

[[3]]
   date     ndvi   precip
1     b 0.656463 332.5700
2     c 0.347482  94.7870
3     d 0.215425 431.3770
4     e 0.063100 499.2245
5     f 0.419460 304.5190
6     g 0.712057 226.7125
7     h 0.666700 284.9645
8     i 0.778547 182.0295
9     k 0.902520  82.5515
10    l 0.593219 430.6630
11    m 0.788715 443.5345
12    n 0.347482 132.3950
13    q 0.719538  79.1835
14    r 0.911370 100.7025
15    s 0.258743 309.3575
16    t 0.940644 142.3725
17    u 0.626980 335.4360
18    v 0.167640 390.4915
19    w 0.826368  63.3760
20    x 0.937211 439.8685
  • Related