Filling a dataset based on conditions from raw data

General
1

Hello. I need to fill the missing values in a dataset from the raw data. The data frame data_2015 and data_2020 are the list of countries and the indicators (there are 4 indicators). In the raw data, the 4 indicators are coded as:
indi_1=ABC
indi_2=GHI
indi_3=JKL
indi_4=MNO
Based on the information in raw data on whether the indicator is available for a particular year, there must appear 1 or 0 in the data frames data_2015 and data_2020. For example in the year 2020 GHI (indi_2) is available for Denmark. Hence in data frame data_2020, the indi_2 must appear as 1. Otherwise it must appear as 0. How can I do this?

library(tidyverse)
library(janitor)

data_2015<-tibble::tribble(
          ~country, ~indi_1, ~indi_2, ~indi_3, ~indi_4,
     "afghanistan",      NA,      NA,      NA,      NA,
         "albania",      NA,      NA,      NA,      NA,
         "bolivia",      NA,      NA,      NA,      NA,
          "cyprus",      NA,      NA,      NA,      NA,
  "czeck republic",      NA,      NA,      NA,      NA,
         "denmark",      NA,      NA,      NA,      NA,
         "england",      NA,      NA,      NA,      NA,
         "finland",      NA,      NA,      NA,      NA,
          "france",      NA,      NA,      NA,      NA,
           "india",      NA,      NA,      NA,      NA,
        "pakistan",      NA,      NA,      NA,      NA
  )

data_2020<-tibble::tribble(
                     ~country, ~indi_1, ~indi_2, ~indi_3, ~indi_4,
                "afghanistan",      NA,      NA,      NA,      NA,
                    "albania",      NA,      NA,      NA,      NA,
                    "bolivia",      NA,      NA,      NA,      NA,
                     "cyprus",      NA,      NA,      NA,      NA,
             "czeck republic",      NA,      NA,      NA,      NA,
                    "denmark",      NA,      NA,      NA,      NA,
                    "england",      NA,      NA,      NA,      NA,
                    "finland",      NA,      NA,      NA,      NA,
                     "france",      NA,      NA,      NA,      NA,
                      "india",      NA,      NA,      NA,      NA,
                   "pakistan",      NA,      NA,      NA,      NA
             )

raw_data<-tibble::tribble(
            ~indicator_id, ~year,         ~country,
                    "ABC", 2015L,    "afghanistan",
                    "ABC", 2015L,        "albania",
                    "ABC", 2015L,          "india",
                    "ABC", 2015L,       "pakistan",
                    "ABC", 2020L,        "denmark",
                    "ABC", 2020L,        "england",
                    "ABC", 2020L,        "finland",
                    "ABC", 2020L,         "france",
                    "ABC", 2020L,          "india",
                    "ABC", 2020L,       "pakistan",
                    "ABC", 2020L,        "albania",
                    "GHI", 2015L,        "denmark",
                    "GHI", 2015L,        "england",
                    "GHI", 2015L,        "finland",
                    "GHI", 2015L,         "france",
                    "GHI", 2020L,       "pakistan",
                    "GHI", 2020L,        "albania",
                    "GHI", 2020L,        "denmark",
                    "JKL", 2015L,          "india",
                    "JKL", 2015L,       "pakistan",
                    "JKL", 2015L,        "denmark",
                    "JKL", 2015L,        "england",
                    "JKL", 2015L,        "finland",
                    "JKL", 2020L,        "denmark",
                    "JKL", 2020L,          "india",
                    "JKL", 2020L,       "pakistan",
                    "MNO", 2015L,         "cyprus",
                    "MNO", 2015L, "czeck republic",
                    "MNO", 2015L,        "denmark",
                    "MNO", 2020L,         "cyprus",
                    "MNO", 2020L, "czeck republic",
                    "MNO", 2020L,        "denmark"
            )
2

Wouldn't it be easier to rotate the raw data? Here is an example. You could change the column names afterward or make indicator_id a factor before pivoting the data.

library(tidyverse)
library(janitor)
#> 
#> Attaching package: 'janitor'
#> The following objects are masked from 'package:stats':
#> 
#>     chisq.test, fisher.test

raw_data<-tibble::tribble(
  ~indicator_id, ~year,         ~country,
  "ABC", 2015L,    "afghanistan",
  "ABC", 2015L,        "albania",
  "ABC", 2015L,          "india",
  "ABC", 2015L,       "pakistan",
  "ABC", 2020L,        "denmark",
  "ABC", 2020L,        "england",
  "ABC", 2020L,        "finland",
  "ABC", 2020L,         "france",
  "ABC", 2020L,          "india",
  "ABC", 2020L,       "pakistan",
  "ABC", 2020L,        "albania",
  "GHI", 2015L,        "denmark",
  "GHI", 2015L,        "england",
  "GHI", 2015L,        "finland",
  "GHI", 2015L,         "france",
  "GHI", 2020L,       "pakistan",
  "GHI", 2020L,        "albania",
  "GHI", 2020L,        "denmark",
  "JKL", 2015L,          "india",
  "JKL", 2015L,       "pakistan",
  "JKL", 2015L,        "denmark",
  "JKL", 2015L,        "england",
  "JKL", 2015L,        "finland",
  "JKL", 2020L,        "denmark",
  "JKL", 2020L,          "india",
  "JKL", 2020L,       "pakistan",
  "MNO", 2015L,         "cyprus",
  "MNO", 2015L, "czeck republic",
  "MNO", 2015L,        "denmark",
  "MNO", 2020L,         "cyprus",
  "MNO", 2020L, "czeck republic",
  "MNO", 2020L,        "denmark"
)

data_2015 <- raw_data |> filter(year == 2015) |> 
  mutate(Present = 1) |> 
  pivot_wider(names_from = 'indicator_id', values_from = 'Present', values_fill = 0)
data_2015
#> # A tibble: 10 × 6
#>     year country          ABC   GHI   JKL   MNO
#>    <int> <chr>          <dbl> <dbl> <dbl> <dbl>
#>  1  2015 afghanistan        1     0     0     0
#>  2  2015 albania            1     0     0     0
#>  3  2015 india              1     0     1     0
#>  4  2015 pakistan           1     0     1     0
#>  5  2015 denmark            0     1     1     1
#>  6  2015 england            0     1     1     0
#>  7  2015 finland            0     1     1     0
#>  8  2015 france             0     1     0     0
#>  9  2015 cyprus             0     0     0     1
#> 10  2015 czeck republic     0     0     0     1

Created on 2025-02-27 with reprex v2.1.1