【python】处理excel月薪和城市字段,爬虫数据预处理,xlrd
使用爬虫收集到的数据中月薪有很多种格式,如4000千/月,0.4万/月,200/天,怎样将他们的格式统一呢?
#coding=utf-8
import xlrd
import codecs
import re
def open_xlsx():
# 加载Excel数据,处理数据
data = xlrd.open_workbook('测试.xlsx') # 读取工作表
table = data.sheet_by_name('Sheet1') # 读取当前sheet表对象
rows = table.nrows # 获取行数
print('一共有{}行数据,开始清洗数据'.format(rows))
for i in range(1,rows):
# Excel 中的数据第一行分别是 company, job, degree, fuli, salary, experience, area, zhize, yaoqiu
company = table.row_values(i)[0]
job = table.row_values(i)[1]
degree = table.row_values(i)[2]
fuli = table.row_values(i)[3]
salary = table.row_values(i)[4]
experience = table.row_values(i)[5]
area = table.row_values(i)[6][:2] # 地区取到城市,把区域去掉
zhize = table.row_values(i)[7]
yaoqiu = table.row_values(i)[8]
if salary: # 如果待遇这栏不为空,计算最低最高待遇
result = handle_salary(salary)
low_salary = result[0]
high_salary = result[1]
else:
low_salary = high_salary = ""
print('正在写入第{}条,最低工资是{},最高工资是{}'.format(i, low_salary, high_salary))
output = ('{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\n').format(company, job, degree, fuli, salary, low_salary, high_salary, experience, area, zhize, yaoqiu)
f = codecs.open('清洗后的数据.xls', 'a+')
f.write(output)
f.close()
def handle_salary(salary):
# 利用正则表达式提取月薪,把待遇规范成千/月的形式
# 返回最低工资,最高工资的形式
if '-' in salary: # 针对1-2万/月或者10-20万/年的情况,包含-
low_salary = re.findall(re.compile('(\d*\.?\d+)'), salary)[0]
high_salary = re.findall(re.compile('(\d?\.?\d+)'), salary)[1]
if u'万' in salary and u'年' in salary: # 单位统一成千/月的形式
low_salary = float(low_salary) / 12 * 10
high_salary = float(high_salary) / 12 * 10
elif u'万' in salary and u'月' in salary:
low_salary = float(low_salary) * 10
high_salary = float(high_salary) * 10
else: # 针对20万以上/年和100元/天这种情况,不包含-,取最低工资,没有最高工资
low_salary = re.findall(re.compile('(\d*\.?\d+)'), salary)[0]
high_salary = ""
if u'万' in salary and u'年' in salary: # 单位统一成千/月的形式
low_salary = float(low_salary) / 12 * 10
elif u'万' in salary and u'月' in salary:
low_salary = float(low_salary) * 10
elif u'元' in salary and u'天' in salary:
low_salary = float(low_salary) / 1000 * 21 # 每月工作日21天
return low_salary, high_salary
if __name__ == '__main__':
open_xlsx()