这次要处理的是一份公司个体为单位的excel表格,其中公司包含“…公司XX支公司”、“…公司XX分公司”、““…公司XX营销部”等字样,其中XX为城市或者省份,目标是从【公司】列中提取对应城市。
前言
想要根据【公司】列提取城市列,主要有两种思路:
① 匹配
② 切分
由于【公司】内字段的样式并不统一,因此放弃②切分的方式,选用①匹配。
1 生成包含所有城市的正则表达式
这里用到的技术是【将excel列进行读取并生成正则表达式】。
这里有一份excel1,其中包含了一列【城市】,用python生成正则表达式。
# 读取 Excel 文件
df = pd.read_excel('D:/桌面/excel1.xlsx', sheet_name=1) # 读取excel1的sheet2
# 提取 "城市" 列的数值,并去除空值
city_list = df['城市'].dropna().tolist() # dropna删除包含缺失值(NaN)的行,tolist将其转换为Python列表
# 格式化城市名称列表
formatted_city_list = [city.strip() for city in city_list] # strip() 方法用于去除字符串两端的空格或特定字符
# 打印城市列表
print(formatted_city_list) # 这里就能生成所有的城市名称
运行python,得到包含所有城市的正则表达式,结果如下图(未完全显示)。
2 匹配城市
这里用到的技术是【用正则表达式匹配城市】。
复制上文的print结果,用于接下来的代码,要进行匹配的内容在excel2中。
import pandas as pd
import re
df = pd.read_excel('D:/桌面/excel2.xlsx', sheet_name=1)
city_list = ['城市A','城市B',......]
# 构造正则表达式模式
pattern = '(' + '|'.join(city_list) + ')'
# 使用字符串匹配找到城市并生成新的 city 列
df['city'] = df['公司'].str.extract(pattern, expand=False)
# 保存修改后的Excel文件
df.to_excel('D:/桌面/excel3.xlsx', index=False)
3 将未匹配出的行筛出并重新提取
3.1 检查未被筛选原因
检查excel3,将【城市】列选中,点击excel中筛选指令,选中空白区并检查分析。
并根据选取内容逐行检查,发现未匹配得出城市的原因主要有两个:
①【公司】内容为省份分公司,如“公司河北分公司‘,因此原先的城市文本无法匹配。
②【公司】列未包含任何省份或城市信息,代表总公司。
解决方案:
①问题的解决方案是:生成省份正则表达式,并重新匹配,并将省份对应该省省会提取出城市。
②问题的解决方案是:查找总公司的经营地址,获取城市信息。
3.2 选中要处理内容并处理省份信息
3.1已经将【城市】中空白内容选中,接下来是处理这部分内容。
首先,点击“查找——定位——可见单元格”进行复制粘贴到sheet2,得到所有未提取出城市的列表。
在此基础上,用python对数据进行省份匹配,并对应各自的省会城市。
import pandas as pd
import re
df = pd.read_excel('D:/桌面/excel3.xlsx', sheet_name=1)
province_list = ['安徽', '澳门', '北京', '重庆', '福建', '甘肃', '广东', '广西', '贵州', '海南', '河北', '河南',
'黑龙江', '湖北', '湖南', '吉林', '江苏', '江西', '辽宁', '内蒙古', '宁夏', '青海', '山东',
'山西', '陕西', '上海', '四川', '台湾', '天津', '新疆', '西藏', '香港', '云南', '浙江']
# 正则表达式,生成province列
pattern = '(' + '|'.join(province_list) + ')'
df['province'] = df['公司'].str.extract(pattern, expand=False)
# 生成省份-省会词典,匹配城市
capital_cities = {
"安徽": "合肥",
"澳门": "澳门",
"北京": "北京",
"重庆": "重庆",
"福建": "福州",
"甘肃": "兰州",
"广东": "广州",
"广西": "南宁",
"贵州": "贵阳",
"海南": "海口",
"河北": "石家庄",
"河南": "郑州",
"黑龙江": "哈尔滨",
"湖北": "武汉",
"湖南": "长沙",
"吉林": "长春",
"江苏": "南京",
"江西": "南昌",
"辽宁": "沈阳",
"内蒙古": "呼和浩特",
"宁夏": "银川",
"青海": "西宁",
"山东": "济南",
"山西": "太原",
"陕西": "西安",
"上海": "上海",
"四川": "成都",
"台湾": "台北",
"天津": "天津",
"新疆": "乌鲁木齐",
"西藏": "拉萨",
"香港": "香港",
"云南": "昆明",
"浙江": "杭州"
}
# 通过匹配"province"列的值,生成"city"列
df["city"] = df["province"].map(capital_cities)
# 保存修改后的Excel文件
df.to_excel('D:/桌面/excel4', index=False)
3.3处理剩余未提取信息
继续检查excel中剩余的【city】列无信息的文本。