how to melt or gather a data frame based on multiple columns?

Question

Following is an example of the type and format of the dataframe I have

fnum<-c('510-1','510-2','510-3')
refcharge<-c('assault','theft','rape')
refsev<-c('F','F','F')
refclass<-c('B','B','A')
issuedch<-c('assault','robbery','sodomy')
isssev<-c('F','M','F')
issclass<-c('A','A','B')

df<-data.frame(fnum,refcharge,refsev,refclass,issuedch,isssev,issclass)
df

     fnum refcharge refsev refclass issuedch isssev issclass
1 510-1   assault      F        B  assault      F        A
2 510-2     theft      F        B  robbery      M        A
3 510-3      rape      F        A   sodomy      F        B

I want to be able to gather or melt this data frame to to be a taller dataset so that it looks like the following format. I have provided an example of how it might look for fnum 510-2

fnum chargeoutcome charge Severity Class
510-1  refcharge   assault  F       B
510-1  issuedch    assault  F       A
510-2  refcharge   theft    F       B
510-2  issuedch    robberry M       A
510-3  refcharge   rape     F       A
510-3  issuedch    sodomy   F       B

I used melt(df,id=c('fnum','refcharge','issuedch')) however this doesn't provide it in the format I need the dataframe

akrun · Accepted Answer · 2019-12-17 18:11:16Z

0

We can use the names_pattern from pivot_longer

library(dplyr)
library(tidyr)
df %>%
  pivot_longer(cols = -fnum, names_pattern = "^(ref|iss)(.*)", 
       names_to = c('chargeoutcome', '.value')) %>% 
  mutate(charge = coalesce(as.character(charge), as.character(uedch))) %>% 
  select(-uedch) %>%
  mutate(chargeoutcome = factor(chargeoutcome, levels = c('ref', 'iss'), 
          labels = c('refcharge', 'issuedch')))
# A tibble: 6 x 5
#  fnum  chargeoutcome charge  sev   class
#  <fct> <fct>         <chr>   <fct> <fct>
#1 510-1 refcharge     assault F     B    
#2 510-1 issuedch      assault F     A    
#3 510-2 refcharge     theft   F     B    
#4 510-2 issuedch      robbery M     A    
#5 510-3 refcharge     rape    F     A    
#6 510-3 issuedch      sodomy  F     B

answered Dec 17, 2019 at 18:11

akrun

891k38 gold badges590 silver badges700 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to melt or gather a data frame based on multiple columns?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related