创建数据的excel工作表

Question

我有500个文本文件，我需要找出每个文件中有多少实体。我在以下代码中做到了这一点：

import os
import pandas as pd

path = "newData"
files = [file for file in os.listdir(path) if file.endswith(".txt")]

c=0
for file in files:
    df = pd.read_csv(os.path.join(path, file), 
                     sep=' ',engine='python')
    
    df.columns = ['word','token','?']
    
    problem = df['token'].tolist().count('B-Problem')
    method = df['token'].tolist().count('B-Method\oTool')
    data = df['token'].tolist().count('B-Dataset')

我需要创建一张excel工作表来显示excel输出的信息expect：

Filename  #ofProblem  #ofMethod  #ofData
admin.txt    {problem}   {method}  {data}

如何将它们存储到一个大的excel工作表中，以存储所有500个文件？

Answer 1

Pandas可以编写excel表格，只需将您的值存储在一个数据帧中，您就可以用它来写入文件

import os
import pandas as pd

path = "newData"
files = [file for file in os.listdir(path) if file.endswith(".txt")]

out_data = []

c=0
for file in files:
    df = pd.read_csv(os.path.join(path, file), 
                     sep=' ',engine='python')
    
    df.columns = ['word','token','?']
    
    problem = df['token'].tolist().count('B-Problem')
    method = df['token'].tolist().count('B-Method\oTool')
    data = df['token'].tolist().count('B-Dataset')

    out_data.append(
        {
            "Filename": file,
            "#ofProblem": problem,
            "#ofMethod": method,
            "#ofData": data,
        }
    )

pd.DataFrame(out_data).to_excel("your_excel_name.xlsx", index=None)