R新手技术求助：遍历数据集并转换为MySQL长格式数据表

Hey there! As an R newbie tackling this wide-to-long data reshaping task (and prepping for MySQL), let me walk you through a simple, scalable approach using tidyverse tools—they’re perfect for this kind of work.

Step 1: Install & Load Required Packages #

First, we’ll use tidyr for reshaping, dplyr for data wrangling, purrr for batch processing multiple datasets, and DBI/RMariaDB to connect to MySQL. Install them if you haven’t already:

install.packages(c("tidyverse", "DBI", "RMariaDB"))
library(tidyverse)
library(DBI)
library(RMariaDB)

Step 2: Reshape a Single Dataset (Example) #

Let’s start with your sample data to get the hang of it. First, let’s recreate your original dataset correctly (I think there was a tiny formatting quirk in your example):

# Original wide-format dataset
original_data <- tibble(
  ID = c(2589, 2590, 2591),
  NAME = c("Joe", "Joseph", "Maria"),
  AGE = c(31, 15, 40)
)

Now use pivot_longer() to convert it to the long format you need. This function lets you specify which columns are your identifiers (ID here), and how to map the column names/values to your question and answer columns:

# Reshape to long format
long_data <- original_data %>%
  pivot_longer(
    cols = -ID,  # Keep ID as the identifier, reshape all other columns
    names_to = "question",  # Column names become the 'question' values
    values_to = "answer"    # Column values become the 'answer' values
  ) %>%
  rename(id = ID)  # Match your desired output column name 'id'

# Check the result
print(long_data)

This will give you exactly the output you showed:

# A tibble: 6 × 3
     id question answer
  <dbl> <chr>    <chr> 
1  2589 NAME     Joe   
2  2589 AGE      31    
3  2590 NAME     Joseph
4  2590 AGE      15    
5  2591 NAME     Maria 
6  2591 AGE      40    

Step 3: Batch Process Multiple Datasets #

If you have multiple datasets (say, data1, data2, data3 in your R environment, or CSV files in a folder), we can automate this with purrr::map().

Case A: Datasets are already in your R environment #

Put all your datasets into a list, then apply the reshaping function to each one, then combine them into a single large dataframe:

# Create a list of your datasets
dataset_list <- list(data1, data2, data3)

# Define a reusable reshaping function
reshape_to_long <- function(data) {
  data %>%
    pivot_longer(
      cols = -ID,
      names_to = "question",
      values_to = "answer"
    ) %>%
    rename(id = ID)
}

# Batch process and combine all datasets
combined_long_data <- dataset_list %>%
  map(reshape_to_long) %>%
  bind_rows()  # Stack all reshaped datasets into one

Case B: Datasets are CSV files in a folder #

If your data is stored as CSVs, you can load and reshape them in one go:

# Get paths to all CSV files in your target folder
csv_paths <- list.files("path/to/your/csv/folder", pattern = "\\.csv$", full.names = TRUE)

# Load, reshape, and combine all CSVs
combined_long_data <- csv_paths %>%
  map(read_csv) %>%  # Load each CSV
  map(reshape_to_long) %>%  # Reshape each
  bind_rows()

Step 4: Export to MySQL #

Finally, connect to your MySQL database and write the combined long-form data to a table:

# Connect to your MySQL database
con <- dbConnect(
  RMariaDB::MariaDB(),
  host = "your_host",  # e.g., "localhost"
  user = "your_username",
  password = "your_password",
  dbname = "your_database_name"
)

# Write the combined data to a MySQL table
dbWriteTable(
  conn = con,
  name = "your_target_table_name",  # Name of the table in MySQL
  value = combined_long_data,
  overwrite = TRUE  # Set to FALSE if you don't want to replace existing table
)

# Close the connection when done
dbDisconnect(con)

Quick Notes #

• Make sure all your datasets have the same ID column name (or adjust the cols = -ID part if your identifier has a different name).
• If some datasets have extra columns, you can standardize them first (e.g., select only the columns you need) before reshaping.
• For large datasets, consider using dbAppendTable() instead of dbWriteTable() if you want to add to an existing table incrementally.

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