基于Site_ID频率构建模板的R语言数据导入方法问询

Got it, let's work through this together! You’ve already built a template based on the maximum frequency of Site_ID (which is 3), and now need to populate all your data into this structure. Here’s a straightforward R solution using tidyverse tools—they’re made for exactly this kind of data reshaping:

First, let's set up context with sample data #

Let’s assume your source data looks something like this (adjust to match your actual dataset):

# Sample source data
source_data <- tibble(
  Site_ID = c("A", "A", "A", "B", "B", "C"),
  Measurement = c(10.2, 15.7, 12.1, 8.9, 9.5, 22.3)
)

And your pre-built template (based on max frequency 3) would have each Site_ID repeated 3 times, like:

# Pre-created template (matches your max frequency requirement)
template <- tibble(
  Site_ID = rep(c("A", "B", "C"), each = 3)
)

Method 1: Populate the template using row number matching #

This approach aligns your source data rows to the template by their position within each Site_ID group, filling missing spots with NA (or a value of your choice):

library(tidyverse)

# Add row numbers to both source data and template for alignment
template_with_data <- template %>%
  group_by(Site_ID) %>%
  mutate(row_position = row_number()) %>%
  left_join(
    source_data %>%
      group_by(Site_ID) %>%
      mutate(row_position = row_number()),
    by = c("Site_ID", "row_position")
  ) %>%
  ungroup() %>%
  select(Site_ID, Measurement) # Drop the helper row_position column

# View the result
template_with_data

Method 2: Dynamically build and populate the template (if you don't have a pre-made one) #

If you want to skip manually creating the template and generate it on the fly from your source data:

# Calculate max frequency dynamically (in case it changes later)
max_site_freq <- source_data %>%
  count(Site_ID) %>%
  pull(n) %>%
  max()

# Expand each Site_ID to max frequency and fill in data
template_with_data <- source_data %>%
  group_by(Site_ID) %>%
  mutate(row_position = row_number()) %>%
  complete(row_position = 1:max_site_freq) %>% # Fills missing rows with NA
  ungroup() %>%
  select(Site_ID, Measurement)

Customization tip #

If you don’t want NA for missing values, replace them with a default (like 0 or "Missing") using replace_na():

template_with_data <- template_with_data %>%
  replace_na(list(Measurement = 0)) # Replace NA with 0

Expected output #

Running either method will give you this structure (matching your template requirement):

# A tibble: 9 × 2
  Site_ID Measurement
  <chr>         <dbl>
1 A              10.2
2 A              15.7
3 A              12.1
4 B               8.9
5 B               9.5
6 B              NA  
7 C              22.3
8 C              NA  
9 C              NA  

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