在R中拆分数据框列：将indicator列拆分为两个独立指标列

Got it, let's sort out this pivot problem for you! The tidyr::pivot_wider() function is exactly what you need here—chances are you just had a parameter misalignment or didn't explicitly define your grouping keys. Let's break this down with your dataset structure.

First, let's replicate a small version of your data to test with:

library(tibble)
library(tidyr)
library(dplyr) # For the pipe operator %>%

# Sample data matching your structure
df <- tibble(
  reporter = rep("Aruba", 4),
  indicator = c("No. Of Export partners", "No. Of Export partners", 
                "No. Of Export partners", "No. Of Import partners"),
  year = c("year_2018", "year_2016", "year_2014", "year_2018"),
  value = c(6, 15, 21, 20)
)

Basic Pivot to Wide Format #

The core task is converting your long-format data to wide format, where each indicator value becomes its own column. Here's the correct pivot_wider() call:

df_wide <- df %>%
  pivot_wider(
    names_from = indicator, # Which column to use for new column names
    values_from = value     # Which column to pull values from for the new columns
  )

This will produce a tibble where each unique combination of reporter and year gets its own row, with separate columns for your export and import partner counts:

# A tibble: 3 x 4
  reporter year     `No. Of Export partners` `No. Of Import partners`
  <chr>    <chr>                       <dbl>                    <dbl>
1 Aruba    year_2018                       6                       20
2 Aruba    year_2016                      15                       NA
3 Aruba    year_2014                      21                       NA

Handling Missing Values #

You'll notice NA values where a year has data for one indicator but not the other. If you want to replace these with a default value (like 0), add the values_fill parameter:

df_wide_filled <- df %>%
  pivot_wider(
    names_from = indicator,
    values_from = value,
    values_fill = 0 # Replace NA with 0
  )

Cleaning Up Column Names #

The long indicator names can be cumbersome. You can simplify them using names_transform to adjust the column names during the pivot:

df_wide_clean <- df %>%
  pivot_wider(
    names_from = indicator,
    values_from = value,
    # Shorten column names to snake_case for easier use in R
    names_transform = list(indicator = ~ gsub("No. Of | ", "_", .x) %>% tolower())
  )

This will give you cleaner, R-friendly column names:

# A tibble: 3 x 4
  reporter year     export_partners import_partners
  <chr>    <chr>              <dbl>           <dbl>
1 Aruba    year_2018              6              20
2 Aruba    year_2016             15               0
3 Aruba    year_2014             21               0

If you were having issues before, double-check that your original dataset doesn't have duplicate reporter/year/indicator combinations—those would cause errors or unexpected results. You can verify with df %>% count(reporter, year, indicator) %>% filter(n > 1) to spot duplicates.

内容的提问来源于stack exchange，提问作者Oskar Helles Olesen