R语言长格式数据集转宽格式遇重复值报错求助

Hey there! Let's sort out that frustrating duplicate value error you're hitting when converting your dataset to wide format. First, let's confirm: looking at your sample data, every combination of Yr and Geo is unique—so the error might be coming from hidden duplicates in your full dataset. Let's start by checking for those, then jump into the exact conversion you need.

Step 1: Check for Duplicate (Yr, Geo) Pairs #

Before converting, make sure you don't have overlapping entries for the same year and district. Here's how to spot them in two common tools:

In R (with tidyverse) #

library(dplyr)

# Flag duplicates
df <- df %>%
  mutate(is_duplicate = duplicated(select(., Yr, Geo)) | duplicated(select(., Yr, Geo), fromLast = TRUE))

# View duplicates
filter(df, is_duplicate)

In Python (with pandas) #

import pandas as pd

# Flag duplicates
df['is_duplicate'] = df.duplicated(subset=['Yr', 'Geo'], keep=False)

# View duplicates
print(df[df['is_duplicate']])

If you find duplicates, decide how to handle them (e.g., average the values, keep the first entry) before proceeding.

Step 2: Convert to Your Desired Wide Format #

Once your dataset has unique (Yr, Geo) pairs, here's how to get the exact structure you want—with columns yr, dist1obs1, dist1obs2, dist2obs1, dist2obs2:

In R (tidyverse) #

library(tidyverse)

# Sample data (replace with your actual df)
df <- tibble(
  Yr = c(2001, 2002, 2003, 2004, 2001, 2002, 2003, 2004),
  Geo = c("Dist1", "Dist1", "Dist1", "Dist1", "Dist2", "Dist2", "Dist2", "Dist2"),
  Obs1 = c(1, 2, 4, 2, 1, 0.9, 6, 2),
  Obs2 = c(3, 5, 2, 1, 3, 5, 8, 0.2)
)

# Pivot to wide format
wide_df <- df %>%
  pivot_wider(
    id_cols = Yr,  # Keep Yr as the row identifier
    names_from = Geo,  # Use Geo values to name columns
    values_from = c(Obs1, Obs2),  # Spread these variables
    names_glue = "{tolower(Geo)}{.value}"  # Format column names like dist1obs1
  ) %>%
  # Reorder columns to match your desired structure
  select(Yr, dist1obs1, dist1obs2, dist2obs1, dist2obs2)

print(wide_df)

In Python (pandas) #

import pandas as pd

# Sample data (replace with your actual df)
data = {
    'Yr': [2001, 2002, 2003, 2004, 2001, 2002, 2003, 2004],
    'Geo': ['Dist1', 'Dist1', 'Dist1', 'Dist1', 'Dist2', 'Dist2', 'Dist2', 'Dist2'],
    'Obs1': [1, 2, 4, 2, 1, 0.9, 6, 2],
    'Obs2': [3, 5, 2, 1, 3, 5, 8, 0.2]
}
df = pd.DataFrame(data)

# Pivot to wide format
wide_df = df.pivot(
    index='Yr',
    columns='Geo',
    values=['Obs1', 'Obs2']
)

# Flatten and rename columns to match your desired format
wide_df.columns = [f"{dist.lower()}{var.lower()}" for var, dist in wide_df.columns]

# Reset index to make Yr a column, then reorder columns
wide_df = wide_df.reset_index()[['Yr', 'dist1obs1', 'dist1obs2', 'dist2obs1', 'dist2obs2']]

print(wide_df)

Handling Persistent Duplicates #

If you still have duplicates after checking, you can aggregate the values instead of failing. For example:

• In R, add values_fn = list(Obs1 = mean, Obs2 = mean) to pivot_wider to average duplicates.
• In Python, use pd.pivot_table() instead of pivot(), and set aggfunc='mean' (or 'sum', 'first', etc.).

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