R语言Pivot longer操作：将单列多信息拆分为多列的实现方法

First, let's break down your data structure: your original data frame has columns where the first 4 rows hold metadata (Country, ISO code, Industry, Sector), and the next 4 rows contain values tied to different fuel types (from the V2 column). We'll use tidyverse packages (dplyr, purrr, tibble) to reshape and clean this data efficiently.

Step 1: Load Required Packages #

library(tidyverse)

Step 2: Extract Fuel and Indicator Metadata #

First, we isolate the measurement indicator (Energy Usage (TJ)) and fuel type information from the first two columns—these correspond directly to the value rows later:

fuel_metadata <- DI_SMALL %>%
  slice(5:8) %>% # Grab rows with fuel/indicator details (X to X.3)
  select(V1, V2) %>%
  rename(Indicator = V1, Fuel = V2) %>%
  mutate(value_id = row_number()) # Add index to match values consistently

Step 3: Process Each Data Column #

We’ll loop through each data column (V3 to V6) to extract metadata, pair it with the corresponding values, and combine with the fuel info we just extracted:

# Create a list of processed data frames for each column
processed_columns <- map(names(DI_SMALL)[3:6], function(col) {
  # Pull all values from the current column and convert to character
  col_vals <- DI_SMALL %>% pull(all_of(col)) %>% as.character()
  
  # Extract metadata from the first 4 rows of the column
  metadata <- list(
    Country = col_vals[1],
    ISO = col_vals[2],
    Industry = col_vals[3],
    Sector = col_vals[4]
  )
  
  # Extract values from rows 5-8 and convert to numeric
  values <- as.numeric(col_vals[5:8])
  
  # Combine all components into a tidy tibble
  tibble(
    Indicator = fuel_metadata$Indicator,
    Fuel = fuel_metadata$Fuel,
    !!!metadata, # Unpack the metadata list into separate columns
    Value = values
  )
})

# Combine all processed columns into one final data frame
final_output <- bind_rows(processed_columns)

Step 4: Verify the Result #

When you view final_output, it will match your desired structure:

• Indicator: The measurement type (Energy Usage (TJ))
• Fuel: The fuel category (Natural Gas, Coal, Petroleum, Nuclear Electricity)
• Country, ISO, Industry, Sector: The split metadata columns
• Value: The numeric value for each combination of metadata and fuel type

Quick Explanation #

• We first isolate fuel/indicator info because it’s consistent across all data columns.
• Using map(), we process each data column individually: grabbing metadata from the first 4 rows, pulling numeric values from the next 4, and merging everything with the fuel metadata.
• bind_rows() stitches all the processed column data frames into one tidy, usable data frame.

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