CODEWITHSUNDEEP
r
Dr. Abrar
Dr. Abrar

Reputation: 337

Changing values of same column based on condition

I have a list of values in column Net.Chg that have negative, positive, and Zero. I want to replace all zeros with 1 if it is followed by a positive number, and with -1 if it is followed by a negative number.

Here is the sample data 

Time.Interval            Net.Chg 
2-Jan-17 _00:00:00.000000    NA
19:15 - 19:16           -0.0047
19:16 - 19:17            0
19:17 - 19:18            0
3-Jan-17 _00:00:00.000000    NA
03:45 - 03:46            0
03:54 - 03:55           -0.0002
19:43 - 19:44           -0.0008
20:01 - 20:02            0.0025
4-Jan-17 _00:00:00.000000    NA
00:54 - 00:55            0
01:10 - 01:11            0.0005
01:11 - 01:12            0

My concern is to change all positive values to 1 and all negative values to -1 as well. I used the following code 

data$Net.Chg <- sign(Net.Chg)

but it can not work with zeros. 

Time.Interval <- c('2-Jan-17 _00:00:00.000000', '19:15 - 19:16', '19:16 - 19:17', '19:17 - 19:18', '3-Jan-17 _00:00:00.000000', '03:45 - 03:46', '03:54 - 03:55', '19:43 - 19:44', '20:01 - 20:02', '4-Jan-17 _00:00:00.000000', '00:54 - 00:55', '01:10 - 01:11', '01:11 - 01:12')
Net.Chg <- c(NA, -0.0047, 0, 0, NA, 0, -0.0002, -0.0008, 0.0025, NA, 0, 0.0005, 0)

data <- data.frame(Time.Interval, Net.Chg)

The required out should be 

Time.Interval           Net.Chg     Required
2-Jan-17 _00:00:00.000000   NA      NA
19:15 - 19:16           -0.0047    -1
19:16 - 19:17            0         -1
19:17 - 19:18            0         -1
3-Jan-17 _00:00:00.000000    NA     NA
03:45 - 03:46            0         -1
03:54 - 03:55            -0.0002   -1
19:43 - 19:44            -0.0008   -1
20:01 - 20:02             0.0025    1
4-Jan-17 _00:00:00.000000    NA     NA
00:54 - 00:55            0          1
01:10 - 01:11            0.0005     1
01:11 - 01:12            0          1

If you look the date is irrelevant when changing Zero to 1 and -1

Upvotes: 0

Views: 70

Answers (2)

Balamurali N.R
Balamurali N.R

Reputation: 353

library(zoo)

df <- data.frame(Net.chg = c(NA, -0.0047, 0, 0, NA, 0, -0.002, -0.008, 0.0025, NA, 0, 0.005, 0))

Getting the row index of NA values

ri <- is.na(df$Net.chg)

Replacing all 0s with NA

df$Net.chg[df$Net.chg == 0] <- NA

Filling NAs with preceding numbers using locf() from zoo package

df$Net.chg <- na.locf(df$Net.chg, na.rm = F)

Then converting negative numbers to -1 and positive numbers to 1 

df$Net.chg[df$Net.chg < 0] <- -1
df$Net.chg[df$Net.chg > 0] <- 1

Refilling the original NA values again in the column

df[ri,] <- NA

Upvotes: 0

Pavel Filatov
Pavel Filatov

Reputation: 596

That's a simple solution there:

library(tidyverse)

data %>% 
  mutate(
    Required = case_when(
      Net.Chg > 0 ~ 1, 
      Net.Chg < 0 ~ -1,
      TRUE ~ NA_real_
    )
  ) %>% 
  fill(Required) %>% 
  mutate(Required = ifelse(is.na(Net.Chg), NA, Required))

First, we can assign 1 or -1 to Require if it is greater than or less than zero.

Second, we can fill it downward.

Third, recover NA values.

That's all!

And here's the output of last two columns:

   Net.Chg Required
1       NA       NA
2  -0.0047       -1
3   0.0000       -1
4   0.0000       -1
5       NA       NA
6   0.0000       -1
7  -0.0002       -1
8  -0.0008       -1
9   0.0025        1
10      NA       NA
11  0.0000        1
12  0.0005        1
13  0.0000        1

Upvotes: 1

Related Questions