Calculate sum of each row of external index in multi-index pandas dataframe

Question content

I have a data frame: seller, item, price, shipping, Free shipping minimum , count available and count required. My goal is to find the cheapest combination of seller and item based on total which is calculated later (the calculation code is shown below). Sample data is as follows:

import pandas as pd

item1 = ['item 1', 'item 2', 'item 1', 'item 1', 'item 2']
seller1 = ['seller 1', 'seller 2', 'seller 3', 'seller 4', 'seller 1']
price1 = [1.85, 1.94, 2.00, 2.00, 2.02]
shipping1 = [0.99, 0.99, 0.99, 2.99, 0.99]
freeship1 = [5, 5, 5, 50, 5]
countavailable1 = [1, 2, 2, 5, 2]
countneeded1 = [2, 1, 2, 2, 1]

df1 = pd.dataframe({'seller':seller1,
                    'item':item1,
                    'price':price1,
                    'shipping':shipping1,
                    'free shipping minimum':freeship1,
                    'count available':countavailable1,
                    'count needed':countneeded1})

# create columns that states if seller has all counts needed.
# this will be used to sort by to prioritize the smallest number of orders possible
for index, row in df1.iterrows():
    if row['count available'] >= row['count needed']:
        df1.at[index, 'fulfills count needed'] = 'yes'
    else:
        df1.at[index, 'fulfills count needed'] = 'no'

# dont want to calc price based on [count available], so need to check if seller has count i need and calc cost based on [count needed].
# if doesn't have [count needed], then calc cost on [count available].
for index, row in df1.iterrows():
    if row['count available'] >= row['count needed']:
        df1.at[index, 'price x count'] = row['count needed'] * row['price']
    else:
        df1.at[index, 'price x count'] = row['count available'] * row['price']

However, any seller can sell multiple item. I want to minimize how much I pay for shipping, so I want to group the items together by seller. So I grouped them using the .first() method based on what I saw in another thread to keep each column in a new grouped dataframe.

# don't calc [total] until sellers have been grouped
# use first() method to return all columns and perform no other aggregations
grouped1 = df1.sort_values('price').groupby(['seller', 'item']).first()

At this time I want to calculate total through seller. So I have the following code but it calculates total for each item instead of seller which means shipping based on each The number of items in the group is added multiple times, or free shipping minimum free shipping is not applied when price x count ends.

# calc [Total]
for index, row in grouped1.iterrows():
    if (row['Free Shipping Minimum'] == 50) & (row['Price x Count'] > 50):
        grouped1.at[index, 'Total'] = row['Price x Count'] + 0
    elif (row['Free Shipping Minimum'] == 5) & (row['Price x Count'] > 5):
        grouped1.at[index, 'Total'] = row['Price x Count'] + 0
    else:
        grouped1.at[index, 'Total'] = row['Price x Count'] + row['Shipping']

Actually it looks like I might need to sum price x count for each seller when calculating total, but it's essentially the same problem , because I don't know how to calculate each row and column of the external index. What methods can I use to do this?

Also, if anyone has any suggestions on how to achieve the second half of my goals, please feel free to ask. I just want to return every item I need. For example, I need 2 "Project 1" and 2 "Project 2". If "Seller 1" has 2 "Item 1" and 1 "Item 2", and "Seller 2" has 1 "Item 1" and 1 "Item 2", then I want all of "Seller 1" Item (assuming it's the cheapest), but there is only 1 "Item1" for "Seller2". This seems to affect the calculation of the total column, but I'm not sure how to implement it.

Correct answer

I finally decided to group seller first and sum price x count to Find the subtotals, convert them to a dataframe, and then merge the df1 with the new subtotal dataframe to create the groupedphpcnend cphpcn dataframe. I then created the <code>totals column using the np.where suggestion (this is much more elegant than my for loop and handles nan values ​​easily). Finally, group by seller, total, item to return the results I want. The final code is as follows:

import pandas as pd
import numpy as np

item1 = ['item 1', 'item 2', 'item 1', 'item 1', 'item 2']
seller1 = ['Seller 1', 'Seller 2', 'Seller 3', 'Seller 4', 'Seller 1']
price1 = [1.85, 1.94, 2.69, 2.00, 2.02]
shipping1 = [0.99, 0.99, 0.99, 2.99, 0.99]
freeship1 = [5, 5, 5, 50, 5]
countavailable1 = [1, 2, 2, 5, 2]
countneeded1 = [2, 1, 2, 2, 1]

df1 = pd.DataFrame({'Seller':seller1,
                    'Item':item1,
                    'Price':price1,
                    'Shipping':shipping1,
                    'Free Shipping Minimum':freeship1,
                    'Count Available':countavailable1,
                    'Count Needed':countneeded1})

# create columns that states if seller has all counts needed.
# this will be used to sort by to prioritize the smallest number of orders possible
for index, row in df1.iterrows():
    if row['Count Available'] >= row['Count Needed']:
        df1.at[index, 'Fulfills Count Needed'] = 'Yes'
    else:
        df1.at[index, 'Fulfills Count Needed'] = 'No'

# dont want to calc price based on [count available], so need to check if seller has count I need and calc cost based on [count needed].
# if doesn't have [count needed], then calc cost on [count available].
for index, row in df1.iterrows():
    if row['Count Available'] >= row['Count Needed']:
        df1.at[index, 'Price x Count'] = row['Count Needed'] * row['Price']
    else:
        df1.at[index, 'Price x Count'] = row['Count Available'] * row['Price']

# subtotals by seller, then assign calcs to column called [Subtotal] and merge into dataframe
subtotals = df1.groupby(['Seller'])['Price x Count'].sum().reset_index()

subtotals.rename({'Price x Count':'Subtotal'}, axis=1, inplace=True)

grouped = df1.merge(subtotals[['Subtotal', 'Seller']], on='Seller')


# calc [Total]
grouped['Total'] = np.where(grouped['Subtotal'] > grouped['Free Shipping Minimum'],
                             grouped['Subtotal'], grouped['Subtotal'] + grouped['Shipping'])

grouped.groupby(['Seller', 'Total', 'Item']).first()

The above is the detailed content of Calculate sum of each row of external index in multi-index pandas dataframe. For more information, please follow other related articles on the PHP Chinese website!