故障日报生成v12

1、lambda 函数加入了 条件语句

2、生成报错文件

# -*- coding:utf-8 -*-

import pandas as pd, datetime
import os
import traceback
date = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
date2 = str(datetime.date.today().strftime('%Y/%m/%d')) + ' 09:30:00'
date3 = datetime.datetime.strptime(date2, '%Y/%m/%d %H:%M:%S')
input_file1 = '小区状态.xls'
input_file2 = 'TD-LTE小区.CSV'
input_file3 = '前一日告警日报.xlsx'
input_file4 = '当前告警.xls'
output_file = '大唐告警日报{}.xlsx'.format(date)
def main():
    writer = pd.ExcelWriter(output_file)
    area = {'JN': '黄岛区', 'JZ': '胶州市', 'HD': '黄岛区', 'KF': '开发区', 'LS': '崂山区'}
    out_service = pd.read_excel(input_file1)
    out_service0 = out_service[(out_service.loc[:, '运行状态'] != '可用(0)')]
    out_service01 = out_service0[(~out_service.loc[:, '失败原因'].isin(['连接超时(10022)查询小区状态信息-----------------------(结果个数=0)']))]
    out_service1 = out_service01[(~out_service.loc[:, '管理状态-激活状态'].isin(['去激活(1)']))]
    out_service2 = out_service1[['网元标识', '网元名称', '小区本地ID', '失败原因']]
    out_service2['站号'] = [i[0:10] for i in out_service2['网元名称']]
    out_service2['厂家'] = '大唐'
    out_service2['归属']=out_service2.apply(lambda x: x['网元名称'][13:15] if x['网元名称'][11:13]=='3M' else x['网元名称'][11:13],axis=1)

    out_service2['区县'] = [area[i] for i in out_service2['归属']]
    out_service2['是否疑似传输导致'] = '否'
    cell_info = pd.read_csv(input_file2, encoding='utf-16', sep='\t', error_bad_lines=False)
    cell_info1 = cell_info[['网元标识', '小区本地ID', '小区友好名']]
    out_service3 = pd.merge(out_service2, cell_info1, how='left', on=['网元标识', '小区本地ID'])
    out_service4 = out_service3.rename(columns={'小区本地ID': '小区号', '小区友好名': '小区名'})
    yesterday_out_service = pd.read_excel(input_file3, sheet_name=0)
    yesterday_out_service1 = yesterday_out_service[['小区名', '退服时间', '备注（枚举：长期、短期、三方+厂家）']]
    out_service5 = pd.merge(out_service4, yesterday_out_service1, how='left', on=['小区名'])
    m = 0
    for j in out_service5['失败原因']:
        if j == '设备已掉线':
            out_service5['是否疑似传输导致'][m] = '是'
        m += 1

    out_service6 = out_service5[['区县', '退服时间', '小区号', '小区名', '站号', '厂家', '备注（枚举：长期、短期、三方+厂家）', '是否疑似传输导致']]
    out_service6['退服时间'].fillna(date3, inplace=True)
    k = 0
    for i in out_service6['退服时间']:
        if i <= date3 + datetime.timedelta(-4):
            out_service6['备注（枚举：长期、短期、三方+厂家）'][k] = '长期'
        else:
            if i <= date3 + datetime.timedelta(-3):
                out_service6['备注（枚举：长期、短期、三方+厂家）'][k] = '短期三天'
            else:
                if i <= date3 + datetime.timedelta(-2):
                    out_service6['备注（枚举：长期、短期、三方+厂家）'][k] = '短期两天'
                else:
                    out_service6['备注（枚举：长期、短期、三方+厂家）'][k] = '短期一天'
        k += 1

    out_service6.to_excel(writer, sheet_name='退服故障明细', index=False)
    un_out_service = pd.read_excel(input_file4, parse_dates=['产生时间'], header=1)
    un_out_service1 = un_out_service[['故障源', '产生时间', '告警名称', '对象标识']]
    un_out_service1 = un_out_service1[(un_out_service1['故障源'] != 'PLMN=1,OMC=1')]
    un_out_service2 = un_out_service1.rename(columns={'故障源': '告警MO', '产生时间': '告警时间', '告警名称': '告警', '对象标识': '告警标准名'})
    un_out_service2['厂家'] = '大唐'
    un_out_service2['是否影响业务'] = '是'
    un_out_service2['是否为退服伴随告警'] = '否'
    un_out_service2['站号'] = [i[3:13] for i in un_out_service2['告警MO']]

    un_out_service2['归属'] = un_out_service2.apply(lamda x: x['告警MO'][16:18] if x['告警MO'][14:16]=='3M' else x['告警MO'][14:16],axis=1)

    un_out_service2['站名'] = un_out_service2['告警MO'].str.split(',', expand=True)[1]
    un_out_service3 = un_out_service2
    un_out_service3['区县'] = [area[i] for i in un_out_service3['归属']]
    un_out_service5 = un_out_service3[['区县', '厂家', '告警时间', '站号', '站名', '告警', '告警标准名', '告警MO', '是否影响业务', '是否为退服伴随告警']]
    un_out_service6 = un_out_service5[(~un_out_service3['告警'].isin(['小区退服', '基站退服']))]
    un_out_service7 = un_out_service6
    for i in un_out_service7['站号']:
        for j in out_service6['站号']:
            if i == j:
                un_out_service7['是否为退服伴随告警'][un_out_service7[un_out_service7['站号'].isin([i])].index] = '是'

    un_out_service7.to_excel(writer, sheet_name='非退服故障明细', index=False)
    writer.save()
    # okay decompiling 一键生成故障日报V11.pyc
if __name__=='__main__':
    try :
        main()
    except Exception:
        print(traceback.format_exc())
        rootDir = os.path.split(os.path.realpath(__file__))[0]
        blogpath = os.path.join(rootDir, 'error{}.txt'.format(date))
        f = open(blogpath, 'w+')
        f.writelines(str(traceback.format_exc()))
        f.close()