【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 = ('{}	{}	{}	{}	{}	{}	{}	{}	{}	{}	{}
').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()