基于引用列表与现有Pandas DataFrame创建关联文章新DataFrame

Got it, let's break this down into simple, actionable steps to get the exact DataFrame you're looking for. Here's how to do it using Pandas:

Step 1: Prepare Sample Data (for testing) #

First, let's create a reproducible sample DataFrame matching your structure so you can follow along:

import pandas as pd

# Sample input DataFrame
data = {
    "Article": [1, 2, 3, 4, 5, 9, 10],
    "Reference": ["3,4,5", "5,9,10", "", "", "", "", ""],
    "Text": ["xyz1", "xyz2", "xyz3", "xyz4", "xyz5", "xyz9", "xyz10"]
}
df = pd.DataFrame(data)

Step 2: Explode the Reference Column #

We need to split the comma-separated reference values into individual rows so each reference gets its own entry:

# Split Reference into a list of strings, then explode into separate rows
df_exploded = df.assign(Reference=df["Reference"].str.split(",")).explode("Reference")

# Remove empty rows from articles with no references
df_exploded = df_exploded[df_exploded["Reference"] != ""].reset_index(drop=True)

# Convert Reference to integer to match the Article column's data type
df_exploded["Reference"] = df_exploded["Reference"].astype(int)

Step 3: Merge to Get Referenced Article Texts #

Now we'll merge the exploded DataFrame with the original one to pull in the text for each referenced article:

# Merge with original DataFrame to fetch the referenced article's details
result = df_exploded.merge(
    df[["Article", "Text"]],
    left_on="Reference",
    right_on="Article",
    suffixes=("_1", "_2")
)

# Rename columns to match your desired output format
final_df = result[["Article_1", "Text_1", "Article_2", "Text_2"]].rename(
    columns={
        "Article_1": "Article1",
        "Text_1": "Text1",
        "Article_2": "Article2",
        "Text_2": "Text2"
    }
)

# Reset index to start at 1 like your example
final_df = final_df.reset_index(drop=True)
final_df.index += 1

Step 4: View the Result #

If you print final_df, you'll get exactly the structure you want:

print(final_df)

Output:

Article1 Text1  Article2 Text2
1         1  xyz1         3  xyz3
2         1  xyz1         4  xyz4
3         1  xyz1         5  xyz5
4         2  xyz2         5  xyz5
5         2  xyz2         9  xyz9
6         2  xyz2        10  xyz10

Edge Case Handling #

If some references don't exist in your Article column, you can modify the merge to keep those rows and flag missing references:

# Use left join to retain references that don't have a matching article
result = df_exploded.merge(
    df[["Article", "Text"]],
    left_on="Reference",
    right_on="Article",
    suffixes=("_1", "_2"),
    how="left"
)

# Fill missing Text2 values with a placeholder
result["Text_2"] = result["Text_2"].fillna("Reference not found")

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