如何将Excel导入Python并检测A列重复值并导出至TXT文件

Got it, let's break this down step by step to solve your problem. Here's a complete, easy-to-follow solution that combines all the parts you need to handle your 7000+ rows of data:

Step 1: Install Required Tools #

First, we'll use pandas (a powerful Python library for data handling) to read your spreadsheet and process duplicates. If you haven't installed it yet, run this command in your terminal:

pip install pandas openpyxl

(The openpyxl package lets pandas read modern Excel files; if your data is in a CSV, you don't need this.)

Step 2: Full Working Code #

Copy this code, then adjust the file paths and column references to match your data:

import pandas as pd
from collections import Counter

# 1. Load your data file (replace with your actual file path)
# For Excel:
df = pd.read_excel("your_data_file.xlsx", usecols="A")  # Only load column A
# For CSV:
# df = pd.read_csv("your_data_file.csv", usecols=[0])  # Use [0] for first column if no header

# 2. Convert column A to a Python list
column_a_list = df.iloc[:, 0].tolist()  # iloc[:,0] targets the first column regardless of header

# 3. Detect duplicates
# Option 1: Get ALL instances of duplicate items (e.g., if "Apple" appears 3 times, it will show up 3 times)
item_counts = Counter(column_a_list)
all_duplicate_instances = [item for item in column_a_list if item_counts[item] > 1]

# Option 2: Get ONLY unique duplicate values (each repeated item shows up once)
unique_duplicate_values = df[df.duplicated(subset=df.columns[0], keep=False)].iloc[:,0].unique().tolist()

# 4. Write duplicates to a TXT file
# Choose either Option 1 or 2 below based on your needs
with open("duplicates.txt", "w", encoding="utf-8") as output_file:
    # For all duplicate instances:
    for item in all_duplicate_instances:
        output_file.write(f"{item}\n")
    
    # For unique duplicate values (comment out the above loop and uncomment this):
    # for item in unique_duplicate_values:
    #     output_file.write(f"{item}\n")

Key Notes to Customize: #

• File Paths: Replace "your_data_file.xlsx" with the actual path to your spreadsheet (e.g., C:/Documents/my_data.xlsx on Windows or ~/Documents/my_data.xlsx on Mac/Linux).
• Column Reference: If your sheet has a header and you want to use the column name instead of position, replace iloc[:,0] with df['YourColumnName'] (e.g., df['ProductName']).
• Duplicate Type: Decide whether you want every repeated entry (Option 1) or just a list of values that were duplicated at least once (Option 2).

How It Works: #

• pandas loads only column A to save memory (perfect for 7000+ rows).
• Counter from the collections module counts how many times each item appears, so we can filter out anything that shows up once.
• The duplicated() method in pandas flags rows that have duplicate values; using keep=False ensures we capture every instance of a duplicate, not just the ones after the first occurrence.
• Writing with encoding="utf-8" prevents messy character issues if your data has non-English text.

内容的提问来源于stack exchange，提问作者poddpython