CODEWITHSUNDEEP
r
Rahul Mishra
Rahul Mishra

Reputation: 1

How to subtract multiple columns in R with condition

I have a dataset which looks like this. In my new dataset, I want to subtract the amount column(s) with principal column(s) and remainder(s) column.

For instance, if the amount column is 4, the principalcolumn is 2 and remainder is 3, then the first amount column must be subtracted from the first principal column and first remainder column, 2nd with 2nd principal column and 2nd remainder column and 3rd with 3rd remainder column (since now there is no more principal column). And the last amount4 column must stay as it is as newamount4

amount1  amount2   amount3 amount4  principal1  principal2  remainder1  remainder2    remainder3  
 100      250       150    100           250       100         80         100          100 
 200      200       350    25            450       100        120         100          50
 300      150       450    30            200       100        150         100          100
 250      550       550    100           100       200         50         500          200
 550      200       650    200          250       200        500         100          500

My new dataset must look like this. Please note am stands for amount and pr stands for principal and rem stands for remainder.

 newamount1          newamount2         newamount3     newamount4       
 20(am1-pr1-rem1)    150(am2-pr2-rem2)  50(am3-rem3)   amount4        
 80                  100                300            amount4        
 150                 50                 350            amount4        
 200                 50                 350            amount4        
 50                  100                100            amount4

My code includes following script through which I am trying to get my desired output. My dataframe is named as revised

am_cols <- grep('amount_', names(revised)) 
rm_cols <- grep('principal_', names(revised)) 
sm_cols <- grep('remainder_', names(revised))

revised[is.na(revised)] <- 0


result <- cbind(revised[head(am_cols, rm_cols, length(sm_cols))] - revised[rm_cols] - revised[sm_cols], 
                revised[head(am_cols, length(rm_cols))] - revised[sm_cols], 
                revised[tail(am_cols, -length(rm_cols), -length(sm_cols))])

Upvotes: 0

Views: 120

Answers (1)

akrun
akrun

Reputation: 886948

We can use split.default

lst1 <- lapply(split.default(df1,
        sub("\\D+", "", names(df1))), function(x) Reduce(`-`, x))
df1[paste0('newamount', seq_along(lst1)] <- lst1

data

df1 <- structure(list(amount1 = c(100L, 200L, 300L, 250L, 550L), amount2 = c(250L, 
200L, 150L, 550L, 200L), amount3 = c(150L, 350L, 450L, 550L, 
650L), amount4 = c(100L, 25L, 30L, 100L, 200L), principal1 = c(250L, 
450L, 200L, 100L, 250L), principal2 = c(100L, 100L, 100L, 200L, 
200L), remainder1 = c(80L, 120L, 150L, 50L, 500L), remainder2 = c(100L, 
100L, 100L, 500L, 100L), remainder3 = c(100L, 50L, 100L, 200L, 
500L)), class = "data.frame", row.names = c(NA, -5L))

Upvotes: 2

Related Questions