Pandas & NumPy Academy · 课时

合并与清理文本列

将多个列连接为一个字符串，去除空白，并统一不一致的类别标签。

合并与清理文本列 是 CoddyKit 上的免费 Pandas & NumPy Academy 课时。这是第 4 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 Pandas & NumPy Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 Pandas & NumPy Academy 课程共包含 4 节课。

本课时的部分内容尚未翻译，以英文显示。

Concatenating Text Columns

A common data preparation task is building a single string by combining values from multiple columns — for example, creating a full name from first and last name, or an address from street, city, and country. In Pandas, you concatenate string columns using the + operator on two Series (or a Series and a string literal), after converting numeric columns to strings with .astype(str).

import pandas as pd

df = pd.DataFrame({
    'first_name': ['Alice', 'Bob', 'Carol'],
    'last_name': ['Smith', 'Jones', 'White']
})

df['full_name'] = df['first_name'] + ' ' + df['last_name']
print(df['full_name'].tolist())
# ['Alice Smith', 'Bob Jones', 'Carol White']

Concatenating with Non-String Columns

When combining a numeric column with a string column, you must first convert the numeric column to string using .astype(str). Python's + operator raises a TypeError if you try to add a string Series and an integer Series. This is a common source of confusion when building composite keys or labels from mixed-type columns.

import pandas as pd

df = pd.DataFrame({
    'product': ['Widget', 'Gadget'],
    'version': [3, 5]
})

# Must convert int to str before concatenating
df['label'] = df['product'] + ' v' + df['version'].astype(str)
print(df['label'].tolist())
# ['Widget v3', 'Gadget v5']

.str.cat() for Joining with Separator

Series.str.cat(others, sep=) is the Pandas-native way to concatenate multiple Series with a separator, handling NaN values gracefully. By default, a NaN in any column causes the result for that row to be NaN. Pass na_rep='?' (or any string) to replace NaN with a placeholder instead of propagating it.

import pandas as pd
import numpy as np

df = pd.DataFrame({
    'city': ['NYC', 'LA', None],
    'state': ['NY', None, 'TX'],
    'country': ['USA', 'USA', 'USA']
})

# Join with ', ' — NaN rows produce NaN by default
df['location'] = df['city'].str.cat(
    [df['state'], df['country']],
    sep=', ',
    na_rep='N/A'
)
print(df['location'].tolist())
# ['NYC, NY, USA', 'LA, N/A, USA', 'N/A, TX, USA']

Normalising Inconsistent Category Labels

Real-world category columns often have inconsistent labels caused by typos, case variations, or different abbreviations (e.g., 'US', 'USA', 'United States'). The standard fix is to create a mapping dictionary that maps every variant to the canonical form, then apply it with .map() or .replace().

import pandas as pd

df = pd.DataFrame({
    'country': ['US', 'USA', 'United States', 'uk', 'UK', 'United Kingdom']
})

canonical = {
    'US': 'United States', 'USA': 'United States', 'United States': 'United States',
    'uk': 'United Kingdom', 'UK': 'United Kingdom', 'United Kingdom': 'United Kingdom'
}

df['country_clean'] = df['country'].map(canonical)
print(df['country_clean'].tolist())
# ['United States', 'United States', 'United States',
#  'United Kingdom', 'United Kingdom', 'United Kingdom']

Stripping and Standardising Case

Before comparing or grouping text columns, always strip whitespace and normalise case as the first cleaning step. Two values that look the same to a human (' Active' and 'active') are treated as different by Pandas because of the leading space and capitalisation difference. A two-step chain — strip then lower — catches both issues.

import pandas as pd

df = pd.DataFrame({
    'status': ['  Active', 'INACTIVE', 'active ', ' Pending  ', 'ACTIVE']
})

df['status_clean'] = df['status'].str.strip().str.lower()
print(df['status_clean'].value_counts())
# active      3
# inactive    1
# pending     1

Fuzzy Matching for Typo Correction

When typos prevent exact matching, fuzzy matching computes similarity scores between strings. The rapidfuzz library (or the classic fuzzywuzzy) provides vectorised fuzzy matching. A typical workflow: for each unique dirty value, find the closest canonical value above a similarity threshold and build a correction map.

# Conceptual example (requires: pip install rapidfuzz)
from rapidfuzz import process

canonical_cities = ['New York', 'Los Angeles', 'Chicago', 'Houston']
dirty_values = ['New Yrok', 'Los Angelas', 'Chicagoo']

for dirty in dirty_values:
    best, score, _ = process.extractOne(dirty, canonical_cities)
    print(f'{dirty!r} -> {best!r} (score={score:.0f}%)')
# 'New Yrok'   -> 'New York'    (score=91%)
# 'Los Angelas'-> 'Los Angeles' (score=96%)
# 'Chicagoo'   -> 'Chicago'     (score=94%)

Splitting Multi-Value Cells with explode()

Sometimes a cell contains multiple values separated by a delimiter (e.g., 'python,sql,pandas'). Split the column to get lists, then call .explode() to create one row per value. This converts a wide, packed cell into a tidy long format where each row has exactly one value — necessary for groupby and join operations on those values.

import pandas as pd

df = pd.DataFrame({
    'user_id': [1, 2, 3],
    'skills': ['python,sql', 'java,kotlin,sql', 'python']
})

df['skills'] = df['skills'].str.split(',')
exploded = df.explode('skills')
print(exploded)
#    user_id   skills
# 0        1   python
# 0        1      sql
# 1        2     java
# 1        2   kotlin
# 1        2      sql
# 2        3   python

Handling Mixed Encoding in Text

Text data from different sources may have encoding issues — odd characters like \xa0 (non-breaking space), \u2019 (curly apostrophe), or garbled accented characters. Use .str.encode('ascii', errors='replace').str.decode('ascii') for a quick ASCII-only clean, or .str.normalize('NFKD') to decompose accents before stripping them.

import pandas as pd
import unicodedata

df = pd.DataFrame({'name': ['Caf\u00e9', 'na\u00efve', 'r\u00e9sum\u00e9']})

# Normalize and strip accents (NFKD decomposition + ascii encoding)
df['name_ascii'] = (
    df['name']
    .str.normalize('NFKD')
    .str.encode('ascii', errors='ignore')
    .str.decode('ascii')
)
print(df)
#       name name_ascii
# 0     Cafe       Cafe
# 1    naive      naive
# 2   resume     resume

Creating Composite Keys

In many datasets you need to create a composite key from multiple columns to uniquely identify a row for joins or deduplication. Combining cleaned string columns (stripped, lowercased, normalised) into a single key string ensures consistent matching across data sources where the same entity might be spelled slightly differently in each source.

import pandas as pd

df = pd.DataFrame({
    'first': ['  Alice', 'BOB', ' Carol'],
    'last': ['Smith ', 'JONES', 'White  '],
    'dob': ['1990-01-15', '1985-07-22', '1992-11-30']
})

df['match_key'] = (
    df['first'].str.strip().str.lower() + '|' +
    df['last'].str.strip().str.lower() + '|' +
    df['dob']
)
print(df['match_key'].tolist())
# ['alice|smith|1990-01-15', 'bob|jones|1985-07-22', 'carol|white|1992-11-30']

Enforcing a Canonical Category List

After cleaning, validate that all values in a categorical text column belong to a known canonical set. Any value not in the set may indicate a new legitimate category or a data error. Log or flag unknown values for manual review rather than silently dropping them, so nothing slips through unnoticed.

import pandas as pd

df = pd.DataFrame({
    'status': ['active', 'inactive', 'active', 'archived', 'unknown_status']
})

canonical = {'active', 'inactive', 'archived'}

unknown = df[~df['status'].isin(canonical)]['status'].unique()
print('Unknown statuses:', unknown.tolist())
# ['unknown_status']

# Flag unknown rows
df['status_valid'] = df['status'].isin(canonical)
print(df)

Full Text Cleaning Pipeline

Here is a complete text cleaning pipeline that applies all the techniques from this lesson: strip whitespace, normalise case, fix abbreviations, remove special characters, and validate against a canonical list. This is the kind of reusable function you would add to any data ingestion module.

import pandas as pd

def clean_category_column(series, canonical_map, canonical_set):
    cleaned = (
        series
        .str.strip()
        .str.lower()
        .map(lambda x: canonical_map.get(x, x))  # fix known variants
    )
    unknown = cleaned[~cleaned.isin(canonical_set)]
    if not unknown.empty:
        print('Warning: unknown values:', unknown.unique().tolist())
    return cleaned

cmap = {'eng': 'engineering', 'hr': 'human_resources', 'mktg': 'marketing'}
cset = {'engineering', 'human_resources', 'marketing', 'finance'}

df = pd.DataFrame({'dept': ['Eng', 'HR', 'Marketing', 'MKTG', 'Legal']})
df['dept_clean'] = clean_category_column(df['dept'], cmap, cset)
print(df)

Quick Check

Test your understanding of combining and cleaning text columns.

Lesson Recap

In this lesson you learned: concatenate columns with + operator after converting numerics to strings, use str.cat(sep=) to join with a delimiter and handle NaN, apply a canonical map with .map() to normalise inconsistent labels, and use explode() to expand list-valued cells into rows. Enforce canonical sets to catch data quality issues early. Next up we sort DataFrames by column values.

常见问题解答

「合并与清理文本列」课时是免费的吗？

是的 — 「合并与清理文本列」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 Pandas & NumPy Academy 课程的其余内容，请升级到 CoddyKit PRO。 Pandas & NumPy Academy 课程共包含 4 节课。

「合并与清理文本列」这节课中我会学到什么？

将多个列连接为一个字符串，去除空白，并统一不一致的类别标签。你通过在浏览器中直接运行的动手代码来练习 Pandas & NumPy Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 Pandas & NumPy Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 Pandas & NumPy Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 4 节课，共 4 节。

「合并与清理文本列」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 Pandas & NumPy Academy 课中编写并运行代码吗？

能。每节 Pandas & NumPy Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 Pandas & NumPy Academy