使用正则表达式拆分合并的数据行条目（附R语言示例数据集）

Alright, let's break down how to split those combined country-capital entries in your R dataframe using regex, plus a little help from the existing capital and url columns to make things more accurate. Here's a step-by-step solution:

Looking at your data, we have a few different cases to handle:

• Simple concatenations: e.g., GermanyBerlin → Germany + Berlin
• Country names with parentheses: e.g., England (UK)London → England (UK) + London
• Multi-word country names: e.g., United States of AmericaWashington DC → United States of America + Washington DC
• Capitals with special characters: e.g., HaitiPort-au-Prince → Haiti + Port-au-Prince
• Test cases with generic names: e.g., country66city → country66 + city

We'll use dplyr for data manipulation and stringr for regex handling (both are part of the tidyverse, so you can load the whole tidyverse if you prefer).

# Load required packages
library(dplyr)
library(stringr)

# Your original dataset
df <- data.frame( 
  country = c("GermanyBerlin", "England (UK)London", "SpainMadrid", "United States of AmericaWashington DC", "HaitiPort-au-Prince", "country66city"), 
  capital = c("#Berlin", "NA", "#Madrid", "NA", "NA", "NA"), 
  url = c("/country/germany/01", "/country/england-uk/02", "/country/spain/03", "country/united-states-of-america/04", "country/haiti/05", "country/country6/06"), 
  stringsAsFactors = FALSE 
)

# Step 1: Clean up the capital column first
df_clean <- df %>%
  mutate(
    # Convert "NA" strings to actual NA values, and strip the # prefix from capitals
    capital_clean = ifelse(capital == "NA", NA, str_remove(capital, "^#"))
  )

# Step 2: Split the country column using regex and fallback to URL data
df_clean <- df_clean %>%
  mutate(
    # Case 1: If we already have a known capital, remove it from the country string
    country_clean = case_when(
      !is.na(capital_clean) ~ str_remove(country, fixed(capital_clean)),
      # Case 2: Use regex to split where a lowercase/parenthesis ends and a capital starts
      TRUE ~ str_extract(country, "^.*?(?=[A-Z](?![a-z]*(\\s|$)))")
    ),
    # Extract capital for cases where we don't have it already
    capital_clean = case_when(
      !is.na(capital_clean) ~ capital_clean,
      TRUE ~ str_extract(country, "(?<=[a-z)])([A-Z].*)")
    ),
    # Fix edge cases like multi-word countries using the standardized URL
    country_clean = ifelse(
      is.na(country_clean) | str_length(country_clean) < 3,
      str_remove(url, "(^country/|/\\d+$)") %>% str_replace_all("-", " ") %>% str_to_title(),
      country_clean
    )
  )

# Final cleaned dataframe
final_df <- df_clean %>%
  select(
    original_country = country,
    cleaned_country = country_clean,
    cleaned_capital = capital_clean,
    country_url = url
  )

• Capital Column Cleaning: We first convert string "NA" values to real NAs, and remove the # prefix from existing capital entries to make them usable for splitting.
• Regex Splitting: The regex ^.*?(?=[A-Z](?![a-z]*(\\s|$))) matches everything up to the first uppercase letter that isn't part of a multi-word country name. For capitals, (?<=[a-z)])([A-Z].*) grabs everything starting after a lowercase letter or closing parenthesis.
• URL Fallback: For tricky cases like multi-word countries (e.g., United States of America), we use the standardized country name from the url column—we strip the country/ prefix and trailing /XX digits, replace hyphens with spaces, and format it to match the original title case.

When you run the code, your final_df will look like this:

• If you encounter country names that end in an uppercase letter (rare, but possible), you might need to tweak the regex slightly.
• The url column is a great safety net here because it provides a standardized version of the country name—always lean on that when regex alone isn't enough.

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