基于R语言将两行结构数据集melt为6列目标数据集的实现方案

It looks like your dataset has a somewhat tricky structure: each country has 4 columns where the first row contains the feature names (var1-var4) and subsequent rows hold the values for each quarter. Here's a step-by-step approach to reshape it into your desired format (either long or wide, since you mentioned both in your example):

Step 1: Load Required Package #

We'll use the tidyverse suite (specifically dplyr and tidyr) for intuitive data manipulation:

library(tidyverse)

Step 2: Recreate Your Full Raw Dataset #

First, let's build the complete raw data (including both AT and BE countries) to work with:

date_or <- c("2001 q1", "2001 q2", "2001 q3","2001 q4")
AT <- c("var1","1","2","3")
AT1 <- c("var2","1","2","3")
AT2 <- c("var3","1","2","3")
AT3 <- c("var4","1","2","3")
BE <- c("var1","4","5","6")
BE1 <- c("var2","4","5","6")
BE2 <- c("var3","4","5","6")
BE3 <- c("var4","4","5","6")
dt_or <- data.frame(date_or, AT, AT1, AT2, AT3, BE, BE1, BE2, BE3, stringsAsFactors = FALSE)

Step 3: Reshape to Long Format (First Step) #

We'll first convert the wide dataset to a long format, which makes it easier to handle country and feature mappings:

# Convert all country columns to long format
dt_long <- dt_or %>%
  pivot_longer(cols = -date_or, names_to = "country_col", values_to = "value") %>%
  # Extract country name by removing trailing numbers from column names (e.g., AT1 → AT)
  mutate(country = str_remove(country_col, "\\d+$"))

Step 4: Map Feature Names (from First Row) #

The first row of your raw data contains the feature names (var1-var4). We'll create a lookup table for these, then merge it back to our long dataset:

# Create a map of column names to feature names (from the first row)
feature_map <- dt_long %>%
  filter(date_or == dt_or$date_or[1]) %>%
  select(country_col, feature = value)

# Merge the feature map and clean up the data
dt_long_clean <- dt_long %>%
  left_join(feature_map, by = "country_col") %>%
  # Remove the first row (since it's just feature labels, not actual data)
  filter(date_or != dt_or$date_or[1]) %>%
  # Convert value column to numeric (since it was stored as character)
  mutate(value = as.numeric(value)) %>%
  # Rename date column to match your desired output
  rename(date = date_or) %>%
  # Keep only the columns we care about
  select(country, date, feature, value)

Step 5: Convert to Wide Format (Your Final Desired Structure) #

If you want the 6-column wide format (country, date, var1, var2, var3, var4), use pivot_wider:

dt_wide_final <- dt_long_clean %>%
  pivot_wider(names_from = feature, values_from = value) %>%
  select(country, date, var1, var2, var3, var4)

# View the result
print(dt_wide_final)

Output Preview #

# A tibble: 6 × 6
  country date   var1  var2  var3  var4
  <chr>   <chr>  <dbl> <dbl> <dbl> <dbl>
1 AT      2001 q2     1     1     1     1
2 AT      2001 q3     2     2     2     2
3 AT      2001 q4     3     3     3     3
4 BE      2001 q2     4     4     4     4
5 BE      2001 q3     5     5     5     5
6 BE      2001 q4     6     6     6     6

Alternative: Using reshape2 (If You Prefer melt) #

If you want to stick with melt from the reshape2 package, here's an equivalent approach:

library(reshape2)

# Melt to long format
dt_melt <- melt(dt_or, id.vars = "date_or", variable.name = "country_col", value.name = "value")

# Extract country names
dt_melt$country <- gsub("\\d+$", "", dt_melt$country_col)

# Create feature map
feature_map <- dt_melt[dt_melt$date_or == dt_or$date_or[1], c("country_col", "value")]
colnames(feature_map)[2] <- "feature"

# Merge and clean
dt_melt_clean <- merge(dt_melt, feature_map, by = "country_col")
dt_melt_clean <- dt_melt_clean[dt_melt_clean$date_or != dt_or$date_or[1], ]
dt_melt_clean$value.x <- as.numeric(dt_melt_clean$value.x)

# Convert to wide format
dt_wide_reshape2 <- dcast(dt_melt_clean, country + date_or ~ feature, value.var = "value.x")
colnames(dt_wide_reshape2)[2] <- "date"

This will give you the same final structure as the tidyverse method.

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