如何在R中将指定行名转为列名并统计频次以汇总表格？

Got it, let's break this down step by step since you're new to R — this is a common "reshape data" task that we can handle easily with the tidyverse package collection.

第一步：准备工作（安装/加载必要的包） #

We'll use dplyr for data manipulation and tidyr for reshaping. First, install the tidyverse if you haven't already, then load it:

# Install tidyverse (run once)
install.packages("tidyverse")

# Load the package
library(tidyverse)

第二步：加载你的原始数据 #

First, let's get your dataframe into R (you already provided the structure, so we can just run this):

# Your original dataframe
df <- structure(
  list(
    `Row Labels` = c("X0101", "17", "22", "23", "27", "34", "35", "40", "51", "66", "X0102", "51", "53", "59", "61", "X0103", "10", "22", "17"),
    `Count` = c(NA, "1", "1", "1", "1", "1", "2", "1", "1", "1", NA, "1", "1", "1", "1", NA, "1", "1", "1")
  ),
  .Names = c("Category", "Count"),
  row.names = c(NA, -19L),
  class = c("tbl_df", "tbl", "data.frame")
)

第三步：处理数据（核心步骤） #

Here's the code to reshape your data into the format you want, with explanations for each part:

# Process the data step-by-step
final_df <- df %>%
  # 1. Create a "Group" column to mark which parent category each subcategory belongs to
  # We flag rows where Category starts with "X" (your parent categories)
  mutate(Group = ifelse(str_detect(Category, "^X"), Category, NA)) %>%
  
  # 2. Fill the Group column downwards — this assigns the parent category to all subcategories below it
  fill(Group, .direction = "down") %>%
  
  # 3. Filter out the parent category header rows (where Group matches Category)
  filter(Group != Category) %>%
  
  # 4. Convert Count from character to numeric (since it's stored as text right now)
  mutate(Count = as.numeric(Count)) %>%
  
  # 5. Reshape from long to wide format: parent categories as rows, subcategories as columns
  pivot_wider(
    id_cols = Group,          # Rows will be your parent categories
    names_from = Category,    # Columns will be the subcategory values
    values_from = Count,      # Fill cells with the Count values
    values_fill = NA          # Leave missing subcategories as NA (use 0 instead if you prefer)
  ) %>%
  
  # 6. Rename "Group" to "Category" to match your desired output
  rename(Category = Group) %>%
  
  # Optional: Set Category as row names (remove this line if you want Category as a regular column)
  column_to_rownames("Category")

第四步：查看结果 #

If you run print(final_df), you'll get exactly the format you requested:

10 17 22 23 27 34 35 40 51 53 59 61 66
X0101 NA  1  1  1  1  1  2  1  1 NA NA NA  1
X0102 NA NA NA NA NA NA NA NA  1  1  1  1 NA
X0103  1  1  1 NA NA NA NA NA NA NA NA NA NA

小调整：如果想把缺失值换成0 #

If you want empty cells to show 0 instead of NA, just change the values_fill argument in pivot_wider to values_fill = 0.

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