如何迭代将多维列联表转换为tidy data以适配逻辑回归？

Got it, let's break this down step by step to convert your entire contingency table into tidy data—no need for manual iteration over segments. The key is to first map all your categorical variables to the raw data, then reshape it systematically.

Step 1: Understand Your Variable Structure #

First, let's clarify the categorical variables hidden in your raw data (inferred from your partial table):

• Region: Repeats as Northeast → Mid-Atlantic → Southern, each with 2 rows (for Satisfied/Not satisfied)
• Satisfaction: Alternates Satisfied / Not satisfied across rows
• Race: First 6 columns (V1-V6) = White, last 6 (V7-V12) = Black
• Age Group: Each race group has 3 age brackets: <35, 35-44, >44 (each with 2 gender columns)
• Gender: Each age bracket has Male / Female columns

Step 2: Label Raw Data Rows #

Start by adding explicit labels for Region and Satisfaction to your raw data:

library(tidyverse)

# Load your raw data
df_raw <- structure(list(V1 = c(288L, 177L, 90L, 45L, 226L, 128L), V2 = c(60L, 57L, 19L, 12L, 88L, 57L), V3 = c(224L, 166L, 96L, 42L, 189L, 117L), V4 = c(35L, 19L, 12L, 5L, 44L, 34L), V5 = c(337L, 172L, 124L, 39L, 156L, 73L), V6 = c(70L, 30L, 17L, 2L, 70L, 25L), V7 = c(38L, 33L, 18L, 6L, 45L, 31L), V8 = c(19L, 35L, 13L, 7L, 47L, 35L), V9 = c(32L, 11L, 7L, 2L, 18L, 3L), V10 = c(22L, 20L, 0L, 3L, 13L, 7L), V11 = c(21L, 8L, 9L, 2L, 11L, 2L), V12 = c(15L, 10L, 1L, 1L, 9L, 2L)), class = "data.frame", row.names = c(NA, -6L))

# Add row-level labels
df_labeled <- df_raw %>%
  mutate(
    Region = rep(c("Northeast", "Mid-Atlantic", "Southern"), each = 2),
    Satisfaction = rep(c("Satisfied", "Not satisfied"), times = 3)
  ) %>%
  relocate(Region, Satisfaction) # Move labels to the front for clarity

Step 3: Reshape Columns to Long Format #

Create metadata to map each V* column to its Race, Age_Group, and Gender, then reshape the table:

# Define metadata for all columns
col_metadata <- tibble(
  col_name = paste0("V", 1:12),
  Race = rep(c("White", "Black"), each = 6),
  Age_Group = rep(rep(c("<35", "35-44", ">44"), each = 2), times = 2),
  Gender = rep(c("Male", "Female"), times = 6)
)

# Reshape to long format and join with metadata
df_long <- df_labeled %>%
  pivot_longer(
    cols = starts_with("V"),
    names_to = "col_name",
    values_to = "Count"
  ) %>%
  left_join(col_metadata, by = "col_name") %>%
  select(-col_name) # Remove temporary column name

At this point, df_long is a tidy count table—each row represents a unique combination of variables with its corresponding count. This is already suitable for logistic regression (you can use weights = Count in your model formula instead of expanding rows).

Step 4: Expand Counts to Individual Observations (Optional) #

If you need each row to represent one respondent (like your manual partial processing), expand the count column:

df_expanded <- df_long %>%
  filter(Count > 0) %>% # Skip rows with zero responses
  mutate(observation = map(Count, ~rep(1, .x))) %>% # Create list of repeated entries
  unnest(observation) %>%
  select(-Count, -observation) # Clean up extra columns

Step 5: Verify the Output #

Check that the expanded data matches your manual test case:

df_expanded %>%
  filter(Region == "Northeast", Race == "White", Age_Group == "<35") %>%
  count(Satisfaction, Gender)

You'll get the exact counts from your ne35 test:

Satisfaction Gender   n
1          Not satisfied Female  57
2          Not satisfied Male   177
3            Satisfied Female  60
4            Satisfied Male   288

This approach scales seamlessly to your entire table without manual iteration, and keeps all your categorical variables explicitly labeled for logistic regression.

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