如何从含空值的67列18000行DataFrame提取所有8位数字至单列？

Hey there! Let's get those scattered 8-digit numbers pulled into a single column—here's a straightforward, step-by-step solution using tidyverse tools that preserves duplicates exactly as you need.

Step 1: Set Up Your Tools #

First, make sure you have the necessary packages installed and loaded. We'll use these for reshaping data and regex matching:

# Install packages if you haven't already
install.packages(c("dplyr", "tidyr", "stringr"))

# Load the packages
library(dplyr)
library(tidyr)
library(stringr)

Step 2: Reshape Your Wide DataFrame to Long Format #

Since your numbers are spread across 67 columns, converting to long format will gather every cell's content into one single column—making it easy to scan for matches:

# Convert all columns to character type (critical for regex to work) and reshape
long_data <- data %>%
  mutate(across(everything(), as.character)) %>%
  pivot_longer(
    cols = everything(),  # Include all 67 columns
    names_to = "source_column",  # Optional: tracks which column each value came from
    values_to = "cell_content"  # All cell values land here
  )

Step 3: Extract & Expand 8-Digit Numbers #

Now we'll use a regex to find exact 8-digit numbers, then expand any multi-number cells into individual rows (keeping all duplicates):

extracted_numbers <- long_data %>%
  # Pull ALL 8-digit numbers from each cell (handles multiple numbers per cell)
  mutate(eight_digit = str_extract_all(cell_content, "\\b\\d{8}\\b")) %>%
  # Unnest the list of numbers into separate rows
  unnest(eight_digit) %>%
  # Remove rows where no 8-digit number was found
  filter(!is.na(eight_digit))

Final Result #

The extracted_numbers DataFrame will have:

• A column eight_digit with every 8-digit number from your original data (duplicates included)
• Optional source_column and cell_content columns if you want to track where each number originated

If you only want the standalone column of numbers, you can simplify it:

just_numbers <- extracted_numbers %>% select(eight_digit)

Quick Regex Explanation #

The pattern \\b\\d{8}\\b ensures we match exactly 8-digit numbers:

• \\d{8} targets any sequence of 8 digits
• \\b (word boundary) prevents us from grabbing partial digits from longer numbers (like the first 8 digits of a 10-digit ID)

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