This version removes the columns that end in X and it establishes four groups of MX numbers. I don't know where all your breaks are between the groups, so you will have to tweak that part of the code. As before, all of the countries are aggregated. You can add COUNTRY to the group_by() to get values for each country.
library(dplyr)
#>
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#>
#> filter, lag
#> The following objects are masked from 'package:base':
#>
#> intersect, setdiff, setequal, union
library(stringr)
library(tidyr)
DF <- read.csv("~/R/Play/Round_3.csv")
MX <- DF |> select(COUNTRY|(starts_with("MX")& !ends_with("X")))
MXlong <- MX |> pivot_longer(-COUNTRY, names_to = "ColName",
values_to = "Value")
head(MXlong)
#> # A tibble: 6 x 3
#> COUNTRY ColName Value
#> <chr> <chr> <int>
#> 1 Afghanistan MX01X1990 NA
#> 2 Afghanistan MX01X1991 NA
#> 3 Afghanistan MX01X1992 0
#> 4 Afghanistan MX01X1993 0
#> 5 Afghanistan MX01X1994 0
#> 6 Afghanistan MX01X1995 0
MXlong <- MXlong |> mutate(Year = str_extract(ColName, "\\d{4}"),
ColNumber = str_sub(ColName, start = 3, end = 4),
Group = case_when(
ColNumber >= "01" & ColNumber <= "12" ~ "A",
ColNumber >= "13" & ColNumber <= "20" ~ "B",
ColNumber >= "21" & ColNumber <= "28" ~ "C",
ColNumber >= "29" & ColNumber <= "36" ~ "D",
))
head(MXlong)
#> # A tibble: 6 x 6
#> COUNTRY ColName Value Year ColNumber Group
#> <chr> <chr> <int> <chr> <chr> <chr>
#> 1 Afghanistan MX01X1990 NA 1990 01 A
#> 2 Afghanistan MX01X1991 NA 1991 01 A
#> 3 Afghanistan MX01X1992 0 1992 01 A
#> 4 Afghanistan MX01X1993 0 1993 01 A
#> 5 Afghanistan MX01X1994 0 1994 01 A
#> 6 Afghanistan MX01X1995 0 1995 01 A
MX_Summary <- MXlong |> group_by(Year, Group) |>
summarize(Total = sum(Value, na.rm = TRUE))
#> `summarise()` has grouped output by 'Year'. You can override using the `.groups` argument.
head(MX_Summary)
#> # A tibble: 6 x 3
#> # Groups: Year [2]
#> Year Group Total
#> <chr> <chr> <int>
#> 1 1990 A 395
#> 2 1990 B 496
#> 3 1990 C 443
#> 4 1990 D 341
#> 5 1991 A 452
#> 6 1991 B 591
Created on 2021-11-18 by the reprex package (v2.0.1)