多后缀文件合并至DataFrame及按前缀批量处理bed文件输出CSV

Alright, let's tackle these two pandas data processing tasks one by one. I'll assume you're working with Python and pandas since it's the standard tool for this kind of DataFrame manipulation.

1. Merge two files with different suffixes into a single DataFrame #

First, we'll load each file using pandas' appropriate read function (adjust based on your file types: .csv, .xlsx, .json, etc.), then combine them. The method depends on whether you want to stack rows (if columns match) or merge columns (using a shared key).

import pandas as pd

# Load your two files (swap read_csv/read_excel/read_json as needed)
df_suffix1 = pd.read_csv("example_file.csv")
df_suffix2 = pd.read_excel("another_file.xlsx")

# Option 1: Stack rows (use if both DataFrames have the same columns)
combined_df = pd.concat([df_suffix1, df_suffix2], ignore_index=True)

# Option 2: Merge columns (use if you need to join on a common key column)
# combined_df = pd.merge(df_suffix1, df_suffix2, on="shared_key_column", how="inner")

• Note: For .bed files specifically, you'll want to use sep="\t" in read_csv since they're tab-separated by default.

2. Batch process prefixed .bed files, merge with another dataset, and generate CSVs #

For this task, we'll automate grouping files by their prefix (like dog from dog_A_final.bed), merge each pair, combine with your other dataset, and save the result as a named CSV.

import os
import pandas as pd

# Configure your paths and datasets
bed_dir = "./path/to/your/bed_files"  # Replace with your actual directory path
other_data = pd.read_csv("your_other_dataset.csv")  # Load the dataset to merge with

# Get all .bed files in the target directory
bed_files = [f for f in os.listdir(bed_dir) if f.endswith(".bed")]

# Group files by their prefix (e.g., "dog" from "dog_A_final.bed")
prefix_groups = {}
for filename in bed_files:
    # Extract prefix (split on first underscore; adjust if your naming differs)
    prefix = filename.split("_")[0]
    if prefix not in prefix_groups:
        prefix_groups[prefix] = []
    prefix_groups[prefix].append(os.path.join(bed_dir, filename))

# Process each prefix group
for prefix, file_paths in prefix_groups.items():
    # Skip groups that don't have exactly 2 files (as per your requirement)
    if len(file_paths) != 2:
        print(f"Warning: Skipping {prefix} - found {len(file_paths)} files, expected 2")
        continue
    
    # Load both .bed files (tab-separated)
    df1 = pd.read_csv(file_paths[0], sep="\t")
    df2 = pd.read_csv(file_paths[1], sep="\t")
    
    # Merge the two .bed files (choose concat or merge based on your needs)
    merged_bed = pd.concat([df1, df2], ignore_index=True)  # Stack rows
    # merged_bed = pd.merge(df1, df2, on="key_column", how="inner")  # Join on key
    
    # Merge with your other dataset
    final_df = pd.merge(merged_bed, other_data, on="common_key_column", how="inner")
    
    # Save to CSV with the prefix as the filename
    final_df.to_csv(f"{prefix}.csv", index=False)
    print(f"Successfully saved: {prefix}.csv")

Key Notes: #

• Prefix Extraction: If your filenames don't split on the first underscore (e.g., my_dog_A_final.bed), adjust the split logic (e.g., prefix = "_".join(filename.split("_")[:2]) to get my_dog).
• .bed Format: Double-check the separator for your .bed files—some might use different delimiters, but tab is standard.
• Merge Logic: Adjust the how parameter in pd.merge (e.g., left, outer) based on whether you need to keep all rows from one dataset or only matching ones.

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