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

[Anonymous]

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