Many colleagues have asked me how to batch process data in spreadsheets, so I will share some text processing tips that I often use at work. The Python ‘re’ library can indeed save us a lot of trouble!
1. Processing Data Exported from Excel
Often the Excel data we receive is not standardized and requires some cleaning. Here I share a tool for processing various formats of data.
import re
class DataCleaner:
"""Data cleaning utility class"""
@staticmethod
def clean_phone(value):
"""Clean phone number formats
Supports:
- Regular phone number: 13912345678
- With separators: 139-1234-5678
- With parentheses: (139) 1234-5678
- With international code: +86 13912345678
"""
if not value: # Handle empty values
return None
# Extract numbers
numbers = re.sub(r'\D', '', str(value))
# Handle cases with international code
if len(numbers) > 11 and numbers.startswith('86'):
numbers = numbers[2:]
# Validate if it's a valid phone number
if re.match(r'^1[3-9]\d{9}$', numbers):
return numbers
return None
@staticmethod
def clean_name(value):
"""Clean name formats
- Remove extra spaces
- Remove special characters
- Standardize case format
"""
if not value:
return None
# Remove special characters and extra spaces
name = re.sub(r'[^-\u4e00-\u9fff\w\s]', '', str(value))
name = ' '.join(name.split())
# Handle English name format
if re.match(r'^[a-zA-Z\s]+$', name):
# Convert English names to title case
return ' '.join(word.capitalize() for word in name.split())
return name
@staticmethod
def clean_date(value):
"""Clean date formats
Supports:
- 2024-01-01
- 2024/01/01
- 2024.01.01
- 2024年01月01日
"""
if not value:
return None
# Match year, month, and day
pattern = r'\n (\d{4}) # Year\n [-/年\\.] # Separator\n (\d{1,2}) # Month\n [-/月\\.] # Separator\n (\d{1,2}) # Day\n 日? # Optional '日' character\n '
match = re.search(pattern, str(value), re.VERBOSE)
if match:
year, month, day = match.groups()
# Format date
return f"{year}-{int(month):02d}-{int(day):02d}"
return None
# Example usage
cleaner = DataCleaner()
# Test data
test_data = {
"Phone": [
"13912345678",
"139-1234-5678",
"(139) 1234-5678",
"+86 13912345678"
],
"Name": [
"张 三",
"JOHN smith",
"李四(实习生)",
"王 五"
],
"Date": [
"2024-1-1",
"2024/01/01",
"2024.1.1",
"2024年1月1日"
]
}
# Test each data type
for data_type, values in test_data.items():
print(f"\nProcessing {data_type}：")
for value in values:
if data_type == "Phone":
result = cleaner.clean_phone(value)
elif data_type == "Name":
result = cleaner.clean_name(value)
else:
result = cleaner.clean_date(value)
print(f"Original value：{value}")
print(f"Processed value：{result}\n")2. Automatically Extract Tasks from Meeting Minutes
When processing meeting minutes, we often need to extract to-do items and responsible persons.
import re
from datetime import datetime
from typing import List, Dict
class MeetingMinutesParser:
"""Meeting minutes parser"""
def __init__(self, text: str):
self.text = text
def extract_meeting_info(self) -> Dict:
"""Extract basic meeting information"""
info = {}
# Extract meeting time
time_pattern = r'时间[：:]\\s*(\d{4}[-/年]\d{1,2}[-/月]\d{1,2}日?(?:\s*\d{1,2}:\d{2})?)'
time_match = re.search(time_pattern, self.text)
if time_match:
info['时间'] = time_match.group(1)
# Extract attendees
attendee_pattern = r'参会[人员].*?[：:]\s*([\u4e00-\u9fff、\s]+)'
attendee_match = re.search(attendee_pattern, self.text)
if attendee_match:
attendees = re.split(r'[、\s]+', attendee_match.group(1))
info['参会人员'] = [a for a in attendees if a]
return info
def extract_tasks(self) -> List[Dict]:
"""Extract to-do items"""
tasks = []
# Patterns for matching to-do items
task_patterns = [
# 1. Standard format: 1. Task content (Responsible person: Zhang San)
r'\d+\.\s*([^（(]+)[\(（]责任人?[：:]\s*([\u4e00-\u9fff]+)[\)）]',
# 2. Simple format: - Task content @ Zhang San
r'[-\*]\s*([^@]+)@([\u4e00-\u9fff]+)',
# 3. Other formats: Task content (Zhang San responsible)
r'([^（(]+)[\(（]([\u4e00-\u9fff]+)负责[\)）]'
]
for pattern in task_patterns:
for match in re.finditer(pattern, self.text):
task, owner = match.groups()
tasks.append({
'任务': task.strip(),
'负责人': owner.strip()
})
return tasks
# Example usage
test_minutes = """
项目周会纪要
时间：2024年2月23日 14:30
地点：三楼会议室
参会人员：张三、李四、王五
会议内容：
1. 需求文档修订（责任人：张三）
2. 性能测试报告@李四
3. 代码优化（王五负责）
下次会议时间：下周五下午2点
"""
parser = MeetingMinutesParser(test_minutes)
# Extract meeting information
meeting_info = parser.extract_meeting_info()
print("Meeting Information：")
for key, value in meeting_info.items():
print(f"{key}: {value}")
# Extract tasks
print("\nTo-Do Items：")
tasks = parser.extract_tasks()
for task in tasks:
print(f"Task：{task['任务']}")
print(f"Responsible Person：{task['负责人']}\n")3. Batch Rename Files
When organizing files, it is often necessary to standardize the naming format of files.
import os
import re
from datetime import datetime
class FileRenamer:
"""File renaming utility"""
def __init__(self, folder_path: str):
self.folder_path = folder_path
def standardize_filename(self, pattern: str, new_format: str) -> None:
"""Standardize file names
Args:
pattern: Regular expression to match file names
new_format: New file name format, using {} as placeholders
"""
for filename in os.listdir(self.folder_path):
match = re.match(pattern, filename)
if match:
# Extract matching groups
groups = match.groupdict()
# Format new file name
new_name = new_format.format(**groups)
# Rename file
old_path = os.path.join(self.folder_path, filename)
new_path = os.path.join(self.folder_path, new_name)
if old_path != new_path:
os.rename(old_path, new_path)
print(f"Renamed: {filename} -> {new_name}")
# Example usage
renamer = FileRenamer("./files")
# Example 1: Handle files named by date
date_pattern = r'\n # Match year and type in file name\n (?P\d{4}) # Year\n (?P\d{2}) # Month\n (?P\d{2}) # Day\n _? # Optional underscore\n (?P[a-zA-Z]+) # File type\n \.(?P\w+) # File extension\n'\n
# New naming format
new_format = "{year}-{month}-{day}_{type}.{ext}"
# Execute renaming
renamer.standardize_filename(date_pattern, new_format)
# Example 2: Handle screenshot files
screenshot_pattern = r'\n Screenshot_ # Fixed prefix\n (?P\d{14}) # Timestamp\n \.(?P\w+) # File extension\n'\n
# Convert timestamp to readable format
def format_timestamp(groups):
timestamp = groups['timestamp']
date = datetime.strptime(timestamp, '%Y%m%d%H%M%S')
return date.strftime('%Y-%m-%d_%H-%M-%S')
# New naming format
screenshot_format = "Screenshot_{timestamp}.{ext}"
# Execute renaming
renamer.standardize_filename(screenshot_pattern, screenshot_format)4. Clean HTML Text
Text copied from web pages often contains HTML tags that need to be cleaned before use.
import re
class HTMLCleaner:
"""HTML text cleaning utility"""
@staticmethod
def remove_tags(html: str) -> str:
"""Remove HTML tags"""
# First handle special tags
html = re.sub(r'<style[^>]*>[\s\S]*?</style>', '', html)
html = re.sub(r'<script[^>]*>[\s\S]*?</script>', '', html)
# Remove other HTML tags
text = re.sub(r'<[^>]+>', '', html)
# Clean whitespace characters
text = re.sub(r'\s+', ' ', text)
return text.strip()
@staticmethod
def extract_text(html: str, tag: str) -> list:
"""Extract text from specified tags"""
pattern = f'<{tag}[^>]*>(.*?)</{tag}>'
return re.findall(pattern, html, re.IGNORECASE | re.DOTALL)
@staticmethod
def extract_links(html: str) -> list:
"""Extract all links"""
pattern = r'href=["\'](.*?)['\"]'
return re.findall(pattern, html)
# Example usage
html_text = """
<div class="content">
<h1>Article Title</h1>
<p>This is a <strong>important</strong> text.</p>
<a href="https://example.com">Link</a>
<style>
.content { color: red; }
</style>
<script>
console.log("Test");
</script>
</div>
"""
cleaner = HTMLCleaner()
# Clean HTML tags
clean_text = cleaner.remove_tags(html_text)
print("Cleaned text：", clean_text)
# Extract specific tag content
paragraphs = cleaner.extract_text(html_text, 'p')
print("\nParagraph content：", paragraphs)
# Extract links
links = cleaner.extract_links(html_text)
print("\nLink list：", links)5. Extract Key Information from Articles
When analyzing articles, we may need to extract key information such as dates, names, locations, etc.
class TextAnalyzer:
"""Text analysis utility"""
def __init__(self, text: str):
self.text = text
def extract_dates(self) -> list:
"""Extract dates from text"""
patterns = [
# Standard date format
r'\d{4}[-/年]\d{1,2}[-/月]\d{1,2}日?',
# Month and day format
r'\d{1,2}月\d{1,2}日',
# Date with time
r'\d{4}[-/年]\d{1,2}[-/月]\d{1,2}日?\s*\d{1,2}:\d{2}'
]
dates = []
for pattern in patterns:
dates.extend(re.findall(pattern, self.text))
return dates
def extract_names(self) -> list:
"""Extract possible Chinese names"""
# Simple Chinese name matching (2-4 characters)
pattern = r'[\u4e00-\u9fff]{2,4}'
# Extract names surrounded by titles
name_patterns = [
r'(?:Mr|Ms|Classmate|Teacher)[\u4e00-\u9fff]{2,4}',
r'[\u4e00-\u9fff]{2,4}(?:Mr|Ms|Classmate|Teacher)'
]
names = set()
for pattern in name_patterns:
matches = re.findall(pattern, self.text)
names.update(matches)
return list(names)
def extract_locations(self) -> list:
"""Extract place names"""
# Common place name suffixes
suffixes = 'Province|City|District|County|Town|Village|Road|Street|Building|University|Community'
pattern = f'[\u4e00-\u9fff]+(?:{suffixes})'
return re.findall(pattern, self.text)
def extract_keywords(self, min_length: int = 2) -> list:
"""Extract possible keywords (consecutive Chinese phrases)"""
# Extract consecutive Chinese characters
pattern = f'[\u4e00-\u9fff]{{{min_length},}}'
return re.findall(pattern, self.text)
def extract_numbers(self) -> list:
"""Extract numbers (including integers, decimals, percentages)"""
patterns = [
# Integers and decimals
r'-?\d+(?:\.\d+)?',
# Percentages
r'-?\d+(?:\.\d+)?%',
# Chinese numbers
r'[一二三四五六七八九十百千万亿]+',
]
numbers = []
for pattern in patterns:
numbers.extend(re.findall(pattern, self.text))
return numbers
# Example usage
test_article = """
On February 23, 2024, Mr. Zhang San held a speech at Tsinghua University in Haidian District, Beijing.
During the speech, it was mentioned that the company's revenue increased by 123.4% last year, and the market share of the new product reached 35%. Mr. Li Si and Teacher Wang Wu also participated in this event.
After the meeting, everyone had an in-depth discussion in the conference room on the third floor of Zhongguancun Building. The next event is scheduled to be held in Shanghai on March 15 at 2:30 PM.
"""
analyzer = TextAnalyzer(test_article)
# Extract various information
print("Date List：")
print(analyzer.extract_dates())
print("\nName List：")
print(analyzer.extract_names())
print("\nLocation List：")
print(analyzer.extract_locations())
print("\nNumber List：")
print(analyzer.extract_numbers())
print("\nKeyword List：")
print(analyzer.extract_keywords(min_length=3))Practical Tips Summary
-
Pattern Reuse
# Define commonly used regex patterns as constants
PATTERNS = {
'phone': r'1[3-9]\d{9}',
'email': r'[\w\.-]+@[\w\.-]+\.\w+',
'date': r'\d{4}[-/年]\d{1,2}[-/月]\d{1,2}日?',
'chinese': r'[\u4e00-\u9fff]+',
'url': r'https?://(?:[\w-]+\.)+[\w-]+(?:/[\w-./?%&=]*)?'
}
# Reuse in code
def find_pattern(text: str, pattern_name: str) -> list:
if pattern_name in PATTERNS:
return re.findall(PATTERNS[pattern_name], text)
return []-
Using Named Groups for Improved Readability
# Using named groups syntax
pattern = r'\n (?P\d{4}) # Year\n [-/年] # Separator\n (?P\d{2}) # Month\n [-/月] # Separator\n (?P\d{2}) # Day\n'
match = re.search(pattern, '2024年02月23日', re.VERBOSE)
if match:
print(match.groupdict())-
Considerations When Processing Large Files
def process_large_file(filepath: str, pattern: str):
"""Process large files line by line"""
compiled_pattern = re.compile(pattern)
with open(filepath, 'r', encoding='utf-8') as f:
for line in f:
matches = compiled_pattern.findall(line)
for match in matches:
yield matchConclusion
Through these examples, we can see that regular expressions are very useful in office automation. The key is to:
-
Encapsulate commonly used patterns into utility classes
-
Select appropriate processing methods for specific issues
-
Pay attention to code maintainability and reusability
-
Handle edge cases and abnormal data
Most importantly, practice more, collect new problems, and gradually build your own toolkit.
If you find it useful, feel free to like and share～
If you have any questions, you can leave a message in the comments, and we can discuss together!
Follow Xiaoyao to not get lost, Python knowledge every day!
For those interested in Python, AI, office automation efficiency, and side business development, scan the code to add Xiaoyao, free group chat
Note: [Growth Exchange]
