Recode different values in multiple columns as factor

Question

assuming I have a data set that looks something like this:

df <- data.frame("age" = c(55, 21, 11), 
                 "sex" = c("m", "m", "f"),
                 "a2" = c(0,1,0),
                 "a3" = c(0,0,0),
                 "a4" = c(0,1,0),
                 "b2" = c(0,0,1),
                 "b3" = c(0,0,0),
                 "b4" = c(1,0,1),
                 "c2" = c(0,1,0)
                 )

age sex a2 a3 a4 b2 b3 b4 c2
 55   m  0  0  0  0  0  1  0
 21   m  1  0  1  0  0  0  1
 11   f  0  0  0  1  0  1  0

How would I be able to transform it into long format and change the values of columns a1-c1 so that every column has the value of its name? e. g. in a2 0=1 and 1=2?

I tried it with pivot_longer like this:

df %>% pivot_longer(
   cols = stars_with("a"),
   names_to = "A"
)

My desired output looks like this:

age sex a2 a3 a4 b2 b3 b4 c2
 55   m  1  1  1  1  1  4  1
 21   m  2  1  4  1  3  1  2
 11   f  1  1  1  2  1  4  1

Thanks!

I'm not clear what your desired output is. a3=0 becomes 1 or ?? And 1 becomes 2 or ??3. You ask for longer a* to c* but are only using starts with a? Can you post a reproducable example i.e. the code to make df (see dput) and what you are hoping to see? — CALUM Polwart
– CALUM Polwart, Commented Mar 20, 2021 at 23:17
@CALUMPolwart Hi! I edited my post. Basically I want the number in the column name as the value for 1. I dont use a-c because in the data set they are not in that order. — Mer
– Mer, Commented Mar 20, 2021 at 23:37

Ali · Accepted Answer · 2021-03-21 00:49:13Z

4

This is a good question because you cannot access column names within mutate_at, which is how you would normally mutate across a list of variables. Here is a solution using map_dfc from purrr:

library(purrr)
library(dplyr)
library(tidyr)
library(readr)

We define a function (although not necessary but helpful to avoid foreign syntax if you are not accustomed to purrr.

my_fun <- function(x) transmute(df, !!x := ifelse(!!sym(x) == 0, 1, parse_number(x)))

transmute to avoid duplicate columns

!!x := is a way to assign a column name based on a string from a variable. In our case, a column name.

!!sym() converts our variable name from a string to a symbol. Without this, for column 'a2' this would read "a2" == 0 thus replacing with the column number. Refer to this answer for an in-depth explanation.

parse_number will find the number within the column name

ifelse states if the column value is equal to 0, then the value equals 1, otherwise the value is equal to the number in the column name

var_names <- c("a2","a3","a4","b2","b3","b4","c2")

map_dfc(var_names, my_fun)

edited Mar 21, 2021 at 0:49

answered Mar 21, 2021 at 0:07

Ali

1,1228 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Eric Over a year ago

Can you please add a brief blurb about what sym() is doing here?

Ali Over a year ago

@iamericfletcher I have edited the explanation in.

AnilGoyal · Accepted Answer · 2021-03-21 02:09:57Z

4

pivot_longer is not appropriate strategy here. Follow mutate with across.

across will pass desired columns into mutate statement
cur_column() will pass current colnames into syntax

library(dplyr)
library (stringr) 
df %>% mutate(across(-c("age", "sex"), ~ ifelse(. == 0, 1, as.numeric(str_extract(cur_column(), "\\d$")))))

  age sex a2 a3 a4 b2 b3 b4 c2
1  55   m  1  1  1  1  1  4  1
2  21   m  2  1  4  1  1  1  2
3  11   f  1  1  1  2  1  4  1

edited Mar 21, 2021 at 2:09

answered Mar 21, 2021 at 1:52

AnilGoyal

26.3k4 gold badges34 silver badges50 bronze badges

Comments

user12728748 · Accepted Answer · 2021-03-21 01:47:08Z

2

In base R you could do:

# extract numbers from column names
vals <- as.numeric(gsub("[^0-9]+", "", colnames(df)[-c(1:2)]))

# replace 0 with 1 and 1 with extracted number in each column
df[, -c(1:2)] <- sapply(seq_along(vals), function(x) ifelse(df[, x+2]==0, 1, vals[x]))

answered Mar 21, 2021 at 1:47

user12728748

8,5462 gold badges14 silver badges16 bronze badges

1 Comment

rawr Over a year ago

similar df[, -c(1:2)] <- Map('^', vals, df[, -c(1:2)])

Ronak Shah · Accepted Answer · 2021-03-21 03:12:34Z

2

Base R option using Map :

inds <- -(1:2)
cols <- as.integer(sub('[a-z](\\d+)', '\\1', names(df)[inds]))
df[inds] <- Map(function(x, y) ifelse(x == 1, y, 1), df[inds], cols)
df

#  age sex a2 a3 a4 b2 b3 b4 c2
#1  55   m  1  1  1  1  1  4  1
#2  21   m  2  1  4  1  1  1  2
#3  11   f  1  1  1  2  1  4  1

answered Mar 21, 2021 at 3:12

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

Comments

Yang Wu · Accepted Answer · 2021-03-20 23:54:13Z

0

One possibility (though may not be the most efficient)

library(tidyverse)
as_tibble(df) %>% transmute(
  a2 = case_when(a2 == 1 ~ 2, a2 == 0 ~ 1),
  a3 = case_when(a3 == 1 ~ 3, a3 == 0 ~ 1),
  a4 = case_when(a4 == 1 ~ 4, a4 == 0 ~ 1),
  b2 = case_when(b2 == 1 ~ 2, b2 == 0 ~ 1),
  b3 = case_when(b3 == 1 ~ 3, b3 == 0 ~ 1),
  b4 = case_when(b4 == 1 ~ 4, b4 == 0 ~ 1),
  c2 = case_when(c2 == 1 ~ 2, c2 == 0 ~ 1)
)

If you wish to keep the other columns use mutate() instead of transmute().

edited Mar 20, 2021 at 23:54

answered Mar 20, 2021 at 23:45

Yang Wu

1942 silver badges12 bronze badges

Collectives™ on Stack Overflow

Recode different values in multiple columns as factor

5 Answers 5

2 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related