Interlacing two columns but with other columns in the dataframe

Question

I would like to interlace two columns of a dataframe (move sd underneath the mean) similar to here, but my problem is that I have other columns in the same dataframe.

My code looks like this:

year <- rep(2014:2015, length.out = 10000)
group <- sample(c(0,1,2,3,4,5,6), replace=TRUE, size=10000)
value <- sample(10000, replace = T)

dta <- data.frame(year = year, group = group, value = value)

library(dplyr)
dta2 <- dta %>% 
  group_by(year, group) %>% 
    summarize(mean=mean(value), sd=sd(value)) %>% 
  
# round:
dta2[,3:4] <- round(dta2[,3:4], 2)

# add parenthesis around sd:
dta2$sd <- paste0("(", dta2$sd, ")")

# Now I would like to interlace mean and sd:
dta3 <- data.frame(c(rbind(dta2[1:3], dta2[3])))

This gives me an error, because of the other columns in the dataframe.

# This is the output I would like:

Do you want these in two different rows (format like excel)? or just in one row? — AnilGoyal, Feb 13 '21 at 10:12
You mean the mean and the sd in different row? yes, different rows pls. — Stata_user, Feb 13 '21 at 10:22

score 0 · Accepted Answer · answered Feb 13 '21 at 10:32

I suggest this much to do in R/dplyr

d1 <- dta2[,c(1,2,4)]
d2 <- dta2[,1:3]

#making the colnames same
colnames(d2) <- colnames(d1)

#changing data type
d2$sd <- as.character(d2$sd)

#creating output
output <- arrange(rbind(d2, d1), year, group)

#changing colname of output table
colnames(output[,3]) <- 'mean (sd in parenthesis)'

output
# A tibble: 28 x 3
# Groups:   year [2]
    year group sd       
   <int> <dbl> <chr>    
 1  2014     0 5058.15  
 2  2014     0 (2815.17)
 3  2014     1 5240.23  
 4  2014     1 (2871.65)
 5  2014     2 5095.8   
 6  2014     2 (2808.97)
 7  2014     3 5112.54  
 8  2014     3 (2917.15)
 9  2014     4 4983.58  
10  2014     4 (2917.3) 
# ... with 18 more rows

hereinafter you can use some package to display table in desired html format as discussed here or here

Interlacing two columns but with other columns in the dataframe

1 Answers1