Adding column name into a new column if it equals row value-CodePudding

Using tidyverse, I would like to add a new column in which contains the value of one column where the column name equals a value of another column.

I know the explanation is kind of confusing, so using a simple table for example:

| Name | ID | A01 | A02 | A03 | A04 | A05 | new_column |
| ---- | -- | ----| --- | --- | --- | --- | ---------- |
| a    | A01| 2   | 3   | 1   | 4   | 6   | 2          |
| b    | A03| 3.4 | 4.5 | 2.2 | 3.6 | 7.2 | 2.2        |
| c    | A02| 1.3 | 2.0 | 4.4 | 6.5 | 9.2 | 2.0        |
| d    | A05| 0.8 | 7.6 | 3.3 | 4.5 | 1.1 | 1.1        |

Any ideas for adding in column 'new_column' when the original data name is d, by using a code somewhat similar to

d %>% mutate(new_column = )

Thanks is advance :)

CodePudding user response：

You can use the match function in R to match the values of ID with column names.

df_new <- df %>%
  mutate(new_column = df[cbind(1:4, match(df$ID, names(df)))])

Here's the output of df_new:

  Name  ID A01 A02 A03 A04 A05 new_column
1    a A01 2.0 3.0 1.0 4.0 6.0        2.0
2    b A03 3.4 4.5 2.2 3.6 7.2        2.2
3    c A02 1.3 2.0 4.4 6.5 9.2        2.0
4    d A05 0.8 7.6 3.3 4.5 1.1        1.1

You can see this post for further information.

CodePudding user response：

This syntax is a bit strange, but it works.

d$new_column <- d[unique(d$ID)][cbind(1:nrow(d), match(d$i, unique(d$ID)))]

This is similar to Vishal's answer, but it ensures the variable type of new_column is correct (their answer may give you character values instead of numeric values in new_column).

CodePudding user response：

library(tidyverse)

df %>% 
  pivot_longer(-c(Name, ID)) %>% 
  transmute(Name, ID, new_column = ifelse(ID == name, value, NA_real_)) %>% 
  drop_na(new_column) %>% 
  left_join(df)