在R语言中实现行转列：将长格式数据转为宽格式

DevOps 小满

2026-5-25

Hey there! Converting your long-format dataset to wide format is a common data reshaping task, and we can easily tackle this with either Python's Pandas library or R's tidyr package. Let me walk you through both practical approaches:

1. Using Python with Pandas

Since each ID-date combination is unique, we can use pivot along with a row counter to generate the numbered columns you need:

import pandas as pd

# Load your dataset into a DataFrame (you can also read from a CSV with pd.read_csv())
raw_data = [
    [9101001, "11-04-2010", 4],
    [9101001, "11-10-2010", 4],
    [9101002, "28-12-2009", 104],
    [9101002, "31-03-2010", 193],
    [9101002, "26-08-2010", 130],
    [9101002, "13-01-2011", 128],
    [9101002, "12-04-2011", 27],
    [9101002, "08-12-2011", 18],
    [9101002, "17-07-2012", 85],
    [9101002, "10-10-2012", 86],
    [9101002, "19-12-2012", 4],
    [9101002, "21-01-2013", 31],
    [9101003, "16-09-2008", 273],
    [9101003, "24-03-2009", 311],
    [9101003, "15-03-2011", 166],
    [9101003, "21-04-2011", 62]
]

df = pd.DataFrame(raw_data, columns=["ID", "DATE", "VALUE"])

# Add a sequential number for each entry within the same ID
df["entry_num"] = df.groupby("ID").cumcount() + 1

# Pivot to wide format and clean up column names
wide_df = df.pivot(index="ID", columns="entry_num", values=["DATE", "VALUE"])
wide_df.columns = [f"{col[0]}{col[1]}" for col in wide_df.columns]
wide_df = wide_df.reset_index()

# View the result
print(wide_df)

This code will produce a DataFrame where each ID has its corresponding DATE1, VALUE1, DATE2, VALUE2, etc., columns exactly as you requested.

2. Using R with tidyr & dplyr

For R users, the pivot_wider function from tidyr is built for this kind of reshaping. We'll add a row number to create the column suffixes:

library(tidyr)
library(dplyr)

# Create the raw dataset
raw_data <- data.frame(
  ID = c(9101001, 9101001, 9101002, 9101002, 9101002, 9101002, 9101002, 9101002, 9101002, 9101002, 9101002, 9101002, 9101003, 9101003, 9101003, 9101003),
  DATE = c("11-04-2010", "11-10-2010", "28-12-2009", "31-03-2010", "26-08-2010", "13-01-2011", "12-04-2011", "08-12-2011", "17-07-2012", "10-10-2012", "19-12-2012", "21-01-2013", "16-09-2008", "24-03-2009", "15-03-2011", "21-04-2011"),
  VALUE = c(4, 4, 104, 193, 130, 128, 27, 18, 85, 86, 4, 31, 273, 311, 166, 62)
)

# Add entry numbers per ID and pivot to wide format
wide_data <- raw_data %>%
  group_by(ID) %>%
  mutate(entry_num = row_number()) %>%
  ungroup() %>%
  pivot_wider(
    id_cols = ID,
    names_from = entry_num,
    values_from = c(DATE, VALUE),
    names_glue = "{.value}{entry_num}"
  )

# Print the result
print(wide_data)

The names_glue parameter ensures the columns are named DATE1, VALUE1, etc., matching your desired output structure.

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