How to tell my lambda function in Python to Return None instead of throwing up Key Value error

user_16306126 0
Viewed 210

I have 3 pandas DataFrames that describe different aspects of a transaction. I am merging these to "complete the transaction". In essence, I need to add the correct SKU pricing and customer/sku discounts to the correct transaction in df_sales_volume in order to add the financial information to the transaction.

How do I tell my script to Return None, instead of returning a KeyError, when a combination of Customer and SKU exists in df_customer_discounts that does not exist in df_sales_volume?

DataFrames:

df_sales_volume contains 3 columns: Customer, SKU and Units Purchased. This details how many Units of a given SKU and given Customer purchased.

df_sku_prices contains 3 columns: SKU, List Price and Markedup Price. and the SKU pricing for that month that I need to merge into df_sales_volume.

df_customer_discounts contains 3 columns: Customer, SKU and Discount. The dataset contains all discounts the business has in place with every customer, although not all Customer/Discount combinations will be present in df_sales_volume.

Here is the code to create a sample dataset:

import pandas as pd

df_sales_volume = pd.DataFrame({ 
    "Customer": ["John's Fruit Shop"]*2 + ["Adam's Grocery's"]*3 + ["Lucy's Fresh Food"]*2, 
    "SKU": ["Bannanas"] + ["Apples"] + ["Avocados"] + ["Purple Grapes"] + ["Dragon Fruit"]*2 +                            ["Mangos"],
    "Units Purchased": [4] + [2] + [13] + [5] + [70] + [34] + [8],
    })

df_sku_prices = pd.DataFrame({
    "SKU": ["Avocados"] + ["Dragon Fruit"] + ["Grapes"] + ["Bannanas"] + ["Apples"],
    "List Price": [103.21] + [4.55] + [42.01] + [7.00] + [3.35], 
    "Markedup Price": [109.34] + [7.20] + [59.00] + [13.78] + [4.10]
}).set_index(["SKU"])


df_customer_discounts = pd.DataFrame({
    "Customer": ["John's Fruit Shop"]*4 + ["Adam's Grocery's"]*3 + ["Lucy's Fresh Food"]*3, 
    "SKU": ["Apples"] + ["Bannanas"] + ["Purple Grapes"] + ["Mandarins"] + ["Avocados"] + ["Purple Grapes"] + ["Dragon Fruit"] + ["Avocados"] + ["Dragon Fruit"] + ["Mangos"],
    "Discount": [0.05] + [0.35] + [0.22] + [0.15] + [0.50] + [0.40] + [0.10] + [0.75] + [0.01] + [0.24]
}).set_index(["SKU", "Customer"])

This is what I have tried:

# Create copy of original volume file to work with

df_monthly_sales_report = df_sales_volume.copy()

# Lookup list price by sku 

df_monthly_sales_report["SKU List Price"] = df_monthly_sales_report.apply(
lambda row: df_sku_prices.loc[row["SKU"], "List Price"], 
axis=1,
)

# Lookup marked up price by sku

df_monthly_sales_report["SKU Markedup Price"] = df_monthly_sales_report.apply(
lambda row: df_sku_prices.loc[row["SKU"], "Markedup Price"], 
axis=1, 
)

# Lookup discounts by customer and sku

df_monthly_sales_report["Customer Discount"] = df_monthly_sales_report.apply(
lambda row: df_customer_discounts.loc[(row['SKU'], row['Customer']), "Discount"], 
axis=1, 
)

However, I get returned KeyError: 'Purple Grapes'

The desired result is a DataFrame with:

  • A new SKU List Price column the assigns the correct SKU list price to the transaction
  • A new SKU Markedup Price column the assigns the correct SKU markedup price to the transaction
  • A new Discount column that assigns the correct discount to the Customer/SKU combination

Notes about dataset:

  • The real life datasets are far larger
  • The df_customer_discounts will often contain combinations that are not present in the df_sales_volume dataset. In other words, there are Discounts in place on some Customers that are not activated because that Customer did not purchase any of that product. I believe this is what is causing the key error.

I am open to approaches that do not involve lambda, however I am new to Python so my knowledge isn't expansive. This is a script that I will be sharing with colleagues eventually and will be re-run a lot.

keyerror pandas function lambda python
3 Answers

I think you're looking for pandas.merge.

Instead of applying a lookup function to each row, you'd simply do a SQL-style left join:

merged = (
    df_sales_volume.merge(
        df_sku_prices, 
        left_on="SKU", 
        right_index=True, 
        how="left"
    ).merge(
        df_customer_discounts, 
        left_on=["SKU", "Customer"], 
        right_index=True, 
        how="left"
    )
)

Result:

            Customer            SKU  Units Purchased  List Price  Markedup Price  Discount
0  John's Fruit Shop       Bannanas                4        7.00           13.78      0.35   
1  John's Fruit Shop         Apples                2        3.35            4.10      0.05   
2   Adam's Grocery's       Avocados               13      103.21          109.34      0.50   
3   Adam's Grocery's  Purple Grapes                5         NaN             NaN      0.40   
4   Adam's Grocery's   Dragon Fruit               70        4.55            7.20      0.10   
5  Lucy's Fresh Food   Dragon Fruit               34        4.55            7.20      0.01   
6  Lucy's Fresh Food         Mangos                8         NaN             NaN      0.24
user_93114800

It's better to write the code manually..

Try:
    #The Code where you think the error occurs.
Except Exception as e:
    print(e)
    return None
user_142312980

How do I tell my script to Return None, instead of returning a KeyError, when a combination of Customer and SKU exists in df_customer_discounts that does not exist in df_sales_volume?

Focusing on just on this problem for now - it's probably not your full implementation but we might need a better understanding of this to answer better.

Initially, I would avoid multi-indexing the customer_discounts dataframe:

df_customer_discounts_cols = pd.DataFrame({
"Customer": ["John's Fruit Shop"]*4 + ["Adam's Grocery's"]*3 + ["Lucy's Fresh Food"]*3, 
"SKU": ["Apples"] + ["Bannanas"] + ["Purple Grapes"] + ["Mandarins"] + ["Avocados"] + ["Purple Grapes"] + ["Dragon Fruit"] + ["Avocados"] + ["Dragon Fruit"] + ["Mangos"],
"Discount": [0.05] + [0.35] + [0.22] + [0.15] + [0.50] + [0.40] + [0.10] + [0.75] + [0.01] + [0.24]})

Then we can use pandas.query() method to find results existing in one but not the other. Query should be faster faster than apply. We could probably condense this down to fewer lines but to be explicit:

fruit = "Purple Grapes"
customer = "John's Fruit Shop"

cd_query = df_customer_discounts_cols.query("SKU == @fruit and Customer == @customer")

sv_query = df_sales_volume.query("SKU == @fruit and Customer == @customer")

result = sv_query
sv_query
if not cd_query.empty:
    if sv_query.empty:
        result = None


print(type(result))
print(result)
user_97817160
Related