用pandas读取csv文件，并跳过不规则的非数值行，计算df列的平均值
用pandas读取csv文件，并跳过不规则的非数值行，计算df列的平均值
最近在用pandas处理csv文件时，发现一个头大的问题：系统导出的csv文件表头之前有些不需要的行，这些行在每个csv文件中的数目都不一样
一、待处理的网元csv日志文件
目录：Lange_N41_RSRP0309
CDL-A CASE0.csv
查看代码
网元名称,BJIGNB01_turn
任务类型,性能监测-小区性能监测
保存时间,2022-03-09 11:01:23
网元版本,BTS5900 V100R017C00SPC100
时间,"NR DU小区标识","下行RLC总吞吐率(bps)","上行RLC总吞吐率(bps)","下行MAC总吞吐率(bps)","上行MAC总吞吐率(bps)"
03-09 11:01:21(949),"21","1101284672","964120","1219751160","1149152"
03-09 11:01:22(969),"21","1086584088","914360","1207563360","1206648"
03-09 11:01:23(949),"21","1093880872","924128","1216729816","1164952"
03-09 11:01:24(949),"21","1081807848","934496","1204736448","1252160"
03-09 11:01:25(969),"21","1054864328","904768","1167371600","1196128"
03-09 11:01:26(939),"21","998016480","976184","1112138088","1240240"
03-09 11:01:27(949),"21","978282432","910072","1096194072","1166848"
03-09 11:01:28(939),"21","976951624","841608","1077764224","1134752"
03-09 11:01:29(938),"21","1026227488","932736","1153665256","1188672"
03-09 11:01:30(939),"21","1022991936","967576","1141611488","1231488"
03-09 11:01:31(949),"21","1038911560","896408","1150961320","1179952"
03-09 11:01:32(969),"21","1078508792","902184","1205576336","1201392"
03-09 11:01:33(966),"21","1056336608","923544","1196652776","1211680"
03-09 11:01:34(966),"21","1067465240","1009912","1166485264","1281136"
03-09 11:01:35(949),"21","1096801368","943936","1210285464","1221208"
03-09 11:01:36(959),"21","1092690616","926336","1218678328","1203920"
03-09 11:01:37(959),"21","1070899552","907096","1195140520","1192960"
03-09 11:01:38(969),"21","1071070040","928384","1185417888","1202424"
03-09 11:01:39(949),"21","1073769536","939680","1188301792","1211008"
03-09 11:01:40(949),"21","1024114560","920208","1142975656","1174312"
03-09 11:01:41(969),"21","978075368","913864","1096556272","1192792"
03-09 11:01:42(936),"21","959354232","909072","1068592088","1172376"
03-09 11:01:43(946),"21","959739176","926232","1061650296","1177160"
03-09 11:01:44(949),"21","942416528","874848","1071083096","1135632"
03-09 11:01:45(938),"21","879348336","887456","974437352","1121584"CDL-A CASE1.csv
查看代码
网元名称,BJIGNB01_turn
任务类型,性能监测-小区性能监测
保存时间,2022-03-09 11:06:29
网元版本,BTS5900 V100R017C00SPC100
时间,"NR DU小区标识","下行RLC总吞吐率(bps)","上行RLC总吞吐率(bps)","下行MAC总吞吐率(bps)","上行MAC总吞吐率(bps)"
03-09 11:06:27(809),"21","981060560","908616","1079772688","1136864"
03-09 11:06:28(819),"21","932033064","819496","1049692472","1045056"
03-09 11:06:29(819),"21","962823792","873304","1063677832","1132464"
03-09 11:06:30(809),"21","886680008","812520","1013285632","1050576"
03-09 11:06:31(819),"21","855878800","833944","933134928","1059560"
03-09 11:06:32(819),"21","816497272","802008","913195232","1010784"
03-09 11:06:33(819),"21","795354096","777832","903648576","1026104"
03-09 11:06:34(819),"21","772119552","748088","856641752","969504"
03-09 11:06:35(819),"21","839838336","837680","925944616","1073552"
03-09 11:06:36(819),"21","815036512","837304","908815272","1048560"
03-09 11:06:37(809),"21","833592072","830112","941180296","1044376"
03-09 11:06:38(819),"21","853660648","833144","956211744","1068448"
03-09 11:06:39(819),"21","916166416","890352","1018323040","1120256"
03-09 11:06:40(812),"21","928645696","885520","1026680024","1116496"
03-09 11:06:41(819),"21","925439600","872896","1053247968","1111680"
03-09 11:06:42(812),"21","924442248","850600","1019607888","1065920"
03-09 11:06:43(819),"21","906180840","842584","1018850064","1073912"
03-09 11:06:44(809),"21","862878704","824120","962715184","1081272"
03-09 11:06:45(819),"21","848806944","806152","958079024","1043312"
03-09 11:06:46(819),"21","865431320","836368","958960048","1064536"
03-09 11:06:47(819),"21","774865912","807168","859213512","1018528"
03-09 11:06:48(812),"21","772208104","786032","865572880","1056000"
03-09 11:06:49(819),"21","747627040","752728","826733816","991536"
03-09 11:06:50(819),"21","705564048","753536","810258056","1038560"
03-09 11:06:51(819),"21","638562024","628944","706197928","888480"CDL-A CASE2.csv
查看代码
网元名称,BJIGNB01_turn
任务类型,性能监测-小区性能监测
保存时间,2022-03-09 11:12:01
网元版本,BTS5900 V100R017C00SPC100
时间,"NR DU小区标识","下行RLC总吞吐率(bps)","上行RLC总吞吐率(bps)","下行MAC总吞吐率(bps)","上行MAC总吞吐率(bps)"
03-09 11:11:59(749),"21","826078344","752528","919272552","1292928"
03-09 11:12:00(749),"21","830771760","786520","921620384","1183120"
03-09 11:12:01(738),"21","831738832","785480","921439232","1082264"
03-09 11:12:02(749),"21","829533584","804128","923510448","1201904"
03-09 11:12:03(749),"21","831507096","882240","923974544","1641488"
03-09 11:12:04(749),"21","840005952","801600","913428664","1176336"
03-09 11:12:05(749),"21","847090056","812960","960586440","1100936"
03-09 11:12:06(739),"21","863042952","828912","937121064","1044920"
03-09 11:12:07(739),"21","778092232","769240","884660552","1095760"
03-09 11:12:08(749),"21","705707608","743896","780663880","964040"
03-09 11:12:09(749),"21","672248768","717496","763322352","2589184"
03-09 11:12:10(749),"21","742959008","768824","816017400","1485592"
03-09 11:12:11(739),"21","656482016","696152","736170152","913008"
03-09 11:12:12(749),"21","590267216","638448","655272520","974864"
03-09 11:12:13(739),"21","603054768","679592","669755552","963856"
03-09 11:12:14(749),"21","593143824","691696","669226008","1034192"
03-09 11:12:15(749),"21","603317736","718776","663188104","1119448"
03-09 11:12:16(749),"21","645492344","745336","721283024","968312"
03-09 11:12:17(739),"21","641637032","730096","715448408","1174400"
03-09 11:12:18(749),"21","668127672","753872","747467776","986136"
03-09 11:12:19(739),"21","668078256","759984","746488280","1134728"
03-09 11:12:20(739),"21","706838144","787608","785630704","1296032"
03-09 11:12:21(739),"21","707933672","804208","780098128","1060416"
03-09 11:12:22(749),"21","725464304","789920","812648160","1127408"
03-09 11:12:23(739),"21","724295832","776480","796659008","1075416"二、跳过csv前面不规则的几行
read_csv_pandas_avg.py
# -*- coding: UTF-8 -*-
# 自动读取“网页版csv日志格式”脚本
import os
import pandas as pd
# 给定待读取的csv文件的路径到列表里
csv_filepath = r"D:\\myproject\\read_csv_calculate_avg\\report\\Lange_N41_RSRP0309\\CDL-A CASE0.csv"
# df1 = df.iloc[:, 0:4] #读取第1列到第4列数据
###1.跳过每个csv文件开头不需要的行的函数
def skip_to(fle,**kwargs):
if os.stat(fle).st_size == 0:
raise ValueError("File is empty")
with open(fle) as f:
pos = 0
cur_line = f.readline()
while not cur_line.find('下行MAC总吞吐率(bps)","上行MAC总吞吐率(bps)')>=0:
pos = f.tell()
cur_line = f.readline()
f.seek(pos)
return pd.read_csv(f, **kwargs)
# 2.读取csv文件到内存
df = skip_to(csv_filepath,encoding = 'gbk') # 读取csv用模块skip_to()
df = df["下行MAC总吞吐率(bps)"]
avg = df.mean() #求一列的平均值
print(df)
print(avg)
三、项目名称
read_csv_calculate_avg
pyqt5的ui文件
查看代码
<?xml version="1.0" encoding="UTF-8"?>
<ui version="4.0">
<class>MainWindowAresRT</class>
<widget class="QMainWindow" name="MainWindowAresRT">
<property name="geometry">
<rect>
<x>0</x>
<y>0</y>
<width>640</width>
<height>480</height>
</rect>
</property>
<property name="minimumSize">
<size>
<width>640</width>
<height>480</height>
</size>
</property>
<property name="maximumSize">
<size>
<width>1920</width>
<height>1080</height>
</size>
</property>
<property name="windowTitle">
<string>读取web网元日志统计平均值工具（作者：康冕峰）</string>
</property>
<widget class="QWidget" name="centralwidget">
<widget class="QTextEdit" name="textEdit_logimport_directory">
<property name="geometry">
<rect>
<x>180</x>
<y>30</y>
<width>270</width>
<height>60</height>
</rect>
</property>
<property name="minimumSize">
<size>
<width>270</width>
<height>60</height>
</size>
</property>
<property name="font">
<font>
<pointsize>10</pointsize>
</font>
</property>
</widget>
<widget class="QPushButton" name="pushButton_openfile">
<property name="geometry">
<rect>
<x>520</x>
<y>30</y>
<width>91</width>
<height>71</height>
</rect>
</property>
<property name="font">
<font>
<pointsize>12</pointsize>
</font>
</property>
<property name="text">
<string>打开目录</string>
</property>
</widget>
<widget class="QLabel" name="label_input">
<property name="geometry">
<rect>
<x>40</x>
<y>20</y>
<width>111</width>
<height>51</height>
</rect>
</property>
<property name="font">
<font>
<family>Arial</family>
<pointsize>12</pointsize>
</font>
</property>
<property name="text">
<string>处理报告目录：</string>
</property>
</widget>
<widget class="QLabel" name="label_output">
<property name="geometry">
<rect>
<x>40</x>
<y>320</y>
<width>111</width>
<height>51</height>
</rect>
</property>
<property name="font">
<font>
<pointsize>12</pointsize>
</font>
</property>
<property name="layoutDirection">
<enum>Qt::LeftToRight</enum>
</property>
<property name="text">
<string>正在执行命令：</string>
</property>
</widget>
<widget class="QLabel" name="label_bottom">
<property name="geometry">
<rect>
<x>160</x>
<y>330</y>
<width>421</width>
<height>41</height>
</rect>
</property>
<property name="font">
<font>
<pointsize>12</pointsize>
</font>
</property>
<property name="autoFillBackground">
<bool>true</bool>
</property>
<property name="frameShadow">
<enum>QFrame::Plain</enum>
</property>
<property name="lineWidth">
<number>1</number>
</property>
<property name="text">
<string>1）点击文件选择导入的目录 2）点击处理输出到Excel文件</string>
</property>
</widget>
<widget class="QWidget" name="verticalLayoutWidget">
<property name="geometry">
<rect>
<x>180</x>
<y>160</y>
<width>271</width>
<height>121</height>
</rect>
</property>
<layout class="QVBoxLayout" name="verticalLayout_1">
<property name="sizeConstraint">
<enum>QLayout::SetDefaultConstraint</enum>
</property>
<property name="leftMargin">
<number>10</number>
</property>
<item>
<widget class="QCheckBox" name="checkBox_download">
<property name="font">
<font>
<pointsize>11</pointsize>
</font>
</property>
<property name="text">
<string>下行MAC总吞吐率(bps)</string>
</property>
<property name="checkable">
<bool>true</bool>
</property>
<property name="checked">
<bool>false</bool>
</property>
</widget>
</item>
<item>
<widget class="QCheckBox" name="checkBox_upload">
<property name="font">
<font>
<pointsize>11</pointsize>
</font>
</property>
<property name="text">
<string>上行MAC总吞吐率(bps)</string>
</property>
<property name="checkable">
<bool>true</bool>
</property>
<property name="checked">
<bool>false</bool>
</property>
</widget>
</item>
<item>
<widget class="QCheckBox" name="checkBox_downMA">
<property name="font">
<font>
<pointsize>11</pointsize>
</font>
</property>
<property name="text">
<string>下行MAC总吞吐率(Mbps)</string>
</property>
<property name="checkable">
<bool>true</bool>
</property>
</widget>
</item>
</layout>
</widget>
<widget class="QPushButton" name="pushButton_dispose">
<property name="geometry">
<rect>
<x>520</x>
<y>180</y>
<width>91</width>
<height>71</height>
</rect>
</property>
<property name="font">
<font>
<pointsize>12</pointsize>
</font>
</property>
<property name="text">
<string>处理输出</string>
</property>
</widget>
<widget class="QLabel" name="label_selected_filepath">
<property name="geometry">
<rect>
<x>190</x>
<y>40</y>
<width>281</width>
<height>31</height>
</rect>
</property>
<property name="font">
<font>
<pointsize>12</pointsize>
</font>
</property>
<property name="text">
<string>TextLabel</string>
</property>
</widget>
</widget>
<widget class="QMenuBar" name="menubar">
<property name="geometry">
<rect>
<x>0</x>
<y>0</y>
<width>640</width>
<height>23</height>
</rect>
</property>
<widget class="QMenu" name="menu_file">
<property name="title">
<string>文件</string>
</property>
<widget class="QMenu" name="menurecently_import">
<property name="title">
<string>recently_import</string>
</property>
<addaction name="action_first_dir"/>
<addaction name="action_second_dir"/>
<addaction name="action_third_dir"/>
</widget>
<addaction name="action_directory_import"/>
<addaction name="menurecently_import"/>
<addaction name="separator"/>
</widget>
<widget class="QMenu" name="menu_help">
<property name="title">
<string>帮助</string>
</property>
<addaction name="action_about_help"/>
</widget>
<widget class="QMenu" name="menu_process">
<property name="title">
<string>处理</string>
</property>
<addaction name="action_read_start"/>
</widget>
<widget class="QMenu" name="menu_reset">
<property name="title">
<string>重置</string>
</property>
<addaction name="action_test_reset"/>
</widget>
<addaction name="menu_file"/>
<addaction name="menu_process"/>
<addaction name="menu_reset"/>
<addaction name="menu_help"/>
</widget>
<widget class="QStatusBar" name="statusbar"/>
<action name="action_directory_import">
<property name="text">
<string>directory_import</string>
</property>
<property name="font">
<font>
<family>Arial</family>
<pointsize>10</pointsize>
</font>
</property>
</action>
<action name="action_read_start">
<property name="text">
<string>read_output</string>
</property>
<property name="font">
<font>
<family>Arial</family>
<pointsize>10</pointsize>
</font>
</property>
</action>
<action name="action_about_help">
<property name="text">
<string>about_help</string>
</property>
</action>
<action name="action_first_dir">
<property name="text">
<string>first_dir</string>
</property>
</action>
<action name="action_second_dir">
<property name="text">
<string>second_dir</string>
</property>
</action>
<action name="action_third_dir">
<property name="text">
<string>third_dir</string>
</property>
</action>
<action name="action_test_reset">
<property name="text">
<string>tset_reset</string>
</property>
</action>
</widget>
<resources/>
<connections>
<connection>
<sender>pushButton_dispose</sender>
<signal>clicked()</signal>
<receiver>textEdit_logimport_directory</receiver>
<slot>hide()</slot>
<hints>
<hint type="sourcelabel">
<x>565</x>
<y>238</y>
</hint>
<hint type="destinationlabel">
<x>314</x>
<y>82</y>
</hint>
</hints>
</connection>
<connection>
<sender>checkBox_upload</sender>
<signal>clicked(bool)</signal>
<receiver>label_selected_filepath</receiver>
<slot>hide()</slot>
<hints>
<hint type="sourcelabel">
<x>319</x>
<y>242</y>
</hint>
<hint type="destinationlabel">
<x>330</x>
<y>78</y>
</hint>
</hints>
</connection>
<connection>
<sender>checkBox_download</sender>
<signal>clicked(bool)</signal>
<receiver>label_selected_filepath</receiver>
<slot>hide()</slot>
<hints>
<hint type="sourcelabel">
<x>319</x>
<y>205</y>
</hint>
<hint type="destinationlabel">
<x>330</x>
<y>78</y>
</hint>
</hints>
</connection>
<connection>
<sender>checkBox_downMA</sender>
<signal>clicked(bool)</signal>
<receiver>label_selected_filepath</receiver>
<slot>hide()</slot>
<hints>
<hint type="sourcelabel">
<x>319</x>
<y>279</y>
</hint>
<hint type="destinationlabel">
<x>330</x>
<y>78</y>
</hint>
</hints>
</connection>
</connections>
</ui>
pyuic5 -o ReadCsvStringOutputAvg.py ReadCsvStringOutputAvg.ui
ReadCsvStringOutputAvg.py
查看代码
# -*- coding: utf-8 -*-
# Form implementation generated from reading ui file 'ReadCsvStringOutputAvg.ui'
#
# Created by: PyQt5 UI code generator 5.15.4
#
# WARNING: Any manual changes made to this file will be lost when pyuic5 is
# run again. Do not edit this file unless you know what you are doing.
from __future__ import division
from PyQt5 import QtCore, QtGui, QtWidgets
import os
import pandas as pd
# 全局变量
filePath_parent="空"
filePath="空"
class Ui_MainWindowAresRT(object):
cls_fileName ="空"
log_dir_list = [] # 测试人员重命名包含有大量.csv的日志文件，的目录名称组成的列表
csvlog_absfilepath = [] #待处理的所有.csv文件，构成的列表
def setupUi(self, MainWindowAresRT):
MainWindowAresRT.setObjectName("MainWindowAresRT")
MainWindowAresRT.resize(640, 480)
MainWindowAresRT.setMinimumSize(QtCore.QSize(640, 480))
MainWindowAresRT.setMaximumSize(QtCore.QSize(1920, 1080))
self.centralwidget = QtWidgets.QWidget(MainWindowAresRT)
self.centralwidget.setObjectName("centralwidget")
self.textEdit_logimport_directory = QtWidgets.QTextEdit(self.centralwidget)
self.textEdit_logimport_directory.setGeometry(QtCore.QRect(180, 30, 270, 60))
self.textEdit_logimport_directory.setMinimumSize(QtCore.QSize(270, 60))
font = QtGui.QFont()
font.setPointSize(10)
self.textEdit_logimport_directory.setFont(font)
self.textEdit_logimport_directory.setObjectName("textEdit_logimport_directory")
self.pushButton_openfile = QtWidgets.QPushButton(self.centralwidget)
self.pushButton_openfile.setGeometry(QtCore.QRect(520, 30, 91, 71))
font = QtGui.QFont()
font.setPointSize(12)
self.pushButton_openfile.setFont(font)
self.pushButton_openfile.setObjectName("pushButton_openfile")
self.label_input = QtWidgets.QLabel(self.centralwidget)
self.label_input.setGeometry(QtCore.QRect(40, 20, 111, 51))
font = QtGui.QFont()
font.setFamily("Arial")
font.setPointSize(12)
self.label_input.setFont(font)
self.label_input.setObjectName("label_input")
self.label_output = QtWidgets.QLabel(self.centralwidget)
self.label_output.setGeometry(QtCore.QRect(40, 320, 111, 51))
font = QtGui.QFont()
font.setPointSize(12)
self.label_output.setFont(font)
self.label_output.setLayoutDirection(QtCore.Qt.LeftToRight)
self.label_output.setObjectName("label_output")
self.label_bottom = QtWidgets.QLabel(self.centralwidget)
self.label_bottom.setGeometry(QtCore.QRect(160, 330, 421, 41))
font = QtGui.QFont()
font.setPointSize(12)
self.label_bottom.setFont(font)
self.label_bottom.setAutoFillBackground(True)
self.label_bottom.setFrameShadow(QtWidgets.QFrame.Plain)
self.label_bottom.setLineWidth(1)
self.label_bottom.setObjectName("label_bottom")
self.verticalLayoutWidget = QtWidgets.QWidget(self.centralwidget)
self.verticalLayoutWidget.setGeometry(QtCore.QRect(180, 160, 271, 121))
self.verticalLayoutWidget.setObjectName("verticalLayoutWidget")
self.verticalLayout_1 = QtWidgets.QVBoxLayout(self.verticalLayoutWidget)
self.verticalLayout_1.setSizeConstraint(QtWidgets.QLayout.SetDefaultConstraint)
self.verticalLayout_1.setContentsMargins(10, 0, 0, 0)
self.verticalLayout_1.setObjectName("verticalLayout_1")
self.checkBox_download = QtWidgets.QCheckBox(self.verticalLayoutWidget)
font = QtGui.QFont()
font.setPointSize(11)
self.checkBox_download.setFont(font)
self.checkBox_download.setCheckable(True)
self.checkBox_download.setChecked(False)
self.checkBox_download.setObjectName("checkBox_download")
self.verticalLayout_1.addWidget(self.checkBox_download)
self.checkBox_upload = QtWidgets.QCheckBox(self.verticalLayoutWidget)
font = QtGui.QFont()
font.setPointSize(11)
self.checkBox_upload.setFont(font)
self.checkBox_upload.setCheckable(True)
self.checkBox_upload.setChecked(False)
self.checkBox_upload.setObjectName("checkBox_upload")
self.verticalLayout_1.addWidget(self.checkBox_upload)
self.checkBox_downMA = QtWidgets.QCheckBox(self.verticalLayoutWidget)
font = QtGui.QFont()
font.setPointSize(11)
self.checkBox_downMA.setFont(font)
self.checkBox_downMA.setCheckable(True)
self.checkBox_downMA.setObjectName("checkBox_downMA")
self.verticalLayout_1.addWidget(self.checkBox_downMA)
self.pushButton_dispose = QtWidgets.QPushButton(self.centralwidget)
self.pushButton_dispose.setGeometry(QtCore.QRect(520, 180, 91, 71))
font = QtGui.QFont()
font.setPointSize(12)
self.pushButton_dispose.setFont(font)
self.pushButton_dispose.setObjectName("pushButton_dispose")
self.label_selected_filepath = QtWidgets.QLabel(self.centralwidget)
self.label_selected_filepath.setGeometry(QtCore.QRect(190, 40, 281, 31))
font = QtGui.QFont()
font.setPointSize(12)
self.label_selected_filepath.setFont(font)
self.label_selected_filepath.setObjectName("label_selected_filepath")
MainWindowAresRT.setCentralWidget(self.centralwidget)
self.menubar = QtWidgets.QMenuBar(MainWindowAresRT)
self.menubar.setGeometry(QtCore.QRect(0, 0, 640, 23))
self.menubar.setObjectName("menubar")
self.menu_file = QtWidgets.QMenu(self.menubar)
self.menu_file.setObjectName("menu_file")
self.menurecently_import = QtWidgets.QMenu(self.menu_file)
self.menurecently_import.setObjectName("menurecently_import")
self.menu_help = QtWidgets.QMenu(self.menubar)
self.menu_help.setObjectName("menu_help")
self.menu_process = QtWidgets.QMenu(self.menubar)
self.menu_process.setObjectName("menu_process")
self.menu_reset = QtWidgets.QMenu(self.menubar)
self.menu_reset.setObjectName("menu_reset")
MainWindowAresRT.setMenuBar(self.menubar)
self.statusbar = QtWidgets.QStatusBar(MainWindowAresRT)
self.statusbar.setObjectName("statusbar")
MainWindowAresRT.setStatusBar(self.statusbar)
self.action_directory_import = QtWidgets.QAction(MainWindowAresRT)
font = QtGui.QFont()
font.setFamily("Arial")
font.setPointSize(10)
self.action_directory_import.setFont(font)
self.action_directory_import.setObjectName("action_directory_import")
self.action_read_start = QtWidgets.QAction(MainWindowAresRT)
font = QtGui.QFont()
font.setFamily("Arial")
font.setPointSize(10)
self.action_read_start.setFont(font)
self.action_read_start.setObjectName("action_read_start")
self.action_about_help = QtWidgets.QAction(MainWindowAresRT)
self.action_about_help.setObjectName("action_about_help")
self.action_first_dir = QtWidgets.QAction(MainWindowAresRT)
self.action_first_dir.setObjectName("action_first_dir")
self.action_second_dir = QtWidgets.QAction(MainWindowAresRT)
self.action_second_dir.setObjectName("action_second_dir")
self.action_third_dir = QtWidgets.QAction(MainWindowAresRT)
self.action_third_dir.setObjectName("action_third_dir")
self.action_test_reset = QtWidgets.QAction(MainWindowAresRT)
self.action_test_reset.setObjectName("action_test_reset")
self.menurecently_import.addAction(self.action_first_dir)
self.menurecently_import.addAction(self.action_second_dir)
self.menurecently_import.addAction(self.action_third_dir)
self.menu_file.addAction(self.action_directory_import)
self.menu_file.addAction(self.menurecently_import.menuAction())
self.menu_file.addSeparator()
self.menu_help.addAction(self.action_about_help)
self.menu_process.addAction(self.action_read_start)
self.menu_reset.addAction(self.action_test_reset)
self.menubar.addAction(self.menu_file.menuAction())
self.menubar.addAction(self.menu_process.menuAction())
self.menubar.addAction(self.menu_reset.menuAction())
self.menubar.addAction(self.menu_help.menuAction())
self.retranslateUi(MainWindowAresRT)
self.pushButton_dispose.clicked.connect(self.textEdit_logimport_directory.hide)
self.checkBox_upload.clicked['bool'].connect(self.label_selected_filepath.hide)
self.checkBox_download.clicked['bool'].connect(self.label_selected_filepath.hide)
self.checkBox_downMA.clicked['bool'].connect(self.label_selected_filepath.hide)
## 用户自定义手动填写代码部分
self.pushButton_openfile.clicked.connect(self.open_file_dialogbox)
self.pushButton_dispose.clicked.connect(self.read_and_output)
QtCore.QMetaObject.connectSlotsByName(MainWindowAresRT)
def retranslateUi(self, MainWindowAresRT):
_translate = QtCore.QCoreApplication.translate
MainWindowAresRT.setWindowTitle(_translate("MainWindowAresRT", "读取web网元日志统计平均值工具（作者：康冕峰）"))
self.pushButton_openfile.setText(_translate("MainWindowAresRT", "打开目录"))
self.label_input.setText(_translate("MainWindowAresRT", "处理报告目录："))
self.label_output.setText(_translate("MainWindowAresRT", "正在执行命令："))
self.label_bottom.setText(_translate("MainWindowAresRT", "1）点击文件选择导入的目录 2）点击处理输出到Excel文件"))
self.checkBox_download.setText(_translate("MainWindowAresRT", "下行MAC总吞吐率(bps)"))
self.checkBox_upload.setText(_translate("MainWindowAresRT", "上行MAC总吞吐率(bps)"))
self.checkBox_downMA.setText(_translate("MainWindowAresRT", "下行MAC总吞吐率(Mbps)"))
self.pushButton_dispose.setText(_translate("MainWindowAresRT", "处理输出"))
self.label_selected_filepath.setText(_translate("MainWindowAresRT", "TextLabel"))
self.menu_file.setTitle(_translate("MainWindowAresRT", "文件"))
self.menurecently_import.setTitle(_translate("MainWindowAresRT", "recently_import"))
self.menu_help.setTitle(_translate("MainWindowAresRT", "帮助"))
self.menu_process.setTitle(_translate("MainWindowAresRT", "处理"))
self.menu_reset.setTitle(_translate("MainWindowAresRT", "重置"))
self.action_directory_import.setText(_translate("MainWindowAresRT", "directory_import"))
self.action_read_start.setText(_translate("MainWindowAresRT", "read_output"))
self.action_about_help.setText(_translate("MainWindowAresRT", "about_help"))
self.action_first_dir.setText(_translate("MainWindowAresRT", "first_dir"))
self.action_second_dir.setText(_translate("MainWindowAresRT", "second_dir"))
self.action_third_dir.setText(_translate("MainWindowAresRT", "third_dir"))
self.action_test_reset.setText(_translate("MainWindowAresRT", "tset_reset"))
## 用户自定义手动填写代码部分
# 打开文件目录选择对话窗口
def open_file_dialogbox(self):
fileName, fileType = QtWidgets.QFileDialog.getOpenFileName(self, "请选取目录下任意的一个文件作为路径使用", "D:\\", "All Files(*);;CSV Files(*.csv)")
'''
参数一：设置GUI父组件
参数二：QFileDialog的标题
参数三：默认打开的目录，“.”点表示程序运行目录，/表示当前盘符根目录
参数四：对话框的文件扩展名过滤器Filter，比如使用 Image files(*.jpg *.gif) 表示只能显示扩展名为.jpg或者.gif文件
设置多个文件扩展名过滤，使用双引号隔开；
“All Files(*);;PDF Files(*.pdf);;Text Files(*.txt)”
选取文件夹 QFileDialog.getExistingDirectory()
'''
# 1.获取当前导入的目录
# 是以 "PATH" 中最后一个 '/' 作为分隔符，分隔后，将索引为0的视为目录（路径），将索引为1的视为文件名
global filePath_parent
filePath_parent,_ = os.path.split(fileName)
global filePath
filePath = fileName
# 再把变量赋值给类的静态变量
self.cls_fileName = fileName
# 2.从给定目录开始条件遍历
if filePath_parent == "D:/":
print("你选的路径是 D:\\ 可能会使得电脑卡死！ 请重新选择。")
elif filePath_parent == "C:/":
print("你选的路径是 C:\\ 可能会使得电脑卡死！ 请重新选择。")
elif (filePath_parent == "空") or (filePath_parent == ""):
print("你还没有通过点击“文件”选择待导入处理的文件目录！")
else:
# 3.选择完文件路径后的处理
print("已选择处理文件："+ "\t" + self.cls_fileName) # 调试用的会在黑窗口打印
log_dir_list1 = os.listdir(filePath_parent) #返回一个列表，包含目录和文件
abs_path_list1 = abspath_preprocess(filePath_parent, log_dir_list1) #1代表需要处理剔除文件的列表
abs_path_list2 = listdir_post_process(abs_path_list1) #2代表处理后的只含有目录的绝对路径列表
self.log_dir_list = abs_path_list2
# print(self.log_dir_list)
# self.csvlog_absfilepat = output_csvlog_absfilepath(self.log_dir_list)
# 4.设定方法的返回
return "-----执行 open_file_dialogbox 方法-----"
def read_and_output(self):
abs_csv_fileName_list = output_csvlog_absfilepath(self.log_dir_list)
# 分类调用处理函数
# print(abs_csv_fileName_list)
my_dict = jisuan_download_Mbps(abs_csv_fileName_list)
# 调用输出文件的函数
write_dict_into_excel(my_dict)
return
# 定义一个方法用于拼接字符串，返回给定文件列表里的所有文件的绝对目录
def abspath_preprocess( str_path="", file_list=[] ):
result_list = []
for i in file_list:
s = str_path +"/" + i # 字符串拼接
result_list.append(s)
return result_list
# 定义一个方法：输入一个路径的列表，只保留目录，剔除文件
def listdir_post_process( dir_list=[] ):
result_list = []
for i in dir_list:
if os.path.isdir(i):
result_list.append(i)
elif os.path.isfile(i):
print("------ 程序正在处理文件中 ------")
else:
pass
# 返回结果只含有目录的列表
return result_list
# 定义一个方法：根据csv文件所在目录的列表，获取所有csv文件的绝对路径
def output_csvlog_absfilepath( log_dir_list=[] ):
csvlog_absfilepath = [] #创建1个空列表做容器
for f in log_dir_list: #遍历包含csv日志的目录绝对路径构成的列表
fileNames = os.listdir(f)
# print(fileNames)
num = len(fileNames) #获取列表元素个数
for i in range(num): #遍历单个的日志目录下的文件、目录
kang = f+'/'+fileNames[i]
# print(kang)
if os.path.isfile(kang): #判断是文件
if kang.endswith(".csv"): #判断是csv文件
csvlog_absfilepath.append(kang) #添加到收集容器里
# print(csvlog_absfilepath) #打印输出显示所有待处理的CSV文件
print("-----过滤待处理.csv文件已完成-----")
return csvlog_absfilepath
## 定义一个方法，输入1个网元csv文件的列表，输出结果到1个Excel文档里
###1.跳过每个csv文件开头不需要的行的函数
def skip_to(fle,**kwargs):
if os.stat(fle).st_size == 0:
raise ValueError("File is empty")
with open(fle) as f:
pos = 0
cur_line = f.readline()
while not cur_line.find('下行MAC总吞吐率(bps)","上行MAC总吞吐率(bps)')>=0:
pos = f.tell()
cur_line = f.readline()
f.seek(pos)
return pd.read_csv(f, **kwargs)
# 计算输出 下行Mbps 平均值到Excel文档
# 输入一个csv文件的绝对路径的列表
def jisuan_download_Mbps(csvlog_absfilepath):
mydict = {}
if len(csvlog_absfilepath) == 0:
print("你还没有通过点击“文件”选择待导入处理的文件目录！")
# 2.读取csv文件到内存
elif len(csvlog_absfilepath) >= 1:
num = len(csvlog_absfilepath)
for j in range(num): #逐项遍历
df1 = skip_to(csvlog_absfilepath[j],encoding = 'gbk') # 读取csv用模块skip_to()
df2 = df1["下行MAC总吞吐率(bps)"]
avg_result_bps = df2.mean() #单位是bps
avg_result_Mbps = avg_result_bps/(1024*1024) #单位是Mbps
mydict.setdefault(csvlog_absfilepath[j],avg_result_Mbps)
return mydict #把结果放在字典里返回
# 定义一个方法，将字典数据结构输出到Excel文件里
def write_dict_into_excel(var = "dict"):
# 先构造1个列表
mylist_filename = []
mylist_throughput = []
for k,v in var.items():
mylist_filename.append(k)
mylist_throughput.append(v)
df1 = pd.DataFrame(zip(mylist_filename,mylist_throughput),columns=["导入CSV报告文件名的绝对路径","Avg 下行MAC总吞吐率(Mbps)"])
excel_filename = os.getcwd()+"/"+"读取CSV报告输出下行MAC总吞吐率Mbps.xlsx"
df1.to_excel(excel_writer = excel_filename )
print("已输出Excel文件：{}".format('\t') + excel_filename )
return excel_filenameCallReadCsvStringOutput.py
查看代码
# -*- coding: UTF-8 -*-
"""
【简介】
更新输出视图层文件的命令行
pyuic5 -o ReadPdfStringOutput.py ReadPdfStringOutput.ui
pyuic5 -o dialog_output_prompt.py dialog_output_prompt.ui
更新前务必要先备份，因为业务逻辑写在上述2个.py文件里
"""
import sys
from PyQt5.QtWidgets import QApplication, QDialog, QMainWindow, QWidget
from ReadCsvStringOutputAvg import Ui_MainWindowAresRT
# 实例化程序的主界面
class TurnplateWindowShow(QMainWindow, Ui_MainWindowAresRT): # Python多继承
def __init__(self, parent=None):
super( TurnplateWindowShow, self).__init__(parent)
# self.setupUi(self) # 调用写在视图层文件的页面布局方法
self.initUI()
def initUI(self):
# 子类调用继承父类的方法
super( TurnplateWindowShow, self).setupUi(self) # 调用的父类方法 super().方法名()
# 实例化输出结果文件的对话框
# class OutputDialogShow(QDialog, Ui_Dialog): # Python多继承
# def __init__(self, parent=None):
# super( OutputDialogShow, self).__init__(parent)
# # self.setupUi(self) # 调用写在视图层文件的页面布局函数
# self.initUI()
# def initUI(self):
# # 子类调用继承父类的方法
# super(OutputDialogShow, self).setupUi(self) # 等价于参考书里的self.setupUi()
# # 在业务层文件里定义主界面调用Dialog窗体函数
# def show_d1():
# d1.show() # OutputDialogShow的实例化对象d1
###### 调试程序的主入口
if __name__ == "__main__":
app = QApplication(sys.argv)
w1 = TurnplateWindowShow()
w1.show() #主窗口
# 调用界面d1对象的槽函数
# w1.action_timer_setup.triggered.connect(show_d1) #槽函数只填写函数名不能带()
sys.exit(app.exec_())参考资料：
除法总是会返回真实的商，不管操作数是整形还是浮点型
部分内容来自于学习编程期间收集于网络的免费分享资源和工作后购买的付费内容。
如需获取教程配套的资源文件和一对一专属答疑支持，请加vx:kangmf24联系作者。
