基于Python3.8处理Excel订单数据集：筛选含'ended'记录并计算买家订单占比

Let's work through this problem step by step. I'll use Python's pandas library (the go-to for tabular data tasks) to implement your filtering rules, then compute the buyer order percentages.

Step 1: Prepare the Sample Data #

First, let's recreate your sample dataset as a pandas DataFrame so we can test the code directly:

import pandas as pd

# Sample data matching your example
data = [
    ["AX1", "2/1/2019", "检查库存", "开具发票", "产品请求今日启动", "HTN"],
    ["X5L", "2/1/2019", "库存盘点", "未来请求规划", "无操作更新", None],
    ["NO6", "2/1/2019", "请求处理中", "关闭请求", "产品操作请求已结束", "KLW"],
    ["ZS5", "2/1/2019", "已下单", "处理请求", "产品请求已启动", "WHM"],
    ["AX1", "3/1/2019", "开具订单发票", "物流规划", "产品请求仍待处理", "HTN"],
    ["X5L", "3/1/2019", "新请求规划", "处理订单", "新的产品请求已启动", "LKJ"],
    ["NO6", "3/1/2019", "关闭请求", "无新请求", "无新操作更新", None],
    ["ZS5", "3/1/2019", "请求已结束", "新订单已下单", "产品库存盘点", "WHM"],
    ["AX1", "4/1/2019", "物流规划", "关闭请求", "产品请求已结束", "HTN"],
    ["X5L", "4/1/2019", "请求处理中", "物流规划", "产品请求处理中", "LKJ"],
    ["NO6", "4/1/2019", "无更新", "新请求规划", "无新操作更新", None],
    ["ZS5", "4/1/2019", "新订单已启动", "请求开票", "库存与物流规划", "KLW"]
]

df = pd.DataFrame(data, columns=["Product", "Date", "Last24", "Next24", "Summary", "Buyer"])
# Convert Date to datetime for proper sorting (critical for getting the last row per group)
df["Date"] = pd.to_datetime(df["Date"])

Step 2: Implement Filtering Rules #

Rule 2: Keep Last Row of (Product + Buyer) Groups if Contains 'ended' #

Let's start with this rule because it relies on grouping by unique Product-Buyer pairs and retaining only the final entry (sorted by date) if it has 'ended' in any of the target columns.

# Group by Product and Buyer, get the last row of each group (sorted by Date)
grouped_last = df.sort_values("Date").groupby(["Product", "Buyer"], as_index=False).last()

# Filter rows where at least one of Last24/Next24/Summary contains 'ended'
rule2_results = grouped_last[
    grouped_last[["Last24", "Next24", "Summary"]].apply(lambda x: x.str.contains("ended", na=False)).any(axis=1)
]

Rule 1: Keep Rows with Same Product but Different Buyer if Contains 'ended' #

Next, we need to identify rows where the same Product has multiple Buyers, then keep those rows only if they have 'ended' in any target column.

# Find Products that have more than one unique Buyer
products_with_multiple_buyers = df.groupby("Product")["Buyer"].nunique()[lambda x: x > 1].index

# Filter the original DataFrame to only these products
multi_buyer_products = df[df["Product"].isin(products_with_multiple_buyers)]

# Now filter rows where at least one target column contains 'ended'
rule1_results = multi_buyer_products[
    multi_buyer_products[["Last24", "Next24", "Summary"]].apply(lambda x: x.str.contains("ended", na=False)).any(axis=1)
]

Combine & Deduplicate Results #

Some rows might qualify for both rules, so we'll combine the results and remove duplicates to avoid double-counting:

# Combine the two result sets
final_filtered = pd.concat([rule1_results, rule2_results]).drop_duplicates().reset_index(drop=True)

Step 3: Calculate Buyer Order Ratios #

Finally, compute the percentage of orders each buyer has in the filtered dataset:

# Calculate order counts per buyer
buyer_counts = final_filtered["Buyer"].value_counts()

# Calculate percentages and format for readability
buyer_ratios = (buyer_counts / buyer_counts.sum() * 100).round(2).astype(str) + "%"

print("Buyer Order Ratios:")
print(buyer_ratios)

Sample Output Explanation #

For your provided data, the final filtered rows will include:

• From Rule 2: The last AX1-HTN row (contains 'ended' in Summary)
• From Rule 1: The NO6-KLW row (contains 'ended' in Summary) and ZS5-WHM row (contains 'ended' in Last24)

The resulting buyer ratios will look like this:

HTN     50.0%
KLW     25.0%
WHM     25.0%
Name: Buyer, dtype: object

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