R cleaning, change data format horizontal to vertical, repeating some data

Question

Right now, my data looks like this:

Coder Bill Witness1name Witness1job Witness2name Witness2Job

Joe    123    Fred        Plumber       Bob         Coach
Karen  122    Sally       Barista       Helen       Translator
Harry  431    Lisa        Swimmer       N/A         N/A
Frank  301    N/A         N/A           N/A         N/A

But I want my data to look like this:

Coder    Bill   WitnessName   WitnessJob

Joe       123    Fred          Plumber
Joe       123    Bob           Coach
Karen     122    Sally         Barista
Karen     122    Helen         Translator
Harry     431    Lisa          Swimmer
Frank     301    N/A           N/A

So I want to take it from the coder/bill level to the "witness" level. Some coder/bills have up to 10 witnesses in their rows. Some have no witnesses, but I do not want to completely drop them from the dataset (see Frank).

All help is appreciated! I am familiar with the tidyverse package.

https://stackoverflow.com/questions/61367186/pivot-longer-into-multiple-columns — Andrea M, Jun 15 '22 at 18:35
Does this answer your question? [pivot\_longer into multiple columns](https://stackoverflow.com/questions/61367186/pivot-longer-into-multiple-columns) — qwerty, Jun 15 '22 at 20:58

score 0 · Answer 1 · answered Jun 16 '22 at 13:54

For those interested, I figured it out. I had to change all the column names like this: Witness1Name to Witness1_Name Witness1Job to Witness1_Job etc.

Then I ran this:

cleandata <- pivot_longer(mddata, cols = -c(Coder, Bill), 
                    names_to = c("Witness", ".value"),
                    names_pattern = 'Witness(\\d)_(.*)') %>%
  drop_na(Name)

And it gave me this:

Coder    Bill    Witness   Name         Job

Joe       123       1       Fred          Plumber
Joe       123       2       Bob           Coach
Karen     122       1       Sally         Barista
Karen     122       2      Helen         Translator
Harry     431       1       Lisa          Swimmer

Close enough to what I wanted

R cleaning, change data format horizontal to vertical, repeating some data

1 Answers1