How to add new variable according another value of variable?

Question

This is my dataframe:

enter image description here

or you may copy it:

structure(list(A_levels = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), 
    College_Uni = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1), CSEs = c(0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0), NVQ_HND_HNC = c(0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0), O_levels_GCSEs = c(1, 1, 1, 1, 1, 1, 
    1, 1, 1, 1, 1), Other_prof_qual = c(0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0), Prefer_not_to_answer = c(0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0), None_of_the_above = c(0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0), id = 5000:5010), row.names = c(NA, -11L), class = c("tbl_df", 
"tbl", "data. Frame")) -> df

I want to mutate a edu_level of new variable, it's value according to this:

#my vairabe               edu_level
#College_Uni              high
#A_levels                 medium
#O_levels_GCSEs           medium
#CSEs)                    low
#NVQ_HND_HNC              low
#Other_prof_qual          low
#Prefer_not_to_answer     NA
#None_of_the_above        NA

I think maybe because it is a multiple-choice question, some people have more than two levels, so I don't know how to specify the classification.

I want to choose the priority of its edu_level to be the highest degree first. For example, if it has both College_Uni and A_levels, the value of edu_level is high.

akrun's answer is better if you wanna accept that. – M-- May 24 '23 at 13:31 — M--, May 24 '23 at 13:31

Evan Friedland · Answer 1 · 2023-04-28T13:49:17.157

Try this out and let me know if it helps. I convert your survey from 0s and 1s to ranked numbers, and then find the maximum number from each row.

Reproducible Data

dat <- data.frame(A_levels = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), 
               College_Uni = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1), 
               CSEs = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), 
               NVQ_HND_HNC = c(0, 0, 0, 0,  0, 0, 0, 0, 0, 0, 0), 
               O_levels_GCSEs = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1), 
               Other_prof_qual = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), 
               Prefer_not_to_answer = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), 
               None_of_the_above = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), 
               id = 5000:5010)


scoring_df <- data.frame(variable = c("A_levels", "College_Uni", "CSEs", "NVQ_HND_HNC", "O_levels_GCSEs", "Other_prof_qual", "Prefer_not_to_answer","None_of_the_above"),
                         edu_level = c("medium", "high", "low","low","medium","low",NA, NA),
                         edu_score = c(2,3,1,1,2,1,NA,NA))
# I added the "edu_score"

The work

# remove the id column
dat_scores_only <- dat[, -which(colnames(dat) == "id")] 

# fetch the numeric scores for each column
scoring_ordered <- scoring_df$edu_score[match(scoring_df$variable, colnames(dat) )] 

# replace 0s & 1s with scores using multiplication, https://stackoverflow.com/questions/36111444/multiply-columns-in-a-data-frame-by-a-vector
results <- t(t(dat_scores_only)*scoring_ordered) 

# now check for the maximum level per row
# using apply(matrix, by row = 1 and by column = 2, function is max, argument is to ignore NAs)
max_values_by_row <- apply(results, 1, max, na.rm = T)

# and look back up the score in your scoring df to get the words you wanted per row as a new variable "edu_level"
dat$edu_level <- scoring_df[match(max_values_by_row,scoring_df$edu_score), "edu_level"]

The result (see far right column)

dat
#   A_levels College_Uni CSEs NVQ_HND_HNC O_levels_GCSEs Other_prof_qual Prefer_not_to_answer None_of_the_above   id edu_score
#1         0           1    0           0              1               0                    0                 0 5000      high
#2         0           1    0           0              1               0                    0                 0 5001      high
#3         0           1    0           0              1               0                    0                 0 5002      high
#4         0           1    0           0              1               0                    0                 0 5003      high
#5         0           1    0           0              1               0                    0                 0 5004      high
#6         0           1    0           0              1               0                    0                 0 5005      high
#7         0           1    0           0              1               0                    0                 0 5006      high
#8         0           1    0           0              1               0                    0                 0 5007      high
#9         0           1    0           0              1               0                    0                 0 5008      high
#10        0           1    0           0              1               0                    0                 0 5009      high
#11        0           1    0           0              1               0                    0                 0 5010      high

Thank you! It's little difficult for me. Too many function I'm not familiar with. — Bruce, Apr 28 '23 at 02:33
Sure, I edited to be clearer for those who don't know the functions. This code is written in base R meaning it uses no libraries. — Evan Friedland, Apr 28 '23 at 13:50

score 1 · Accepted Answer · answered Apr 27 '23 at 03:09

I created a dataframe with different levels of education to showcase the solution;

library(dplyr)
library(tidyr)

myvars %>% 
  mutate(num_level = case_when(is.na(edu_level) ~ 0, 
                               edu_level == "low" ~ 1, 
                               edu_level == "medium" ~ 2,
                               edu_level == "high" ~ 3)) -> myvars

df1 %>% 
  pivot_longer(-id, names_to = "degree") %>% 
  filter(value != 0) %>% 
  right_join(., myvars, join_by(degree == variable)) %>% 
  slice(which.max(num_level), .by = id) %>% 
  select(-value, -num_level) %>% ## you can also drop "degree"
  left_join(., df1)

#> Joining with `by = join_by(id)`
#> # A tibble: 11 x 11
#>       id degree  edu_level A_levels College_Uni  CSEs NVQ_HND_HNC O_levels_GCSEs
#>    <int> <chr>   <chr>        <dbl>       <dbl> <dbl>       <dbl>          <dbl>
#>  1  5000 Colleg~ high             0           1     0           0              1
#>  2  5001 A_leve~ medium           1           0     0           0              1
#>  3  5002 A_leve~ medium           1           0     0           0              1
#>  4  5003 O_leve~ medium           0           0     0           1              1
#>  5  5004 Colleg~ high             0           1     0           0              1
#>  6  5005 Other_~ low              0           0     0           0              0
#>  7  5006 Colleg~ high             0           1     0           1              1
#>  8  5007 CSEs    low              0           0     1           0              0
#>  9  5008 Colleg~ high             0           1     0           0              0
#> 10  5009 Prefer~ <NA>             0           0     0           0              0
#> 11  5010 None_o~ <NA>             0           0     0           0              0
#> # i 3 more variables: Other_prof_qual <dbl>, Prefer_not_to_answer <dbl>,
#> #   None_of_the_above <dbl>

Data:

data.frame(A_levels =             c(0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0), 
           College_Uni =          c(1, 0, 0, 0, 1, 0, 1, 0, 1, 0, 0), 
           CSEs =                 c(0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0), 
           NVQ_HND_HNC =          c(0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0), 
           O_levels_GCSEs =       c(1, 1, 1, 1, 1, 0, 1, 0, 0, 0, 0), 
           Other_prof_qual =      c(0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0), 
           Prefer_not_to_answer = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0), 
           None_of_the_above =    c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1), 
           id = 5000:5010) -> df1


read.table(text = "variable              edu_level
                   College_Uni           high
                   A_levels              medium
                   O_levels_GCSEs        medium
                   CSEs                  low
                   NVQ_HND_HNC           low
                   Other_prof_qual       low
                   Prefer_not_to_answer  NA
                   None_of_the_above     NA", header = T, stringsAsFactor = F) -> myvars

score 1 · Answer 3 · answered Apr 27 '23 at 06:18

Here is one way

library(dplyr)
keyval$edu_level <- ordered(keyval$edu_level, levels = c("low", "medium", "high"))
df1 %>% 
  mutate(edu_level = do.call(pmax, c(across(any_of(keyval$variable), 
   ~ case_when(as.logical(.x) ~ keyval$edu_level[match(cur_column(), 
     keyval$variable)])), na.rm = TRUE))
 )

-output

A_levels College_Uni CSEs NVQ_HND_HNC O_levels_GCSEs Other_prof_qual Prefer_not_to_answer None_of_the_above   id edu_level
1         0           1    0           0              1               0                    0                 0 5000      high
2         1           0    0           0              1               0                    0                 0 5001    medium
3         1           0    0           0              1               0                    0                 0 5002    medium
4         0           0    0           1              1               0                    0                 0 5003    medium
5         0           1    0           0              1               0                    0                 0 5004      high
6         0           0    0           0              0               1                    0                 0 5005       low
7         0           1    0           1              1               0                    0                 0 5006      high
8         0           0    1           0              0               0                    0                 0 5007       low
9         0           1    0           0              0               0                    0                 0 5008      high
10        0           0    0           0              0               0                    1                 0 5009      <NA>
11        0           0    0           0              0               0                    0                 1 5010      <NA>

score 0 · Answer 4 · answered Aug 07 '23 at 18:01

I ended up using this approach and I believe allows a bit more soft-coding once the list is set up:

using a list allows me to call scores$high and get a vector of names
rowwise() allows max() to work row-by-row, otherwise it will evaluate all rows at once and return a the same value the whole way down
c_across(one_of) allows me to look across c_across() a specific set of variables and one_of() allows me to use strings as my column references
ungroup() undoes the rowwise() call

library(tidyverse)

df <-
  tibble(
    A_levels = c(0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0),
    College_Uni = c(1, 0, 0, 0, 1, 0, 1, 0, 1, 0, 0),
    CSEs = c(0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0),
    NVQ_HND_HNC = c(0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0),
    O_levels_GCSEs = c(1, 1, 1, 1, 1, 0, 1, 0, 0, 0, 0),
    Other_prof_qual = c(0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0),
    Prefer_not_to_answer = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0),
    None_of_the_above = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1),
    id = 5000:5010
  )

scores <- 
  list(
    high = "College_Uni",
    medium = c("A_levels", "O_levels_GCSEs"),
    low = c("CSEs", "NVQ_HND_HNC", "Other_prof_qual"),
    na = c("Prefer_not_to_answer", "None_of_the_above")
  )

df |> 
  rowwise() |> 
  mutate(
    edu_level = 
      case_when(
        max(c_across(one_of(scores$high))) == 1  ~ "high",
        max(c_across(one_of(scores$medium))) == 1  ~ "medium",
        max(c_across(one_of(scores$low))) == 1  ~ "low"
      )
  ) |> 
  ungroup()

How to add new variable according another value of variable?

4 Answers4

Data: