如何从Excel现有列提取特定信息创建新列？含多字段拆分需求

Hey there! Let's break down how to solve this data extraction and splitting problem—this is a super common task in data cleaning, so I'll walk you through step-by-step solutions, assuming you're using Python's Pandas (the most popular tool for this kind of work). I'll also throw in an R alternative just in case that's your tool of choice.

Your stimulus values are underscore-delimited, and baseFace is just the first segment of each string. Here's how to pull that out quickly:

import pandas as pd

# Sample dataframe matching your data
df = pd.DataFrame({
    'stimulus': ['005_y_f_n_b_m', '004_m_m_n_b_m', '005_y_f_s_b_m']
})

# Grab the first segment after splitting by underscores
df['baseFace'] = df['stimulus'].str.split('_').str[0]

This works because str.split('_') turns each string into a list (e.g., ['005', 'y', 'f', 'n', 'b', 'm']), and str[0] picks the first item from that list.

If you want to split the entire string into separate columns (one for each y, f, n, etc.), you have a couple of clean options:

Option 1: Split directly into a new DataFrame #

Use expand=True with str.split() to turn the split results into columns, then name them based on what each segment means (replace the column names with your actual labels):

# Split the full string into columns
split_components = df['stimulus'].str.split('_', expand=True)

# Rename columns to match your segment meanings
split_components.columns = ['baseFace', 'gender', 'feature_1', 'feature_2', 'feature_3', 'feature_4']

# Merge these new columns back into your original dataframe (skip baseFace to avoid duplicates)
df = pd.concat([df, split_components.drop('baseFace', axis=1)], axis=1)

Option 2: Extract segments individually (great if you only need specific columns) #

If you don't need every segment, you can pull them one by one using their position in the split list:

df['gender'] = df['stimulus'].str.split('_').str[1]
df['feature_1'] = df['stimulus'].str.split('_').str[2]
df['feature_2'] = df['stimulus'].str.split('_').str[3]
# Repeat for as many segments as you need

Option 3: Use Regular Expressions (for strict pattern matching) #

If your stimulus format is fixed (e.g., always 3 digits followed by 5 single characters), regex gives you precise control:

# Extract all segments in one go with regex groups
df[['baseFace', 'gender', 'feature_1', 'feature_2', 'feature_3', 'feature_4']] = df['stimulus'].str.extract(r'^(\d{3})_(\w)_(\w)_(\w)_(\w)_(\w)$')

The regex ^(\d{3})_(\w)_(\w)_(\w)_(\w)_(\w)$ matches exactly your string structure: 3 digits, then 5 single characters separated by underscores.

If you're using R instead of Python, the tidyr package makes this trivial:

library(tidyr)

# Sample dataframe
df <- data.frame(stimulus = c("005_y_f_n_b_m", "004_m_m_n_b_m", "005_y_f_s_b_m"))

# Extract baseFace
df$baseFace <- sapply(strsplit(df$stimulus, "_"), `[`, 1)

# Split into all columns
df <- separate(df, stimulus, into = c("baseFace", "gender", "feature_1", "feature_2", "feature_3", "feature_4"), sep = "_")

Let me know if you need adjustments based on your exact tool or more context about what each segment represents—I can tweak the code to fit your needs perfectly!

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