需求：一個文件夾下的txt文件谢揪，并計算每N（3）行數(shù)據(jù)為平均值蕉陋，結果需要轉換成excel，輸出到不同的列拨扶。
原始數(shù)據(jù)：（結構構成）
1321,214124,214124
32423,324525,235
2141,2154124,2132134
1321,214124,214124
32423,324525,235
2141,2154124,2132134
輸出結果：（結構構成）
| x | y | z |
|---|---|---|
| 1231 | 3213 | 4214 |
| 1231 | 3213 | 4214 |
rolnum=0
colnum=0
#閱讀txt到excel
def Txt_to_Excel(inputTxt, sheetName, start_row, start_col, outputExcel):
ws = excel.get_sheet(sheetName)
with open(inputTxt, "r") as file_txt:
lines = file_txt. readlines() #不是很大的文件都可以先讀取出來
# count = len(lines)
rolnum =len(lines)
rol=0
#獲取每行數(shù)據(jù)
for datasrol in lines:
# print(datarol)
# 列數(shù)歸0
col=0
datarol = datasrol. split(",")
# print(datarol)
row_excel = rol + start_row-1
rol += 1
#把數(shù)據(jù)按列插入
for data in datarol:
col_excel = col + start_col- 1
# print(data)
ws.write(row_excel, col_excel, data)
excel.save (outputExcel)
# print(col_excel)
col +=1
cotnum = col
print("行數(shù), " + str(rolnum) + "列數(shù) " + str(colnum))
return excel
#處理exceL,計算每列3組的平均值凳鬓，newSheet為空時代表輸入追加第一個sheet
def excelUnit(excelFile,newSheet,tabName):
#結果輸入到新的sheet文件
if newSheet != None and newSheet !='':
#創(chuàng)建表格
CreatExcel(sheetName=newSheet, tabName=tabName, outputExcel=excelFile)
ws = excel.get_sheet (newSheet)
col=0
Data_0 = []
i=0
for data in tabName:
col_excel = col + start_col -1
df = pd.DataFrame (pd. read_excel(excelFile))
print(data)
while i < len(df):
Data0 = df[data][i:i + group] # 每間隔3個數(shù)據(jù)取-次數(shù)
print(Data0)
Data1 = sum(Data0) / group # 求取每組數(shù)據(jù)的平均值
Data_0. append((format(Data1,'.6f')))
i=i+group
print(Data_0)
rol=0
for data0 in Data_0:
rol_excel = rol + start_row - 1
ws. write(rol_excel, col_excel, data0)
excel.save(excelFile)
rol += 1
col += 1
Data_0 = []
i=0
return
else:#追加到原來的sheet
Data_0 = []
i=0
count=len(tabName)
icount=0#用于合并增加列數(shù)
for data in tabName:
worksheet.excel.get_sheet(sheetname)
worksheet.write(i,icount+count,data+'平均數(shù)')
df = pd.DataFrame (pd. read_excel(excelFile))
print(data)
index=0
while i < len(df):
Data0 = df[data][i:i + group] # 每間隔3個數(shù)據(jù)取-次數(shù)
print(Data0)
Data1 = sum(Data0) / group # 求取每組數(shù)據(jù)的平均值
Data_0.append((format(Data1,'.6f')))
#合并單元格
worksheet.write_merge(i+1,i+group , count+icount, count+icount, Data_0[int(index)], style=Style.default_style)
i=i+group
index+=1
#while index<group:
#index+=1
#Data_0.append('')
print(Data_0)
excel.save(excelFile)
#df[data+"3組平均數(shù)"]=Data_0
#df.to_excel(excelFile,sheetName,index=False)
Data_0 = []
i=0
icount+=1
return
# 創(chuàng)建excel, sheetName
def CreatExcel(sheetName, tabName, outputExcel):
if sheetName == None and sheetName =='' :
sheetName =" 默認sheet"
ws = excel.add_sheet(sheetName)
col=0
for data in tabName:
col_excel =col + start_col - 1
ws.write(0, col_excel,data)
excel. save (outputExcel)
col+= 1
return excel
#閱讀文件夾下的所有txt文件
def readTxtDir(path):
findtxt = os.listdir(path) # 文件夾下的所有文件
txtsPath=[]
for file in findtxt:#遍歷文件夾
if os.path.splitext(file)[1] == '.txt':
# print(file)
txtsPath.append(file)
print(txtsPath)
return txtsPath
if __name__ =='__main__' :
#原始文件參數(shù)
outputExcel = '.xls' #輸入excel格式
inputfileDir = 'D://yuanhua/test/case' # 文件目錄
txts=readTxtDir(path=inputfileDir)
for txt in txts:
#創(chuàng)建文檔
excel = xlwt .Workbook(encoding='utf-8')
sheetName = "導航定位綜合誤差原始數(shù)據(jù)" # 需要寫入exceL中的Sheet中，可以自己
tabName=['x','y','z']
start_row = 2 # 從第2行開始寫
start_col = 1 # 從第1列開始寫
#轉換文件為excel
outputExcel ='.xls'
print(inputfileDir + "/" + txt)
print(txt.split('.')[0] + outputExcel)
outputExcel.txt.split('.')[0] + outputExcel
inputfile = inputfileDir + "/" + txt
excel = CreatExcel( sheetName=sheetName,tabName=tabName,outputExcel=outputExcel)
Txt_to_Excel(inputTxt =inputfile, sheetName=sheetName, start_row=start_row,start_col=start_col,outputExcel=outputExcel)
avgName = "平均值 "
group = 3 # n個數(shù)據(jù)組合為一組
# tabName = ['x', 'y', 'z']
excelUnit(excelFile=outputExcel, newSheet=avgName, tabName=tabName)
