Replace column based on column names

user330 0
Viewed 66

I have 65 columns, but a sample of data could be as follows:

 df<-read.table (text="  Name   D   A   D   E
    Rose    D   D   C   B
    Smith   B   A   D   D
    Lora    A   A   D   D
    Javid   A   D   D   B
    Ahmed   C   A   E   A
    Helen   B   A   D   D
    Nadia   A   A   D   A

", header=TRUE)

I want to get the following table:

Name    D   A   D   E
Rose    2   1   1   1
Smith   1   2   2   1
Lora    1   2   2   1
Javid   1   1   2   1
Ahmed   1   2   1   1
Helen   1   2   2   1
Nadia   1   2   2   1

The numbers follow the first raw. For example, the second column is D, so all Ds should read 2 and else should read 1. Or in the third column, which is A, all As should read 2 and else should read 1 and so on. Please consider I have 65 columns. I understand I should have different names for the columns, but In this case, I cannot change them as you understand it.

r
6 Answers

With ifelse and sapply:

df[2:ncol(df)] <- sapply(2:ncol(df), function(i) ifelse(df[i] == colnames(df[i]), 2, 1))

output

#> df
   Name D A D E
1  Rose 2 1 1 1
2 Smith 1 2 2 1
3  Lora 1 2 2 1
4 Javid 1 1 2 1
5 Ahmed 1 2 1 1
6 Helen 1 2 2 1
7 Nadia 1 2 2 1

data

df <- structure(list(Name = c("Rose", "Smith", "Lora", "Javid", "Ahmed", 
"Helen", "Nadia"), D = c("D", "B", "A", "A", "C", "B", "A"), 
    A = c("D", "A", "A", "D", "A", "A", "A"), D = c("C", "D", 
    "D", "D", "E", "D", "D"), E = c("B", "D", "D", "B", "A", 
    "D", "A")), class = "data.frame", row.names = c(NA, -7L))
Maël0 
cols = names(df)[-1]
df[cols] = lapply(cols, \(x) (df[[x]] == x) + 1L)

#    Name D A
# 1  Rose 2 1
# 2 Smith 1 2
# 3  Lora 1 2
# 4 Javid 1 1
# 5 Ahmed 1 2
# 6 Helen 1 2
# 7 Nadia 1 2

Simplified data (without repeated column names)

df <- data.frame(
  Name = c("Rose", "Smith", "Lora", "Javid", "Ahmed", "Helen", "Nadia"),
  D    = c("D", "B", "A", "A", "C", "B", "A"),
  A    = c("D", "A", "A", "D", "A", "A", "A")
)
sindri_baldur0

Another approach, you can stack, replace and unstack, i.e

stack_df <- stack(df[-1])
stack_df$values <- ifelse(stack_df$values == stack_df$ind, 2, 1)
cbind.data.frame(Name = df$Name, unstack(stack_df))

#   Name D A E
#1  Rose 2 1 1
#2 Smith 1 2 1
#3  Lora 1 2 1
#4 Javid 1 1 1
#5 Ahmed 1 2 1
#6 Helen 1 2 1
#7 Nadia 1 2 1

DATA

structure(list(Name = c("Rose", "Smith", "Lora", "Javid", "Ahmed", 
"Helen", "Nadia"), D = c("D", "B", "A", "A", "C", "B", "A"), 
    A = c("D", "A", "A", "D", "A", "A", "A"), E = c("B", "D", 
    "D", "B", "A", "D", "A")), row.names = c(NA, -7L), class = "data.frame")
Sotos0

dplyr option with ifelse like this:

library(dplyr)
df %>% 
  mutate(across(D:E, ~ifelse(. == cur_column(), 2, 1)))
#>    Name D A D.1 E
#> 1  Rose 2 1   1 1
#> 2 Smith 1 2   1 1
#> 3  Lora 1 2   1 1
#> 4 Javid 1 1   1 1
#> 5 Ahmed 1 2   1 1
#> 6 Helen 1 2   1 1
#> 7 Nadia 1 2   1 1

Created on 2022-09-19 with reprex v2.0.2

Quinten0

Using dplyr:

The data:

df <- read.table (
  text = "  Name   A   B   C   D
    Rose    D   D   C   B
    Smith   B   A   D   D
    Lora    A   A   D   D
    Javid   A   D   D   B
    Ahmed   C   A   E   A
    Helen   B   A   D   D
    Nadia   A   A   D   A

",
  header = TRUE
)

> df
   Name A B C D
1  Rose D D C B
2 Smith B A D D
3  Lora A A D D
4 Javid A D D B
5 Ahmed C A E A
6 Helen B A D D
7 Nadia A A D A

Note that i changed the column names

df %>%
  mutate(across(!c(Name),
                .fns = ~ ifelse(.x == cur_column(), 2, 1)))

   Name A B C D
1  Rose 1 1 2 1
2 Smith 1 1 1 2
3  Lora 2 1 1 2
4 Javid 2 1 1 1
5 Ahmed 1 1 1 1
6 Helen 1 1 1 2
7 Nadia 2 1 1 1

The mutate-command modifies columns in dataframes. Using the across()-function we specify that the mutation should be applied to more than one column. inside the across-call, we select every column but the name column using !c(Name). We then specify a function that compares the name of the column cur_column() with the values in the column .x. If they are the same, set the value to 2, else to 1.

EDIT: used ifelse instead of case_when as there is only one condition to check

brettljausn0

You can compare each row with the column names. Adding 1 to the logical values converts FALSE and TRUE into 1 and 2 respectively.

df[-1] <- t((t(df[-1]) == names(df)[-1]) + 1)
df

#    Name D A D E
# 1  Rose 2 1 1 1
# 2 Smith 1 2 2 1
# 3  Lora 1 2 2 1
# 4 Javid 1 1 2 1
# 5 Ahmed 1 2 1 1
# 6 Helen 1 2 2 1
# 7 Nadia 1 2 2 1
Darren Tsai0
Related