Use python's pandas, xlrd, and openpyxl libraries to merge specific sheets in excel.
# -*- coding: UTF-8 -*-
import xlrd
import pandas as pd
from pandas import DataFrame
from openpyxl import load_workbook
#Form Position
excel_name = ''
# Get all the tables in the workbook
wb = xlrd.open_workbook(excel_name)
#getsheets
sheets = wb.sheet_names()
# Loop through the required sheets
newdata = DataFrame()
Fill in the number of pages of the sheet to be merged after #in ().
for i in (3,4,5):
df = pd.read_excel(excel_name, sheet_name=(i-1), header = None,index_col=0,encoding='utf-8')
newdata = (df,ignore_index = False)
# Save as a new sheet, first of all, create a new sheet, merged data saved to the new sheet
writer = ('',engin='openpyxl')
book = load_workbook()
= book
# Use dataframe.to_excel to save the merged data to a new sheet, generate a new sheet named newdata
newdata.to_excel(excel_writer=writer,sheet_name="newdata")
()
()
print('Processing complete!')
included among these
df = pd.read_excel(excel_name, sheet_name=(i-1), header = None,index_col=0,encoding='utf-8')
You need to specify header = None, otherwise you will get the following warning:
FutureWarning: Sorting because non-concatenation axis is not aligned. A future version
of pandas will change to not sort by default.
And the columns in the new sheet generated will be out of order as well as Unnamed columns.
Addendum: Unnamed:0 solution when reading and writing csv file in pandas
When reading a csv file, a new column is automatically added by default, Unnamed:0
Solution:
When read_csv(), just set index_col=0.
When writing a csv file, a new column is automatically added by default, Unnamed:0
Solution:
When to_csv(), set index=False. or add index=True, index_label="id"
The above is a personal experience, I hope it can give you a reference, and I hope you can support me more. If there is any mistake or something that has not been fully considered, please do not hesitate to give me advice.