格式异常数据整理：处理导入的R语言格式异常数据文件

Got it, let's break down how to clean up this oddly formatted data frame. The key issue here is that your data is transposed—what should be column headers are in the first few rows, and each column represents a single sample with its metadata + measurements. Here's a step-by-step solution using both tidyverse (for readability) and base R (if you prefer no extra packages):

Using Tidyverse (Recommended) #

First, load the tidyverse packages if you haven't already:

library(tidyverse)

Step 1: Remove Unwanted Quotes #

Every value is wrapped in double quotes, so we'll strip those out first:

df_clean <- df %>%
  mutate(across(everything(), ~ str_remove_all(.x, '"')))

Step 2: Transpose and Fix Column Names #

Right now, rows represent metadata/measurement types, and columns represent samples. We'll transpose the data so samples are rows, then set the first row (originally the X1 column values) as our column headers:

df_transposed <- df_clean %>%
  t() %>%
  as.data.frame(stringsAsFactors = FALSE) %>%
  set_names(.[1, ]) %>%  # Use the first row as column names
  slice(-1)  # Drop the now-redundant first row

Step 3: Reshape to Tidy Format #

Now we'll convert the wide format (one column per measurement) to a tidy long format where each row is a single observation:

df_tidy <- df_transposed %>%
  pivot_longer(
    cols = starts_with("7") | starts_with("8"),  # Target all measurement columns (795-800)
    names_to = "Measurement",
    values_to = "Value"
  ) %>%
  mutate(Value = as.numeric(Value))  # Convert the Value column to numeric

You can check the result with head(df_tidy)—it should look like this:

# A tibble: 6 × 5
  ID    Parameter Year  Measurement Value      
  <chr> <chr>     <chr> <chr>       <dbl>      
1 001   ap        2016  800         -0.000978  
2 001   ap        2016  799         -0.000854  
3 001   ap        2016  798         -0.000771  
4 001   ap        2016  797         -0.000763  
5 001   ap        2016  796         -0.000964  
6 001   ap        2016  795         -0.000839  

Using Base R (No Extra Packages) #

If you don't want to use tidyverse, here's the equivalent base R workflow:

Step 1: Strip Quotes #

df_clean <- lapply(df, function(x) gsub('"', '', x))
df_clean <- as.data.frame(df_clean, stringsAsFactors = FALSE)

Step 2: Transpose and Clean Columns #

df_transposed <- t(df_clean)
colnames(df_transposed) <- df_transposed[1, ]
df_transposed <- df_transposed[-1, , drop = FALSE]
df_transposed <- as.data.frame(df_transposed, stringsAsFactors = FALSE)

Step 3: Reshape to Long Format #

# Identify measurement columns (all columns after the first 3)
measurement_cols <- colnames(df_transposed)[4:ncol(df_transposed)]

df_tidy <- reshape(
  df_transposed,
  varying = measurement_cols,
  v.names = "Value",
  timevar = "Measurement",
  times = measurement_cols,
  direction = "long"
)

# Convert Value to numeric and clean row names
df_tidy$Value <- as.numeric(df_tidy$Value)
rownames(df_tidy) <- NULL

Either approach will give you a clean, usable data frame ready for analysis.

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