Conditionally take value from column1 if the column1 name == first(value) from column2 BY GROUP

Question

I have this fake dataframe:

df <- structure(list(Group = c(1L, 1L, 2L, 2L), A = 1:4, B = 5:8, C = 9:12, 
X = c("A", "A", "B", "B")), class = "data.frame", row.names = c(NA, -4L))

  Group A B  C X
1     1 1 5  9 A
2     1 2 6 10 A
3     2 3 7 11 B
4     2 4 8 12 B

I try to mutate a new column, which should take the value of THE column that has the column name in an other column:

Desired output:

Group   A   B   C   X new_col
1       1   5   9     A 1
1       2   6   10    A 1
2       3   7   11    B 7
2       4   8   12    B 7

My try so far:

library(dplyr)

df %>% 
  group_by(Group) %>% 
  mutate(across(c(A,B,C), ~ifelse(first(X) %in% colnames(.), first(.), .), .names = "new_{.col}"))

  Group     A     B     C X     new_A new_B new_C
  <int> <int> <int> <int> <chr> <int> <int> <int>
1     1     1     5     9 A         1     5     9
2     1     2     6    10 A         1     5     9
3     2     3     7    11 B         3     7    11
4     2     4     8    12 B         3     7    11

score 1 · Accepted Answer · answered Dec 25 '21 at 10:37

1

One option might be:

df %>%
    rowwise() %>%
    mutate(new_col = get(X)) %>%
    group_by(Group, X) %>%
    mutate(new_col = first(new_col))

 Group     A     B     C X     new_col
  <int> <int> <int> <int> <chr>   <int>
1     1     1     5     9 A           1
2     1     2     6    10 A           1
3     2     3     7    11 B           7
4     2     4     8    12 B           7

answered Dec 25 '21 at 10:37

tmfmnk

38,881
4
47
67

1

Yes! Because of thinking of how to handle `across`, I newer thought about `get`. – TarJae Dec 25 '21 at 10:49
You may have a look here, where I applied your solution – TarJae Dec 25 '21 at 11:42

score 1 · Answer 2 · answered Dec 25 '21 at 11:39

Using by and add + 1 to the group number to select column. Assuming group columns are arranged as in example after "Group" column.

transform(df, new_col=do.call(rbind, by(df, df$Group, \(x) 
                                        cbind(paste(x$X, x[1, x$Group[1] + 1])))))
#   Group A B  C X new_col
# 1     1 1 5  9 A     A 1
# 2     1 2 6 10 A     A 1
# 3     2 3 7 11 B     B 7
# 4     2 4 8 12 B     B 7

Note: R version 4.1.2 (2021-11-01).

Data:

df <- structure(list(Group = c(1L, 1L, 2L, 2L), A = 1:4, B = 5:8, C = 9:12, 
    X = c("A", "A", "B", "B")), class = "data.frame", row.names = c(NA, 
-4L))

score 1 · Answer 3 · answered Dec 25 '21 at 17:09

1

In base R, we may use row/column indexing

df$new_col <- df[2:4][cbind(match(unique(df$Group), df$Group)[df$Group], 
       match(df$X, names(df)[2:4]))]
df$new_col
[1] 1 1 7 7

answered Dec 25 '21 at 17:09

akrun

874,273
37
540
662

1

Thanks master! I needed it for this: See here – TarJae Dec 25 '21 at 17:10

Conditionally take value from column1 if the column1 name == first(value) from column2 BY GROUP

3 Answers3

Linked