如何在R中实现类似SQL的分组、透视与统计（基于GermanCredit数据集）

Got it, let's fix this and get the exact grouping stats you need from your credit.all dataset. The error with reshape2 likely stems from misaligning variables or misconfiguring the melt/dcast workflow, but we'll cover multiple straightforward approaches to match your SQL logic:

1. Use dplyr (Most Intuitive, SQL-like Syntax) #

This method mirrors your original SQL structure closely, making it easy to follow and modify:

library(dplyr)

# Calculate grouped stats
credit_stats <- credit.all %>%
  group_by(Class) %>%
  summarise(
    Critical = sum(CreditHistory.Critical == 1),
    NotCritical = sum(CreditHistory.Critical == 0),
    PctCritical = Critical / n()  # n() = total rows per group (matches COUNT(*))
  )

# View the result
print(credit_stats)

Each line maps directly to your SQL clauses: group_by(Class) replaces GROUP BY Class, and summarise() handles the aggregated calculations just like your SUM(CASE...) statements.

2. Fix reshape2 Workflow (Resolve the Original Error) #

Your earlier error probably came from including unnecessary variables in the melt step. Here's the corrected approach:

library(reshape2)

# Melt only the variables we need for grouping and counting
melted_data <- melt(credit.all, 
                    id.vars = "Class", 
                    measure.vars = "CreditHistory.Critical")

# Generate counts via dcast
count_table <- dcast(melted_data, 
                     Class ~ value, 
                     fun.aggregate = length, 
                     value.var = "value")

# Rename columns and add percentage calculation
colnames(count_table) <- c("Class", "NotCritical", "Critical")
credit_stats_reshape2 <- count_table %>%
  mutate(PctCritical = Critical / (Critical + NotCritical))

print(credit_stats_reshape2)

By melting only Class and CreditHistory.Critical, we avoid the "length 0" error caused by extraneous variables. We then calculate the percentage separately to match your SQL output.

3. Base R (No Extra Packages Required) #

If you prefer not to load external libraries, use base R's built-in functions:

# Create a cross-tabulation of Class vs CreditHistory.Critical
cross_tab <- table(credit.all$Class, credit.all$CreditHistory.Critical)

# Convert to data frame and clean up
credit_stats_base <- as.data.frame.matrix(cross_tab)
colnames(credit_stats_base) <- c("NotCritical", "Critical")
credit_stats_base$Class <- rownames(credit_stats_base)

# Add percentage column and reorder columns to match your SQL output
credit_stats_base$PctCritical <- credit_stats_base$Critical / rowSums(credit_stats_base)
credit_stats_base <- credit_stats_base[, c("Class", "Critical", "NotCritical", "PctCritical")]

print(credit_stats_base)

This uses table() to get grouped counts, then transforms the result to match your desired output structure.

内容的提问来源于stack exchange，提问作者Matthew James Briggs