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?