Categorizing variable with multiple values in one cell in R

user_18845083 0
Viewed 42

I am new to coding in R and posting here, so pls let me know if I can add anything helpful. I am trying to create a new categorical variable "income" (3 levels) that categorizes a subset of predetermined countries (x, y, z) into the different levels. My issue is that the countries variable has multiple countries in each cell, so I don't know how to sort this.

ID           countries        **income**
1            x, y, z          LMIC, HMIC, UMIC
2            y                HMIC
3            x, z             LMIC, UMIC
1            z                UMIC

This is the code I have, but it is only working on the rows that only have one country, ie the rows with x, y, z remain unchanged. My ultimate goal is to create the income variable and be able to get a total count of how many ID's fall into each income category.

data.set$countries <- revalue(data.set$income, c("x"=1, "y"=2, "z"=3))

data.set$income[dataset$countries == 1] <- "LMIC"
data.set$income[dataset$countries == 2] <- "HMIC"
data.set$income[dataset$countries == 3] <- "UMIC"
r
1 Answers

One option is to first separate countries into individual rows, then we can use recode to assign the income category. Then, we can use tally to get a count of the IDs in each income category.

library(tidyverse)

df %>%
  separate_rows(countries, sep =", ") %>%
  mutate(income = recode(countries, "x" = "LMIC", "y" = "HMIC", "z" = "UMIC")) %>% 
  group_by(income) %>% 
  tally(ID)

Output

  income     n
  <chr>  <int>
1 HMIC       3
2 LMIC       4
3 UMIC       5

Data

df <- structure(list(ID = c(1L, 2L, 3L, 1L), countries = c("x, y, z", 
"y", "x, z", "z")), class = "data.frame", row.names = c(NA, -4L
))
user_152931910
Related