python pandas merges Sheet to deal with out of order columns and solve the problem of Unnamed columns

Use the pandas, xlrd and openpyxl libraries in python to merge the specified sheet in excel

# -*- coding: UTF-8 -*- 
import xlrd
import pandas as pd
from pandas import DataFrame
from openpyxl import load_workbook

# Table position 
excel_name = '1.xlsx'
#  Get workbook All the tables in the 
wb = xlrd.open_workbook(excel_name)
# Get sheets
sheets = wb.sheet_names()

#  Required for circulation sheet
newdata = DataFrame()
#in After (), fill in the ones to be merged sheet Number of pages 
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 = newdata.append(df,ignore_index = False)
# Save as new sheet, New first sheet, The merged data is saved to the new sheet Medium 
writer = pd.ExcelWriter('1.xlsx',engin='openpyxl')
book = load_workbook(writer.path)
writer.book = book
# Utilization dataframe.to_excel Save the merged data to the new sheet, Generate a new sheet Named newdata
newdata.to_excel(excel_writer=writer,sheet_name="newdata")
writer.save()
writer.close()
print(' Processing complete! ')

Among them

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 the following warning will appear:

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 generated new sheet will be out of order and Unnamed columns.

Supplement: Unnamed: 0 solution appears when reading and writing csv files in pandas

When reading the csv file, a new 1 column is automatically added by default, Unnamed: 0

Solution:

For read_csv (), set index_col=0.

When writing an csv file, a new 1 column is automatically added by default, Unnamed: 0

Solution:

When to_csv (), set index=False. Or add index=True, index_label= "id"