CODEWITHSUNDEEP
rdataframedplyr
John_dydx
John_dydx

Reputation: 961

Tricky Data manipulation

I have a dataset on which I would like to perform the following transformation: For each ID in x_1, change the largest negative number (closest to zero) under z_1 into zero. Leave other negative or positive numbers as they are. If there is no negative number or if there is a zero, do nothing.

x_1 <- c("A1", "A1","A1", "B10", "B10", "B10","B10", "B500", "C100", "C100", "C100", "D40", "G100", "G100")

z_1 <- c(1.1, 1.4, 1.6, -1.0, -2.2, 3, 2.3, 2.0, -3.4, -4.1, 2, 2, 2.4, -3.5)

A <- data.frame(x_1, z_1)

Desired result:

x_1   z_1
A1    1.1
A1    1.4
A1    1.6
B10  -2.2
B10   0
B10   2.3
B10   3.0
B500  2.0
C100 -4.1
C100  0
C100  2.0
D40   2.0
G100  0
G100  2.4

I've tried a few things using dplyr but I don't seem to be getting the right result. 

A3 <- A %>%group_by(x_1, z_1)%>% summarize(neg = max(z_1 < 0))

Clearly, this code is incomplete but I would really appreciate any assistance with this.

Upvotes: 1

Views: 51

Answers (2)

akrun
akrun

Reputation: 887088

Using data.table

library(data.table)
i1 <- setDT(A)[, .I[z_1== max(c(-Inf, z_1[z_1 <0]))],,x_1]$V1
A[i1, z_1:= 0.0][order(x_1, z_1)]

Upvotes: 1

MrFlick
MrFlick

Reputation: 206197

This seems to produce the result you desire

A %>% group_by(x_1) %>% 
    mutate(z_1=ifelse(z_1==max(c(-Inf,z_1[z_1<0])), 0,z_1)) %>% 
    arrange(x_1, z_1)

Upvotes: 3

Related Questions