在R语言中按分组变量计算均值及置信区间并生成表格

Hey there! I totally get how frustrating it is when something that’s straightforward in SPSS feels like a slog in R as a newbie. Let’s break this down step by step—by the end, you’ll have your summary table ready for Plotly in no time.

We’ll use dplyr for clean data grouping/summarizing, tidyr for reshaping tables, and broom to tidy up statistical outputs (though we’ll also use a simple helper function to keep things clear).

# Install packages if you haven’t already
install.packages(c("dplyr", "tidyr", "broom"))

# Load the packages
library(dplyr)
library(tidyr)
library(broom)

This function will calculate the mean and 95% confidence interval (CI) for any numeric column, handling missing values automatically:

get_mean_ci <- function(x) {
  # Calculate mean (ignoring NAs)
  metric_mean <- mean(x, na.rm = TRUE)
  # Calculate 95% CI using t-test (appropriate for small/medium samples)
  ci_results <- t.test(x, na.rm = TRUE)$conf.int
  
  # Return a tidy table with values
  tibble(
    mean = metric_mean,
    ci_low = ci_results[1],
    ci_high = ci_results[2]
  )
}

Assume your dataset is stored in a data frame named df. This code will group your data by every combination of Group and Time, then compute mean/CI for all variables from mean_PctPasses to mean_Rate:

# Generate the summary table
summary_table <- df %>%
  # Group by both grouping variables
  group_by(Group, Time) %>%
  # Apply our helper function to all target variables
  summarize(
    across(mean_PctPasses:mean_Rate, get_mean_ci),
    .groups = "drop"  # Stop grouping after calculation
  ) %>%
  # Reshape to long format (ideal for Plotly plotting)
  pivot_longer(
    cols = mean_PctPasses:mean_Rate,
    names_to = "Metric",
    values_to = c("mean", "ci_low", "ci_high"),
    values_transform = list(
      mean = ~.$mean,
      ci_low = ~.$ci_low,
      ci_high = ~.$ci_high
    )
  )

Save your summary table as a CSV file (easy to load back into R for Plotly):

write.csv(summary_table, "group_time_summary.csv", row.names = FALSE)

Here’s a simple bar chart with error bars (representing CIs) to get you started:

library(plotly)

plot_ly(
  summary_table,
  x = ~Time,
  y = ~mean,
  color = ~Group,
  facet_col = ~Metric,
  type = "bar",
  error_y = list(
    type = "data",
    array = ~ci_high - mean,
    arrayminus = ~mean - ci_low
  )
) %>%
  layout(
    title = "Mean Metrics by Group & Time (95% CIs)",
    yaxis = list(title = "Value")
  )

Notes for Adjustments: #

• If you prefer a wide-format table (each metric has its own mean/CI columns), replace the pivot_longer step with pivot_wider (just ask if you need help with that!).
• For large samples, you can swap the t-test CI calculation for a z-test using qnorm(0.975)*sd(x, na.rm=TRUE)/sqrt(sum(!is.na(x))) in the helper function.

内容的提问来源于stack exchange，提问作者Patrick