Subtracting the first row from all following rows

user_8688494 0
Viewed 2233

From the following data set I want to subtract the values in the row labeled "BLK" from all subsequent rows (regardles of their lables). 

 label   Int_A Int_B Int_C
 BLK     10    20    30    
 SMP     12.5  22.5  35    
 STD     15    25    40

Desired output:

 label   Int_A Int_B Int_C 
 BLK     10    20    30    
 SMP     2.5   2.5   5    
 STD     5     5     10

It does not matter if the BLK-row remains unchanged or will be set to zero.

Unfortunately, all answers I found consider only one variable, not all. I tried using the dplyr package, especially rowwise() and transmute() (there is no need to keep the old values) but failed also in calling an entire row and not only a certain variable. In basic R I also tried (and failed) but working with dplyr would be preferable, as the entire data set could exist of more than one of these sections and subsetting is easy with group_by() using a separate column.

I would be very glad if you could give me some advise or the required code.

dplyr r
3 Answers

If we need to subtract the first row from others, use mutate_at to specify the index of numeric columns or mutate_if and then subtract the first element from the all the elements of the column

library(dplyr)
df1 %>% 
    mutate_at(2:4, funs(c(first(.), (. - first(.))[-1])) )

Or with mutate_if

df1 %>%
    mutate_if(is.numeric, funs(c(first(.), (. - first(.))[-1])) )
user_37322710 
df[-1, -1] <- df[-1 , -1] - df[rep(1, nrow(df) - 1), -1]
df
#   label Int_A Int_B Int_C
# 1   BLK  10.0  20.0    30
# 2   SMP   2.5   2.5     5
# 3   STD   5.0   5.0    10

From relevant rows and columns df[-1 , -1], subtract the first row repeated to the necessary number of rows (nrow(df) - 1).

user_18517120

As an alternative using no packages:

diff is a matrix of the same size like df, with first row 0 and the rest equal to the 1st row in df. Then simply subtract.

df <- mtcars

diff <- matrix(c(rep(0,ncol(df)),rep(as.numeric(df[1,]),nrow(df)-1)),nrow=nrow(df),ncol=ncol(df),byrow=T)

df - diff
user_68529370
Related