CODEWITHSUNDEEP
rdplyrtidyversedata-manipulation
L. Tucker
L. Tucker

Reputation: 543

dplyr: rearrange one row dataframe to multiple rows

I have a few small data frames each with one row that are results from a mediation analysis. I need to rearrange the cells to be three rows, each with two of the variables and their corresponding coefficient. Example below:

HAVE DATA:

lab_x lab_m lab_y    coef_xm    coef_my   coef_xy
    x     m     y   -0.21***    0.08*** -0.084***

WANT DATA:

var1   var2        coef
   x      m    -0.21***
   m      y     0.08***
   x      y   -0.084***

UPDATE

Changing the data slightly, I need to keep the variable names that appear originally in the DF

df<-read.table(text="lab_x lab_m lab_y    coef_xm    coef_my   coef_xy
    v1    v2        v3  -0.21***    0.08*** -0.084***", header = TRUE)

WANT:

 var1    var2        coef
   v1      v2    -0.21***
   v2      v3     0.08***
   v1      v3   -0.084***

Upvotes: 0

Views: 151

Answers (2)

Jon Spring
Jon Spring

Reputation: 66490

If the data's in that consistent format, you could hard code the reshaping:

data.frame(var1 = c(df[1,1], df[1,2], df[1,1]), 
           var2 = c(df[1,2], df[1,3], df[1,3]),
           coef = as.character(df[1,4:6]))


  var1 var2      coef
1   v1   v2  -0.21***
2   v2   v3   0.08***
3   v1   v3 -0.084***

Or more obscurely:

v <- \(i) {as.character(df[1,i])} 
data.frame(var1 = v(c(1,2,1)), var2 = v(c(2,3,3)), coef = v(c(4:6)))

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 388982

In base R, you can do extract the name of the coefficient columns, get the corresponding lab names and create a dataframe.

col1 <- grep('coef', names(df), value = TRUE)
col2 <- sub('coef_', '', fixed = TRUE, cols)

do.call(rbind, lapply(strsplit(col2, ''), function(x) 
  setNames(df[paste0('lab_', x)], c('var1', 'var2')))) |>
  transform(coef = unlist(df[col1], use.names = FALSE))

#  var1 var2      coef
#1   v1   v2  -0.21***
#2   v2   v3   0.08***
#3   v1   v3 -0.084***

Upvotes: 1

Related Questions