使用openxlsx导入Excel至R后，如何拆分单元格字符与数值并将1.4 x 10*4格式转换为1.4E4？

Hey there! Let's walk through how to handle this data cleaning workflow with openxlsx and regex, just as you outlined. I’ll use your sample data to make this concrete.

First, since you mentioned openxlsx auto-handles merged cells, importing is straightforward. Here’s how to load your file (or use your sample data for testing):

library(openxlsx)
# Load your actual Excel file
# df <- read.xlsx("your_file_path_here.xlsx")

# Use your provided sample data for testing
df <- data.frame(
  `Brilliance UTI Agar cfu/m3` = c(0, "Enterococcus spp 1.4 x 10*4", NA, 
                                   "Enterococcus spp 8.3 x 10*3", 0, NA, 
                                   "Proteus 8 x 10*4", "Enterococcus spp 1.7 x 10*3")
)

Next, we’ll split each cell into the organism name (text part) and the numeric expression. We’ll use the stringr package for regex-based extraction, since it’s intuitive and reliable.

First, load stringr, then add two new columns to separate the components:

library(stringr)

df <- df %>%
  mutate(
    # Extract organism name: grab all text before the first number, then trim extra spaces
    organism = case_when(
      is.na(`Brilliance UTI Agar cfu/m3`) ~ NA_character_,
      `Brilliance UTI Agar cfu/m3` == "0" ~ NA_character_,
      TRUE ~ str_trim(str_extract(`Brilliance UTI Agar cfu/m3`, "^[^0-9]+"))
    ),
    # Extract the numeric expression (e.g., "1.4 x 10*4")
    num_raw = case_when(
      is.na(`Brilliance UTI Agar cfu/m3`) ~ NA_character_,
      `Brilliance UTI Agar cfu/m3` == "0" ~ "0",
      TRUE ~ str_extract(`Brilliance UTI Agar cfu/m3`, "[0-9.]+ x 10\\*[0-9]+")
    )
  )

Regex Breakdown: #

• ^[^0-9]+: Matches all characters from the start until the first number (perfect for grabbing organism names).
• [0-9.]+ x 10\\*[0-9]+: Targets the numeric part—note we escape * with \\* since it’s a special regex character.

Now let’s turn those "1.4 x 10*4" strings into usable numeric values (either scientific notation like 1.4E4 or plain integers like 14000). We’ll rewrite the string to R’s standard scientific notation, then convert to numeric:

df <- df %>%
  mutate(
    # Convert to standard numeric (e.g., 1.4e4 → 14000 as a numeric type)
    standard_num = case_when(
      is.na(num_raw) ~ NA_real_,
      num_raw == "0" ~ 0,
      TRUE ~ as.numeric(str_replace(num_raw, " x 10\\*", "e"))
    ),
    # Optional: Convert to integer if whole numbers are preferred
    standard_int = case_when(
      is.na(standard_num) ~ NA_integer_,
      TRUE ~ as.integer(standard_num)
    )
  )

This replaces "x 10*" with "e", so "1.4 x 10*4" becomes "1.4e4"—which R can directly convert to a numeric value.

If you don’t need the original columns, you can rearrange or drop them for clarity:

final_df <- df %>%
  select(organism, standard_num, standard_int, `Brilliance UTI Agar cfu/m3`)

Sample Output: #

Running this on your test data will give you a data frame like this:

• If your data has negative exponents (e.g., "2.5 x 10*-3"), adjust the regex for num_raw to [0-9.]+ x 10\\*[-0-9]+ to capture the minus sign.
• If some organism names include numbers (unlikely here), tweak the organism extraction regex to be more specific (e.g., match until " x 10*" instead of the first number).
• Always check for NA values—case_when ensures we don’t get errors when handling empty cells or zeros.

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