0Pricing
Pandas & NumPy Academy · Lesson

Combining and Cleaning Text Columns

Concatenate multiple columns into one string, strip whitespace, and normalise inconsistent category labels.

Combining and Cleaning Text Columns is a free Pandas & NumPy Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Pandas & NumPy Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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.

Frequently asked questions

Is the “Combining and Cleaning Text Columns” lesson free?

Yes — the full text of “Combining and Cleaning Text Columns” is free to read here on the web, and the Pandas & NumPy Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Pandas & NumPy Academy course, upgrade to CoddyKit PRO.

What will I learn in “Combining and Cleaning Text Columns”?

Concatenate multiple columns into one string, strip whitespace, and normalise inconsistent category labels. You practise Pandas & NumPy Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start Pandas & NumPy Academy?

No prior experience is required. Pandas & NumPy Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Combining and Cleaning Text Columns” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this Pandas & NumPy Academy lesson?

Yes. Every Pandas & NumPy Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. The .str Accessor
  2. Splitting and Replacing Strings
  3. Pattern Matching with Regex
  4. Combining and Cleaning Text Columns
← Back to Pandas & NumPy Academy