R语言实现Symbol列值转列名、原列名转列的数据集透视方案

Hey there! As someone new to R, pivot transformations can feel a bit confusing at first, but there are two super straightforward ways to get your desired table structure. Let’s walk through them step by step:

Method 1: Using the Tidyverse (dplyr + tidyr) #

This is the most intuitive approach for beginners, as it uses readable verbs to manipulate your data. First, make sure you have the tidyverse installed (if not, run install.packages("tidyverse") first):

# Load the tidyverse package
library(tidyverse)

# Let's create a sample data frame matching your original table (replace with your actual data)
df <- tibble(
  Symbol = c("STAT1", "GAPDH", "ACTB", "PRPF8", "CAPNS1"),
  GSM1573135 = c(0.29600048, -0.06155872, -0.03187084, 0.29218674, 0.28366375),
  GSM1573160 = c(-0.60394290, 0.59726906, 0.13835812, 0.13059807, 0.69432163),
  GSM1573175 = c(-0.10135269, 0.18338203, 0.03187466, 0.18707657, 0.01103115),
  GSM1573186 = c(0.2308369, -0.4480076, -0.7396250, -0.3613615, -0.9594564),
  GSM1573132 = c(-0.13862038, 0.13947392, -0.09272575, -0.04664040, -0.13679028)
)

# Step 1: Convert wide table to long format (extract GSM IDs and their values)
long_df <- df %>%
  pivot_longer(
    cols = starts_with("GSM"),  # Target all columns starting with "GSM"
    names_to = "GSM ID",        # Name the new column for GSM IDs
    values_to = "Expression"    # Name the new column for expression values
  )

# Step 2: Convert long table back to wide format (use Symbols as column names)
target_df <- long_df %>%
  pivot_wider(
    names_from = Symbol,        # Use Symbol values as new column names
    values_from = Expression    # Fill columns with corresponding expression values
  )

# View the final result
target_df

How this works: #

• pivot_longer takes all your GSM columns and collapses them into two columns: one for the GSM ID, and one for the expression value.
• pivot_wider then takes the unique values from the Symbol column and turns them into new columns, filling each with the matching expression values from the long table.

Method 2: Base R (No Extra Packages Needed) #

If you prefer not to install additional packages, you can use base R's transpose function. This is a quicker one-liner once you get the hang of it:

# Using the same sample df from Method 1
# First, set the Symbol column as row names
rownames(df) <- df$Symbol
df <- df[, -1]  # Remove the original Symbol column

# Transpose the data frame (swap rows and columns)
transposed_df <- as.data.frame(t(df))

# Add the GSM ID column from the row names
target_df_base <- cbind("GSM ID" = rownames(transposed_df), transposed_df)
rownames(target_df_base) <- NULL  # Reset row names to sequential numbers

# View the result
target_df_base

How this works: #

• We first turn the Symbol values into row names so they become the column names when we transpose.
• t(df) swaps rows and columns—your original GSM column names become rows, and your Symbols become columns.
• Finally, we convert the row names (now GSM IDs) into a proper column and clean up the row numbering.

Both methods will give you exactly the target table structure you need. The tidyverse method is more readable for beginners, while the base R method is great if you want to avoid package installations.

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