如何将Python分析TXT文件得到的结果关联文件名存入单个Excel文件？

Got it, let's walk through how to tackle this task efficiently. We'll use Python's pandas (for data manipulation and Excel writing) and os (for file system traversal)—these tools make this workflow straightforward and scalable.

Step 1: Install Required Libraries #

First, make sure you have the necessary packages installed (if you don't already):

pip install pandas openpyxl

openpyxl is required for writing .xlsx files with pandas.

Step 2: Full Code Implementation #

Here's a complete, reusable script with comments to explain each part. You’ll just need to tweak the analysis logic to match your specific results:

import os
import pandas as pd

def analyze_txt_file(file_path):
    """Replace this with your existing analysis logic."""
    # Example: This is where you'll plug in your code to process the TXT file
    # For demonstration, we'll compute simple text metrics—swap this with your actual analysis
    with open(file_path, 'r', encoding='utf-8') as f:
        content = f.read()
    
    # Example analysis output (adjust keys/values to match your results)
    return {
        'total_words': len(content.split()),
        'total_lines': content.count('\n') + 1,  # +1 to account for final line without newline
        'char_count': len(content.strip())
    }

def main():
    # Set the path to your folder of TXT files
    txt_directory = './your_txt_files_folder'  # Replace with your actual directory path
    
    # Initialize a list to store all results (each item becomes a row in Excel)
    aggregated_results = []
    
    # Loop through every TXT file in the directory
    for filename in os.listdir(txt_directory):
        if filename.endswith('.txt'):
            full_file_path = os.path.join(txt_directory, filename)
            
            # Run your analysis on the current file
            file_results = analyze_txt_file(full_file_path)
            
            # Attach the filename to the results (critical for linking data to source)
            file_results['source_filename'] = filename
            
            # Add the combined data to our results list
            aggregated_results.append(file_results)
    
    # Convert the list of dictionaries to a pandas DataFrame (easy to work with for Excel)
    results_df = pd.DataFrame(aggregated_results)
    
    # Optional: Reorder columns to put the filename first for readability
    column_order = ['source_filename'] + [col for col in results_df.columns if col != 'source_filename']
    results_df = results_df[column_order]
    
    # Write the DataFrame to an Excel file
    output_excel_path = 'txt_analysis_summary.xlsx'
    results_df.to_excel(output_excel_path, index=False, engine='openpyxl')
    
    print(f"Done! Results saved to {output_excel_path}")

if __name__ == "__main__":
    main()

Key Customizations You Need to Make #

• Update txt_directory: Replace ./your_txt_files_folder with the actual path to your folder of TXT files.
• Modify analyze_txt_file(): This is where you insert your existing analysis code. Instead of the word/line count example, return a dictionary where keys are your result metrics (e.g., 'average_score', 'peak_value') and values are the computed results from each TXT file.
• Adjust Column Order: The code reorders columns to put the filename first, but you can rearrange this to match your preferred layout.

Handling Edge Cases #

• Nested Subdirectories: If your TXT files are in subfolders, replace os.listdir() with os.walk() to traverse recursively. Here’s a quick snippet to swap in:

  for root, _, files in os.walk(txt_directory):
      for filename in files:
          if filename.endswith('.txt'):
              full_file_path = os.path.join(root, filename)
              # Rest of the logic remains the same
  

• Multi-Row Results: If your analysis returns multiple rows of data per TXT file, adjust the code to append multiple entries to aggregated_results, each linked to the same filename.

Final Output #

The script will generate an Excel file where each row corresponds to a TXT file, with the source filename in the first column followed by all your analysis metrics—exactly the linked, organized view you’re looking for.

内容的提问来源于stack exchange，提问作者Sujith Kumar S