Perform a previous values calculation in column on R

Asked

Viewed 62 times

2

I would like to assemble a column according to the result of 2 lines of another column, follows the formula below, I have no idea how to assemble this in R or Python and would like a collaboration from you.

Giving an example in Excel

                coluna 1   coluna 2
      célula D2 - 558,8       1
      célula D3 - 584,3       1
      célula D4 - 603,3   =SE(D4-D2>=D2*0,02;1;SE(D4-D2<=-D2*0,02;-1;0))

And so on and so forth...

python r excel

2 answers

2

Here is a solution in R base, vectorized with sign.

n <- nrow(dados)
inx <- seq_len(n)[-(1:2)]
dados$col2 <- 1
dados$col2[inx] <- sign(dados$col1[inx] - 1.02*dados$col1[inx - 2])

Test data

col1 <- c(558.8, 584.3, 603.3)
dados <- data.frame(col1)

1


Maybe this will help you, using dplyr

Data test frame

dados <- data.frame(coluna_1 = c(558.8, 584.3, 603.3))

The logic

library(dplyr)

dados <- dados %>% mutate(coluna_2 = case_when(
  coluna_1 - lag(coluna_1, n = 2) >=  lag(coluna_1, n = 2) * 0.02 ~ 1,
  coluna_1 - lag(coluna_1, n = 2) <=  (-lag(coluna_1, n = 2)) * 0.02 ~ -1,
  T ~ 0 
))
dados$coluna_2[1:2] <- 1

With mutate we create a new column called column_2, the case_when serves to do something when a condition is accepted, the lag is to give a delay, in this case we give a lag of 2 which would be two lines above. T ~ 0 is when no condition was found then fill with 0.

Note: As the calculation is done by returning two lines the first results will be zero, because there is no way to calculate.

A Python possibility using pandas

import pandas as pd

dados = pd.DataFrame({'coluna_1': [558.8, 584.3, 603.3]})

dados.loc[dados['coluna_1'] - dados['coluna_1'].shift(2) >= dados['coluna_1'].shift(2) * 0.02, 'coluna_2'] = 1
dados.loc[dados['coluna_1'] - dados['coluna_1'].shift(2) <= -dados['coluna_1'].shift(2) * 0.02, 'coluna_2'] = -1
dados['coluna_2'].iloc[0:2] = 1
dados.fillna(0)

  • Thank you so much for the lesson, it helped me a lot, would have some way to leave the first items with the value 1?

  • @Roneywesleygalan, good afternoon! As the first two values do not enter into the conditions of fulfillment of the remainder, we can place 1 arbitrarily. I updated the code to resolve this issue. If conditions are always met, you can put fill_na or T ~ equal to 1 as they will be the only missing results. Example: fill_na(1) or in R T ~ 1 Hug!

  • Thank you so much!! Helped me so much, one day I arrive at this level expert rsrs.

  • @Roneywesleygalan, for nothing! needing we are there! It will surely come! Hug!!!

Browser other questions tagged python r excel

You are not signed in. Login or sign up in order to post.