如何管理具有一对多关系的两个Pandas DataFrame并实现关联查询？

Great question! Dealing with one-to-many relationships in pandas can feel tricky at first, especially when you're tempted to cram lists into cells (we've all been there 😅). Let's break down the best approaches here, since storing lists in DataFrame cells or splitting ingredients into multiple columns are both anti-patterns for pandas.

Why Your Initial Approaches Cause Problems #

First, you’re absolutely right about the issues with list values:

• Pandas is built for tabular, row-column aligned data—list values break this model. Assigning a single list to a cell fails because pandas expects the list length to match the number of rows selected.
• Queries like merged.loc[['vitamin C'] in merged['ingredient_list']] don’t work because pandas checks for exact list matches, not whether the ingredient exists inside the list.
• Splitting ingredients into ingredient1, ingredient2, etc., leads to sparse, unmaintainable data—you’d have to add new columns every time a new ingredient is introduced.

The Right Approach: Keep DataFrames Separate (Relational Style) #

The best way to handle this is to treat your two DataFrames like tables in a relational database, and use pandas’ built-in functions to link them without modifying their structure. Here’s how to find all drugs containing a specific ingredient (e.g., "vitamin C"):

Step 1: Identify Matching Medication IDs #

First, pull all med_id values from the ingredients DataFrame that are associated with your target ingredient:

import pandas as pd

# Your original data
medications = pd.DataFrame({
    'med_id': [1, 2],
    'med_name': ['multivitamin', 'aspirin'],
})
ingredients = pd.DataFrame({
    'med_id': [1, 1, 2],
    'ingredient_id': [1, 2, 3],
    'ingredient_name': ['vitamin C', 'vitamin D', 'acetylacetic acid']
})

# Target ingredient to search for
target_ingredient = "vitamin C"

# Get all med_ids linked to the target ingredient
matching_med_ids = ingredients[ingredients["ingredient_name"] == target_ingredient]["med_id"].unique()

Step 2: Fetch Corresponding Medications #

Use isin() to filter the medications DataFrame to only include the IDs you found:

# Get all medications that contain the target ingredient
result = medications[medications["med_id"].isin(matching_med_ids)]
print(result)

This will output:

med_id       med_name
0       1  multivitamin

Advanced: Show All Ingredients for Matching Drugs #

If you want to see all ingredients for the matching drugs (without storing lists), you can merge the DataFrames temporarily and group to format ingredients as a string:

# Merge the two DataFrames
merged = pd.merge(medications, ingredients, on="med_id")

# Filter to only rows with the target ingredient, then group to show all ingredients per drug
result_with_all_ingredients = merged[merged["med_id"].isin(matching_med_ids)] \
    .groupby(["med_id", "med_name"])["ingredient_name"] \
    .apply(", ".join) \
    .reset_index()

print(result_with_all_ingredients)

Output:

med_id       med_name          ingredient_name
0       1  multivitamin  vitamin C, vitamin D

Key Takeaways #

• Keep your two DataFrames separate: This follows relational database best practices and plays to pandas’ strengths.
• Avoid complex cell values: Lists, dictionaries, or other non-scalar values in cells make queries, updates, and maintenance far harder than necessary.
• Use isin() for cross-DataFrame queries: It’s the cleanest way to link one-to-many relationships without modifying your core data structure.

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