在大Pandas中按行分组

Question

我是Python熊猫的新手。 我有过这样的例子

PRODUCT REGION  COUNTRY MEASURE     Month_ID     QTY
P1      West    UK      M1          Mon_1        200
P1      West    UK      M2          Mon_1        150
P1      East    JAPAN   M1          Mon_1        100
P1      East    JAPAN   M2          Mon_1        100
P1      West    UK      M1          Mon_2        300
P1      West    UK      M2          Mon_2        450
P1      East    JAPAN   M1          Mon_2        500
P1      East    JAPAN   M2          Mon_2        600

我需要以下数据：

PRODUCT REGION  COUNTRY MEASURE     Month_ID     QTY
P1      West    UK      M1          Mon_1        200
P1      West    UK      M2          Mon_1        150
P1      West    UK      NEW_M       Mon_1        350
P1      East    JAPAN   M1          Mon_1        100
P1      East    JAPAN   M2          Mon_1        100
P1      East    JAPAN   NEW_M       Mon_1        200
P1      West    UK      M1          Mon_2        300
P1      West    UK      M2          Mon_2        450
P1      West    UK      NEW_M       Mon_2        750
P1      East    JAPAN   M1          Mon_2        500
P1      East    JAPAN   M2          Mon_2        600
P1      East    JAPAN   NEW_M       Mon_2        1100

我想按列(PRODUCT, REGION, COUNTRY, Month_ID)和SUM(QTY)分组

并在每个组后面添加新行，列MEASURE为NEW_M

Answer 1

您可以通过聚合sum创建新的数据帧，然后为正确排序，使用^{}添加最后一个重复索引，因此在^{}之后为每个组后面的新行添加^{}：

cols = ['PRODUCT', 'REGION', 'COUNTRY', 'Month_ID']
idx = df.index[df.duplicated(cols)]
df1 = (df.groupby(cols, as_index=False, sort=False)['QTY']
         .sum()
         .assign(MEASURE = 'NEW_M')
         .set_index(idx))

df = pd.concat([df, df1], sort=False).sort_index(kind='mergesort').reset_index(drop=True)
print (df)
   PRODUCT REGION COUNTRY MEASURE Month_ID   QTY
0       P1   West      UK      M1    Mon_1   200
1       P1   West      UK      M2    Mon_1   150
2       P1   West      UK   NEW_M    Mon_1   350
3       P1   East   JAPAN      M1    Mon_1   100
4       P1   East   JAPAN      M2    Mon_1   100
5       P1   East   JAPAN   NEW_M    Mon_1   200
6       P1   West      UK      M1    Mon_2   300
7       P1   West      UK      M2    Mon_2   450
8       P1   West      UK   NEW_M    Mon_2   750
9       P1   East   JAPAN      M1    Mon_2   500
10      P1   East   JAPAN      M2    Mon_2   600
11      P1   East   JAPAN   NEW_M    Mon_2  1100

编辑：对于减法，使用小技巧-在MEASURE中带有M2的QTY的值是-1的倍数，因此如果聚合sum得到差异：

#if need only `M1` and `M2` rows
df = df[df['MEASURE'].isin(['M1','M2'])]
cols = ['PRODUCT', 'REGION', 'COUNTRY', 'Month_ID']
idx = df.index[df.duplicated(cols)]

df1 = (df.assign(QTY=df['QTY'].mask(df['MEASURE'].eq('M2'),df['QTY'] * -1))
          .groupby(cols, as_index=False, sort=False)['QTY']
         .sum()
         .assign(MEASURE = 'NEW_M')
         .set_index(idx)
         )

df2 = pd.concat([df, df1], sort=False).sort_index(kind='mergesort').reset_index(drop=True)
print (df2)
   PRODUCT REGION COUNTRY MEASURE Month_ID  QTY
0       P1   West      UK      M1    Mon_1  200
1       P1   West      UK      M2    Mon_1  150
2       P1   West      UK   NEW_M    Mon_1   50
3       P1   East   JAPAN      M1    Mon_1  100
4       P1   East   JAPAN      M2    Mon_1  100
5       P1   East   JAPAN   NEW_M    Mon_1    0
6       P1   West      UK      M1    Mon_2  300
7       P1   West      UK      M2    Mon_2  450
8       P1   West      UK   NEW_M    Mon_2 -150
9       P1   East   JAPAN      M1    Mon_2  500
10      P1   East   JAPAN      M2    Mon_2  600
11      P1   East   JAPAN   NEW_M    Mon_2 -100