Cause:
There is a batch of data that needs to be analyzed every month, the data is stored in excel with consistent row headings and needs to be merged horizontally for analysis.
Data schematic:
Multiple
Code:
# -*- coding: utf-8 -*- """ Created on Sun Nov 12 11:19:03 2017 @author: Li Ying """ # Read the first column as the first column of the merged table from pandas import read_csv df = read_csv(r'E:\excel\vb\',header=None) sample_name = df[0] file="combine" filedestination = "E://excel//" import glob #from numpy import * filearray=[] for filename in (r'E:\excel\*.xlsx'): (filename) #Above is to read all the excel tables from the excel folder and store all the names into the list filearray print("There are %d documents in the default folder."%len(filearray)) ge=len(filearray) matrix = [None]*ge # Realize read and write data # Below is reading data from all the files into the 3D list cell[][][] (without the table headers) import xlrd for i in range(ge): fname=filearray[i] bk=xlrd.open_workbook(fname) try: sh=bk.sheet_by_name("Sheet1") except: print ("In the file %s did not find sheet1, read the file data failed, or you change the name of the table?" %fname) ncols= matrix[i] = [0]*(ncols-1) nrows= for m in range(ncols-1): matrix[i][m] = ["0"]*nrows for k in range(1,ncols): for j in range(0,nrows): matrix[i][k-1][j]=(j,k).value import xlwt filename=() sheet=filename.add_sheet("hel") # Here's what to put on the first column for i in range(0,len(sample_name)): (i,0,sample_name[i]) # Sum the number of columns written in the previous file. zh=1 for i in range(ge): for j in range(len(matrix[i])): for k in range(len(matrix[i][j])): (k,zh,matrix[i][j][k]) zh=zh+1 print("I have merged %d files into 1 file and named it %."%(ge,file)) (filedestination+file+".xls")
Consolidated results:
The above example of using Python to merge excel files horizontally is all that I have shared with you.