CODEWITHSUNDEEP
pythonpandasdataframe
Michael Adwijk
Michael Adwijk

Reputation: 35

Pandas apply a function to a column while the condition is valid - deeper version

There is a dataframe let's say:

  Product NewNetProfitMargin  Certain Value   Cost    Price
0       A       50               10              10      20
1       B       12               40              5       17
2       C       13               20              6       12

I would like to apply a function to the price column. Something like this :

def update_price(df):
  while df["New Net Profit Margin"] < df["Certain Value"]:
     df["New Price"] = df["New Total Cost"] + df["Certain Value"] * df["Purchase Price"]
     update_columns() # This function updates the profit margin and cost depended on new price.


df["Price"] = df.apply(update_price,axis=1)

So basically, if new net profit margin of a product is lower than a certain value, the function has to update the price until net profit margin is greater than the certain value.

The problem that I am facing is an infinite loop. There seems to be df["Price"] is not updating for each iteration. Could be because of not returning the value after calculation but I have no idea how to do it.

The actual dataset is complex but I tried to simplified. Hope it is easy to understand.

Here some additional details:

def update_columns():
  df["New Comission Amount"] = df.apply(new_commission_amount,axis=1).astype(float)
  df["New Total Cost"] = df.apply(new_total_cost,axis=1).astype(float)
  df["New Net Profit"] = df.apply(new_net_profit,axis=1).astype(float)
  df["New Net Profit Margin"] = df.apply(new_net_profit_margin,axis=1).astype(float)
  print("Columns updated succesfully!")




def new_commission_amount(df):
  
   return df['New Price'] * df['Comission Rate'] 



def new_total_cost(df):
  
  return df['Purchase Price'] + df['New Comission Amount'] + df['Shipping Cost']



def new_net_profit(df):
  return df["New Price"] - df["New Total Cost"]


def new_net_profit_margin(df):
  
  return df["New Net Profit"] / df["Purchase Price"]

Note: update_columns() function uses current columns and adds new results to the end of the dataframe as new columns.

Most of the time many retailers put their prices by hand. I am trying to prevent if someone puts very low numbers, I will calculate a new price according to a rate and correct it. So they don't lose money

Upvotes: 2

Views: 93

Answers (2)

jezrael
jezrael

Reputation: 863226

Because working with arrays, instead while set new values by mask, also for apply function for all DataFrame is used DataFrame.pipe:

EDIT:

def new_func(df):
     return df['Price'] * df['Cost'] 


def update_columns(df):
  df["New Amount"] = df.apply(new_func,axis=1).astype(float)
  print("Columns updated succesfully!")
  return df


def update_price(df):
   
    df['Price'] = df['Price']*df['Certain Value'] + df['Cost']
    # This function updates the profit margin and cost depended on new price.
    df = df.pipe(update_columns) 
    return df

Returned updated columns and also new columns:

mask =  df["New Net Profit Margin"] < df["Certain Value"]
df1 = df[mask].copy().pipe(update_price)
print (df1)
  Product  New Net Profit Margin  Certain Value  Cost  Price  New Amount
1       B                     12             40     5    685      3425.0
2       C                     13             20     6    246      1476.0

Add new columns filled by missing values by DataFrame.reindex:

df = df.reindex(df1.columns, axis=1)
print (df)
  Product  New Net Profit Margin  Certain Value  Cost  Price  New Amount
0       A                     50             10    10     20         NaN
1       B                     12             40     5     17         NaN
2       C                     13             20     6     12         NaN

Last update by DataFrame.update:

df.update(df1)
print (df)
  Product  New Net Profit Margin  Certain Value  Cost  Price  New Amount
0       A                   50.0           10.0  10.0   20.0         NaN
1       B                   12.0           40.0   5.0  685.0      3425.0
2       C                   13.0           20.0   6.0  246.0      1476.0

Upvotes: 3

ckdl1
ckdl1

Reputation: 1

May be you can do both updates in 1 function like this:

mask  = df['Net Profit Margin']<df['Certain value']
def update_columns(df):
    df['Price'] = df['Price']*df['Certain value'] + df['Cost']
# This updates the profit margin and cost depended on new price.
    df['Net Profit Margin'] = ...
    df['Cost'] = ...
    return df
df[mask] = update_price(df[mask])

Upvotes: 0

Related Questions