Python-玩转数据-办公自动化案例

一、如何对超大数据量的excel文件记录去重（windows环境）

一段最简单的代码实现：
功能：将原文件记录排序去重后保存至新文件

import pandas as pd
df = pd.read_excel(r"d:\111.xlsx")
df.sort_values('city',ascending=False)#对文件按指定关键字进行排序
print(len(df))  #去重前记录数
a=df.drop_duplicates(['city','name'])#对文件按指定列retro_templates去重，keep='first'保留第一条记录, inplace=True 改变原文件 False 不改变原文件
print(len(a))  #去重后记录数
a.to_excel(r'd:\223.xlsx') #去重后文件重新保存到新文件

二、删除首行数据（linux环境）

1.为何使用python去除？shell脚本同样能实现，看具体的需求，shell适合单个文件，python便于遍历整个文件夹。
2.如何使用删除首行功能？搭积木式，将搜索到的遍历功能和删除首行功能放在一起。

#conding=utf8
import fileinput
import os
#获取目录下的全部文件
g = os.walk(r"/test")
for path,dir_list,file_list in g:
    for file_name in file_list:
         join = os.path.join(path, file_name)
         #逐个删除文件的第一行
         for line in fileinput.input(join, inplace=1):
             if not fileinput.isfirstline():
                 print(line.replace('\n','')) 

三、统计分区表及数据量（阿里云环境）

import re
# 初始化表名
# sql = 'truncate table inspur_sjcj.odps_table_name_list;'
# odps.execute_sql(sql)
#获取已保存历史表名
sql = 'select table_name from inspur_sjcj.odps_table_name_list '
with odps.execute_sql(sql).open_reader() as reader:
    #print('数据查询成功')
    table_list1 = list()
    for record in reader:
        for res in record:
            table_list = list(res[1].split('\n'))
            table_list1 += table_list
    #print(table_list1)

           
#抓取ODPS表与历史表名比对,存入新增表
sql = '''
        show tables;
      '''
sn = 1 
with odps.execute_sql(sql).open_reader() as reader:
    for record in reader:
        record_line = re.sub('\s+', '',str(record).strip()) 
        record_list = re.findall(r'[{](.*?)[}]',record_line)
        record_table = record_list[0].split("'")
        record_table_name1 = record_table[0].split(':')
        record_table_name2 = record_table[1].split(':')
 
        if any([item in record_table_name1[1] for item in table_list1]):print("found1")
        else: 
            sql2 = '''insert into inspur_sjcj.odps_table_name_list
            values(%s,'%s');
            '''%(sn,record_table_name1[1])
            odps.execute_sql(sql2)
            sn += 1
        if any([item in record_table_name2[1] for item in table_list1]):print("found2")
        else:    
            sql3 = '''insert into inspur_sjcj.odps_table_name_list
            values(%s,'%s');
            '''%(sn,record_table_name2[1])
            odps.execute_sql(sql3)
            sn += 1
#只保留最后一次分区表信息
sql = '''
        insert overwrite table  inspur_sjcj.odps_table_name_record_count 
            select  t.id,t.table_name,t.ds,t.record_number,t.count_time from
            (select  t.id,t.table_name,t.ds,t.record_number,t.count_time
            ,row_number() over (partition by t.table_name order by t.ds desc ) as rownum
                from    inspur_sjcj.odps_table_name_record_count t ) t 
            where rownum = 1;
        '''
odps.execute_sql(sql)
#根据表名统计新增分区及数据
sql = '''
        select table_name from inspur_sjcj.odps_table_name_list 
        where 
            (table_name  like '要统计表的过滤条件';
      '''
with odps.execute_sql(sql).open_reader() as reader:
    for record in reader:
        table_name = record.table_name
        #print (table_name)

        sql = '''
            insert into table inspur_sjcj.odps_table_name_record_count
            select  
                unique_id(),
                '%s',     --table_name
                ds,
                count(1),
                now()
            from inspur_sjcj.%s 
            where ds > (select nvl(max(ds),1) from inspur_sjcj.odps_table_name_record_count where table_name = '%s' ) 
            group by ds;
        '''%(table_name,table_name,table_name)
        
        #print (sql)
        odps.execute_sql(sql)
#print ('加载完成')

#输出前一天资源和当天的性能统计结果
sql = ''' 
    truncate table inspur_sjcj.odps_table_record_count_out;
    '''
print (sql)
odps.execute_sql(sql)

sql = '''
    insert into inspur_sjcj.odps_table_record_count_out
    select a.sno,a.table_name,b.ds,b.record_number,now() 
        from  inspur_sjcj.odps_table_name_list a 
        left join  inspur_sjcj.odps_table_name_record_count b 
        on a.table_name = b.table_name and b.ds >= to_char(getdate(),'yyyymmdd')  
        where 
            (a.table_name  like '要输出表的过滤条件'
            and a.table_name not like '%_v1' and a.table_name not like '%_city'
            and a.table_name not like '%_test' and a.table_name not like 'dwd_ft_%'    
    ;
    '''
print (sql)
odps.execute_sql(sql)

sql = '''
    insert into inspur_sjcj.odps_table_record_count_out
    select a.sno,a.table_name,b.ds,b.record_number,now() 
        from  inspur_sjcj.odps_table_name_list a 
        left join  inspur_sjcj.odps_table_name_record_count b 
        on a.table_name = b.table_name and b.ds = to_char(dateadd(getdate(),-1,'dd'),'yyyymmdd')  
        where 
            (a.table_name  like '要输出表的过滤条件%'
       ;
    '''
print (sql)
odps.execute_sql(sql)

#print('运行结束！