I am trying to make a table from my CI output, I do not have experience making tables in R and would really appreciate some help.

CI <- data %>% group_by(group) %>% summarize(CI_z(column1, ci = 0.95)) 

I am using CI_z function to get my confidence interval information. I have 4 groups in that my data is being grouped into. My output looks like this:

group     Measurements   values
   <chr>     <chr>           <dbl>
 1 group1  sample_size     11   
 2 group1  Mean            39.3 
 3 group1  sd               6.35
 4 group1  Margin_Error     3.75
 5 group1  CI.lower.limit  35.5 
 6 group1  CI.Upper.limit  43.0 
 7 group2 sample_size      8   
 8 group2 Mean            35.2 
 9 group2 sd               4.79
10 group2 Margin_Error     3.32

etc. until group4 is finished

I want to extract the data from this output that makes a table in the following format:

I know how to use: CI[,] to get specific data from a column and row, but I do not know how to use this to make a the table that I want. Any advise on how to go about this would be greatly appreciated!

CodePudding user response：

You can do the following, which uses dplyr and tidyr, and assumes data is df

library(dplyr)
library(tidyr)

df %>% 
  filter(!Measurements %in% c("sd", "Margin_Error")) %>% 
  pivot_wider(names_from=Measurements, values_from=values) %>% 
  rename_with(~c("Group", "N", "Mean", "Lower Limit", "Upper Limit"))

Output:

  Group      N  Mean `Lower Limit` `Upper Limit`
  <chr>  <dbl> <dbl>         <dbl>         <dbl>
1 group1    11  39.3          35.5          43  
2 group2     8  35.2          25.8          47.1

Input:

df = structure(list(group = c("group1", "group1", "group1", "group1", 
"group1", "group1", "group2", "group2", "group2", "group2", "group2", 
"group2"), Measurements = c("sample_size", "Mean", "sd", "Margin_Error", 
"CI.lower.limit", "CI.Upper.limit", "sample_size", "Mean", "sd", 
"Margin_Error", "CI.lower.limit", "CI.Upper.limit"), values = c(11, 
39.3, 6.35, 3.75, 35.5, 43, 8, 35.2, 4.79, 3.32, 25.8, 47.1)), row.names = c(NA, 
-12L), class = "data.frame")

CodePudding user response：

Here is a possible data.table option (thanks to @langtang for the data):

library(data.table)

dt <- as.data.table(df)

results <- setnames(dt[,dcast(dt, group ~ Measurements, value.var = "values")][, !c("sd", "Margin_Error"), with=FALSE],
         c("Group", "Upper Limit", "Lower Limit", "Mean", "N"))

Output

    Group Upper Limit Lower Limit Mean  N
1: group1        43.0        35.5 39.3 11
2: group2        47.1        25.8 35.2  8