Merge data frames: Add new levels to factor and match values to newly added factors

user_8061255 0
Viewed 29

I have the following data frames:

df1<- data.frame( year= c(rep("2009", 8), rep("2010", 8)), 
                 elev_cat= c(rep("low",4), rep("high",4), rep("low",4), rep("high",4)), 
                 clim_var=c(rep("ppt", 2),rep("tmax", 2), rep("ppt", 2),rep("tmax", 2), rep("ppt", 2),rep("tmax", 2), rep("ppt", 2),rep("tmax", 2)),
                       div_var=c("lep_div", "psit_div", "lep_div", "psit_div", "lep_div", "psit_div","lep_div", "psit_div", "lep_div", "psit_div", "lep_div", "psit_div", "lep_div", "psit_div","lep_div", "psit_div"),
                       div_value=c(5,4,3,2, 5,4,3,2, 8,9,10,11, 8,9,10,11), 
                       clim_value=c(1.5, 1.5, 3.7, 3.7, 3.4, 3.4, 2.5, 2.5,2.1,2.1,3.1,3.1,4,4,5,5 ))


df2<- data.frame(year=c(rep("2009",4), rep("2010", 4)), 
                     elev_cat= rep(c("low", "high"), 4), 
                     clim_var=rep(c("anom1", "anom1", "anom2", "anom2"),2), 
                     clim_value=c(1,2,3.4, 5, 2.17, 3.7, 8.9, 4.5))

df1 contains diversity values div_value for various levels of div_cat for each year and elevation category elev_cat. Diversity values remain constant for a given level of year, elev_cat and div_cat. Climate variables are represented by clim_var. Corresponding variables for clim_var are in clim_value. Annual climate averages remain constant for each year and elevation.

I want to add df2 to df1. df2 has additional climate variables for each year and elev_cat, these values need to be added in such as way that they are repeated for each level of div_cat and its corresponding div_value.

I have tried:

ex<- merge(df1, df2, by=c("year","elev_cat"))

however, this adds df2 information as columns (ie. clim_var.y and clim_var.x).

The expected result data frame should appear as follows:

expected_result<- data.frame( year= c(rep("2009", 16), rep("2010", 16)), 
                          elev_cat= rep(c("low", "low", "high", "high"),8), 
                          clim_var=c(rep("ppt", 4),rep("tmax", 4), rep("anom1", 4),rep("anom2", 4),rep("ppt", 4),rep("tmax", 4), rep("anom1", 4),rep("anom2", 4)), 
                          div_var=rep(c("lep_div", "psit_div", "lep_div", "psit_div"), 8),
                          div_value=c(rep(c(5,4,3,2),4), rep(c(8,9,10,11),4)), 
                          clim_value=c(1.5, 1.5, 3.7,3.7,3.4,3.4,2.5,2.5,1,1,2,2,
                                 3.4, 3.4, 5, 5,2.17,2.17,3.7,3.7, 8.9,8.9,
                                 4.5,4.5,2.1,2.1,3.1,3.1,4,4,5,5))
r merge
1 Answers

May be we can group_split by 'div_var', loop over the list of datasets, bind with 'df2', and then do a group by 'year', 'elev_cat' and fill the columns 'div_var', 'div_value' NAs with the previous non-NA value

library(dplyr)
library(purrr)
library(tidyr)
df1 %>%
    group_split(div_var) %>%
    map_dfr(~ bind_rows(., df2)  %>% 
    group_by(year, elev_cat) %>% 
    fill(div_var, div_value)) %>%
    ungroup
user_37322710
Related