如何用dplyr工具将纯文本数据转换为规整表格以开展年度统计？

Absolutely! You can definitely use dplyr (paired with helper packages like readr or tidyr) to convert your ASCII grid data into a tidy, matrix-like table—making annual calculations (like averages) straightforward. Let me walk you through a practical workflow tailored to common grid data structures:

Step 1: Load and Parse the Raw Text Data #

First, get your ASCII data into R. If your file has headers (like grid dimensions or year labels) followed by rows of daily values, start by filtering out irrelevant lines and splitting the data into usable chunks:

library(readr)
library(dplyr)
library(tidyr)

# Load raw text file
raw_data <- read_lines("your_grid_data.txt")

# Example: Keep only lines with numeric data (adjust regex to match your data)
grid_rows <- raw_data[grepl("^\\d+|^\\-\\d+", raw_data)]

Step 2: Convert Grid Rows to a DataFrame #

Turn the cleaned lines into a structured dataframe. Let’s assume each row in the grid represents a day, and columns are individual grid cells:

# Split each line into values, convert to dataframe, and add a day identifier
grid_df <- grid_rows %>%
  str_split_fixed("\\s+", n = Inf) %>% # Split on whitespace
  as.data.frame() %>%
  mutate(day = row_number())

# If your data is split into annual blocks, add a year column
# Example: If each year has 365 rows, split the dataframe into yearly chunks
num_years <- length(grid_rows) %/% 365
grid_df_with_year <- split(grid_df, rep(1:num_years, each = 365)) %>%
  bind_rows(.id = "year") %>%
  mutate(year = as.integer(year))

Step 3: Reshape for Annual Calculations #

For easy annual average calculations, convert the data to a long format (one observation per row):

# Convert to long format (day + year + grid cell + observation value)
long_grid_df <- grid_df_with_year %>%
  pivot_longer(
    cols = starts_with("V"), # Match auto-generated column names from as.data.frame()
    names_to = "grid_cell",
    values_to = "observation"
  ) %>%
  mutate(observation = as.numeric(observation)) # Ensure values are numeric

# Calculate annual average per grid cell
annual_avg <- long_grid_df %>%
  group_by(year, grid_cell) %>%
  summarize(avg_value = mean(observation, na.rm = TRUE)) %>%
  ungroup()

Step 4: Convert to Matrix-Style Table (Optional) #

If you want a matrix-like structure where rows are years and columns are grid cells, use pivot_wider:

matrix_table <- annual_avg %>%
  pivot_wider(names_from = grid_cell, values_from = avg_value)

Key Adjustments for Your Data: #

• Header Parsing: If your ASCII file includes metadata (like grid size or year markers), extract that first to correctly split the data into annual blocks.
• Whitespace Handling: Use str_split instead of str_split_fixed if your values have variable spacing.
• Missing Values: Add na.rm = TRUE to summary functions if your data has gaps.

This workflow will transform your raw grid data into a structured table that’s perfect for annual statistical analysis.

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