在RStudio中使用R将宽格式CSV转为长格式的技术咨询

Let's walk through solving your wide-to-long format issue step by step—since your CSV doesn't have headers, we need to handle column naming and identify your identifier columns first.

Step 1: Understand Your Data Structure #

First, let's confirm which columns are your identifier columns (the ones that don't represent years/emissions values). Run this to check the first few rows:

head(df)

Based on the filename (bcghg_emissions_1990-2016_pub2018.csv), it's safe to assume the first 4 columns are categorical identifiers (like sector, gas type, etc.), and the remaining columns correspond to years 1990 through 2016.

Step 2: Rename Columns for Clarity #

Since your CSV has no headers, R assigns default names like V1, V2, etc. Let's replace these with meaningful names, including the actual years:

# Assign names to identifier columns and year columns
colnames(df) <- c("Sector", "Gas_Type", "Subcategory", "Region", as.character(1990:2016))

Adjust the first 4 names (Sector, Gas_Type, etc.) to match what you see in head(df)—they're just placeholders here.

Option 1: Using reshape2::melt #

Now you can correctly set id.vars to your identifier columns. We'll also specify names for the resulting year and emissions columns:

library(reshape2)
dl <- melt(df,
           id.vars = c("Sector", "Gas_Type", "Subcategory", "Region"),
           variable.name = "Year",
           value.name = "Emissions")

• id.vars: List all columns that shouldn't be reshaped (your categorical identifiers)
• variable.name: Sets the name of the new column that will hold the years
• value.name: Sets the name of the new column that will hold the emission values

Option 2: Using tidyr::pivot_longer (Cleaner Modern Approach) #

Your initial pivot_longer attempt showed V1/V2 in the name column because those were the default column names. With our renamed columns, this works seamlessly:

library(tidyr)
dl <- pivot_longer(df,
                   cols = -c(Sector, Gas_Type, Subcategory, Region), # Exclude identifier columns
                   names_to = "Year",
                   values_to = "Emissions")

If you don't want to rename columns upfront, you can convert the default V* names to years after reshaping:

dl <- pivot_longer(df,
                   cols = -(V1:V4),
                   names_to = "Year",
                   values_to = "Emissions") %>%
  dplyr::mutate(Year = 1990 + as.integer(sub("V", "", Year)) - 5)
# How this works: Remove "V" from column names, convert to integer, then calculate the year (V5 = 1990)

Verify the Result #

Run head(dl) to confirm:

• The Year column now shows actual years (1990, 1991, ..., 2016)
• Each row represents a single emission value for a specific category and year

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