I have data frame looks like this above and I want to convert this to:

How can I do this transform? I would appreciate any suggestion. Thank you so much!

I have tried some things but did not really work.I have to do the change based on two columns information and that was confusing me.

CodePudding user response：

data.table

df <- read.table(text = "set    inst    ind color_Blue
1   0   0   70
1   0   1   60
1   0   2   50
1   1   0   30
1   1   1   20
1   1   2   66
2   0   0   35
2   0   1   22
2   0   2   28
2   1   0   90
2   1   1   47
2   1   2   23", header = T)

library(data.table)
dcast(
  data = setDT(df),
  formula = inst   set ~ paste0("inst_", ind),
  value.var = "color_Blue"
)
#>    inst set inst_0 inst_1 inst_2
#> 1:    0   1     70     60     50
#> 2:    0   2     35     22     28
#> 3:    1   1     30     20     66
#> 4:    1   2     90     47     23

^{Created on 2023-01-19 with reprex v2.0.2}

CodePudding user response：

You can use pivot_wider() from tidyr for reshaping.

library(tidyr)

df %>%
  pivot_wider(names_from = ind, values_from = color_Blue, names_prefix = 'inst_')

# # A tibble: 4 × 5
#     set  inst inst_0 inst_1 inst_2
#   <int> <int>  <int>  <int>  <int>
# 1     1     0     70     60     50
# 2     1     1     30     20     66
# 3     2     0     35     22     28
# 4     2     1     90     47     23

Data

df <- read.table(text = "
set inst    ind color_Blue
1   0   0   70
1   0   1   60
1   0   2   50
1   1   0   30
1   1   1   20
1   1   2   66
2   0   0   35
2   0   1   22
2   0   2   28
2   1   0   90
2   1   1   47
2   1   2   23", header = TRUE)