3

I am a little afraid to ask this question considering all the warnings I see about similarly phrased questions. I do not know how to phrase this question, and I have spent at least the past 5 hours searching for a solution to my particular case. This is a follow-up question to an earlier question I asked. Anyway, I have this data that resembles the following:

Fullname Competency SOCW725 SOCW748 SOCW752 Competency.1 SOCW725.1 SOCW748.1 SOCW799 Competency.2 SOCW751 SOCW748.2 SOCW799.1
D. Smith 1 3.5 2.56 3.25 2 4.5 3.7 3.33 3 3.21 2.07 4.32
H. Black 1 2.09 3.23 4.00 2 3.45 3.45 2.34 3 1.23 3.32 4.54

There are a total of 9 competencies stretching across the spreadsheet and there are three course numbers (i.e., SOCW###) that should be grouped with each preceding Competency. So the data should look something like this:

FullName Competency Course Rating
D. Smith 1 SOCW725 3.5
D. Smith 1 SOCW748 2.56
D. Smith 1 SOCW752 3.25
D. Smith 2 SOCW725 4.5
D. Smith 2 SOCW748 3.7
D. Smith 2 SOCW799 3.33
D. Smith 3 SOCW751 3.21
D. Smith 3 SOCW748 2.07
D. Smith 3 SOCW799 4.32
H. Black 1 SOCW725 2.09
H. Black 1 SOCW748 3.23
H. Black 1 SOCW752 4.00
H. Black 2 SOCW725 3.45
H. Black 2 SOCW748 3.45
H. Black 2 SOCW799 2.34
H. Black 3 SOCW751 1.23
H. Black 3 SOCW725 3.32
H. Black 3 SOCW725 4.54

I can accomplish what I want by subsetting the dataframe and working with 5 variables at a time (i.e., the FullName, The Competency column, and each of the course rating columns). I can create 9 separate dataframes and use rbind to create the final dataframe. For example, to subset I use:

x = 3
y = 6
tempdf <- my_data[c(2,x:y)]

I can then use pivot_longer to produce the 9 dataframes:

d(i) <- pivot_longer(tempdf, -c(1,2),
                   names_to = "Course",
                   values_to = "Ratings",
                   names_pattern = '^(SOCW[^.]+)')

After making sure the rownames match, I can then use rbind to bind together all the subsetted dataframes:

    colnames(d1) <- colnames(d)
d <- rbind(d1,d2,d3,d4,d5,d6,d7,d8,d9)

I can produce the correct output using a pivot_longer and rbind inside a for loop:

d <- data.frame(Name=character(),
                 Competency=integer(), 
                 Course=character(),
                 Rating=double())
                 
x <- c(2, 6, 10, 14, 18, 22, 26, 30, 34)

for (val in x) {
  y <- val + 3
  df <- my_data[c(1,val:y)] %>% 
    select(Name, starts_with("Comp"), starts_with("SOCW")) %>% 
    pivot_longer(cols = starts_with("SOCW")) %>% 
    mutate(name = str_remove(name, "\\.+\\d+$"))
    colnames(df) <- c("Name","Competency","Course","Rating")
    d <- rbind(d,df)
}

I know that is a lot of steps, and that there is a much cleaner way to do this. I am frustrated because I keep attempting various solutions and keep getting errors. I tried using pivot_longer, but I do not understand how to handle the Competency column. This was my latest attempt which produced the following error: Error in str_extract(names, names_to, regex = names_pattern) : is_string(regex) is not TRUE

dlong <- my_data %>% 
  select(FullName, starts_with("Comp"), starts_with("SOCW")) %>% 
  pivot_longer(cols = c(starts_with("Comp"), starts_with("SOCW")), 
               names_to = c("Competency", ".value"), 
               names_pattern = c('\\d', '^(SOCW[^.]+)'))

I have tried several variations of the above code, but I do not want to list all of them. In essence, I am uncertain about how to code names_to and names_pattern. I am also uncertain about other parameters I might provide to pivot_longer that would do what I want. As I mentioned, I have done numerous internet searches and combed through dozens of Stack Overflow answers to similar problems, but have not found a solution (or if I saw one, I didn't understand how to use it with my data).

My Data

structure(list(Student.Name = c("D. Smith", "H. Black", "B. Head", 
"R. Rodriguez", "J. Snow", "B. Convy", "R. Dawson", "E. Edwards", 
"J. Jones", "E. Smith", "T. Aikman", "D. Pearson", "H. Truman", 
"D. Trump", "J. Biden", "K. Harris", "G.W. Bush", "H.W. Bush"
), Competeny = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L), SOCW725 = c(3.5, 4.1, NA, 4.3, 3.1, 
3, NA, 3.6, 4.3, 3.3, 3, 5, NA, 4.7, NA, 3.1, NA, 4), SOCW752 = c(4.2, 
4, 3.25, 3.75, 4.3, 4.4, 4.5, 3.8, 4.4, 4.2, 3.25, 4.1, 4, 4, 
5, 3.8, 3.6, 3.6), SOCW782 = c(2.7, 4.4, 4, 4.8, 4, 4.3, 4, 3.9, 
4.9, 5, 3.9, 3.9, 5, 4.8, 4.7, 4, 4.7, 3.3), Competeny.1 = c(2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L), SOCW725.1 = c(4.2, 4.2, NA, 3.72, 4.4, 3, NA, 4.2, 4.8, 
5, 3, 4.52, NA, 5, NA, 3.24, NA, 4), SOCW752.1 = c(3.8, 4.7, 
4.2, 5, 3.7, 4.1, 3.5, 4.2, 4.7, 2, 2.2, NA, 4.3, 4, 4.6, 4.2, 
3.8, 4.4), SOCW782.1 = c(2.8, 4.5, 4, 3.5, 3.7, 4, 4, 4, 4.2, 
5, 4.2, 4, 4.8, 5, 4.7, 4, 5, 3.6), Competeny.2 = c(3L, 3L, 3L, 
3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L), 
    SOCW748 = c(3.5, 5, 4, 5, 4.5, 3, NA, 5, 3.5, 3.5, 4.5, 4.5, 
    5, 5, 5, 3.5, NA, 5), SOCW752.2 = c(5, 4, 4.2, 4.5, 3, 2, 
    3.5, 5, 5, 3, 3.25, 5, 5, 5, 5, 4.2, 5, 5), SOCW782.2 = c(3.2, 
    4, 4, 3.3, 3.2, 4.5, 3.3, 4, 4.5, 5, 3.5, 4, 5, 4.5, 4.5, 
    4, 4.8, 3.7), Competeny.3 = c(4L, 4L, 4L, 4L, 4L, 4L, 4L, 
    4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 4L), SOCW752.3 = c(3.8, 
    4.1, 4.5, 4.5, 4.1, 3.7, 3.5, 3.7, 4.3, 3.6, 2.5, 4.6, 3.5, 
    4.3, 4.8, 3.8, 4.6, 4.1), SOCW782.3 = c(3, 4, 4, 3, 2.7, 
    4.7, 3, 4, 4.3, 5, 4, 4, 5, 4, 5, 4, 5, 4), SOCW799 = c(NA, 
    NA, NA, 2, NA, NA, 3.3, 3.33, NA, 4.5, NA, 3.5, 4, NA, 4, 
    3.5, 4, NA), Competeny.4 = c(5L, 5L, 5L, 5L, 5L, 5L, 5L, 
    5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L), SOCW752.4 = c(NA, 
    NA, 4.2, 5, NA, NA, 3.5, NA, NA, NA, 3.25, NA, NA, NA, NA, 
    4.2, NA, NA), SOCW782.4 = c(3, 4, 4, 3.8, 4.6, 4, 2.6, 4, 
    4.4, 5, 4, 3.8, 5, 4, 4.2, 4, 4.8, 3.4), SOCW799.1 = c(NA, 
    NA, NA, NA, NA, NA, 3.8, 3.25, NA, 4.5, NA, 3.7, 4.5, NA, 
    4.25, 3.5, 4, NA), Competeny.5 = c(6L, 6L, 6L, 6L, 6L, 6L, 
    6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L), SOCW751 = c(3, 
    3, NA, NA, 3, NA, NA, 3.5, 4, 3, NA, 4, 3, 4, 4, NA, 4, 3
    ), SOCW752.5 = c(NA, NA, 4.2, 4.5, NA, NA, 3.5, NA, NA, NA, 
    1, NA, NA, NA, NA, 4.2, NA, NA), SOCW782.5 = c(3.8, 4.7, 
    4, 4.3, 3.8, 4.5, 2.8, 3.8, 4.7, 4.8, 4.25, 3.8, 5, 5, 4.7, 
    4, 4.7, 4.3), Competeny.6 = c(7L, 7L, 7L, 7L, 7L, 7L, 7L, 
    7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L, 7L), SOCW748.1 = c(3.42, 
    4.33, 3.67, 4.1, 4.6, 4.6, NA, 4.37, 3, 4.5, 3.2, 4.13, 4.47, 
    4.9, 4.6, 2.97, NA, 4.6), SOCW751.1 = c(3L, 3L, NA, NA, 3L, 
    NA, NA, 3L, 4L, 3L, NA, 4L, 3L, 4L, 4L, NA, 4L, 3L), SOCW782.6 = c(2.2, 
    4, 4, 3.4, 2.8, 4.4, 2.2, 4, 4.8, 5, 4, 3, 5, 4.2, 4.4, 4, 
    4.6, 3.6), Competeny.7 = c(8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 
    8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L, 8L), SOCW751.2 = c(3L, 
    3L, NA, NA, 3L, NA, NA, 3L, 4L, 3L, NA, 4L, 3L, 4L, 4L, NA, 
    4L, 3L), SOCW752.6 = c(4.1, 4.5, 3, 4.6, 2.8, 4.6, 4.6, 4, 
    4.6, 4.1, 2, 4, 4.3, 4.6, 4.8, 3.75, 5, 3.9), SOCW782.7 = c(2.5, 
    4, 4, 3.5, 2.75, 4.5, 2.5, 4, 4.5, 5, 4, 3.75, 5, 4, 5, 4, 
    4.5, 4), Competeny.8 = c(9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 
    9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L), SOCW744 = c(3.88, 
    4.53, 2.5, 4.25, 3.12, 2.85, 4, 4.02, 4.48, 4, 2.15, 4.15, 
    3.55, 4.15, 4.11, 2, 4.29, 3.38), SOCW782.8 = c(2, 4.5, 4, 
    3, 2, 4.75, 2, 3.75, 4.25, 5, 4, 3.75, 5, 4, 5, 3, 4.5, 4
    ), SOCW799.2 = c(NA, NA, NA, 2, NA, NA, NA, 3.625, NA, 4.5, 
    NA, 3.7, NA, 4, 4.125, 3.5, 4, NA)), row.names = c(NA, -18L
), class = "data.frame")

Desired Output

(Names will be different from data shown above)

structure(list(Name = c("H. Smith", "H. Smith", "H. Smith", "Joe Walsh", 
"Joe Walsh", "Joe Walsh", "Eric Clapton", "Eric Clapton", "Eric Clapton", 
"Stevie Ray Vaughan", "Stevie Ray Vaughan", "Stevie Ray Vaughan", 
"B.B. King", "B.B. King", "B.B. King", "Muddy Waters", "Muddy Waters", 
"Muddy Waters", "Jimmy Vaughan", "Jimmy Vaughan", "Jimmy Vaughan", 
"Buddy Guy", "Buddy Guy", "Buddy Guy", "Jimmy Page", "Jimmy Page", 
"Jimmy Page", "Joe Perry", "Joe Perry", "Joe Perry", "Brad Whitford", 
"Brad Whitford", "Brad Whitford", "Steve Vai", "Steve Vai", "Steve Vai", 
"Edward van Halen", "Edward van Halen", "Edward van Halen", "Jimi Hendrix", 
"Jimi Hendrix", "Jimi Hendrix", "Jeff Beck", "Jeff Beck", "Jeff Beck", 
"Keith Richards", "Keith Richards", "Keith Richards", "Chuck Berry", 
"Chuck Berry", "Chuck Berry", "Carlos Santana", "Carlos Santana", 
"Carlos Santana", "H. Smith", "H. Smith", "H. Smith", "Joe Walsh", 
"Joe Walsh", "Joe Walsh", "Eric Clapton", "Eric Clapton", "Eric Clapton", 
"Stevie Ray Vaughan", "Stevie Ray Vaughan", "Stevie Ray Vaughan", 
"B.B. King", "B.B. King", "B.B. King", "Muddy Waters", "Muddy Waters", 
"Muddy Waters", "Jimmy Vaughan", "Jimmy Vaughan", "Jimmy Vaughan", 
"Buddy Guy", "Buddy Guy", "Buddy Guy", "Jimmy Page", "Jimmy Page", 
"Jimmy Page", "Joe Perry", "Joe Perry", "Joe Perry", "Brad Whitford", 
"Brad Whitford", "Brad Whitford", "Steve Vai", "Steve Vai", "Steve Vai", 
"Edward van Halen", "Edward van Halen", "Edward van Halen", "Jimi Hendrix", 
"Jimi Hendrix", "Jimi Hendrix", "Jeff Beck", "Jeff Beck", "Jeff Beck", 
"Keith Richards", "Keith Richards", "Keith Richards", "Chuck Berry", 
"Chuck Berry", "Chuck Berry", "Carlos Santana", "Carlos Santana", 
"Carlos Santana", "H. Smith", "H. Smith", "H. Smith", "Joe Walsh", 
"Joe Walsh", "Joe Walsh", "Eric Clapton", "Eric Clapton", "Eric Clapton", 
"Stevie Ray Vaughan", "Stevie Ray Vaughan", "Stevie Ray Vaughan", 
"B.B. King", "B.B. King", "B.B. King", "Muddy Waters", "Muddy Waters", 
"Muddy Waters", "Jimmy Vaughan", "Jimmy Vaughan", "Jimmy Vaughan", 
"Buddy Guy", "Buddy Guy", "Buddy Guy", "Jimmy Page", "Jimmy Page", 
"Jimmy Page", "Joe Perry", "Joe Perry", "Joe Perry", "Brad Whitford", 
"Brad Whitford", "Brad Whitford", "Steve Vai", "Steve Vai", "Steve Vai", 
"Edward van Halen", "Edward van Halen", "Edward van Halen", "Jimi Hendrix", 
"Jimi Hendrix", "Jimi Hendrix", "Jeff Beck", "Jeff Beck", "Jeff Beck", 
"Keith Richards", "Keith Richards", "Keith Richards", "Chuck Berry", 
"Chuck Berry", "Chuck Berry", "Carlos Santana", "Carlos Santana", 
"Carlos Santana", "H. Smith", "H. Smith", "H. Smith", "Joe Walsh", 
"Joe Walsh", "Joe Walsh", "Eric Clapton", "Eric Clapton", "Eric Clapton", 
"Stevie Ray Vaughan", "Stevie Ray Vaughan", "Stevie Ray Vaughan", 
"B.B. King", "B.B. King", "B.B. King", "Muddy Waters", "Muddy Waters", 
"Muddy Waters", "Jimmy Vaughan", "Jimmy Vaughan", "Jimmy Vaughan", 
"Buddy Guy", "Buddy Guy", "Buddy Guy", "Jimmy Page", "Jimmy Page", 
"Jimmy Page", "Joe Perry", "Joe Perry", "Joe Perry", "Brad Whitford", 
"Brad Whitford", "Brad Whitford", "Steve Vai", "Steve Vai", "Steve Vai", 
"Edward van Halen", "Edward van Halen", "Edward van Halen", "Jimi Hendrix", 
"Jimi Hendrix", "Jimi Hendrix", "Jeff Beck", "Jeff Beck", "Jeff Beck", 
"Keith Richards", "Keith Richards", "Keith Richards", "Chuck Berry", 
"Chuck Berry", "Chuck Berry", "Carlos Santana", "Carlos Santana", 
"Carlos Santana", "H. Smith", "H. Smith", "H. Smith", "Joe Walsh", 
"Joe Walsh", "Joe Walsh", "Eric Clapton", "Eric Clapton", "Eric Clapton", 
"Stevie Ray Vaughan", "Stevie Ray Vaughan", "Stevie Ray Vaughan", 
"B.B. King", "B.B. King", "B.B. King", "Muddy Waters", "Muddy Waters", 
"Muddy Waters", "Jimmy Vaughan", "Jimmy Vaughan", "Jimmy Vaughan", 
"Buddy Guy", "Buddy Guy", "Buddy Guy", "Jimmy Page", "Jimmy Page", 
"Jimmy Page", "Joe Perry", "Joe Perry", "Joe Perry", "Brad Whitford", 
"Brad Whitford", "Brad Whitford", "Steve Vai", "Steve Vai", "Steve Vai", 
"Edward van Halen", "Edward van Halen", "Edward van Halen", "Jimi Hendrix", 
"Jimi Hendrix", "Jimi Hendrix", "Jeff Beck", "Jeff Beck", "Jeff Beck", 
"Keith Richards", "Keith Richards", "Keith Richards", "Chuck Berry", 
"Chuck Berry", "Chuck Berry", "Carlos Santana", "Carlos Santana", 
"Carlos Santana", "H. Smith", "H. Smith", "H. Smith", "Joe Walsh", 
"Joe Walsh", "Joe Walsh", "Eric Clapton", "Eric Clapton", "Eric Clapton", 
"Stevie Ray Vaughan", "Stevie Ray Vaughan", "Stevie Ray Vaughan", 
"B.B. King", "B.B. King", "B.B. King", "Muddy Waters", "Muddy Waters", 
"Muddy Waters", "Jimmy Vaughan", "Jimmy Vaughan", "Jimmy Vaughan", 
"Buddy Guy", "Buddy Guy", "Buddy Guy", "Jimmy Page", "Jimmy Page", 
"Jimmy Page", "Joe Perry", "Joe Perry", "Joe Perry", "Brad Whitford", 
"Brad Whitford", "Brad Whitford", "Steve Vai", "Steve Vai", "Steve Vai", 
"Edward van Halen", "Edward van Halen", "Edward van Halen", "Jimi Hendrix", 
"Jimi Hendrix", "Jimi Hendrix", "Jeff Beck", "Jeff Beck", "Jeff Beck", 
"Keith Richards", "Keith Richards", "Keith Richards", "Chuck Berry", 
"Chuck Berry", "Chuck Berry", "Carlos Santana", "Carlos Santana", 
"Carlos Santana", "H. Smith", "H. Smith", "H. Smith", "Joe Walsh", 
"Joe Walsh", "Joe Walsh", "Eric Clapton", "Eric Clapton", "Eric Clapton", 
"Stevie Ray Vaughan", "Stevie Ray Vaughan", "Stevie Ray Vaughan", 
"B.B. King", "B.B. King", "B.B. King", "Muddy Waters", "Muddy Waters", 
"Muddy Waters", "Jimmy Vaughan", "Jimmy Vaughan", "Jimmy Vaughan", 
"Buddy Guy", "Buddy Guy", "Buddy Guy", "Jimmy Page", "Jimmy Page", 
"Jimmy Page", "Joe Perry", "Joe Perry", "Joe Perry", "Brad Whitford", 
"Brad Whitford", "Brad Whitford", "Steve Vai", "Steve Vai", "Steve Vai", 
"Edward van Halen", "Edward van Halen", "Edward van Halen", "Jimi Hendrix", 
"Jimi Hendrix", "Jimi Hendrix", "Jeff Beck", "Jeff Beck", "Jeff Beck", 
"Keith Richards", "Keith Richards", "Keith Richards", "Chuck Berry", 
"Chuck Berry", "Chuck Berry", "Carlos Santana", "Carlos Santana", 
"Carlos Santana", "H. Smith", "H. Smith", "H. Smith", "Joe Walsh", 
"Joe Walsh", "Joe Walsh", "Eric Clapton", "Eric Clapton", "Eric Clapton", 
"Stevie Ray Vaughan", "Stevie Ray Vaughan", "Stevie Ray Vaughan", 
"B.B. King", "B.B. King", "B.B. King", "Muddy Waters", "Muddy Waters", 
"Muddy Waters", "Jimmy Vaughan", "Jimmy Vaughan", "Jimmy Vaughan", 
"Buddy Guy", "Buddy Guy", "Buddy Guy", "Jimmy Page", "Jimmy Page", 
"Jimmy Page", "Joe Perry", "Joe Perry", "Joe Perry", "Brad Whitford", 
"Brad Whitford", "Brad Whitford", "Steve Vai", "Steve Vai", "Steve Vai", 
"Edward van Halen", "Edward van Halen", "Edward van Halen", "Jimi Hendrix", 
"Jimi Hendrix", "Jimi Hendrix", "Jeff Beck", "Jeff Beck", "Jeff Beck", 
"Keith Richards", "Keith Richards", "Keith Richards", "Chuck Berry", 
"Chuck Berry", "Chuck Berry", "Carlos Santana", "Carlos Santana", 
"Carlos Santana", "H. Smith", "H. Smith", "H. Smith", "Joe Walsh", 
"Joe Walsh", "Joe Walsh", "Eric Clapton", "Eric Clapton", "Eric Clapton", 
"Stevie Ray Vaughan", "Stevie Ray Vaughan", "Stevie Ray Vaughan", 
"B.B. King", "B.B. King", "B.B. King", "Muddy Waters", "Muddy Waters", 
"Muddy Waters", "Jimmy Vaughan", "Jimmy Vaughan", "Jimmy Vaughan", 
"Buddy Guy", "Buddy Guy", "Buddy Guy", "Jimmy Page", "Jimmy Page", 
"Jimmy Page", "Joe Perry", "Joe Perry", "Joe Perry", "Brad Whitford", 
"Brad Whitford", "Brad Whitford", "Steve Vai", "Steve Vai", "Steve Vai", 
"Edward van Halen", "Edward van Halen", "Edward van Halen", "Jimi Hendrix", 
"Jimi Hendrix", "Jimi Hendrix", "Jeff Beck", "Jeff Beck", "Jeff Beck", 
"Keith Richards", "Keith Richards", "Keith Richards", "Chuck Berry", 
"Chuck Berry", "Chuck Berry", "Carlos Santana", "Carlos Santana", 
"Carlos Santana"), Competency = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 
3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 
3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 
3, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 
4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 
4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 5, 5, 5, 
5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 
5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 
5, 5, 5, 5, 5, 5, 5, 5, 5, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 
6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 
6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 
7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 
7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 
7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 8, 8, 8, 8, 8, 8, 8, 8, 8, 
8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 
8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 
8, 8, 8, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 
9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 
9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9), Course = c("SOCW725", 
"SOCW752", "SOCW782", "SOCW725", "SOCW752", "SOCW782", "SOCW725", 
"SOCW752", "SOCW782", "SOCW725", "SOCW752", "SOCW782", "SOCW725", 
"SOCW752", "SOCW782", "SOCW725", "SOCW752", "SOCW782", "SOCW725", 
"SOCW752", "SOCW782", "SOCW725", "SOCW752", "SOCW782", "SOCW725", 
"SOCW752", "SOCW782", "SOCW725", "SOCW752", "SOCW782", "SOCW725", 
"SOCW752", "SOCW782", "SOCW725", "SOCW752", "SOCW782", "SOCW725", 
"SOCW752", "SOCW782", "SOCW725", "SOCW752", "SOCW782", "SOCW725", 
"SOCW752", "SOCW782", "SOCW725", "SOCW752", "SOCW782", "SOCW725", 
"SOCW752", "SOCW782", "SOCW725", "SOCW752", "SOCW782", "SOCW725", 
"SOCW752", "SOCW782", "SOCW725", "SOCW752", "SOCW782", "SOCW725", 
"SOCW752", "SOCW782", "SOCW725", "SOCW752", "SOCW782", "SOCW725", 
"SOCW752", "SOCW782", "SOCW725", "SOCW752", "SOCW782", "SOCW725", 
"SOCW752", "SOCW782", "SOCW725", "SOCW752", "SOCW782", "SOCW725", 
"SOCW752", "SOCW782", "SOCW725", "SOCW752", "SOCW782", "SOCW725", 
"SOCW752", "SOCW782", "SOCW725", "SOCW752", "SOCW782", "SOCW725", 
"SOCW752", "SOCW782", "SOCW725", "SOCW752", "SOCW782", "SOCW725", 
"SOCW752", "SOCW782", "SOCW725", "SOCW752", "SOCW782", "SOCW725", 
"SOCW752", "SOCW782", "SOCW725", "SOCW752", "SOCW782", "SOCW748", 
"SOCW752", "SOCW782", "SOCW748", "SOCW752", "SOCW782", "SOCW748", 
"SOCW752", "SOCW782", "SOCW748", "SOCW752", "SOCW782", "SOCW748", 
"SOCW752", "SOCW782", "SOCW748", "SOCW752", "SOCW782", "SOCW748", 
"SOCW752", "SOCW782", "SOCW748", "SOCW752", "SOCW782", "SOCW748", 
"SOCW752", "SOCW782", "SOCW748", "SOCW752", "SOCW782", "SOCW748", 
"SOCW752", "SOCW782", "SOCW748", "SOCW752", "SOCW782", "SOCW748", 
"SOCW752", "SOCW782", "SOCW748", "SOCW752", "SOCW782", "SOCW748", 
"SOCW752", "SOCW782", "SOCW748", "SOCW752", "SOCW782", "SOCW748", 
"SOCW752", "SOCW782", "SOCW748", "SOCW752", "SOCW782", "SOCW752", 
"SOCW782", "SOCW799", "SOCW752", "SOCW782", "SOCW799", "SOCW752", 
"SOCW782", "SOCW799", "SOCW752", "SOCW782", "SOCW799", "SOCW752", 
"SOCW782", "SOCW799", "SOCW752", "SOCW782", "SOCW799", "SOCW752", 
"SOCW782", "SOCW799", "SOCW752", "SOCW782", "SOCW799", "SOCW752", 
"SOCW782", "SOCW799", "SOCW752", "SOCW782", "SOCW799", "SOCW752", 
"SOCW782", "SOCW799", "SOCW752", "SOCW782", "SOCW799", "SOCW752", 
"SOCW782", "SOCW799", "SOCW752", "SOCW782", "SOCW799", "SOCW752", 
"SOCW782", "SOCW799", "SOCW752", "SOCW782", "SOCW799", "SOCW752", 
"SOCW782", "SOCW799", "SOCW752", "SOCW782", "SOCW799", "SOCW752", 
"SOCW782", "SOCW799", "SOCW752", "SOCW782", "SOCW799", "SOCW752", 
"SOCW782", "SOCW799", "SOCW752", "SOCW782", "SOCW799", "SOCW752", 
"SOCW782", "SOCW799", "SOCW752", "SOCW782", "SOCW799", "SOCW752", 
"SOCW782", "SOCW799", "SOCW752", "SOCW782", "SOCW799", "SOCW752", 
"SOCW782", "SOCW799", "SOCW752", "SOCW782", "SOCW799", "SOCW752", 
"SOCW782", "SOCW799", "SOCW752", "SOCW782", "SOCW799", "SOCW752", 
"SOCW782", "SOCW799", "SOCW752", "SOCW782", "SOCW799", "SOCW752", 
"SOCW782", "SOCW799", "SOCW752", "SOCW782", "SOCW799", "SOCW752", 
"SOCW782", "SOCW799", "SOCW752", "SOCW782", "SOCW799", "SOCW751", 
"SOCW752", "SOCW782", "SOCW751", "SOCW752", "SOCW782", "SOCW751", 
"SOCW752", "SOCW782", "SOCW751", "SOCW752", "SOCW782", "SOCW751", 
"SOCW752", "SOCW782", "SOCW751", "SOCW752", "SOCW782", "SOCW751", 
"SOCW752", "SOCW782", "SOCW751", "SOCW752", "SOCW782", "SOCW751", 
"SOCW752", "SOCW782", "SOCW751", "SOCW752", "SOCW782", "SOCW751", 
"SOCW752", "SOCW782", "SOCW751", "SOCW752", "SOCW782", "SOCW751", 
"SOCW752", "SOCW782", "SOCW751", "SOCW752", "SOCW782", "SOCW751", 
"SOCW752", "SOCW782", "SOCW751", "SOCW752", "SOCW782", "SOCW751", 
"SOCW752", "SOCW782", "SOCW751", "SOCW752", "SOCW782", "SOCW748", 
"SOCW751", "SOCW782", "SOCW748", "SOCW751", "SOCW782", "SOCW748", 
"SOCW751", "SOCW782", "SOCW748", "SOCW751", "SOCW782", "SOCW748", 
"SOCW751", "SOCW782", "SOCW748", "SOCW751", "SOCW782", "SOCW748", 
"SOCW751", "SOCW782", "SOCW748", "SOCW751", "SOCW782", "SOCW748", 
"SOCW751", "SOCW782", "SOCW748", "SOCW751", "SOCW782", "SOCW748", 
"SOCW751", "SOCW782", "SOCW748", "SOCW751", "SOCW782", "SOCW748", 
"SOCW751", "SOCW782", "SOCW748", "SOCW751", "SOCW782", "SOCW748", 
"SOCW751", "SOCW782", "SOCW748", "SOCW751", "SOCW782", "SOCW748", 
"SOCW751", "SOCW782", "SOCW748", "SOCW751", "SOCW782", "SOCW751", 
"SOCW752", "SOCW782", "SOCW751", "SOCW752", "SOCW782", "SOCW751", 
"SOCW752", "SOCW782", "SOCW751", "SOCW752", "SOCW782", "SOCW751", 
"SOCW752", "SOCW782", "SOCW751", "SOCW752", "SOCW782", "SOCW751", 
"SOCW752", "SOCW782", "SOCW751", "SOCW752", "SOCW782", "SOCW751", 
"SOCW752", "SOCW782", "SOCW751", "SOCW752", "SOCW782", "SOCW751", 
"SOCW752", "SOCW782", "SOCW751", "SOCW752", "SOCW782", "SOCW751", 
"SOCW752", "SOCW782", "SOCW751", "SOCW752", "SOCW782", "SOCW751", 
"SOCW752", "SOCW782", "SOCW751", "SOCW752", "SOCW782", "SOCW751", 
"SOCW752", "SOCW782", "SOCW751", "SOCW752", "SOCW782", "SOCW744", 
"SOCW782", "SOCW799", "SOCW744", "SOCW782", "SOCW799", "SOCW744", 
"SOCW782", "SOCW799", "SOCW744", "SOCW782", "SOCW799", "SOCW744", 
"SOCW782", "SOCW799", "SOCW744", "SOCW782", "SOCW799", "SOCW744", 
"SOCW782", "SOCW799", "SOCW744", "SOCW782", "SOCW799", "SOCW744", 
"SOCW782", "SOCW799", "SOCW744", "SOCW782", "SOCW799", "SOCW744", 
"SOCW782", "SOCW799", "SOCW744", "SOCW782", "SOCW799", "SOCW744", 
"SOCW782", "SOCW799", "SOCW744", "SOCW782", "SOCW799", "SOCW744", 
"SOCW782", "SOCW799", "SOCW744", "SOCW782", "SOCW799", "SOCW744", 
"SOCW782", "SOCW799", "SOCW744", "SOCW782", "SOCW799"), Rating = c(3.5, 
4.2, 2.7, 4.1, 4, 4.4, NA, 3.25, 4, 4.3, 3.75, 4.8, 3.1, 4.3, 
4, 3, 4.4, 4.3, NA, 4.5, 4, 3.6, 3.8, 3.9, 4.3, 4.4, 4.9, 3.3, 
4.2, 5, 3, 3.25, 3.9, 5, 4.1, 3.9, NA, 4, 5, 4.7, 4, 4.8, NA, 
5, 4.7, 3.1, 3.8, 4, NA, 3.6, 4.7, 4, 3.6, 3.3, 4.2, 3.8, 2.8, 
4.2, 4.7, 4.5, NA, 4.2, 4, 3.72, 5, 3.5, 4.4, 3.7, 3.7, 3, 4.1, 
4, NA, 3.5, 4, 4.2, 4.2, 4, 4.8, 4.7, 4.2, 5, 2, 5, 3, 2.2, 4.2, 
4.52, NA, 4, NA, 4.3, 4.8, 5, 4, 5, NA, 4.6, 4.7, 3.24, 4.2, 
4, NA, 3.8, 5, 4, 4.4, 3.6, 3.5, 5, 3.2, 5, 4, 4, 4, 4.2, 4, 
5, 4.5, 3.3, 4.5, 3, 3.2, 3, 2, 4.5, NA, 3.5, 3.3, 5, 5, 4, 3.5, 
5, 4.5, 3.5, 3, 5, 4.5, 3.25, 3.5, 4.5, 5, 4, 5, 5, 5, 5, 5, 
4.5, 5, 5, 4.5, 3.5, 4.2, 4, NA, 5, 4.8, 5, 5, 3.7, 3.8, 3, NA, 
4.1, 4, NA, 4.5, 4, NA, 4.5, 3, 2, 4.1, 2.7, NA, 3.7, 4.7, NA, 
3.5, 3, 3.3, 3.7, 4, 3.33, 4.3, 4.3, NA, 3.6, 5, 4.5, 2.5, 4, 
NA, 4.6, 4, 3.5, 3.5, 5, 4, 4.3, 4, NA, 4.8, 5, 4, 3.8, 4, 3.5, 
4.6, 5, 4, 4.1, 4, NA, NA, 3, NA, NA, 4, NA, 4.2, 4, NA, 5, 3.8, 
NA, NA, 4.6, NA, NA, 4, NA, 3.5, 2.6, 3.8, NA, 4, 3.25, NA, 4.4, 
NA, NA, 5, 4.5, 3.25, 4, NA, NA, 3.8, 3.7, NA, 5, 4.5, NA, 4, 
NA, NA, 4.2, 4.25, 4.2, 4, 3.5, NA, 4.8, 4, NA, 3.4, NA, 3, NA, 
3.8, 3, NA, 4.7, NA, 4.2, 4, NA, 4.5, 4.3, 3, NA, 3.8, NA, NA, 
4.5, NA, 3.5, 2.8, 3.5, NA, 3.8, 4, NA, 4.7, 3, NA, 4.8, NA, 
1, 4.25, 4, NA, 3.8, 3, NA, 5, 4, NA, 5, 4, NA, 4.7, NA, 4.2, 
4, 4, NA, 4.7, 3, NA, 4.3, 3.42, 3, 2.2, 4.33, 3, 4, 3.67, NA, 
4, 4.1, NA, 3.4, 4.6, 3, 2.8, 4.6, NA, 4.4, NA, NA, 2.2, 4.37, 
3, 4, 3, 4, 4.8, 4.5, 3, 5, 3.2, NA, 4, 4.13, 4, 3, 4.47, 3, 
5, 4.9, 4, 4.2, 4.6, 4, 4.4, 2.97, NA, 4, NA, 4, 4.6, 4.6, 3, 
3.6, 3, 4.1, 2.5, 3, 4.5, 4, NA, 3, 4, NA, 4.6, 3.5, 3, 2.8, 
2.75, NA, 4.6, 4.5, NA, 4.6, 2.5, 3, 4, 4, 4, 4.6, 4.5, 3, 4.1, 
5, NA, 2, 4, 4, 4, 3.75, 3, 4.3, 5, 4, 4.6, 4, 4, 4.8, 5, NA, 
3.75, 4, 4, 5, 4.5, 3, 3.9, 4, 3.88, 2, NA, 4.53, 4.5, NA, 2.5, 
4, NA, 4.25, 3, 2, 3.12, 2, NA, 2.85, 4.75, NA, 4, 2, NA, 4.02, 
3.75, 3.625, 4.48, 4.25, NA, 4, 5, 4.5, 2.15, 4, NA, 4.15, 3.75, 
3.7, 3.55, 5, NA, 4.15, 4, 4, 4.11, 5, 4.125, 2, 3, 3.5, 4.29, 
4.5, 4, 3.38, 4, NA)), row.names = c(NA, -486L), class = c("tbl_df", 
"tbl", "data.frame"))
Alan Lipps
  • 43
  • 6

1 Answers1

3
library(tidyverse)
df %>% 
        pivot_longer(cols = contains("SOCW"), 
                     names_to = "Course", 
                     values_to = "Rating") %>% 
        pivot_longer(cols = contains("Competency"), 
                     values_to = "Competency") %>% 
        select(Fullname, Competency, Course, Rating, -name) %>%  # matching your desired table
        mutate(Course = str_replace(Course, "\\..", ""))

Output:

   Fullname Competency Course  Rating
   <chr>         <int> <chr>    <dbl>
 1 D. Smith          1 SOCW725   3.5 
 2 D. Smith          2 SOCW725   3.5 
 3 D. Smith          3 SOCW725   3.5 
 4 D. Smith          1 SOCW748   2.56
 5 D. Smith          2 SOCW748   2.56
 6 D. Smith          3 SOCW748   2.56
 7 D. Smith          1 SOCW752   3.25
 8 D. Smith          2 SOCW752   3.25
 9 D. Smith          3 SOCW752   3.25
10 D. Smith          1 SOCW725   4.5 
# ... with 44 more rows

Data used:

df = structure(list(Fullname = c("D. Smith", "H. Black"), Competency = c(1L, 
1L), SOCW725 = c(3.5, 2.09), SOCW748 = c(2.56, 3.23), SOCW752 = c(3.25, 
4), Competency.1 = c(2L, 2L), SOCW725.1 = c(4.5, 3.45), SOCW748.1 = c(3.7, 
3.45), SOCW799 = c(3.33, 2.34), Competency.2 = c(3L, 3L), SOCW751 = c(3.21, 
1.23), SOCW748.2 = c(2.07, 3.32), SOCW799.1 = c(4.32, 4.54)), row.names = c(NA, 
-2L), class = "data.frame")
bird
  • 2,938
  • 1
  • 6
  • 27
  • I tried that code and it produced an error: Error: `cols` must select at least one column. Run `rlang::last_trace()` to see the full context. > rlang::last_trace() `cols` must select at least one column. Backtrace: x 1. +-`%>%`(...) 2. +-dplyr::mutate(., Course = str_replace(Course, "\\..", "")) 3. +-dplyr::select(., Student.Name, Competency, Course, Rating, -name) 4. +-tidyr::pivot_longer(., cols = contains("Competency"), values_to = "Competency") 5. \-tidyr:::pivot_longer.data.frame(...) 6. \-tidyr::build_longer_spec(...) – Alan Lipps Jul 17 '21 at 21:15
  • I received this error creating the df. :In data.table::fread("Fullname Competency SOCW725 SOCW748 SOCW752 Competency.1 SOCW725.1 SOCW748.1 SOCW799 Competency.2 SOCW751 SOCW748.2 SOCW799.1\nD. Smith 1 3.5 2.56 3.25 2 4.5 3.7 3.33 3 3.21 2.07 4.32\nH. Black 1 2.09 3.23 4.00 2 3.45 3.45 2.34 3 1.23 3.32 4.54") : Detected 13 column names but the data has 14 columns (i.e. invalid file). Added 1 extra default column name for the first column which is guessed to be row names or an index. [I had to shorten a little] – Alan Lipps Jul 17 '21 at 21:34
  • The first initial in Fullname is being split into a separate variable. I will try correcting this and then running your code. – Alan Lipps Jul 17 '21 at 21:38
  • Yes, that works fine. I created the dataframe object df and used your code to transform the dataframe as desired. I tried my data once again and get the same error. – Alan Lipps Jul 17 '21 at 21:56
  • I am not sure if I understand you. The latest `df` that I have shared in the **Data shared** section of my answer should really work (I double checked it). If you mean it works fine but it does not work with your data, you really need to share your own data using `dput()`. This is why it is always recommended to use `dput` to share your data rather than showing the data as a table (like in your post). – bird Jul 17 '21 at 22:00
  • Oh, OK. I am very new to posting questions here. Yes, the code runs flawlessly with your data. However, I am still getting the same error with my data. I will work on sharing my data. – Alan Lipps Jul 17 '21 at 22:04
  • I just now added my data to my original question post. – Alan Lipps Jul 17 '21 at 22:28
  • I just discovered that Competency is misspelled in my data. – Alan Lipps Jul 17 '21 at 22:59
  • @AlanLipps exactly! The problem is that your real data and the data you have posted here are not identical. Do you want me to write the program from scratch based on your original (misspelled) data? – bird Jul 17 '21 at 23:01
  • No, I just modified your code to pivot_longer(cols = contains("Competeny") and now it works. Thank you for your help. I am going to correct the column names in the spreadsheet, change your code back to the original and I should be good to go. – Alan Lipps Jul 17 '21 at 23:08
  • After I worked with this some, I noticed that the output is not correct. The Course's and Ratings are repeating for each value of Competency. For example, in the output you posted, the course SOCW725 appears three times, adjacent to Competencies 1, 2, and 3. The ratings for SOCW725,, competencies 1, 2, and 3 are all identical (i.e., 3.5). There should not be a Competency 3 rating for SOCW725 and the Competency 2 rating for D. Smith, SOCW725, should be 4.5. Apologies for marking this as solved when it isn't quite correct. – Alan Lipps Jul 18 '21 at 03:24