I want to reshape the current dataframe (with some empty cells) into another dataframe and create new columns (concatenate)

Question

I have the following dataframe

df<-read.table(text="Study.ID   Studlab Cohort.size No.DTA  No.TAA  Operative.mortality.DTA     Operative.mortality.TAA
                    30  Coselli2016     3309    0   3309    NA  249
                 42 Estrera2015     1896    646 1273    79  223
                 212    Youssef2015     62  NA  62  NA  14
                 203    Weiss2012   240 72  168 2   7
                 189    Tabayashi2010   102 4   98  0   6", sep="", header=T)

but I need it to look like this

Basically reshaping and adding 2 new columns "Studlab.R and group" . Studlab.R is a combination of 'Studlab" and "group" columns. I found different links for reshaping (eg here but I am still struggling with this

Any advice will greatly appreciated.

I cannot understand the logic behind it, where did those DTA and TAA come from. — user2974951, Aug 23 '19 at 13:20
As stated by @user2974951, you should somehow provide the values for the group column — Chelmy88, Aug 23 '19 at 13:25
Thanks. The logic is that I have a large dataset in that format and I want to do subgroup analysis by DTA vs TAA (that could be found at the end of each column above. — Mohamed Rahouma, Aug 23 '19 at 14:50

score 2 · Accepted Answer · answered Aug 23 '19 at 13:25

I would use gather() from tidyverse package and do this in 3 steps first gather the NO.XXX and than the Operative.XXX after taht join it together in Step 3 add the column 'Studlab.R' with mutate

library(tidyverse)

#1
df2 <- df %>% 
  select(-starts_with("Operative")) %>% 
  gather(key = "group", value = "No.DTR.TAA", No.DTA, No.TAA) %>% 
  mutate(group = str_remove(group, "No\\."))

#2
df_tmp <- df %>% 
  select(-starts_with("No")) %>% 
  gather(key = "group", value = "Operative.mortality.DTA.TAA", Operative.mortality.DTA, Operative.mortality.TAA) %>% 
  mutate(group = str_remove(group, "Operative\\.mortality\\."))

#3
df2 <- df2 %>% 
  full_join(df_tmp) %>% 
  mutate(Studlab.R = paste0(Studlab, " (", group, ")"))

Thanks a lot @domaeg for your help. It works. Greatly appreciated. — Mohamed Rahouma, Aug 23 '19 at 14:48

I want to reshape the current dataframe (with some empty cells) into another dataframe and create new columns (concatenate)

1 Answers1

Linked