如何从两个不同的DataFrame计算平均值？附示例数据

Looks like you've got two DataFrames where one has comma-separated order IDs per material-plant pair, and the other has individual order-level metrics. Here's a step-by-step approach to compute the average of m1 to m5 grouped by material and plant:

Step 1: Split and expand the comma-separated Order values #

First, we need to break down the multi-order entries in df into individual rows, so each order ID gets its own row paired with the corresponding material and plant.

import pandas as pd

# Define your original DataFrames (filling in the sample data you provided)
df = pd.DataFrame({
    'material': [24990, 24990, 166621, 166621, 240758, 276009],
    'plant': [89952, 89952, 3062, 3062, 3062, 3062],
    'Order': ['4568789,5098710', '9448609,1007081', '18364103', '78309139', '55146035', '38501581,857542']
})

# Split the Order column and explode into separate rows
df_expanded = df.assign(Order=df['Order'].str.split(',')).explode('Order')
# Convert Order to integer to match the data type in df1
df_expanded['Order'] = df_expanded['Order'].astype(int)

Step 2: Merge the expanded DataFrame with df1 #

Next, we'll join the expanded df with df1 using material, plant, and Order as matching keys. This will bring in the m1 to m5 values for each individual order.

df1 = pd.DataFrame({
    'material': [24990, 24990, 24990, 24990, 166621, 166621],
    'plant': [89952, 89952, 89952, 89952, 3062, 3062],
    'Order': [4568789, 5098710, 9448609, 1007081, 18364103, 78309139],
    'm1': [0.123, 1.000, 0.0, 0.0, 0.0, 0.0],
    'm2': [0.214, 0.363, 0.345, 0.756, 0.0, 1.0],
    'm3': [0.0, 0.0, 0.0, 0.0, 0.0, 0.0],
    'm4': [0.0, 0.0, 1.0, 1.0, 0.0, 0.0],
    'm5': [0.0, 0.0, 0.0, 0.0, 0.0, 0.0]
})

# Merge using left join to retain all entries from df (even if df1 has no matching data)
merged_df = pd.merge(df_expanded, df1, on=['material', 'plant', 'Order'], how='left')

Using a left join ensures we don't lose any material-plant-order combinations from your original df, even if there's no corresponding data in df1 (those rows will have NaN for m1 to m5).

Step 3: Compute grouped averages #

Finally, group the merged DataFrame by material and plant, then calculate the mean for each of the m columns:

# Calculate mean values, reset_index to keep material and plant as columns
average_df = merged_df.groupby(['material', 'plant'])[['m1', 'm2', 'm3', 'm4', 'm5']].mean().reset_index()

print(average_df)

Example output highlights for your sample data: #

For material=24990 and plant=89952, you'll get:

• m1: (0.123 + 1.0 + 0.0 + 0.0)/4 = 0.28075
• m2: (0.214 + 0.363 + 0.345 + 0.756)/4 = 0.4195
• m4: (0.0 + 0.0 + 1.0 + 1.0)/4 = 0.5

Quick adjustments you might need: #

• If you want to treat missing m values (from orders not present in df1) as 0 instead of ignoring them, add merged_df = merged_df.fillna(0) before grouping.
• The reset_index() converts the grouped indices back to regular columns for easier readability.

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