如何将每两列分组后逐组按行堆叠实现宽表转长表？

Got it, let's tackle this data reshaping problem step by step. Your goal is to turn that wide, unlabeled dataset into a clean long table where each row holds a stock name and its corresponding daily value, stacked day by day. Let's break down what went wrong with your previous attempts and fix it with straightforward solutions.

Why Your Previous Attempts Failed #

• When you used split.default(data_wide, rep(1, each = 2)), you were grouping all pairs of columns into a single group instead of grouping each day's two columns separately. You need rep(1:191, each = 2) to create 191 distinct groups (one per day).
• reshape or melt failed because your dataset had no column names—these functions rely on clear labels to pair related columns correctly.

Solution 1: Base R (No Extra Packages Needed) #

This is the most direct approach for your use case:

# Assume your raw data is stored in a data frame called data_wide (100 rows, 382 columns)
# Step 1: Split the data into 191 groups (each group = 2 columns for one day)
day_groups <- split.default(data_wide, rep(1:191, each = 2))

# Step 2: Rename columns for each group and stack all groups together
data_long <- do.call(rbind, lapply(day_groups, function(day_df) {
  # Standardize column names for every day's data
  colnames(day_df) <- c("stock_name", "value")
  # Ensure the value column is numeric (critical if raw data stored values as text)
  day_df$value <- as.numeric(day_df$value)
  day_df
}))

This will give you a 100*191 row table with two columns: stock_name and value, ordered by day (all day 1 stocks first, then day 2, etc.).

Solution 2: Tidyverse (dplyr + tidyr) #

If you prefer using tidyverse tools for cleaner syntax:

library(dplyr)
library(tidyr)

# Step 1: Add meaningful column names to your raw data
col_names <- paste0(rep(paste0("day_", 1:191), each = 2), c("_stock", "_value"))
colnames(data_wide) <- col_names

# Step 2: Reshape to long format
data_long <- data_wide %>%
  # Pair each day's stock and value columns
  pivot_longer(
    cols = everything(),
    names_to = c("day", ".value"),
    names_pattern = "day_(\\d+)_(stock|value)"
  ) %>%
  # Keep only the columns you need (drop day if you don't need it)
  select(stock_name = stock, value) %>%
  # Ensure values are numeric
  mutate(value = as.numeric(value))

Excel Power Query Alternative (No Manual Work) #

If you want to avoid R, use Excel's Power Query to automate the process:

1. Import your data into Power Query (Data tab → From Table/Range).
2. Add an index column to track original stock rows (Add Column → Index Column → From 1).
3. Select all data columns (excluding the index) and click Transform → Transpose to turn columns into rows.
4. Add a custom column to group rows by day: =Number.RoundUp([Index]/2) (this pairs each stock name row with its value row).
5. Select the custom "day" column and transposed "Value" column, then click Transform → Pivot Column, choosing "Don't Aggregate" as the value aggregation.
6. Remove the index column, rename columns to stock_name and value, then load the data back to Excel.

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