主页 / coding / 问题 / 78843358
Accepted
nicgl
Asked: 2024-08-07 19:10:19 +0800 CST

Python 中高效的 Excel SUMIF 等效函数

  • 772

我正在尝试弄清楚如何在 Python 中创建与 SUMIF 等效的函数。我目前的解决方案有效，但效率太低，运行需要 20 分钟。

什么是最有效的方法来实现我想要的结果？

这是我目前正在做的事情，归结为一个非常简单的形式。在“实际”代码中，还有更多条件。

**sales_data customer_1**
Transactions   | Product Dimension 4 | Product Dimension 2 | Product Dimension 3 | sum_of_sales
-------------- | ------------------- | ------------------- | --------------------| -------------
1              | 50                  | F80                 | ETQ546              | 80
2              | 50                  | F80                 | SAS978              | 20
3              | 50                  | C36                 | JBH148              | 10
4              | 50                  | F80                 | ETQ546              | 80
5              | 50                  | F80                 | SAS978              | 20
6              | 50                  | C36                 | JBH148              | 10
7              | 20                  | A20                 | OPW269              | 15
8              | 20                  | A20                 | DUW987              | 65
9              | 20                  | v90                 | OWQ897              | 47



**condition_types BEFORE ADDING SUMIF TO TABLE**
Transactions   | Type                | Product Dimensions  |
-------------- | ------------------- | ------------------- | 
customer_1     | ABC                 | 50                  | 
customer_1     | DEF                 | F80                 |
customer_1     | GHI                 | JBH148              | 


**condition_types AFTER ADDING SUMIF TO TABLE**
Transactions   | Type                | Product Dimensions  | sum_of_sales
-------------- | ------------------- | ------------------- | -------------
customer_1     | ABC                 | 50                  | 220
customer_1     | DEF                 | F80                 | 200
customer_1     | GHI                 | JBH148              | 20

定义 sumif 函数

def sumif(row, value_column):
    if row['Type'] == "ABC":
        filtered_data = sales_data.loc[
            (sales_data['Product_dimension_4'] == row['Product Dimensions'])
        ]
    elif row['Type'] == "DEF" and row['Product Dimensions'] in sales_data['Product_dimension_2'].unique():
        filtered_data = sales_data.loc[
            (sales_data['Product_dimension_2'] == row['Product Dimensions'])
        ]

    elif row['Type'] == "GHI" and row['Product Dimensions'] in sales_data['Product_dimension_3'].unique():
        filtered_data = sales_data.loc[
            (sales_data['Product_dimension_3'] == row['Product Dimensions'])
        ]

    else:
        return 0  # Return 0 instead of an empty string for consistency
    
    return filtered_data[value_column].sum()

使用 loc 应用 sumif 函数

condition_types['sum_of_sales'] = condition_types.apply(lambda row: sumif(row, value_column="sum_of_sales"), axis=1)

我希望这足够清楚并且例子不太复杂。

python

2 个回答

  1. Best Answer

    对于更通用的方法，您可以melt获取和sales_data之间的关系，然后与进行聚合，并将其合并到：'Product Dimensions''sum_of_sales'groupby'Product Dimensions'sumconditions_data

    sales_data = (
    pd.melt(
        sales_data,
        id_vars=["sum_of_sales"],
        value_vars=sales_data.filter(like="Product Dimension").columns,
        value_name="Product Dimensions",
    )
    .groupby("Product Dimensions", as_index=False)["sum_of_sales"]
    .sum()
)

condition_types = condition_types.merge(sales_data, how="left")

      Transactions Type Product Dimensions  sum_of_sales
0   customer_1  ABC                 50           220
1   customer_1  DEF                F80           200
2   customer_1  GHI             JBH148            20

    但是如果每个都'Type'必须尊重与特定列的关系'Product Dimension'，则可以创建映射并使用'Type'分组和合并：

    m = {
    "Product Dimension 4": "ABC",
    "Product Dimension 2": "DEF",
    "Product Dimension 3": "GHI",
}

sales_data = pd.melt(
    sales_data,
    id_vars=["Transactions", "sum_of_sales"],
    value_vars=sales_data.filter(like="Product Dimension").columns,
    var_name="Product Dimension Type",
    value_name="Product Dimensions",
)

sales_data["Type"] = sales_data["Product Dimension Type"].map(m)

sales_data = sales_data.groupby(["Product Dimensions", "Type"], as_index=False)[
    "sum_of_sales"
].sum()

condition_types = condition_types.merge(
    sales_data, on=["Product Dimensions", "Type"], how="left"
)

    对于提供的数据，输出是相同的，但如果不同'Product Dimension'列中有重复的值，则输出可能会有所不同。

    • 2

  2. 我建议重新制定您的方法，这是基于您分享的条件。如果我理解上面分享的问题，您正在将列中的条目映射Type到 sales_data DataFrame 中的特定列 - 本质上“ABC”映射到“Product_Dimension_4”，“DEF”映射到“Product_Dimension_2”，“GHI”映射到“Product_Dimension_3”。如果我的假设是正确的，那么下面的解决方案应该提供更快/更有效的路线，使用 Pandas 的功能：

    
df={'Transactions':range(1,10), 
    'Product_Dimension_4': [50,50,50,50,50,50,20,20,20], 
    'Product_Dimension_2':['F80','F80','C36','F80','F80','C36','A20','A20','v90'], 
    'Product_Dimension_3':['ETQ546','SAS978','JBH148','ETq546','SAS978','JBH148','OPW269','DUW987','OWQ897'],
    'sum_of_sales':[80,20,10,80,20,10,15,65,47]}
df = pd.DataFrame(df)

cond = {'Transactions':['customer_1','customer_1','customer_1'], 
        'Type':['ABC','DEF','GHI'],
        'Product Dimensions':[50,'F80','JBH148']}
cond = pd.DataFrame(cond)

condition=(df
           .filter(like='Product')
           .isin(cond['Product Dimensions'].array)
           .astype(np.int8)
           .rename(columns={'Product_Dimension_4':'ABC', 
                            'Product_Dimension_2':'DEF',
                            'Product_Dimension_3':'GHI'})
           .mask(lambda df: df.eq(1), 
                 df.sum_of_sales,
                 axis=0)
            .sum()
           )
cond.assign(sum_of_sales=cond.Type.map(condition))
  Transactions Type Product Dimensions  sum_of_sales
0   customer_1  ABC                 50           220
1   customer_1  DEF                F80           200
2   customer_1  GHI             JBH148            20
    • 1

相关问题