在R语言中转置数据框并填充NA：实现指定数据格式转换

Okay, let's tackle this problem step by step. First, let's make sure we're working with the same data structure—here's how to recreate your df1 in R:

df1 <- data.frame(
  X1 = c("A", 1),
  X2 = c(NA, 2),
  X3 = c(NA, 3),
  X4 = c("B", 4),
  X5 = c(NA, 5),
  X6 = c(NA, 6),
  X7 = c("C", 7),
  X8 = c(NA, 8),
  X9 = c(NA, 9),
  stringsAsFactors = FALSE
)

Your goal is to reshape this into a two-column dataframe where each group (A/B/C) is paired with its three corresponding values. Here are a few reliable ways to do this:

1. Base R Approach (No External Packages) #

If you prefer sticking to base R, you can manually extract and repeat the group labels, then pair them with the values:

# Extract non-NA group labels from the first row
group_names <- df1[1, !is.na(df1[1, ])]

# Repeat each group name 3 times (matches your 3 values per group)
groups <- rep(group_names, each = 3)

# Pull numeric values from the second row
values <- as.numeric(df1[2, ])

# Combine into the final dataframe
result_base <- data.frame(Group = groups, Value = values)

This is straightforward for your specific case where each group has exactly 3 values. If the number of values per group varies later, you'd need a dynamic way to count, but this works perfectly here.

2. Tidyverse (dplyr + tidyr) Approach #

For a flexible, pipe-based workflow that adapts to changes in your dataset, use the tidyverse packages:

library(dplyr)
library(tidyr)

result_tidy <- df1 %>%
  # Transpose the data so each original column becomes a row
  t() %>%
  as.data.frame(stringsAsFactors = FALSE) %>%
  # Rename columns for clarity
  rename(Group = V1, Value = V2) %>%
  # Fill down missing Group values with the last valid group name
  fill(Group, .direction = "down") %>%
  # Convert Value to numeric (it starts as character type)
  mutate(Value = as.numeric(Value))

This method doesn't rely on hardcoding the number of values per group, so it will work even if you add more groups or values later.

3. Data.table Approach #

If you're working with large datasets and need speed, data.table is optimized for performance:

library(data.table)

# Convert to data.table and transpose
dt <- as.data.table(t(df1))
# Rename columns to meaningful names
setnames(dt, c("Group", "Value"))
# Fill missing Group values using "last observation carried forward"
dt[, Group := nafill(Group, type = "locf")]
# Convert Value column to numeric
dt[, Value := as.numeric(Value)]

result_dt <- dt

data.table's nafill function handles missing values efficiently, making this a great choice for big datasets.

All three methods will produce your desired output:

Group Value
1     A     1
2     A     2
3     A     3
4     B     4
5     B     5
6     B     6
7     C     7
8     C     8
9     C     9

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