Teach you step by step how to use Python to batch add a new column in Excel, and the content is the Excel table name

[[441492]]

Preface

A few days ago, in the platinum exchange group, there was a person named [? ? ?? ] fans asked a question about Python automated office in the Python exchange group. At first glance, it seemed very simple, but in fact it is indeed difficult. The question is as shown in the picture below.

2. Solution ideas

If you follow the conventional idea, first open an Excel table, and then add Corresponding to the table name, if it is just a table and the table content has only one row, this operation can be done by dividing three by five and dividing by two. But if you encounter many tables, it will be very tiring if you continue to process them one by one.

But here I will introduce to you a method of using Python to automate office work to help you solve the problem and ensure that there will be no mistakes. The battle can be completed in about a few seconds.

Actually [? ? ??] also tried to use Python to solve it, but encountered some problems. Although the Excel file was created, the subsequent column name writing failed, and he finally needed To merge Excel tables, there are actually two requirements here.

3. Solution

The code provided by [?(This is the back of the moon] boss] is given here, The general idea is actually the same, but the implementation method is implemented using Python programs, and the efficiency is very different. Let’s look at the code directly!

1. Code 1

# coding: utf-8 
# 给每个excel中的sheet增加一列，值为excel名.xlsx 
from pathlib import Path 
import pandas as pd 
path = Path(r'E:PythonCrawlerpython_crawler-masterMergeExcelSheetfile777') 
excel_list = [(i.stem, pd.concat(pd.read_excel(i, sheet_name=None))) for i in path.glob("*.xls*")] 
data_list = [] 
for name, data in excel_list: 
    print(name) 
    print(data) 
    data['表名'] = name 
    data_list.append(data) 
result = pd.concat(data_list, ignore_index=True) 
result.to_excel(path.joinpath('给每个excel中的sheet增加一列，值为excel名.xlsx'), index=False, encoding='utf-8') 
print('添加和合并完成！')

i.stem means to get the name of the file, excluding the parent node and suffix, for example D:/Desktop/test.txt, i.stem is test!

2. Code 2

# coding: utf-8 
# 给每个excel中的sheet增加一列，值为excel名-sheet名.xlsx 
from pathlib import Path 
import pandas as pd 
path = Path(r'E:PythonCrawlerpython_crawler-masterMergeExcelSheetfile777') 
excel_list = [(i.stem, pd.concat(pd.read_excel(i, sheet_name=None))) for i in path.glob("*.xls*")] 
data_list = [] 
for name, data in excel_list: 
    print(name) 
    print(data) 
    data['表名'] = name 
    data.reset_index(level=0, inplace=True)  # 重置level_0索引 
    data_list.append(data) 
result = pd.concat(data_list, ignore_index=True) 
result['表名'] = result['表名'] + '-' + result['level_0']  # 更改列值 
del result['level_0']  # 删除多余列 
result.to_excel(path.joinpath('给每个excel中的sheet增加一列，值为excel名-sheet名.xlsx'), index=False, encoding='utf-8') 
print('添加和合并完成！')

This code is actually similar to code 1, except that the column names are slightly different. Because the sheet names in the three excels in the example are the same, they are prefixed with the table name, table name-sheet name. .

After the code is run, the corresponding Excel file will be automatically generated in the code directory, as shown in the figure below.

After Each Excel table also has column names corresponding to the table name, and the merge function of all tables is also implemented, as shown in the figure below.

If you are interested in Excel merging knowledge, you can click on this article to learn: Inventory of 4 ways to use Python to batch merge all Sheet data in Excel files under all subfolders in the same folder. It is full of useful information!

4. Summary

I am an advanced user of Python. Based on fans’ questions about Python automated office, this article provides a solution for basic pandas file processing using Python, which fully meets the requirements of fans. , saving fans a lot of time.

The above is the detailed content of Teach you step by step how to use Python to batch add a new column in Excel, and the content is the Excel table name. For more information, please follow other related articles on the PHP Chinese website!