如何单独提取各列中出现两次及以上的重复值并导出文件？

Hey there! Let's tackle this problem head-on—dealing with a 1.5GB CSV means we need memory-efficient methods to avoid crashes, while making sure we extract exactly the duplicate values per column you're after. I'll walk you through working solutions for both Python Pandas and R, since you've already tried those tools.

Python Pandas Solution (Memory-Friendly for Large Files) #

Loading the entire 1.5GB file into memory might cause issues, so we'll use chunked processing to count value occurrences first, then filter out duplicates.

Step 1: Count Value Frequencies Across Chunks #

We'll iterate through small chunks of the CSV to tally how many times each value appears in every column—no need to load everything at once.

import pandas as pd
from collections import defaultdict

# Initialize a nested dict to track counts for each value per column
value_counts = defaultdict(lambda: defaultdict(int))
chunk_size = 10_000  # Adjust this based on your memory; bigger chunks = faster, but more memory used

# Loop through each chunk of the CSV
for chunk in pd.read_csv("your_large_file.csv", chunksize=chunk_size):
    for col in chunk.columns:
        # Update the count for each value in the current column
        for val, cnt in chunk[col].value_counts().items():
            value_counts[col][val] += cnt

# Filter to keep only values that appear 2 or more times per column
duplicate_values = {
    col: [val for val, cnt in counts.items() if cnt >= 2]
    for col, counts in value_counts.items()
}

Step 2: Export Duplicates to Separate Files #

Now we'll create a CSV for each column, with the original column header and only the duplicate values.

for col, vals in duplicate_values.items():
    # Create a DataFrame with the column name as the header
    df = pd.DataFrame(vals, columns=[col])
    # Save to a CSV (e.g., AO1_duplicates.csv)
    df.to_csv(f"{col}_duplicates.csv", index=False)

R Solution (Using data.table for Speed & Efficiency) #

data.table is built for handling large datasets quickly and with minimal memory usage—way better than base R for this task.

Step 1: Load the CSV and Count Duplicates per Column #

First, we'll load the CSV efficiently and count how often each value shows up in every column.

library(data.table)

# Load the large CSV quickly with fread (faster than read.csv)
dt <- fread("your_large_file.csv")

# For each column, extract values that occur 2 or more times
duplicate_list <- lapply(dt, function(col) {
  value_counts <- table(col)
  # Get the names of values with counts >=2
  names(value_counts[value_counts >= 2])
})

Step 2: Export Each Column's Results #

Convert each column's duplicate values into a data frame with the original header, then save to a separate CSV.

# Loop through each column's duplicate values
for (col_name in names(duplicate_list)) {
  # Create a data frame with the column header
  df <- data.frame(!!col_name := duplicate_list[[col_name]])
  # Save to CSV (e.g., BO1_duplicates.csv)
  write.csv(df, file = paste0(col_name, "_duplicates.csv"), row.names = FALSE)
}

Why Your Previous Attempts Might Have Failed #

• If you used Pandas' duplicated() function, that marks entire rows as duplicates, not individual values per column.
• Loading the full 1.5GB file into memory might have caused partial data loading or crashes, leading to incorrect counts.
• In R, using base read.csv or dplyr without optimizing for large files can slow things down or use too much memory.

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