0

I've got a data matrix where each row represents a course and in another column a teacher is assigned:

enter image description here

I would like to transform the data to this form:

enter image description here

But what I get is this form:

enter image description here

This is my code so far:

course <- c('Math1', 'English', 'Biology', 'Math2', 'Physics')
teacher <- c('Teacher1', 'Teacher1', 'Teacher1', 'Teacher2', 'Teacher2')

data <- data.frame(course, teacher)
data <- tibble::rowid_to_column(data, "courseID")

data_wide <- data %>%
  pivot_wider(values_from = course, names_from = courseID)

So the problem is, that for each course, a new variable is created. While for teacher2 the course Math2 should be on column course1 (since it’s his first course), with my code it gets course4 and filling up the preceding columns with NAs.

Edit: my question was closed because it was considered a duplicate of How to reshape data from long to wide format but this is not the focus of my question: I already succeeded to transform to wide format, but for example for teacher2 Math2 is his first course, so it should appear in column course1 instead of creating a new column course4.

Madamadam
  • 842
  • 2
  • 12
  • 24
  • 1
    You need `data %>% mutate(rn = row_number(), .by = 'teacher') %>% pivot_wider( values_from = course, names_from = rn, names_prefix = 'course')` – akrun Feb 17 '23 at 22:16
  • this is quite better, but if a teacher has only one course assigned, this course should appear on the column course1 and not in a later column. – Madamadam Feb 18 '23 at 09:27
  • Add row numbers grouped by teacher, e.g. `data %>% group_by(teacher) %>% mutate(rn = row_number())` – camille Feb 18 '23 at 18:08
  • @camille your code produces completely different structure than the one I described in my question! – Madamadam Feb 19 '23 at 13:22
  • I meant that in addition to what's in akrun's comment, as an alternative way to set the row numbers by teacher – camille Feb 19 '23 at 20:55
  • In my opinion, my question was closed as duplicate unjustified, because the recommended stack overflow question deals with a very different topic. After a lot of research I know now, that pivoting isn't the way to go for my desired data structure, instead it's all about transposing by group as the topic is dealt with here https://stackoverflow.com/questions/17382097/transpose-data-by-groups-in-r and here https://stackoverflow.com/questions/65778767/r-transposing-row-values-to-colum – Madamadam Feb 24 '23 at 19:53

0 Answers0