在R中导入XLS文件创建DataFrame：如何排除无效数据并提取指定列

Got it, let's walk through how to fix this step by step. Here's a straightforward way to import your XLS file into R, remove those 'xxxx' rows, and create a clean DataFrame:

Step 1: Install and Load Required Packages #

We'll use readxl to read Excel files and dplyr for easy data manipulation. If you haven't installed them yet, run this first:

install.packages(c("readxl", "dplyr"))

Then load the packages into your R environment:

library(readxl)
library(dplyr)

Step 2: Import the Raw Data #

First, read the entire Excel file without setting column headers (since the first row is invalid):

raw_data <- read_excel("your_file_path.xlsx", col_names = FALSE)

Replace "your_file_path.xlsx" with the actual path to your XLS file (e.g., "C:/data/my_file.xlsx" on Windows or "/home/user/data/my_file.xlsx" on macOS/Linux).

Step 3: Filter Out Invalid Rows #

Next, remove any rows that contain 'xxxx' (or variations like 'xxxxx'). We'll use filter_all to check every cell in each row and keep only rows where none of the cells match the 'xxxx' pattern:

clean_data <- raw_data %>%
  filter_all(all_vars(!grepl("^xxxx", .)))

The grepl("^xxxx", .) checks if a cell starts with 'xxxx', and the ! negates it—so we keep rows where no cells start with those characters.

Step 4: Set Column Headers and Clean the Data #

Now, the first row of clean_data is your header (A-G). Let's set those as column names, then remove the header row from the data itself:

# Set column names from the first row
colnames(clean_data) <- clean_data[1, ]

# Remove the header row from the data
clean_data <- clean_data[-1, ]

Step 5: Convert Columns to Numeric (Optional but Recommended) #

Since your data rows are numbers, convert all columns to numeric type to make sure you can perform calculations later:

clean_data <- clean_data %>%
  mutate(across(everything(), as.numeric))

Final Result #

Now clean_data is your desired DataFrame with just the A-G columns and no invalid rows. You can verify this by running:

print(clean_data)

Alternative: Base R Approach (No dplyr) #

If you prefer not to use dplyr, here's how you can do it with base R functions:

# Import raw data
raw_data <- read_excel("your_file_path.xlsx", col_names = FALSE)

# Filter out rows with 'xxxx'
clean_data <- raw_data[!apply(raw_data, 1, function(row) any(grepl("^xxxx", row))), ]

# Set column names and remove header row
colnames(clean_data) <- clean_data[1, ]
clean_data <- clean_data[-1, ]

# Convert to numeric
clean_data <- as.data.frame(lapply(clean_data, as.numeric))

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