How to restructure columns which some of them should have the same values in R

Question

I have a sample like this

df<-read.table (text=" People   Sand    ClaQQ   Time    IK1 K1Q1    K2Q1    K3Q1    GIQ1    Zoo1    IK2 K1Q2    K2Q2    K3Q2    GIQ2    Zoo2    IK3 K1Q3    K2Q3    K3Q3    GIQ3    Zoo3    IK4 K1Q4    K2Q4    K3Q4    GIQ4    Zoo4    IK5 K1Q5    K2Q5    K3Q5    GIQ5    Zoo5    IK6 K1Q6    K2Q6    K3Q6    GIQ6    Zoo6    IK7 K1Q7    K2Q7    K3Q7    GIQ7    Zoo7    IK8 K1Q8    K2Q8    K3Q8    GIQ8    Zoo8    Poor
1   F   Y   A   90  5   2   5   A   F   21  4   2   5   B   P   17  5   2   5   E   P   15  1   2   1   B   F   12  4   2   2   A   P   9   4   2   2   A   F   4   4   4   1   B   F   2   5   4   3   A   P   Y
2   K   N   B   90  1   5   2   A   P   21  1   5   5   B   P   17  1   5   3   E   P   15  1   2   3   B   P   12  4   2   3   E   P   9   4   4   3   C   F   4   4   3   5   C   P   2   4   3   5   B   F   N

", header=TRUE)

I want to get the following table

People  KQ  Sand    ClaQQ   Time    IK  GIQ Zoo Poor    Score
1   K1Q1    F   Y   A   90  A   F   Y   5
1   K2Q1    F   Y   A   90  A   F   Y   2
1   K3Q1    F   Y   A   90  A   F   Y   5
1   K1Q2    F   Y   A   21  B   P   Y   4
1   K2Q2    F   Y   A   21  B   P   Y   2
1   K3Q2    F   Y   A   21  B   P   Y   5
1   K1Q3    F   Y   A   17  E   P   Y   5
1   K2Q3    F   Y   A   17  E   P   Y   2
1   K3Q3    F   Y   A   17  E   P   Y   5
1   K1Q4    F   Y   A   15  B   F   Y   1
1   K2Q4    F   Y   A   15  B   F   Y   2
1   K3Q4    F   Y   A   15  B   F   Y   1
1   K1Q5    F   Y   A   12  A   P   Y   4
1   K2Q5    F   Y   A   12  A   P   Y   2
1   K3Q5    F   Y   A   12  A   P   Y   2
1   K1Q6    F   Y   A   9   A   F   Y   4
1   K2Q6    F   Y   A   9   A   F   Y   2
1   K3Q6    F   Y   A   9   A   F   Y   2
1   K1Q7    F   Y   A   4   B   F   Y   4
1   K2Q7    F   Y   A   4   B   F   Y   4
1   K3Q7    F   Y   A   4   B   F   Y   1
1   K1Q8    F   Y   A   5   A   P   Y   5
1   K2Q8    F   Y   A   5   A   P   Y   4
1   K3Q8    F   Y   A   5   A   P   Y   3
2                                   
2                                   
2                                   
2                                   

I have tried to use the following link: Reshaping data.frame from wide to long format

But it did not help me as it is required multiple steps to run it.

Part of the steps that I have done are as follows:

 library(dplyr)
 library(tidyr)
 library(stringr)
 df %>% 
  select(People, starts_with('IK')) %>% 
   pivot_longer(cols = starts_with('IK'), values_to = 'Score', 
             names_to = 'IK') 

Answer 1

We can use pivot_longer to reshape the columns from 'IK1' to 'Zoo8', then reshape again the 'K1Q' to 'K3Q' as separate

library(dplyr)
library(tidyr)
df %>%
   pivot_longer(cols = IK1:Zoo8,  names_to = c(".value", "grp"), 
    names_pattern= "(.*[A-Za-z])(\\d+$)") %>%
   pivot_longer(cols = K1Q:K3Q, names_to = "KQ", values_to = "Score") %>% 
   unite(KQ, KQ, grp, sep="")

-output

# A tibble: 48 x 10
#   People Sand  ClaQQ Time  Poor  KQ       IK GIQ   Zoo   Score
#    <int> <chr> <chr> <chr> <chr> <chr> <int> <chr> <chr> <int>
# 1      1 F     Y     A     Y     K1Q1     90 A     F         5
# 2      1 F     Y     A     Y     K2Q1     90 A     F         2
# 3      1 F     Y     A     Y     K3Q1     90 A     F         5
# 4      1 F     Y     A     Y     K1Q2     21 B     P         4
# 5      1 F     Y     A     Y     K2Q2     21 B     P         2
# 6      1 F     Y     A     Y     K3Q2     21 B     P         5
# 7      1 F     Y     A     Y     K1Q3     17 E     P         5
# 8      1 F     Y     A     Y     K2Q3     17 E     P         2
# 9      1 F     Y     A     Y     K3Q3     17 E     P         5
#10      1 F     Y     A     Y     K1Q4     15 B     F         1
# … with 38 more rows

NOTE: The 'Zoo6' column was parsed as logical FALSE.

df$Zoo6 <- "F"