How to sort these data to subtaract a specific coloumn with other coloumns in R-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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)}

CodePudding user response：

Another possible solution, using purrr::pmap_dfc:

library(tidyverse)

pmap_dfc(list(df, df$Score, names(df), 1:ncol(df)),
 ~ data.frame(..2 -..1, ..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 10      12 10      23 33      11 54      12
#> 2 11      11 22      11 33      11 55      11
#> 3 11      11 22      11 34      10 55      11
#> 4 10      12 22      11 32      12 55      11
#> 5 11      11 22      11 33      11 54      12