Python DataFrame рассчитывает себестоимость продаж ⇐ Python

[Гость]

I have 4 table tables for purchase, return purchase, sales, and return sales

• I made merge for all in one data frame
• I made sort by item_Id,date,transaction type (purchase,return purchase,return sales,sales) the result is

date item_Id Type qty price 2023-12-24 2 Purchase 5 10 2023-12-24 2 sales 1 50 2023-12-25 2 return purchase 1 10 2023-12-26 2 Purchase 10 11 2023-12-27 2 return purchase 5 11 2023-12-28 2 sales 5 40 2023-12-20 3 Purchase 100 10 2023-12-21 3 return purchase 10 1 2023-12-22 3 sales 20 100 2023-12-23 3 return sales 5 100
DataFrame

Код: Выделить всё

df= pd.DataFrame({'date': ['2023-12-24','2023-12-24','2023-12-25','2023-12-26','2023-12-27','2023-12-28','2023-12-20','2023-12-21','2023-12-22','2023-12-23'],'item_Id': [2, 2, 2, 2, 2, 2, 3, 3, 3, 3],'Type': ['Purchase','sales','return purchase','Purchase','return purchase','sales','Purchase','return purchase','sales','return sales'],'qty': [5, 1, 1, 10, 5, 5, 100, 10, 20, 5],'price':  [10, 50, 10, 11, 11, 40, 10, 15, 100, 100]}) 

I get qty balance using this code

Код: Выделить всё

df['BalanceQty'] = df['qty'].where((df['Type']=='Purchase') | (df['Type']=='return sales'),  df['qty'] * -1).groupby(df['item_Id']).cumsum() 

My Problem is I need to calculate average Cost balance for every transaction group by Item_Id I try this code but shift() work with old value not new

Код: Выделить всё

df['Cost']  = np.where((df['Type']=='Purchase'),  df.qty  * df.price, np.where((df['Type']=='return purchase'),df.qty  * df.price *-1,0))       df['BalanceCost'] = 0 df['BalanceCost'] = np.where((df['Type']=='Purchase') | (df['Type']=='return purchase'), df['BalanceCost'].shift(1) + df['Cost'], df['BalanceCost'].shift(1)/df['BalanceQty'].shift(1) * df['BalanceQty']) 

So I need to calculate the balance cost and unit cost for every transaction

the final result is

Код: Выделить всё

|date|item_Id|Type|qty|price|Qty Balance|cost|balanceCost|unit Cost| |:---- |:------:|:------:|:------:|:------:|:------:|:------:|:------:| -----:| |2023-12-24|2|Purchase|5|10|5|50|50.000000|10.000000| |2023-12-24|2|sales|1|50|4|0|40.000000|10.000000| |2023-12-25|2|return purchase|1|10|3|-10|30.000000|10.000000| |2023-12-26|2|Purchase|10|11|13|110|140.000000|10.769231| |2023-12-27|2|return purchase|5|11|8|-55|85.000000|10.625000| |2023-12-28|2|sales|5|40|3|0|31.875000|10.625000| |2023-12-20|3|Purchase|100|10|100|1000|1000.000000|10.000000| |2023-12-21|3|return purchase|10|15|90|-150|850.000000|9.444444| |2023-12-22|3|sales|20|100|70|0|661.111111|9.444444| |2023-12-23|3|return sales|5|100|65|0|613.888889|9.444444| 

Equation is

• for balanceCost IF Purchase or return purchase get previous balanceCost + cost
• else get previous balanceCost /previous Qty Balance * current Qty Balance

[*]for unit Cost = balanceCost / Qty Balance

Источник: https://stackoverflow.com/questions/781 ... t-of-sales