How to sort these data to subtaract a specific column with other columns in R

Question

Here is a small sample of my data, please consider I have more than 100 columns

I   D   M   N   Score
12  23  11  12  22
11  11  11  11  33
11  11  10  11  44
12  11  12  11  66
11  11  11  12  50

I want to subtract the Score from each column to get EXACTLY the following table:

Score1  I   Score2  D   Score3  M   Score4  N
10  12  -1  23  12  11  10  12
22  11  22  11  0   11  22  11
33  11  33  11  1   10  33  11
54  12  55  11  -1  12  55  11
39  11  39  11  0   11  38  12

Is there a short R code to get this table?

Answer 1

This is exactly what you want:

Once again I needed the help of the community (@akrun) Combining two dataframes with alternating column position

library(dplyr)
df %>% 
  mutate(across(-Score, ~ Score - ., .names = "Score{1:(ncol(df)-1)}")) %>% 
  select(-Score) %>% 
  dplyr::select(all_of(c(matrix(names(.), ncol = ncol(df)-1, byrow = TRUE))))

   I Score1  D Score2  M Score3  N Score4
1 12     10 23     -1 11     11 12     10
2 11     22 11     22 11     22 11     22
3 11     33 11     33 10     34 11     33
4 12     54 11     55 12     54 11     55
5 11     39 11     39 11     39 12     38

Answer 2

Try this for any number of columns

#Load the data.frame
#===================================
x <- "
I   D   M   N   Score
12  23  11  12  22
11  11  11  11  33
11  11  10  11  44
12  11  12  11  66
11  11  11  12  50
"
df <- read.table(textConnection(x) , header = T)
#===================================

ans <- list()
scr <- df["Score"]
cn <- c()
for ( i in 1:(ncol(df)-1)){
  cn <- c(cn , paste0("Score" , i) ,names(df)[i])
  c <-  scr - df[i]
  ans <- append(ans , c(c , df[i]))
}
df <- do.call(cbind , ans)
colnames(df) <- cn
df
#>      Score1  I Score2  D Score3  M Score4  N
#> [1,]     10 12     -1 23     11 11     10 12
#> [2,]     22 11     22 11     22 11     22 11
#> [3,]     33 11     33 11     34 10     33 11
#> [4,]     54 12     55 11     54 12     55 11
#> [5,]     39 11     39 11     39 11     38 12

^{Created on 2022-06-07 by the reprex package (v2.0.1)}

Answer 3

Another possible solution, using purrr::pmap_dfc:

library(tidyverse)

pmap_dfc(list(df, list(df$Score), names(df), 1:ncol(df)),
 ~ data.frame(..1, ..2 -..1) %>% set_names(c(..3, str_c("Score_", ..3)))) %>% 
  select(-last_col(0:1))

#>    I Score_I  D Score_D  M Score_M  N Score_N
#> 1 12      10 23      -1 11      11 12      10
#> 2 11      22 11      22 11      22 11      22
#> 3 11      33 11      33 10      34 11      33
#> 4 12      54 11      55 12      54 11      55
#> 5 11      39 11      39 11      39 12      38

Answer 4

In Base R:

 cbind(df[-5], score = df[,5] - df[-5])

   I  D  M  N score.I score.D score.M score.N
1 12 23 11 12      10      -1      11      10
2 11 11 11 11      22      22      22      22
3 11 11 10 11      33      33      34      33
4 12 11 12 11      54      55      54      55
5 11 11 11 12      39      39      39      38  

if you need the ordering as shown above then:

cbind(score = df[,5] - df[-5], df[-5])[c(matrix(1:8, 2, byrow = 2))]

  score.I  I score.D  D score.M  M score.N  N
1      10 12      -1 23      11 11      10 12
2      22 11      22 11      22 11      22 11
3      33 11      33 11      34 10      33 11
4      54 12      55 11      54 12      55 11
5      39 11      39 11      39 11      38 12

Answer 5

df <- data.frame(
  I = c(12L, 11L, 11L, 12L, 11L),
  D = c(23L, 11L, 11L, 11L, 11L),
  M = c(11L, 11L, 10L, 12L, 11L),
  N = c(12L, 11L, 11L, 11L, 12L),
  Score = c(22L, 33L, 44L, 66L, 50L)
)

library(tidyverse)

df %>% 
  mutate(across(I:N, .fns = ~Score - .x, .names = "Score_{.col}"))
#>    I  D  M  N Score Score_I Score_D Score_M Score_N
#> 1 12 23 11 12    22      10      -1      11      10
#> 2 11 11 11 11    33      22      22      22      22
#> 3 11 11 10 11    44      33      33      34      33
#> 4 12 11 12 11    66      54      55      54      55
#> 5 11 11 11 12    50      39      39      39      38

^{Created on 2022-06-06 by the reprex package (v2.0.1)}