如何保留最高绝对值并从具有重复索引的行返回平均值

3 回答

• 1

  使用：

  df1 = df.groupby(df.index)['value'].agg([lambda x: max(x,key=abs), 'mean'])
  
  df1.columns = ['value', 'average']
  
  print (df1)
  
          value   average
  CDE -3.097715 -0.389547
  CZR -3.270018 -3.163365
  LPI -5.701044 -4.086021
  LXU  4.818995  0.513976
  OAS -3.336539 -3.336539
  SWN -4.349304 -0.644670
  

  回复于 2024-04-19T04:07:33+08:00
• 1

  这是一个使用 groupby agg 的解决方案，它有两个函数，一个用于计算绝对值的最大值，另一个用于计算平均值：

  def max_abs(x):
      return x.iloc[x.abs().values.argmax()]
  
  res = df.groupby(level=0).agg([max_abs, 'mean'])\
          .xs('value', axis=1, drop_level=True)
  
  print(res)
  
        max_abs      mean
  CDE -3.097715 -0.389547
  CZR -3.270018 -3.163365
  LPI -5.701044 -4.086021
  LXU  4.818995  0.513976
  OAS -3.336539 -3.336539
  SWN -4.349304 -0.644670
  

  回复于 2024-04-19T04:07:33+08:00
• 2

  from io import StringIO
  import pandas as pd
  df = pd.read_fwf(StringIO("""
  cod      value
  CDE   2.318620
  CDE  -3.097715
  LXU  -3.791043
  LXU   4.818995
  SWN   3.059964
  SWN  -4.349304
  OAS  -3.336539
  LPI  -3.037097
  LPI  -5.701044
  LPI  -3.519923
  CZR  -3.270018
  CZR  -3.056712
  """), header=1, Index=None)
  
  # Create a new column with absoulte value
  df['abs_value'] = df['value'].abs()
  
  # Calulate the mean in new data farame, grouped by code using
  # pandas groupped aggregation naming the column average
  df_avg = df.groupby("cod").value.agg([('average', 'mean')])
  
  # Choose the row within group with largest abs value
  df_abs = df.sort_values("abs_value").groupby("cod").tail(1)[["cod", "value"]]
  
  # Join the average and the max
  df_abs.join(df_avg, on="cod")
  

  结果：

  cod     value   average
  1   CDE -3.097715 -0.389547
  10  CZR -3.270018 -3.163365
  6   OAS -3.336539 -3.336539
  5   SWN -4.349304 -0.644670
  3   LXU  4.818995  0.513976
  8   LPI -5.701044 -4.086021
  

  回复于 2024-04-19T04:07:33+08:00