Create new column by adding conditions on rows in data table in R

Question

Like the title, it's complicated to describe , so I'll just show the code , what I got and what I want it to be.

set.seed(1)
df<-data.frame('X1'=rnorm(10),
               'X2'=rnorm(10),
               'X3'=c(c(rep('A',5)),c(rep('B',5))))

## create a bew column 'SPX2' which is the smallest positive number OF X2 
## of each group(A and B)

require(data.table)
setDT(df)[X2>0,SPX2:=min(X2),by=X3]
df

then I got the result as:

            X1          X2 X3      SPX2
 1: -0.6264538  1.51178117  A 0.3898432
 2:  0.1836433  0.38984324  A 0.3898432
 3: -0.8356286 -0.62124058  A        NA
 4:  1.5952808 -2.21469989  A        NA
 5:  0.3295078  1.12493092  A 0.3898432
 6: -0.8204684 -0.04493361  B        NA
 7:  0.4874291 -0.01619026  B        NA
 8:  0.7383247  0.94383621  B 0.5939013
 9:  0.5757814  0.82122120  B 0.5939013
10: -0.3053884  0.59390132  B 0.5939013

and what I want is :

            X1          X2 X3      SPX2
 1: -0.6264538  1.51178117  A 0.3898432
 2:  0.1836433  0.38984324  A 0.3898432
 3: -0.8356286 -0.62124058  A 0.3898432
 4:  1.5952808 -2.21469989  A 0.3898432
 5:  0.3295078  1.12493092  A 0.3898432
 6: -0.8204684 -0.04493361  B 0.5939013
 7:  0.4874291 -0.01619026  B 0.5939013
 8:  0.7383247  0.94383621  B 0.5939013
 9:  0.5757814  0.82122120  B 0.5939013
10: -0.3053884  0.59390132  B 0.5939013

cause I want to create a new column df$X4<-df$SPX2 - df$X2,o any other operations that require SPX2 to be like above. I did my search and found several posts like the one here , but that's not what I try do here.

Anyone know how to achieve this?

This should work: setDT(df)[,SPX2:=min(X2[X2 > 0]),by=X3]. I've added the condition to the minimum clause so that the minimum is taken over all X2 which are greater than zero. — jav
– jav, Commented Aug 14, 2017 at 3:47
Thanks @jav, your code is checked ,worked perfectly. Never aware of imposing conditions like that, enlightening! Why not you put it as answer and with a little explanation about why we could do X2[X2 > 0] here for others who need it and me to learn. I could take it as answer for this question by the way. — Jia Gao
– Jia Gao, Commented Aug 14, 2017 at 3:52
Thanks @Ronak Shah for your edition , it made the question much more readable and understandable. I'll be aware of these kind of problems with future posts I may make. — Jia Gao
– Jia Gao, Commented Aug 14, 2017 at 3:55

Aramis7d · Accepted Answer · 2017-08-14 05:44:21Z

2

tidyverse alternative:

df %>%
  group_by(X3) %>%
  mutate(SPX2 = min(X2[X2>0]))

which gives:

           X1          X2     X3      SPX2
        <dbl>       <dbl> <fctr>     <dbl>
 1 -0.6264538  1.51178117      A 0.3898432
 2  0.1836433  0.38984324      A 0.3898432
 3 -0.8356286 -0.62124058      A 0.3898432
 4  1.5952808 -2.21469989      A 0.3898432
 5  0.3295078  1.12493092      A 0.3898432
 6 -0.8204684 -0.04493361      B 0.5939013
 7  0.4874291 -0.01619026      B 0.5939013
 8  0.7383247  0.94383621      B 0.5939013
 9  0.5757814  0.82122120      B 0.5939013
10 -0.3053884  0.59390132      B 0.5939013

answered Aug 14, 2017 at 5:44

Aramis7d

2,49620 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jia Gao Over a year ago

Thanks@Aramis7d, good solution, I took it as the answer to this question.

jav · Accepted Answer · 2017-08-14 21:32:33Z

Using the data.table package:

setDT(df)    
df[,SPX2:=min(X2[X2 > 0]),by=X3]

What this does is for each value of X3, subset on positive values of X2 (i.e. X2[X2 > 0]) and then take the minimum over all positive values. Note that if there are no positive values (i.e. X2[X2 > 0] is empty), then the resulting value would be Inf. Keep this in mind especially if you want to do any further calculations using SPX2.

As per your question why X2[X2 > 0] works, think about it as follows: For each value of X3, a vector of corresponding values of X2 is returned. Now, you can just perform regular vector operations on this vector, one of which is subsetting via X2 > 0. It works much like the following:

x2 = c(-1, 1, 2, 3, -2, 4)
x2[x2 > 0]
# [1] 1 2 3 4

Hope this helps!

Collectives™ on Stack Overflow

Create new column by adding conditions on rows in data table in R

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related