SoFunction
Updated on 2024-10-29

Example of merging excel files horizontally using Python

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:

Python横向合并excel文件

Multiple

Python横向合并excel文件

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:

Python横向合并excel文件

The above example of using Python to merge excel files horizontally is all that I have shared with you.