CODEWITHSUNDEEP
r
mcpeterson
mcpeterson

Reputation: 5134

Normalizing a column of data by another entry in a specific row for R

So I have a dataset (this is a toy example)

dates <- c(1,1,1,2,2,2,3,3,3)
dates2 <- c(-1,1,2,-1,1,2, -1, 2,3) 
amt <- c(1000, 100, 100, 1000, 100, 100, 1000, 100, 100)

dat <- cbind(dates, dates2, amt)

And for dataframe dat, I need to divide amt, by itself but only where dates2 is = -1. So I'd get an output dataframe like:

clean
1 1 0.10  (IE 100 / 1000, for row 2)
1 2 0.10
2 1 0.10
2 2 0.10
3 2 0.10
3 3 0.10

Does someone know an easy way to tackle this? (My brain is like mush right now)

Upvotes: 2

Views: 110

Answers (2)

Ricardo Saporta
Ricardo Saporta

Reputation: 55350

Update:

Nice, one liner, compliments of @Arun (in the comments below): 

DT[, amt := { amt <- amt/amt[dates2 == -1] }, by=dates][dates2 != -1]

or, more succinctly, still: 

DT[, amt := amt/amt[dates2 == -1], by=dates][dates2 != -1]

Original Answer:

library(data.table)

DT <- data.table(dat, key="dates")

# grab "-1" rows, at same time, change col name for simplicity
DT.dates2 <- setnames(DT[dates2==(-1)], "amt", "amt.d")

# remove rows where dates2 == -1
DT <- DT[dates2 != -1] 

# divide as required
DT[DT[dates==dates2][DT.dates2], amt := amt / amt.d]

Results:

DT 

   dates dates2 amt
1:     1      1 0.1
2:     1      2 0.1
3:     2      1 0.1
4:     2      2 0.1
5:     3      2 0.1
6:     3      3 0.1
>

Upvotes: 3

IRTFM
IRTFM

Reputation: 263331

 by(dat, dat[1], FUN= function(dfm) {
        dfm[ dfm$dates2 != -1, 3] <-dfm[ dfm$dates2!= -1, 3]/dfm[ dfm$dates2== -1, 3]
        return(dfm[ dfm$dates2 !=-1 ,])  } )
dates: 1
  dates dates2 amt
2     1      1 0.1
3     1      2 0.1
----------------------------------------------------------------------------- 
dates: 2
  dates dates2 amt
5     2      1 0.1
6     2      2 0.1
----------------------------------------------------------------------------- 
dates: 3
  dates dates2 amt
8     3      2 0.1
9     3      3 0.1

You can use do.call(rbind, ...) if you want them as a dataframe again.

Upvotes: 1

Related Questions