pandas 如何对两个 Dataframe 求平均值

bqucvtff  于 2023-01-07  发布在  其他
关注(0)|答案(2)|浏览(283)

我有两个 Dataframe :

  1. {'id': {4: 1548638, 6: 1953603, 7: 1956216, 8: 1962245, 9: 1981386, 10: 1981773, 11: 2004787, 13: 2017418, 14: 2020989, 15: 2045043}, 'total': {4: 17, 6: 38, 7: 59, 8: 40, 9: 40, 10: 40, 11: 80, 13: 44, 14: 51, 15: 46}}
  2. {'id': {4: 1548638, 6: 1953603, 7: 1956216, 8: 1962245, 9: 1981386, 10: 1981773, 11: 2004787, 13: 2017418, 14: 2020989, 15: 2045043}, 'total': {4: 17, 6: 38, 7: 59, 8: 40, 9: 40, 10: 40, 11: 80, 13: 44, 14: 51, 15: 46}}

对于存在于两个 Dataframe 中的每个"id",我想计算它们在"total"中的平均值,并将其放在一个新的 Dataframe 中。
我试过:

  1. pd.merge(df1, df2, on="id")

希望我能做到

  1. merged_df[['total']].mean(axis=1)

但根本不管用。
你怎么能这么做?

yquaqz18

yquaqz181#

您可以使用:

  1. df1.merge(df2, on='id').set_index('id').mean(axis=1).reset_index(name='total')

或者,如果有许多列,则使用更通用的方法:

  1. (df1.merge(df2, on='id', suffixes=(None, '_other')).set_index('id')
  2. .rename(columns=lambda x: x.removesuffix('_other')) # requires python 3.9+
  3. .groupby(axis=1, level=0)
  4. .mean().reset_index()
  5. )

输出:

  1. id total
  2. 0 1548638 17.0
  3. 1 1953603 38.0
  4. 2 1956216 59.0
  5. 3 1962245 40.0
  6. 4 1981386 40.0
  7. 5 1981773 40.0
  8. 6 2004787 80.0
  9. 7 2017418 44.0
  10. 8 2020989 51.0
  11. 9 2045043 46.0
展开查看全部
atmip9wb

atmip9wb2#

你可以做如下:

  1. df1 = pd.DataFrame({'id': {4: 1548638, 6: 1953603, 7: 1956216, 8: 1962245, 9: 1981386, 10: 1981773, 11: 2004787, 13: 2017418, 14: 2020989, 15: 2045043}, 'total': {4: 17, 6: 38, 7: 59, 8: 40, 9: 40, 10: 40, 11: 80, 13: 44, 14: 51, 15: 46}})
  2. df2 = pd.DataFrame({'id': {4: 1548638, 6: 1953603, 7: 1956216, 8: 1962245, 9: 1981386, 10: 1981773, 11: 2004787, 13: 2017418, 14: 2020989, 15: 2045043}, 'total': {4: 17, 6: 38, 7: 59, 8: 40, 9: 40, 10: 40, 11: 80, 13: 44, 14: 51, 15: 46}})
  3. merged_df = df1.merge(df2, on='id')
  4. merged_df['total_mean'] = merged_df.filter(regex='total').mean(axis=1)
  5. print(merged_df)

输出:

  1. id total_x total_y total_mean
  2. 0 1548638 17 17 17.0
  3. 1 1953603 38 38 38.0
  4. 2 1956216 59 59 59.0
  5. 3 1962245 40 40 40.0
  6. 4 1981386 40 40 40.0
  7. 5 1981773 40 40 40.0
  8. 6 2004787 80 80 80.0
  9. 7 2017418 44 44 44.0
  10. 8 2020989 51 51 51.0
  11. 9 2045043 46 46 46.0
展开查看全部

相关问题