[toc]

python 表格操作

将awk生成的数据导入表格

pandas ：pannel data analysis（面板数据分析）。pandas是基于numpy构建的，为时间序列分析提供了很好的支持。pandas中有两个主要的数据结构，一个是Series，另一个是DataFrame。

Series 类似于一维数组与字典(map)数据结构的结合。它由一组数据和一组与数据相对应的数据标签（索引index）组成。这组数据和索引标签的基础都是一个一维ndarray数组。可将index索引理解为行索引。 Series的表现形式为：索引在左，数据在右。

DataFrame是一个类似表格的数据结构，索引包括列索引和行索引，包含有一组有序的列，每列可以是不同的值类型（数值、字符串、布尔值等）。DataFrame的每一行和每一列都是一个Series，这个Series的name属性为当前的行索引名/列索引名。

COPY
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import openpyxl as xl
import pandas as pd
import numpy as np
import os
import datetime
 
cycle=38
time_year_month_day = str(datetime.date.today()-datetime.timedelta(days=cycle))
def proj_xls_to_xlsx(file_path,sheet_name,save_name,tableTitle=None):
    with open(file_path) as f:
        data = f.readlines()
    if not os.path.exists(save_name):
        wb = xl.Workbook()
        wb.save(save_name)
    else:
        wb = xl.load_workbook(save_name)
    ws1 = wb.create_sheet(0)
    ws1.title = sheet_name
    if tableTitle != None:
        for n in range(len(tableTitle)):
            c = n + 1
            ws1.cell(row=1, column=c).value = tableTitle[n]
    for d in data:
        value = d.split('\t')
        try:
 
           # print(value)
            value[5]=float(value[5])
        except:
            pass
        ws1.append(value)
    wb.save(save_name)
 
def crate_shuju_toushi(save_name,sheet_name,sheet_name2,index_name,values_name,department_name,new_department_name,personnel_name):
    f = pd.read_excel(io=save_name,sheet_name=sheet_name)
    res = pd.pivot_table(f,index=[index_name],values=[values_name],aggfunc=np.sum, margins=True)
 
    wb = xl.load_workbook(save_name)
    old_title = wb.worksheets[0]
    old_title.title = sheet_name2
 
    all_xinxi_title=list(zip(list((res.index)),list(res[values_name])))
    department_personnel = list(zip(f[department_name],f[index_name]))
    department_personnel_size_list = []
 
 
    for user in all_xinxi_title:
         for department_personnel_list in department_personnel:
             if user[0] in department_personnel_list:
                 if user[0] == '-':
                     continue
                 if user[0] == ' ':
                     continue
                 department_personnel_size_list.append((department_personnel_list[0],user[0],user[1]))
 
    end_department_personnel_size_list = sorted(list(set(department_personnel_size_list)), key=lambda x:x[2] ,reverse=True)
    all_xinxi_title_end = all_xinxi_title[-1]
    all_xinxi_title_end = list(all_xinxi_title_end)
    all_xinxi_title_end.insert(0,'')
    end_department_personnel_size_list.append(all_xinxi_title_end)
    end_department_personnel_size_list.insert(0,[new_department_name,personnel_name,values_name])
    end_department_personnel_size_list.pop()
 
    user_volue = []
    for user2 in end_department_personnel_size_list:
        user2 = list(user2)
        if user2[1] == user_volue:
            continue
        user_volue = user2[1]
        old_title.append(user2)
    wb.save(save_name)
 
def create_space_toushi(save_name,sheet_name,sheet_name2,department_num,sum_name,data_total,index_name,values_name):
    f = pd.read_excel(io=save_name,sheet_name=sheet_name,dtype={'业务XX利润编号':str})
    res = pd.pivot_table(f,index=index_name,values=values_name,aggfunc=np.sum,margins=True)
    all_list = list(zip(list(res.index),list(res[values_name])))
 
    wb = xl.load_workbook(save_name)
    sheet = wb.create_sheet(sheet_name2)
 
    space_list = []
    for user in all_list:
        user2 = list(user)[1]
        user3 = list(list(user)[0])
        if user3[0] == '-':
            space_list.append((user3[1],user2))
        if user3[0] == ' ':
            space_list.append((user3[1],user2))
    sum_list = []
    for slist in space_list:
        sum_list.append(slist[1])
    sum_list=sum(sum_list)
    sort_space_list = sorted(list(set(space_list)), key=lambda x:x[1] ,reverse=True)
    sort_space_list.append((sum_name, sum_list))
    sort_space_list.insert(0,(department_num,data_total))
    for all_space_list in sort_space_list:
        sheet.append(all_space_list)
    wb.save(save_name)
 
if __name__=='__main__':
    sheet_name = 'XXX详情'
    save_name = 'TJ-XJ-DATASHOW-' + time_year_month_day + '.xlsx'
    save_name = 'TJ-XJ-DATASHOW-' + time_year_month_day + '.xlsx'
    file_path = './able.' + time_year_month_day +'.lib.xls'
    proj_xls_to_xlsx(file_path=file_path,
                     sheet_name=sheet_name,
                     save_name=save_name)
 
    sheet_name = 'XXX总体情况'
    save_name = 'TJ-XJ-DATASHOW-' +  time_year_month_day + '.xlsx'
    file_path = './' + time_year_month_day + '.ProjInfo.XJpath.xls'
    table_title = ['业务XX名称','业务XX利润编号',
                   '运营经理','信息分析','涉及项目数',
                   '涉及数据量大小（G）','项目编号1',
                   '项目名称1','数据量(G)1',
                   '文库编号1','项目编号2',
                   '项目名称2','数据量(G)2',
                   '文库编号2','项目编号3',
                   '项目名称3','数据量(G)3',
                   '文库编号3','项目编号4',
                   '项目名称4   数据量(G)4','文库编号4',]
    proj_xls_to_xlsx(file_path=file_path,
                     sheet_name=sheet_name,
                     tableTitle=table_title,
                     save_name=save_name)
 
    save_name = 'TJ-XJ-DATASHOW-' + time_year_month_day + '.xlsx'
    sheet_name = 'XXX总体情况'
    sheet_name2 = 'XXX概况'
    index_name = '信息分析'
    values_name = '涉及数据量大小（G）'
    department_name = '业务XX名称'
    new_department_name = 'XX'
    personnel_name = '人员'
    crate_shuju_toushi(save_name=save_name,
                       sheet_name=sheet_name,
                       sheet_name2=sheet_name2,
                       index_name=index_name,
                       values_name=values_name,
                       department_name=department_name,
                       new_department_name=new_department_name,
                       personnel_name=personnel_name)
 
    index_name = ['信息分析','业务XX利润编号']
    save_name = 'TJ-XJ-DATASHOW-' + time_year_month_day + '.xlsx'
    sheet_name = 'XXX总体情况'
    department_num = 'XX编号'
    data_total = '数据量（G）'
    values_name = '涉及数据量大小（G）'
    sheet_name2 = '未匹配人员数据量'
    sum_name = 'ALL'
    create_space_toushi(index_name=index_name,
                        save_name=save_name,
                        sheet_name=sheet_name,
                        department_num=department_num,
                        data_total=data_total,
                        values_name=values_name,
                        sheet_name2=sheet_name2,
                        sum_name=sum_name,)

完成数据透视,

COPY
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import pandas as pd
import openpyxl as xl
 
import datetime
difference_cycle=33
time_year_month_day = str(datetime.date.today()-datetime.timedelta(days=difference_cycle))
def add_last_excel(old_sheet,new_sheet,gaikuang):
    pd01 = pd.read_excel(old_sheet,sheet_name=gaikuang,encoding='utf-8')
    pd02 = pd.read_excel(new_sheet,sheet_name=gaikuang,encoding='utf-8')
 
    pd11 = pd.read_excel(old_sheet,sheet_name='未匹配人员数据量',encoding='utf-8')
    pd12 = pd.read_excel(new_sheet,sheet_name='未匹配人员数据量',encoding='utf-8')
 
 
    result = pd.merge(pd01,pd02[['人员','涉及数据量大小（G）']],on='人员')
    result_list = list(zip(list((result['XXX'])),list((result['人员'])),list((result['涉及数据量大小（G）_x'])),list((result['涉及数据量大小（G）_y']))))
 
    not_match = pd.merge(pd11,pd12[['XXX编号','数据量（G）']],left_on='XXX编号',right_on='XXX编号')
    print(not_match)
    not_match_list = list(zip(list((not_match['XXX编号'])),list((not_match['数据量（G）_x'])),list((not_match['数据量（G）_y']))))
 
    wb2 = xl.load_workbook(new_sheet)
    remove_sheet1 = wb2[gaikuang]
    remove_sheet2 = wb2['未匹配人员数据量']
    wb2.remove(remove_sheet1)
    wb2.remove(remove_sheet2)
    wb2.save(new_sheet)
 
    wb2 = xl.load_workbook(new_sheet)
    sheet21 = wb2.create_sheet(gaikuang,0)
    sheet22 = wb2.create_sheet('未匹配人员数据量')
 
    result_head = ['XXX','人员','涉及数据量（G）','第二次涉及数据量（G）','任务额差']
    result_list.insert(0,result_head)
    result_for_num = 1
    for i in result_list:
        result_i = list(i)
        if result_for_num != 1:
            result_chae=i[2]-i[3]-i[2]*0.2
            result_i.append(float('%.2f'% result_chae))
        result_for_num = result_for_num + 1
        sheet21.append(result_i)
 
    not_match_head = ['XXX编号','数据量（G）','第二次数据量（G）','任务额差']
    not_match_list.insert(0,not_match_head)
    not_match_for_num = 1
    for j in  not_match_list:
        not_match_j = list(j)
        if not_match_for_num != 1:
            not_match_chae=j[1]-j[2]-j[1]*0.2
            not_match_j.append(float('%.2f'% not_match_chae))
        not_match_for_num = not_match_for_num + 1
        sheet22.append(not_match_j)
    wb2.save(new_sheet)
 
if __name__ == '__main__':
    old_sheet='TJ-XJ-DATASHOW-' + time_year_month_day + '-old.xlsx'
    new_sheet='TJ-XJ-DATASHOW-' + time_year_month_day + '.xlsx'
    gaikuang='XXX概况'
    add_last_excel(old_sheet=old_sheet,new_sheet=new_sheet,gaikuang=gaikuang)