-2

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

Myr TH
  • 175
  • 1
  • 9
  • 3
    share sample data run this `dput('yourdf')` and paste output here. – Daman deep Sep 21 '21 at 11:35
  • It would be easier to help if you create a small reproducible example along with expected output. Read about [how to give a reproducible example](http://stackoverflow.com/questions/5963269). Images are not the right way to share data/code. – Ronak Shah Sep 21 '21 at 12:10
  • Please provide enough code so others can better understand or reproduce the problem. – Community Sep 25 '21 at 20:48

3 Answers3

0

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)

danlooo
  • 10,067
  • 2
  • 8
  • 22
  • Hi Danloo, thank you ! As I have 48 countries, do you have suggestion to number each country quickly? – Myr TH Sep 21 '21 at 16:34
  • You probably have the tables stored in a file. Replace Tribble with e.g. read_csv. I did this only to provide example data. What do you mean with numbering countries? – danlooo Sep 21 '21 at 17:01
0

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

Lev
  • 1
  • 1
0

If it helps, I figure out how to do it:

new_data<-merge(Period1, Period2, by.x=c("Country", "AEZ"), by.y=c("Country", "AEZ"), all= TRUE)
Myr TH
  • 175
  • 1
  • 9