Using pivot_longer to restructure wide data, with multiple columns, from a spreadsheet

user_7067380 0
Viewed 53

I am sure there is an answer to this, but I can't find it. I have attempted several solutions using several R functions including reshape, gather, and pivot_longer. My problem is that the data comes from a spreadsheet that has multiple columns. I will attempt to represent a sample of the spreadsheet:

FullName SOCW725 SOCW748 SOCW799 Average SOCW725 SOCW752 SOCW782 Average SOCW725 SOCW748 SOCW752 Average
Beavis B 3.5 3.22 2.56 3.07 2.33 3.33 4.2 3.5 3.33 3.23 No Data 3.00
El Guapo 3.25 3.02 2.75 3.18 3.33 4.33 4.15 2.25 2.67 3.42 4 2.44

The actual data file is much wider. Each set of three courses (e.g., SOCW725, SOCW748, SOCW799) represents a competency and there are nine competencies. I left those off the table as I believe I can insert those into a dataframe once I figure this out (I hope). So, I am trying to pivot_longer into three columns (will be 4 when the CompetencyID is added). The columns are: Name, Course, and Rating. I do not need the average as I can recalculate that. Following is an example of the code I am using:

d1 <- pivot_longer(my_data, 
               cols = !1, 
               names_to = "Course",
               values_to = "Ratings",
               )

This works, but the repeating rownames (i.e., Course names) have a . followed by a number (e.g., SOCW725.1, SOCW725.2, etc.). I understand why, but I don't know how to get rid of it. I can probably figure out how to edit the .#'s out of the result, but wanted to find a faster way with dplyr::pivot_table.

Thank you in advance.

gather dplyr reshape r
2 Answers

If we are interested in returning the 'FullName' and the 'SOCW' columns (duplicated) in single column, select the columns of interest, then use pivot_longer with names_pattern as the ".value" and capture the substring from the column name without the . ([^.]+) followed by digits.

library(dplyr)
library(tidyr)
my_data %>% 
    select(FullName, starts_with("SOCW")) %>% 
    pivot_longer(cols = starts_with("SOCW"), names_to = ".value", 
         names_pattern = '^(SOCW[^.]+)')
# A tibble: 6 x 6
  FullName SOCW725 SOCW748 SOCW799 SOCW752 SOCW782
  <chr>      <dbl>   <dbl>   <dbl>   <dbl>   <dbl>
1 Beavis B    3.5     3.22    2.56    3.33    4.2 
2 Beavis B    2.33    3.23   NA      NA      NA   
3 Beavis B    3.33   NA      NA      NA      NA   
4 El Guapo    3.25    3.02    2.75    4.33    4.15
5 El Guapo    3.33    3.42   NA       4      NA   
6 El Guapo    2.67   NA      NA      NA      NA

data.frame doesn't by default allow duplicate column names. It uses make.unique to modify the column names by appending .1, .2, etc. for each duplicates.

if we need only three columns

library(stringr)
my_data %>% 
   select(FullName, starts_with("SOCW")) %>% 
   pivot_longer(cols = starts_with("SOCW")) %>% 
   mutate(name = str_remove(name, "\\.\\d+$"))
# A tibble: 18 x 3
   FullName name    value
   <chr>    <chr>   <dbl>
 1 Beavis B SOCW725  3.5 
 2 Beavis B SOCW748  3.22
 3 Beavis B SOCW799  2.56
 4 Beavis B SOCW725  2.33
 5 Beavis B SOCW752  3.33
 6 Beavis B SOCW782  4.2 
 7 Beavis B SOCW725  3.33
 8 Beavis B SOCW748  3.23
 9 Beavis B SOCW752 NA   
10 El Guapo SOCW725  3.25
11 El Guapo SOCW748  3.02
12 El Guapo SOCW799  2.75
13 El Guapo SOCW725  3.33
14 El Guapo SOCW752  4.33
15 El Guapo SOCW782  4.15
16 El Guapo SOCW725  2.67
17 El Guapo SOCW748  3.42
18 El Guapo SOCW752  4

data

my_data <- structure(list(FullName = c("Beavis B", "El Guapo"), SOCW725 = c(3.5, 
3.25), SOCW748 = c(3.22, 3.02), SOCW799 = c(2.56, 2.75), Average = c(3.07, 
3.18), SOCW725.1 = c(2.33, 3.33), SOCW752 = c(3.33, 4.33), SOCW782 = c(4.2, 
4.15), Average.1 = c(3.5, 2.25), SOCW725.2 = c(3.33, 2.67), SOCW748.1 = c(3.23, 
3.42), SOCW752.1 = c(NA, 4L), Average.2 = c(3, 2.44)),
 class = "data.frame", row.names = c(NA, 
-2L))
user_37322710

With your help akrun, I was able to get it to work with this:

d1 <- pivot_longer(my_data, 
                    cols = !1, 
                    names_to = "Course",
                    values_to = "Ratings",
                    names_pattern = '^(SOCW[^.]+)'
                    )

Regular expressions are on my bucket list of things to learn.

user_70673800
Related