0

I have a dataframe as below:

data <- data.table (x= c(1,2,3,4,5), y= c(7,6,8,9,10), z= c('a','b','c','d','e'), w=c(01.05, 03.04, 11.08, 05.07, 09.18))

I need to make a new column containing values as I describe below: whenever x equals 3 or 5, or y equals 7, then make a new column called M and insert the value in the corresponding row from w into column M, and leave the other rows empty. The data shuld then look like this:

data <- data.table (x= c(1,2,3,4,5), y= c(7, 6,8,9,10), z= c('a','b','c','d','e'), w=c(01.05, 03.04, 11.08, 05.07, 09,18), M=c(01.05, NA, 11.08, NA, 09.18)

I am a beginner in R and searched a lot but could not find a solution. I tried using case_when and apply functions but no luck. I would really appreciate it if anyone can help me with this.

VvdL
  • 2,799
  • 1
  • 3
  • 14
Rara
  • 105
  • 9

1 Answers1

1

As you are using data.table, this is the way to assign := a new variable. You can use ifelse or even better fifelse (to ensure M will be the same class as w) to check for your conditions, using %in% and |.

library(data.table)
data <- data.table (x = c(1:5), y = c(7,6,8,9,10), z = letters[1:5], 
                    w = c(01.05, 03.04, 11.08, 05.07, 09.18))

data[, M := fifelse(x %in% c(3,5) | y == 7, w, NA_real_)]
data
#>    x  y z     w     M
#> 1: 1  7 a  1.05  1.05
#> 2: 2  6 b  3.04    NA
#> 3: 3  8 c 11.08 11.08
#> 4: 4  9 d  5.07    NA
#> 5: 5 10 e  9.18  9.18

Edit: r2evans' approach in the comment below this answer does the same but is more elegant:

data[x %in% c(3,5) | y == 7, M := w]

VvdL
  • 2,799
  • 1
  • 3
  • 14
  • 1
    If you're already loading `data.table`, I strongly recommend the habit pattern of using `fifelse`. Not only does it guard against some mistakes like inadvertently converting from numbers to strings, it is not prone to [`ifelse` stripping classes](https://stackoverflow.com/q/6668963/3358272). – r2evans Dec 02 '22 at 16:17
  • 1
    An alternative to using ifelse logic at all could be: `data[x %in% c(3,5) | y == 7, M := w]` (assuming that `M` does not previously exist ... which it doesn't here) – r2evans Dec 02 '22 at 16:18
  • @r2evans-GONAVYBEATARMY I like your alternative and think it's better! Will you include it as an answer or shall I include that option in mine crediting you? – VvdL Dec 02 '22 at 16:23
  • 1
    Please add it to yours, it's not cosmically different enough to warrant another answer. (With or without credit, I'm good. Thanks!) – r2evans Dec 02 '22 at 16:27
  • 1
    @VvdL Thank you both for your detailed reply. I just learned that I had confused data.table with dataframe. My data was indeed a dataframe and therefore, Eric's answer worked. Your reply taught me something new. – Rara Dec 05 '22 at 14:34
  • @VvdL I need your help with something else here. Could you please tell me how I can exclude missing values in this line? data[, M := fifelse(x %in% c(3,5) | y == 7, w, NA_real_)] – Rara Dec 15 '22 at 16:13
  • @Eric I need your help with something else here. Could you please tell me how I can exclude missing values in this line? data %>% mutate(m = ifelse(x %in% c(3,5)|y==7,w,NA_real_)) – Rara Dec 15 '22 at 16:15