R - Filling column of dataframe with values of other columns based on a string in another column

user_11290981 0
Viewed 776

I actually have a pretty easy task to do, but I just cannot find a solution. I have one df with 2 columns of numbers and 1 column of 3 different strings. I want to add now a 4th column V4 that I want to fill with the values of V1 and V2, depending on the V3 column.

> df
   V1 V2 V3
1   1  6  P
2   2  7  P
3   3  8  N
4   4  9  B
5   5 10  P
6   6 11  B
7   7 12  N
8   8 13  N
9   9 14  P
10 10 15  P

structure(list(V1 = 1:10, V2 = 6:15, V3 = c("P", "P", "N", "B", "P", "B", "N", "N", "P", "P")), row.names = c(NA, -10L), class = "data.frame")

For "P" I want to take V1, for "N" I want to take V2 and for "B" I ideally want both values next to each other (V1|V2), but without making them a character, they have to stay numeric. If that is not possible then the higher number should be filled in.

My output should look like this (as numeric). Or if not possible to display 4|9 or something similar as a numeric, then just the nigher number of these 2.

   V1 V2 V3   V4
1   1  6  P    1
2   2  7  P    2
3   3  8  N    8
4   4  9  B  4|9
5   5 10  P    5
6   6 11  B 6|11
7   7 12  N   12
8   8 13  N   13
9   9 14  P    9
10 10 15  P   10

I found a lot how to do this with just filling the column, but I cant find any examples filling the column with values of other columns based on 3 conditions. I tried if-statements with loops and subsets, but I failed so far.

subset conditional-statements if-statement r
1 Answers

We may create the condition with case_when.

library(dplyr)
library(stringr)
df %>% 
    mutate(V4 = case_when(V3 == 'B' ~ str_c(V1, V2, sep = '|'),
                          V3 == 'P' ~ as.character(V1), 
                           TRUE ~ as.character(V2)))

-output

df
   V1 V2 V3   V4
1   1  6  P    1
2   2  7  P    2
3   3  8  N    8
4   4  9  B  4|9
5   5 10  P    5
6   6 11  B 6|11
7   7 12  N   12
8   8 13  N   13
9   9 14  P    9
10 10 15  P   10

If we need a numeric column and the 'B' should be NA

df %>%
    mutate(V4 = case_when(V3 == 'P' ~ V1,
                          V3 == 'N' ~ V2))

-output

   V1 V2 V3 V4
1   1  6  P  1
2   2  7  P  2
3   3  8  N  8
4   4  9  B NA
5   5 10  P  5
6   6 11  B NA
7   7 12  N 12
8   8 13  N 13
9   9 14  P  9
10 10 15  P 10

Or if we need the numeric column and the max per row, use pmax to return the max per row when 'B' is the case

df %>%
    mutate(V4 = case_when(V3 == 'P' ~ V1,
                          V3 == 'N' ~ V2, V3 == 'B' ~ pmax(V1, V2)))

-output

  V1 V2 V3 V4
1   1  6  P  1
2   2  7  P  2
3   3  8  N  8
4   4  9  B  9
5   5 10  P  5
6   6 11  B 11
7   7 12  N 12
8   8 13  N 13
9   9 14  P  9
10 10 15  P 10
user_37322710
Related