如何分组，直到Pandas中的值不同？

Question

在我获得df_base中所需的所有数据后（为了简单起见，我将不包括这些数据），我希望返回df_product_final，并包含以下列：

• 产品（产品id/密钥）
• 速度
• 特快专递

对于前两列，这不是问题，因为我只是从df_base复制列，并将它们粘贴到df_product_final中

对于SpeedAvg，我需要在df_product_final中插入该产品的平均速度，直到新产品出现在product列中

我的代码：

    df_product_final['Product'] = df_product_total['Product']
    df_product_final['Speed'] = df_base['production'] / df_base['time_production']
    df_product_final=df_product_final.fillna(0)     
    df_product_final['SpeedAvg'] = df_product_final["Speed"].groupby(df_product_final['Product']).mean()     

    df_product_final['newindex'] = df_base['date_key']+df_base['hour']+df_base['minute']
    df_product_final['newindex'] = pd.to_datetime(df_product_final['newindex'], utc=1, format = "%Y%m%d%H%M%S")
    df_product_final.set_index('newindex',inplace=True)
    df_product_final=df_product_final.fillna(0)

df_产品_最终版本：

newindex                  Product        Speed   SpeedAvg 
                                             
2020-10-15 22:00:00+00:00        0    0.000000  52.944285
2020-10-15 23:00:00+00:00        0    0.000000   0.000000
2020-10-16 00:00:00+00:00        0    0.000000   0.000000
2020-10-16 01:00:00+00:00        0    0.000000   0.000000
2020-10-16 02:00:00+00:00        0    0.000000   0.000000
...
2020-10-16 20:00:00+00:00        0    154.000000   0.000000
2020-10-16 21:00:00+00:00        0    150.000000   0.000000

我希望得到以下结果：

newindex                  Product        Speed   SpeedAvg 
                                             
2020-10-15 22:00:00+00:00        0    0.000000  52.944285
2020-10-15 23:00:00+00:00        0    0.000000  52.944285
2020-10-16 00:00:00+00:00        0    0.000000  52.944285
2020-10-16 01:00:00+00:00        0    0.000000  52.944285
...

2020-10-16 20:00:00+00:00        0    154.000000   52.944285
2020-10-16 21:00:00+00:00        0    0.000000   52.944285

为了使事情变得更加复杂，可能会有相同的产品，但会分离一个多小时。 在这种情况下，我的SpeedAvg取决于这些新值，而不是以前的值

例如：

                           Product       Speed   SpeedAvg
newindex                                                 
2020-10-15 22:00:00+00:00        0    0.000000  52.944285
2020-10-15 23:00:00+00:00        0    0.000000  52.944285
2020-10-16 00:00:00+00:00        0    0.000000  52.944285
2020-10-16 01:00:00+00:00        0    0.000000  52.944285
2020-10-16 02:00:00+00:00        1    10.000000  10.000000
2020-10-16 03:00:00+00:00        1    10.000000  10.000000
2020-10-16 04:00:00+00:00        1    10.000000  10.000000
2020-10-16 05:00:00+00:00        1    10.000000  10.000000
2020-10-16 06:00:00+00:00        1    10.000000  10.000000
2020-10-16 07:00:00+00:00        0    0.000000   31.500000
2020-10-16 08:00:00+00:00        0    0.000000   31.500000
2020-10-16 16:00:00+00:00        0  183.000000   31.500000
2020-10-16 17:00:00+00:00        0   69.000000   31.500000
2020-10-16 18:00:00+00:00        0    0.000000   31.500000
2020-10-16 19:00:00+00:00        0    0.000000   31.500000
2020-10-16 20:00:00+00:00        0    0.000000   31.500000
2020-10-16 21:00:00+00:00        0    0.000000   31.500000

如果我不是很全面，我很抱歉，我会提供解决这个问题所需的每一点信息

Answer 1

找到另一个使用分组方式的解决方案。如果这对你有用的话

def _mean(df):
    df['SpeedAvg'] = df['Speed'].mean()
    return df
df_product_final = df_product_final.groupby(df['Product'].ne(df['Product'].shift()).cumsum()).apply(_mean)     

改编自对this post的回答

Answer 2

我想我找到了一个更容易解决问题的方法：

从一个空字典开始，我将df_base的所有键插入其中，如下所示：

    product_keys = {}
    product_keys = df_base['product_key'].drop_duplicates().reset_index(inplace=False, drop=True).to_dict()

生成的字典将类似于：

 {0: 2,
  1: 1,
  2: 31
 }

使用df.apply（）完成此步骤后，我可以迭代dataframe的每一行，使用刚刚创建的字典的键更改产品键的行值：

 df_product_final['Product'] = df_base['product_key']
 df_product_final.apply(
                                self.keys_from_value,
                                dict = product_keys,
                                axis='columns',
                                raw = False,
                                result_type='broadcast',
                            )

self.keys_from_值：

 def keys_from_value(self, row, dict):
    if row is None:
        return
    else:
        row['Product'] = list(dict.keys())[list(dict.values()).index(row['Product'])]
        return row

最后一步是计算并在数据帧内插入正确的SpeedAvg（这很容易：第一个循环是基于刚刚修改的行获取列组id；第二个循环是基于组id插入SpeedAvg）：

 gid = 0
for i, row in df_base.iterrows():
    if row['diff'] != 0:
        gid += 1
    df_base.at[i,'group_id'] = gid

avg = df_product_final["Speed"].groupby(df_base['group_id']).mean()
#avg is a Pandas Series of all the SpeedAvg based on their position relative to #the list
for i, row in df_product_final.iterrows():
    
    for row_avg in avg.index.values.tolist():
        if row.at['group_id'] == row_avg:   
            df_product_final.at[i,'SpeedAvg'] = avg[row_avg]

这是经过以下步骤后生成的数据帧（df_product_final）：

                           Product       Speed    SpeedAvg
newindex                                                  
2020-10-20 09:00:00+00:00        0    0.000000    0.000000
2020-10-20 09:00:00+00:00        1    0.000000  104.528338
2020-10-20 10:00:00+00:00        1    0.000000  104.528338
2020-10-20 11:00:00+00:00        1    0.000000  104.528338
2020-10-20 12:00:00+00:00        1   68.375000  104.528338
2020-10-20 13:00:00+00:00        1  188.074074  104.528338
2020-10-20 14:00:00+00:00        1  172.192982  104.528338
2020-10-20 15:00:00+00:00        1  162.553571  104.528338
2020-10-20 16:00:00+00:00        1  178.867925  104.528338
2020-10-20 17:00:00+00:00        1  181.844828  104.528338
2020-10-20 18:00:00+00:00        1   93.375000  104.528338
2020-10-19 20:00:00+00:00        0    0.000000    0.000000
2020-10-19 21:00:00+00:00        0    0.000000    0.000000
2020-10-19 22:00:00+00:00        0    0.000000    0.000000
2020-10-19 23:00:00+00:00        0    0.000000    0.000000
2020-10-20 00:00:00+00:00        0    0.000000    0.000000
2020-10-20 01:00:00+00:00        0    0.000000    0.000000
2020-10-20 02:00:00+00:00        0    0.000000    0.000000
2020-10-20 03:00:00+00:00        0    0.000000    0.000000
2020-10-20 04:00:00+00:00        0    0.000000    0.000000
2020-10-20 05:00:00+00:00        0    0.000000    0.000000
2020-10-20 06:00:00+00:00        0    0.000000    0.000000
2020-10-20 07:00:00+00:00        0    0.000000    0.000000
2020-10-20 08:00:00+00:00        0    0.000000    0.000000
2020-10-20 09:00:00+00:00        2    0.000000   95.025762
2020-10-20 10:00:00+00:00        2    0.000000   95.025762
2020-10-20 11:00:00+00:00        2    0.000000   95.025762
2020-10-20 12:00:00+00:00        2   68.375000   95.025762
2020-10-20 13:00:00+00:00        2  188.074074   95.025762
2020-10-20 14:00:00+00:00        2  172.192982   95.025762
2020-10-20 15:00:00+00:00        2  162.553571   95.025762
2020-10-20 16:00:00+00:00        2  178.867925   95.025762
2020-10-20 17:00:00+00:00        2  181.844828   95.025762
2020-10-20 18:00:00+00:00        2   93.375000   95.025762
2020-10-20 19:00:00+00:00        2    0.000000   95.025762