Merging two data frames with different numbers of observations and matching them-CodePudding

The data frame below presents two data frames that I merged through cbindX(Period1, Period2). Both have the same columns but represent two time periods and have different observations for AEZ.

Example for Abyei and Angola

> dput(new_data2[1:6, c(1,2,3,5,7,8,9,11) ])

structure(list(AEZ_1 = c("Tropics, lowland semi-arid", "Dominantly hydromorphic soils", "Tropics, lowland sub-humid", "Tropics, lowland semi-arid", "Dominantly built-up land", "Dominantly hydromorphic soils"), Country_1 = c("Abyei", "Abyei", "Angola", "Angola", "Angola", "Angola"), File_name_1 = c("PRIO_AEZ_FS_1981_2010", "PRIO_AEZ_FS_1981_2010", "PRIO_AEZ_FS_1981_2010", "PRIO_AEZ_FS_1981_2010", "PRIO_AEZ_FS_1981_2010", "PRIO_AEZ_FS_1981_2010"), Share_1 = c(9418.132755827, 520.625044495, 616817.473747498, 278142.684969026, 1330.4290338252, 74581.3053271609), AEZ_2 = c("Tropics, lowland semi-arid", "Tropics, lowland sub-humid", "Dominantly hydromorphic soils", "Tropics, lowland sub-humid", "Tropics, lowland semi-arid", "Dominantly built-up land"), Country_2 = c("Abyei", "Abyei", "Abyei", "Angola", "Angola", "Angola"), File_name_2 = c("PRIO_AEZ_FS_2011_2040", "PRIO_AEZ_FS_2011_2040", "PRIO_AEZ_FS_2011_2040", "PRIO_AEZ_FS_2011_2040", "PRIO_AEZ_FS_2011_2040", "PRIO_AEZ_FS_2011_2040"), Share_2 = c(8475.525647713, 942.6071081139, 520.625044495, 754641.194306016, 289900.409286599, 1330.4290338252)), row.names = c(NA, 6L), class = "data.frame")

I would like to have matching Country to see the change of AEZ over time.

Image 2

Thanks

CodePudding user response：

Assume you have two data frames (an old and a new one) with country properties:

library(tidyverse)

old <- tribble(
  ~AEZ, ~Country,
  1, "Abyei",
  2, "Angola"
) %>%
  mutate(time = "old")
old
#> # A tibble: 2 x 3
#>     AEZ Country time 
#>   <dbl> <chr>   <chr>
#> 1     1 Abyei   old  
#> 2     2 Angola  old

new <- tribble(
  ~AEZ, ~Country,
  1, "Abyei",
  2, "Angola",
  3, "Angola"
) %>%
  mutate(time = "new")
new
#> # A tibble: 3 x 3
#>     AEZ Country time 
#>   <dbl> <chr>   <chr>
#> 1     1 Abyei   new  
#> 2     2 Angola  new  
#> 3     3 Angola  new

old %>%
  full_join(new) %>%
  pivot_wider(names_from = time, values_from = AEZ) %>%
  unnest(old) %>%
  unnest(new)
#> Joining, by = c("AEZ", "Country", "time")
#> Warning: Values are not uniquely identified; output will contain list-cols.
#> * Use `values_fn = list` to suppress this warning.
#> * Use `values_fn = length` to identify where the duplicates arise
#> * Use `values_fn = {summary_fun}` to summarise duplicates
#> # A tibble: 3 x 3
#>   Country   old   new
#>   <chr>   <dbl> <dbl>
#> 1 Abyei       1     1
#> 2 Angola      2     2
#> 3 Angola      2     3

^{Created on 2021-09-21 by the reprex package (v2.0.1)}

CodePudding user response：

My suggestion is: Rename AEZ variable in the first file (data frame) as AEZ_1981 and the same variable in the second file as AEZ_2011 before merging. This is how you can keep all the information and compare the changes in the merged file.

Best, Lev