Text Wrangling Essentials in Python: split, join & formatting

In data analytics, raw data often contains uncurated text columns, delimited strings, and messy user inputs. Mastering Python's string manipulation methods is essential before loading data into Pandas.

---

1. Splitting Strings with .split()

The .split(sep, maxsplit) method breaks a single string into a Python list of substrings based on a delimiter.

# Default split on any whitespace:
raw_text = "Data    Analytics   with   Python"
words = raw_text.split()
# Output: ['Data', 'Analytics', 'with', 'Python']

# Splitting on specific delimiters (e.g. CSV or email):
csv_record = "101,Alice,Engineering,95000,New York"
fields = csv_record.split(",")
# Output: ['101', 'Alice', 'Engineering', '95000', 'New York']

email = "alex.smith@company.com"
username, domain = email.split("@")
# username: 'alex.smith', domain: 'company.com'

# Limiting splits with maxsplit:
log_entry = "2024-06-01:ERROR:Database connection timeout during sync"
parts = log_entry.split(":", maxsplit=2)
# Output: ['2024-06-01', 'ERROR', 'Database connection timeout during sync']

---

2. Joining Sequences with .join()

The inverse of split() is "delimiter".join(iterable), which combines elements of a list into a single string separated by the delimiter.

tags = ["Python", "Pandas", "NumPy", "SQL"]

# Combine with commas:
tag_string = ", ".join(tags)
# Output: 'Python, Pandas, NumPy, SQL'

# Combine lines with newline:
lines = ["Header Row", "Row 1 Data", "Row 2 Data"]
file_content = "\n".join(lines)

Note: All elements inside the list must be strings. If your list contains numbers, convert them first using a list comprehension: ", ".join([str(x) for x in numbers]).

---

3. Common String Cleaning Methods

raw_name = "   ALICE JOHNSON\n  "

# 1. Stripping whitespace
clean_name = raw_name.strip()           # 'ALICE JOHNSON'

# 2. Case normalization
lower_name = clean_name.lower()         # 'alice johnson'
title_name = clean_name.title()         # 'Alice Johnson'

# 3. Substring replacement
phone = "123-456-7890"
clean_phone = phone.replace("-", "")    # '1234567890'

# 4. Prefix / Suffix inspection
filename = "quarterly_revenue_2024.csv"
is_csv = filename.endswith(".csv")      # True
is_quarterly = filename.startswith("quarterly")  # True

---

4. Advanced F-String Formatting for Reports

revenue = 1452350.758
profit_margin = 0.2365
user_count = 8450

# Format with thousands comma separators and 2 decimal places:
print(f"Total Revenue: ${revenue:,.2f}")
# Output: Total Revenue: $1,452,350.76

# Format as Percentage:
print(f"Profit Margin: {profit_margin:.1%}")
# Output: Profit Margin: 23.7%

# Zero-padding numbers:
order_id = 42
print(f"Order #{order_id:06d}")
# Output: Order #000042