在python中为组保留值

Question

我有一个如下所示的数据

country item    week    stock   FCST
A        1       1       105    3
A        1       2       105    6
A        1       3       105    9
A        1       4       105    4
A        1       5       105    7
A        1       6       105    4
A        1       7       105    7

我希望执行的任务是将本周的期末存货指定为下周的期初存货。在上表中，我的库存量一开始是105，根据预测（fcst列），它正在减少，并指定为同一周的期末库存。现在收盘价应该变成下周的开盘价

我已经在SAS中使用retain语句完成了同样的操作。我不知道如何在python中复制相同的内容

还要注意，此操作将针对每个国家/地区项目组合执行。（我们不能总是将价值转换为期初库存，因为新项目可能有不同的当前库存）

有人能帮我吗

我的输出应该如下表所示

country item    week    stock   FCST    OPENING_STOCK   CLOSING_STK
A        1       1       105      3          105           102
A        1       2       105      6          102            96
A        1       3       105      9           96            87
A        1       4       105      4           87            83
A        1       5       105      7           83            76
A        1       6       105      4           76            72
A        1       7       105      7           72            65

请点击上面的链接查看所需的输出

提前谢谢

Answer 1

现在它起作用了。 首先使用combine_填充df开口中的间隙

import pandas as pd

df = pd.DataFrame({
'country': {0: 'A', 1: 'A', 2: 'A', 3: 'A', 4: 'A', 5: 'A', 6: 'A', 7: 'A', 8: 'A', 9: 'A', 10: 'A', 11: 'A', 12: 'A', 13: 'B', 14: 'B', 15: 'B', 16: 'B', 17: 'B', 18: 'B', 19: 'B', 20: 'B', 21: 'B', 22: 'B'}, 
'item': {0: 1, 1: 1, 2: 1, 3: 1, 4: 1, 5: 1, 6: 1, 7: 2, 8: 1, 9: 2, 10: 2, 11: 1, 12: 1, 13: 1, 14: 1, 15: 1, 16: 2, 17: 3, 18: 2, 19: 1, 20: 2, 21: 1, 22: 3},
'week': {0: 1, 1: 2, 2: 3, 3: 4, 4: 5, 5: 6, 6: 7, 7: 7, 8: 8, 9: 8, 10: 9, 11: 9, 12: 10, 13: 1, 14: 2, 15: 3, 16: 3, 17: 3, 18: 4, 19: 4, 20: 5, 21: 5, 22: 5},
'stock': {0: 105, 1: 105, 2: 105, 3: 105, 4: 105, 5: 105, 6: 105, 7: 94, 8: 105, 9: 94, 10: 94, 11: 105, 12: 105, 13: 100, 14: 100, 15: 100, 16: 200, 17: 300, 18: 200, 19: 100, 20: 200, 21: 100, 22: 300},
'FCST': {0: 3, 1: 6, 2: 9, 3: 4, 4: 7, 5: 4, 6: 7, 7: 2, 8: 1, 9: -5, 10: 2, 11: 8, 12: 6, 13: 2, 14: 6, 15: 8, 16: 3, 17: 7, 18: 8, 19: 9, 20: 3, 21: 5, 22: 6}
})

df_new = pd.DataFrame(columns=df.columns)
groups = df.groupby(["country", "item"])
df["closing"] = df.stock - groups.FCST.cumsum()
df["opening"] = groups.closing.shift(1)
df["opening"] = df["opening"].combine_first(df.stock)

产出：

   country  item  week  stock  FCST  closing  opening
0        A     1     1    105     3      102    105.0
1        A     1     2    105     6       96    102.0
2        A     1     3    105     9       87     96.0
3        A     1     4    105     4       83     87.0
4        A     1     5    105     7       76     83.0
5        A     1     6    105     4       72     76.0
6        A     1     7    105     7       65     72.0
7        A     2     7     94     2       92     94.0
8        A     1     8    105     1       64     65.0
9        A     2     8     94    -5       97     92.0
10       A     2     9     94     2       95     97.0
11       A     1     9    105     8       56     64.0
12       A     1    10    105     6       50     56.0
13       B     1     1    100     2       98    100.0
14       B     1     2    100     6       92     98.0
15       B     1     3    100     8       84     92.0
16       B     2     3    200     3      197    200.0
17       B     3     3    300     7      293    300.0
18       B     2     4    200     8      189    197.0
19       B     1     4    100     9       75     84.0
20       B     2     5    200     3      186    189.0
21       B     1     5    100     5       70     75.0
22       B     3     5    300     6      287    293.0

Answer 2

我用来解决这个问题的代码粘贴在下面

    df.sort_values(by=['ITM_CD','Country','WEEK'],inplace=True)
    df['CONCAT']=df['Country']+df['ITM_CD']

#CALCULATE BEGINING STOCK EVERY WEEK
    df['TMP1']=1
    grouper = (df["CONCAT"]!= df["CONCAT"].shift()).cumsum()   
    df["WEEK_NO"] = df.groupby(grouper)['TMP1'].cumsum() 
    df["FCST1"] = df.groupby(grouper)['FCST'].cumsum() 
 
    result = df.CURR_STCK_TOT - df.FCST1
    df["CLOSING"] = result
    df["CLOSING"] = np.where(df["CLOSING"]<0,0,df["CLOSING"])    
    df["OPENING"] = np.where(df["WEEK_NO"]==1,df["STOCK"],result.shift(1))
    df["OPENING"] = np.where(df["OPENING"]<0,0,df["OPENING"])  

我还做了一些额外的操作，比如将所有负值设为0