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

Question

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.

Do you want to join each first list of ndvi.mon1 with the first list of precipitation, the second with the second..etc? — Julian
– Julian, Commented May 1, 2022 at 15:50
Hi Julian. Yes, for example, the ndvi-mon1[1] with precipitation[1] and so on. — Marcio Cure
– Marcio Cure, Commented May 1, 2022 at 15:58

Julian · Accepted Answer · 2022-05-01 15:58:07Z

1

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

answered May 1, 2022 at 15:58

Julian

9,6252 gold badges20 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Marcio Cure Over a year ago

Thank you, Julian, but your solution did not work. I got the following error messages: Error in .f(.x[[i]], ...) : unused argument (precipitation = 2); and Error in .f(.x[[i]], ...) : unused argument (ndvi = 2)

Julian Over a year ago

That weird, my code works for your sample data. Does the rename worked as expected?

Marcio Cure Over a year ago

Your code worked now! I put dplyr::rename. Thank you so much. :)

langtang · Accepted Answer · 2022-05-01 15:58:34Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related