按指定列匹配筛选DataFrame行并保留目标序列相关列

Hey there, let's work through how to get your desired DataFrame based on the conditions you've laid out. Here's a step-by-step solution using pandas:

1. Load Your Data First #

First, we'll read in both DataFrames (adjust the file path/reading method to match your actual data format, like CSV, Excel, etc.):

import pandas as pd

# Load the dataframes
df1 = pd.read_csv("your_df1_file.csv")
df2 = pd.read_csv("your_df2_file.csv")

2. Filter Rows by Matching IDs #

To keep only the rows in df1 where #qseqid matches values in df2's seq2_id column, use isin() to check for matches:

# Get all unique matching IDs from df2
valid_ids = df2["seq2_id"].unique()
# Filter df1 to keep only rows with matching IDs
filtered_rows = df1[df1["#qseqid"].isin(valid_ids)].copy()

3. Select the Right Columns #

Next, we need to retain seq_id1, seq_id2, plus all columns in df1 that relate to the sequence IDs listed in df2's sequences column. We'll cover two common scenarios:

Case 1: sequences stores prefixes (e.g., "seq2", "seq9") #

If df2's sequences column has prefixes (not full column names), we'll find all df1 columns that include these prefixes:

# Get unique sequence prefixes from df2
seq_prefixes = df2["sequences"].unique()

# Start with the mandatory columns
keep_columns = ["seq_id1", "seq_id2"]

# Add any df1 column that contains one of the sequence prefixes
for col in df1.columns:
    if any(prefix in col for prefix in seq_prefixes):
        keep_columns.append(col)

# Remove duplicates while preserving column order
keep_columns = list(dict.fromkeys(keep_columns))

Case 2: sequences stores full column names (e.g., "seq2_A", "seq9_A") #

If df2's sequences column directly lists the exact column names you want to keep, this step is simpler:

# Get the exact column names from df2
seq_columns = df2["sequences"].unique()

# Combine mandatory columns with valid sequence columns (skip any that don't exist in df1)
keep_columns = ["seq_id1", "seq_id2"] + [col for col in seq_columns if col in df1.columns]

Apply Column Filter #

Now apply the column selection to our filtered rows:

final_df = filtered_rows[keep_columns]

4. Verify the Result #

You can print the first few rows to confirm it matches your expected output:

print(final_df.head())

That's it! This should give you the merged, filtered DataFrame you need.

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