R语言：重塑DataFrame为指定宽格式时spread函数报错，求解决方法

Hey there! Let's fix this up and get your data into the exact format you want.

First, let's break down why your initial spread(Table, Currency) call failed: the spread() function (from dplyr's older toolkit) expects only one value column to pivot out, but your data has multiple value1 through value100 columns. That's why you got that confusing "object '' not found" error—it didn't know which value column to use for the spread.

Instead, we'll use the more flexible pivot_longer() and pivot_wider() functions from the tidyverse, which handle multiple value columns smoothly. Here's a step-by-step solution using your sample data:

Step 1: Set up your test data (if you haven't already) #

library(tidyverse)

# Your sample DataFrame
Table <- tibble(
  Month = c("Jan", "Jan", "Feb", "Feb"),
  Currency = c("euro", "dollar", "euro", "dollar"),
  value1 = c(210, 120, 100, 200),
  value2 = c(200, 300, 280, 150)
)

Step 2: Reshape to long format first #

We'll collapse all the value* columns into two columns: one for the value type (e.g., value1, value2) and one for the actual value.

long_table <- Table %>%
  pivot_longer(
    cols = starts_with("value"),  # Target all value columns
    names_to = "value_type",      # Name for the new type column
    values_to = "value"           # Name for the new value column
  )

Step 3: Pivot back to wide format with Currency as columns #

Now we'll spread out the Currency values while grouping by Month and value_type:

wide_intermediate <- long_table %>%
  pivot_wider(
    id_cols = c(Month, value_type),  # Keep these as identifier columns
    names_from = Currency,           # Spread Currency into columns
    values_from = value              # Use the value column for filling
  )

Step 4: Reshape to your final desired format #

Finally, we'll pivot again to get the value* groups as top-level headers, with euro/dollar underneath:

final_table <- wide_intermediate %>%
  pivot_wider(
    names_from = value_type,
    values_from = c(euro, dollar),
    names_vary = "slowest"  # This ensures euro/dollar are nested under each value*
  ) %>%
  # Reorder columns to match your desired layout
  select(Month, starts_with("value1"), starts_with("value2"))

The result #

Running this will give you a data frame that looks like this (column names combine value type and currency):

# A tibble: 2 × 5
  Month value1_euro value1_dollar value2_euro value2_dollar
  <chr>       <dbl>         <dbl>       <dbl>         <dbl>
1 Jan          210           120         200           300
2 Feb          100           200         280           150

If you want the visual two-level header (like your example) #

If you need to display the table with a proper two-level header (not just combined column names), use the gt package to format it for presentation:

library(gt)

final_table %>%
  gt() %>%
  # Create top-level headers for each value type
  tab_spanner(label = "value1", columns = c(value1_euro, value1_dollar)) %>%
  tab_spanner(label = "value2", columns = c(value2_euro, value2_dollar)) %>%
  # Rename the lower-level columns to just euro/dollar
  cols_label(
    value1_euro = "euro",
    value1_dollar = "dollar",
    value2_euro = "euro",
    value2_dollar = "dollar"
  )

This will render a table exactly matching the format you showed in your question.

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