I have a dataframe with information about a stock that looks like this:

Each row represents a purchase/sale of a certain product. Quantity represents the number of units purchased/sold at a given Unit cost. Current stock is the remaining stock after the purchase/sale. For every product, I want to calculate the Weighted Average Cost (WAC) after each sale/purchase. The procedure is the following:

• For the first row of every product, WAC = (Initial stock * Initial unit cost Quantity * Unit cost) / Current stock just if Reference == 'Purch.'. If not, WAC = Initial unit cost.

• For the next rows, WAC[i] = (Current stock[i-1] * WAC[i-1] Quantity[i] * Unit cost[i]) / Current stock[i] just if Reference[i] == 'Purch.'. If not, WAC[i] = WAC[i-1].

The next table shows what I'm looking for (WAC column and how to calculate it):

How would you do it using Pandas? I've tried to use a groupby and a cumsum, but I don't know how to introduce the "if" statement. After that, I want to summarize the information and just get the Product ID along with the final Stock and WAC, just like this:

Thank you in advance!

CodePudding user response：

You may create a function and call it using apply in a groupby dataframe.

I would try something like this

def calc_wac(df_):
  df_ = df_.copy()
  cs_wac = 0
  for counter, row in enumerate(df_.iterrows()):
    idx,row = row
    if counter==0:
      if row['Reference'] == 'Purch.':
        cs_wac  = row['Initial stock'] * row['Initial unit cost']   row['Quantity'] * row['Unit cost']
      else:
        cs_wac  = row['Current stock'] * row['Initial unit cost']
    elif row['Reference'] == 'Purch.':
      cs_wac  = row['Quantity'] * row['Unit cost']
    else:
      cs_wac *= row['Current stock']/df.loc[idx-1,'Current stock']
    df_.loc[idx, 'WAC'] = cs_wac/row['Current stock']
  return pd.Series({'Current stock': row['Current stock'], 'WAC':cs_wac/row['Current stock']})

This should return the summarized information when you call:

df.groupby('Product ID').apply(calc_wac)

If you want the full dataframe just change the function return to the entire dataframe return df_

CodePudding user response：

Hope I understood your question correct.

Code:

#Create new columns using lambda function
df['(how to)WAC']= df.apply(lambda row: (row['Intial stock']*row['Intial unit cost'] row['Quantity']*row['Unit cost'])/row['Current stock'] if row['Reference']=='Purch' else None, axis=1)



#Creating another column WAC, here it will gonna take data from '(how to)WAC' column.
#More, if its None will will take the above value. and if its the first value then it will take from Initial 

df['WAC']  = df.groupby(['Product ID'])['(how to) WAC'].ffill().fillna(df['Initial unit cost'])




#Group by the ID and display the last rows of each
df.groupby('Product ID').tail(1)[['Product ID','Current stock', 'WAC']]#