I need some help with my dataset, where I have to detect transition through time. I think I can built something with if_else statements but it could be very long and complicated. I am sure there's a shortcut.
My dataset looks like:
df <- tibble ("FID" = c(1,2,3,4,5),
"CCSC87"= c(NA, NA,"Boscos d'aciculifolis", NA, "Boscos de caducifolis"),
"CCSC92"= c(NA,"Boscos d'aciculifolis","Matollars",NA,"Bosquines i prats"),
"CCSC97"= c(NA,"Zones cremades", "Matollars","Boscos d'aciculifolis","Bosquines i prats"),
"CCSC02"= c(NA,"Matollars", "Matollars", "Matollars", "Bosquines i prats"),
"CCSC07"= c("Boscos d'escleròfil·les","Boscos d'aciculifolis", NA,"Matollars",NA),
"CCSC12"= c("Matollars",NA,NA,"Boscos d'escleròfil·les",NA),
"CCSC17"= c("Bosquines i prats",NA,NA,NA,NA),
"CCSC20"= c("Boscos d'escleròfil·les", NA, NA,NA,NA))
> df
# A tibble: 5 x 9
FID CCSC87 CCSC92 CCSC97 CCSC02 CCSC07 CCSC12 CCSC17 CCSC20
<dbl> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
1 1 NA NA NA NA Boscos d'escler… Matollars Bosquines i… Boscos d'escler…
2 2 NA Boscos d'acicul… Zones cremades Matollars Boscos d'acicul… NA NA NA
3 3 Boscos d'acicul… Matollars Matollars Matollars NA NA NA NA
4 4 NA NA Boscos d'acicul… Matollars Matollars Boscos d'escler… NA NA
5 5 Boscos de caduc… Bosquines i pra… Bosquines i pra… Bosquines i… NA NA NA NA
As you can see I have different columns, which are Land Cover classifications, from 1987, 1992, 1997, 2002, 2007, 2012, 2017 and 2020.
For each plot (FID=1,2...) I have data from 4 columns of Land Cover, and the other columns al filled with NA's.
To simplify, my data could also be visualized like:
df <- tibble ("FID" = c(1,2,3,4,5),
"CCSC87"= c(NA, NA,"A", NA, "C"),
"CCSC92"= c(NA,"A","E",NA,"F"),
"CCSC97"= c(NA,"D", "E","A","F"),
"CCSC02"= c(NA,"E", "E", "E", "F"),
"CCSC07"= c("B","A", NA,"E",NA),
"CCSC12"= c("E",NA,NA,"B",NA),
"CCSC17"= c("F",NA,NA,NA,NA),
"CCSC20"= c("B", NA, NA,NA,NA))
> df
# A tibble: 5 x 9
FID CCSC87 CCSC92 CCSC97 CCSC02 CCSC07 CCSC12 CCSC17 CCSC20
<dbl> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
1 1 NA NA NA NA B E F B
2 2 NA A D E A NA NA NA
3 3 A E E E NA NA NA NA
4 4 NA NA A E E B NA NA
5 5 C F F F NA NA NA NA
What I need is to compute an extra column that tells me if the Land Cover has changed from the first year I have data to the last year. For example, in FID=1, I would like to check if CCSC07 and CCSC20 are different and if they are what is the transition.
My output should look like:
> df_done
# A tibble: 5 x 10
FID CCSC87 CCSC92 CCSC97 CCSC02 CCSC07 CCSC12 CCSC17 CCSC20 Transition
<dbl> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
1 1 NA NA NA NA B E F B B
2 2 NA A D E A NA NA NA A
3 3 A E E E NA NA NA NA AtoE
4 4 NA NA A E E B NA NA AtoB
5 5 C F F F NA NA NA NA CtoF
We can use apply row-wise, get non-NA values, compare first and last value in each row and paste them if they are different.
apply(df[-1], 1, function(x) {
x <- na.omit(x)
if(x[1] != x[length(x)])
paste(x[1], x[length(x)], sep = 'to')
else x[1]
})
#[1] "B" "A" "AtoE" "AtoB" "CtoF"