0Pricing
Pandas & NumPy Academy · درس

دمج أعمدة النص وتنظيفها

ادمج عدة أعمدة في سلسلة نصية واحدة، وأزل المسافات البيضاء، ووحّد تسميات الفئات غير المتسقة.

دمج أعمدة النص وتنظيفها درس مجاني في Pandas & NumPy Academy على CoddyKit. هذا هو الدرس 4 من أصل 4. يمكنك قراءة الدرس كاملاً أدناه مجاناً — ثم تمرن عليه مباشرة في المتصفح باستخدام محرر أكواد مدمج ومدرس ذكاء اصطناعي متاح 24/7. هذا الدرس جزء من مسار التعلم في 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.

الأسئلة الشائعة

هل درس «دمج أعمدة النص وتنظيفها» مجاني؟

نعم — نص درس «دمج أعمدة النص وتنظيفها» كامل متاح مجاناً هنا على الويب. لتمرينه بشكل تفاعلي (محرر أكواد مدمج ومدرس ذكاء اصطناعي متاح 24/7) وفتح باقي دورة Pandas & NumPy Academy، انتقل إلى CoddyKit PRO. تتضمن دورة Pandas & NumPy Academy 4 دروس في المجموع.

ماذا ستتعلم في «دمج أعمدة النص وتنظيفها»؟

ادمج عدة أعمدة في سلسلة نصية واحدة، وأزل المسافات البيضاء، ووحّد تسميات الفئات غير المتسقة. تتمرن على Pandas & NumPy Academy مع أكواد عملية تشغلها مباشرة في المتصفح، ومدرس ذكاء اصطناعي متاح 24/7 يجيب على أسئلتك أثناء عملك.

هل أحتاج إلى خبرة سابقة لأبدأ Pandas & NumPy Academy؟

لا تُشترط خبرة سابقة. Pandas & NumPy Academy على CoddyKit منظم للمبتدئين حتى المتقدمين، لذا يمكنك البدء من هنا أو من البداية والتقدم بسرعتك الخاصة. هذا هو الدرس 4 من أصل 4.

كم من الوقت يستغرق درس «دمج أعمدة النص وتنظيفها»؟

معظم دروس CoddyKit تستغرق حوالي 5–10 دقائق. كل منها موجز وتفاعلي، لذا تحرز تقدماً مستمراً وتستأنف من حيث توقفت عبر الويب والتطبيق.

هل يمكنني كتابة وتشغيل أكواد في درس Pandas & NumPy Academy هذا؟

نعم. كل درس في Pandas & NumPy Academy يتضمن محرر أكواد مدمج، لذا تكتب وتشغل أكواداً حقيقية مباشرة في متصفحك وتحصل على تعليقات فورية من الذكاء الاصطناعي — بدون إعداد محلي.

جميع الدروس في هذه الدورة

  1. موصّل .str
  2. تقسيم السلاسل النصية واستبدالها
  3. مطابقة الأنماط باستخدام Regex
  4. دمج أعمدة النص وتنظيفها
← العودة إلى Pandas & NumPy Academy