处理美朝进出口tibble时两次调用gather()导致行数异常过多求助

Hey there! Let's break down why your double gather() calls are spawning way more rows than expected, and fix it with cleaner, tidier code.

What's Causing the Row Blowup? #

When you first gather() the import columns (like IJAN, IFEB), you convert those wide columns into long format—so each original row (say, one per year) turns into 12 rows (one per import month). But when you run gather() again on the export columns (like EJAN, EFEB), you’re taking each of those already-expanded rows and splitting them again into 12 more rows (one per export month). That creates a Cartesian product: your final row count ends up being original_rows × 12 (imports) × 12 (exports), which is way more than the tidy format you want (which should be original_rows × 24—one row per year-month-trade type).

The Better Solution: Use pivot_longer() (Tidyverse's Modern Alternative) #

Instead of two separate gather() calls, use pivot_longer() to handle all your trade columns in one go. It’s designed for exactly this kind of multi-part column name scenario:

library(tidyverse)

# Assuming your data frame is named trade_data, with a 'year' column plus monthly trade columns
tidy_trade <- trade_data %>%
  pivot_longer(
    # Select all columns starting with I (import) or E (export)
    cols = starts_with(c("I", "E")),
    # Split column names into two parts: trade type (I/E) and month
    names_to = c("trade_type", "month"),
    # Regex pattern to split the column name: first character is type, rest is month
    names_pattern = "(.)(.*)",
    # Name for the numeric trade value column
    values_to = "trade_value"
  ) %>%
  # Clean up the trade type and month columns for readability
  mutate(
    trade_type = case_when(
      trade_type == "I" ~ "import",
      trade_type == "E" ~ "export"
    ),
    # Convert month abbreviations (JAN, FEB) to numeric months (1-12) (optional but useful)
    month = match(month, toupper(month.abb))
  )

This will give you a clean tidy dataset where each row represents a single year-month-trade type combination—no extra rows, no Cartesian product mess.

If You Must Use gather() (Legacy Code Scenario) #

If you need to stick with gather() for some reason, split the import and export data into separate frames, clean them up, then join them:

# Process imports first
import_data <- trade_data %>%
  select(year, starts_with("I")) %>%
  gather(key = "month", value = "trade_value", starts_with("I")) %>%
  mutate(
    month = str_remove(month, "^I"), # Remove the leading "I" from month names
    trade_type = "import"
  )

# Process exports separately
export_data <- trade_data %>%
  select(year, starts_with("E")) %>%
  gather(key = "month", value = "trade_value", starts_with("E")) %>%
  mutate(
    month = str_remove(month, "^E"), # Remove the leading "E" from month names
    trade_type = "export"
  )

# Combine into one tidy dataset
tidy_trade <- bind_rows(import_data, export_data)

This approach avoids the Cartesian product because you’re handling imports and exports as separate groups before combining them.

Final Check #

Your expected row count should be number_of_original_rows × 24 (12 months × 2 trade types). For 20 years of data (20 original rows), that’s 480 rows—way more manageable than 4000+!

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