python資料清洗excel

python清洗excel的資料還是很簡單的

這裡就列舉例子說一下

這是原始資料，這裡要處理的是地區和薪水兩個字段。

清洗前資料

import xlrd
import codecs
import re

def get_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


def open_xlsx(file):
    # 加載Excel資料，獲得工作表和行數
    data = xlrd.open_workbook(file) #讀取工作表名稱
    table0 = data.sheet_by_name('51') #讀取 目前sheet表
    nrows = table0.nrows # 擷取行數
    return table0, nrows


def main():

    table, nrows = open_xlsx('512.xlsx') # 調用打開excel的函數
    print('一共有{}行資料，開始清洗資料'.format(nrows))
    for i in range(1, nrows):
        job = table.row_values(i)[0]
        company = table.row_values(i)[1]
        companytype = table.row_values(i)[2]
        area = table.row_values(i)[3][:2]  # 地區取到城市，把區域去掉
        if area:
            area_list.append(area)
        experience = table.row_values(i)[4]
        degree = table.row_values(i)[5]
        salary = table.row_values(i)[6]
        if salary:  # 如果待遇這欄不為空，計算最低最高待遇
            getsalary = get_salary(salary)
            low_salary = getsalary[0]
            high_salary = getsalary[1]
        else:
            low_salary = high_salary = ""
        print('正在寫入第{}條，最低工資是{}k,最高工資是{}k'.format(i, low_salary, high_salary))
        output = ('{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\n').format(job, company, companytype, area,
                                                                                   experience,degree, low_salary, high_salary
                                                                                  )
        f = codecs.open('51jobanaly.xls', 'a+')
        f.write(output)
        f.close()


if __name__ == '__main__':
    main()

           

主要把薪資處理成以千/月為機關。保留城市。

處理後的資料：

處理後的資料