基于Pandas匹配两个Excel表数据并按周长分组导出

Here's how you can achieve your desired result using pandas, building directly on the code you already have:

import pandas as pd

# Your existing code to load the Excel files
df = pd.read_excel(open(r'C:\Users\fruits.xlsx','rb'))
mf = pd.read_excel(open(r'C:\Users\fruitsDetail.xlsx','rb'))

# 1. Merge the two tables to get matched fruit data
merged_df = pd.merge(df, mf, on='Name', how='inner')

# 2. Group by fruit name and circumference, concatenate weights with commas
result_df = merged_df.groupby(['Name', 'circumference'])['weight'].agg(lambda x: ','.join(x)).reset_index()

# Optional: Rename 'Name' to lowercase 'name' to match your expected output
result_df.rename(columns={'Name': 'name'}, inplace=True)

# 3. Export the final result to a new Excel file
result_df.to_excel('grouped_fruits_weights.xlsx', index=False)

Breakdown of Each Step: #

• Merging the DataFrames: The pd.merge function combines the two tables using the Name column as the key. We use how='inner' to keep only fruits that exist in both files (if you need to include all fruits from fruits.xlsx even without details, switch to how='left').
• Grouping and Aggregating: groupby(['Name', 'circumference']) groups rows by each unique fruit-circumference pair. The agg(lambda x: ','.join(x)) takes all weight values in each group and joins them into a single comma-separated string.
• Resetting the Index: After grouping, Name and circumference become index columns. reset_index() converts them back to regular columns so they appear properly in your output.
• Exporting: to_excel writes the final DataFrame to a new Excel file. index=False ensures the pandas index isn't included in the output, matching your desired format.

Notes: #

• If your weight column has missing values (NaN), they'll show up as "nan" in the concatenated string. To exclude them, modify the aggregation function to:

  .agg(lambda x: ','.join(x.dropna()))
  

• You can change the output filename ('grouped_fruits_weights.xlsx') to whatever you prefer.

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