CODEWITHSUNDEEP
rdataframemergechronological
CNiessen
CNiessen

Reputation: 89

R - aggregate multiple data frames based on chronologically ordered data

I have several data frames that contain information on temporarily ordered cases and one supplementary variable. I would like to merge the data frames in order to obtain a single frame in which cases are still ordered chronologically and associated to the correct variable value.

A minimal working example would be:

Cases <- c("Flanders", "Flanders", "Wallonia", "Wallonia")
From <- c(1830, 1900, 1830, 1950)
To   <- c(1899, 2020, 1949, 2020)
Variable1 <- c(0, 1, 0, 1)
Variable1.df <- data.frame(Cases, From, To, Variable1)

Cases <- c("Flanders", "Flanders", "Flanders", "Wallonia", "Wallonia", "Wallonia")
From <- c(1830, 1871, 1946, 1830, 1931, 1976)
To   <- c(1870, 1945, 2020, 1930, 1975, 2020)
Variable2 <- c(0, 1, 0, 0, 0, 1)
Variable2.df <- data.frame(Cases, From, To, Variable2)

Cases <- c("Flanders", "Flanders", "Flanders", "Flanders", "Flanders", "Wallonia", "Wallonia", "Wallonia", "Wallonia", "Wallonia")
From <- c(1830, 1850, 1900, 1950, 2000, 1830, 1850, 1900, 1950, 2010)
To   <- c(1849, 1899, 1949, 1999, 2020, 1849, 1899, 1949, 2009, 2020)
Variable3 <- c(0, 1, 0, 0, 1, 0, 1, 0, 0, 1)
Variable3.df <- data.frame(Cases, From, To, Variable3)

The output I am looking for would then be:

Cases     From  To    Variable1  Variable2  Variable3
Flanders  1830  1849  0          0          0
Flanders  1850  1870  0          0          1
Flanders  1871  1899  0          1          1
Flanders  1900  1945  1          1          0
Flanders  1946  1999  1          0          0
Flanders  2000  2020  1          0          1
...
Wallonia  2010  2020  1          1          1

Since I have to repeat the operation several times with a changing number of data frames to merge, I would like to create a function. Could someone help me with this? My sincere thanks in advance.

Upvotes: 0

Views: 77

Answers (2)

akrun
akrun

Reputation: 887711

We can use tidyverse methods

library(dplyr)
library(purrr)
library(tidyr)
mget(ls(pattern = "\\.df")) %>%
    reduce(full_join) %>% 
    mutate(across(everything(), replace_na, 0))

Upvotes: 0

Duck
Duck

Reputation: 39613

Maybe are you looking for this:

library(dplyr)
#Extract data into a list
v1 <- ls(pattern = '\\.df')
#Store in a list
List <- mget(v1)
#Merge
Merged <- Reduce(function(x, y) full_join(x, y),List) %>% replace(is.na(.),0)

Upvotes: 0

Related Questions