Making a table from CI output

user_15846395 0
Viewed 34

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:

Group N Mean Lower Limit Upper limit
Group1 x x x x
Group2 x x x x
Group3 x x x x
Group4 x x x x

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!

output r datatable
2 Answers

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")
user_44475400

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
user_152931910
Related