CODEWITHSUNDEEP
rdataframetidyverse
JEJI
JEJI

Reputation: 59

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

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 :)

Upvotes: 1

Views: 1387

Answers (5)

akrun
akrun

Reputation: 887118

We may use cur_data()

library(dplyr)
df %>% 
   rowwise %>% 
   mutate(new_column = cur_data()[[cur_data()$ID]]) %>%
   ungroup
# A tibble: 4 × 8
  Name  ID      A01   A02   A03   A04   A05 new_column
  <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl>      <dbl>
1 a     A01     2     3     1     4     6          2  
2 b     A03     3.4   4.5   2.2   3.6   7.2        2.2
3 c     A02     1.3   2     4.4   6.5   9.2        2  
4 d     A05     0.8   7.6   3.3   4.5   1.1        1.1

Upvotes: 2

TarJae
TarJae

Reputation: 78927

We can use map2_dbl from purrr:

library(dplyr)
library(purrr)
df %>%
  mutate(new_column = map2_dbl(row_number(),ID,~df[.x,.y]))

output:

 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

data:

df <- structure(list(Name = c("a", "b", "c", "d"), ID = c("A01", "A03", 
"A02", "A05"), A01 = c(2, 3.4, 1.3, 0.8), A02 = c(3, 4.5, 2, 
7.6), A03 = c(1, 2.2, 4.4, 3.3), A04 = c(4, 3.6, 6.5, 4.5), A05 = c(6, 
7.2, 9.2, 1.1)), class = "data.frame", row.names = c(NA, -4L))

Upvotes: 2

Vishal A.
Vishal A.

Reputation: 1381

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

df_new <- df %>%
  mutate(new_column = as.numeric(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.

Upvotes: 2

Yuriy Saraykin
Yuriy Saraykin

Reputation: 8880

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)

Upvotes: 1

Noah
Noah

Reputation: 4414

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).

Upvotes: 0

Related Questions