从where子句创建列并从分类值获取平均值

Question

以下是我的数据帧的前五行：

City    Edition NOC Medal
Athens  1896    HUN Gold
Athens  1896    AUT Silver
Athens  1896    GRE Bronze
Athens  1896    GRE Gold
Athens  1896    GRE Silver

我想创建一个新表，该表将按NOC分组，其他两列将为Average Before 1996，另一列将根据Edition列为Average After 1996。它看起来像这样（所有值都是占位符）：

NOC Average Before 1996  Average After 1996
USA     30              40
URS     25              30
GBR     50              20

我的困难在于我可以为每个国家创建一个总数：

total_medal_count = olympics_df.groupby('NOC')\
                               .Medal.count()\
                               .reset_index(name="Medal_Count")\
                               .sort_values("Medal_Count", ascending=False)

NOC Medal_Count
USA 4334
URS 2049
GBR 1594

但是，我无法获得Edition列中特定值的平均值

我尝试了以下方法：

total_medal_count['Before 1996'] = np.mean(total_medal_count.Medal_Count).where(olympics_df['Edition'] < 1996)

但这不起作用，因为我无法从均值中提取where。在np.mean函数中引用一个数据帧，在where函数中引用另一个数据帧，我可能也会遇到问题

Answer 1

这就是你需要的吗

观察：我在测试中添加了一行（第二行）

import pandas as pd

data = [
 ['Athens',  1896,    'HUN', 'Gold']
  ,['Athens',  1000,    'HUN', 'Gold']
,['Athens',  1997,    'HUN', 'Gold']
,['Athens',  1896,    'AUT', 'Silver']
,['Athens',  1896,    'GRE', 'Bronze']
,['Athens',  1896,    'GRE', 'Gold']
,['Athens',  1896,    'GRE', 'Silver']
]

#Create dataframe
df = pd.DataFrame(data, columns=['City','Edition','NOC','Medal'])

aux_df = df.groupby(by ='NOC')['Edition'].mean().reset_index();

aux_df['Average Before 1996'] = aux_df['NOC'].apply(lambda x: df[(df.NOC == x) & (df.Edition<=1996)].groupby(by ='NOC')['Edition'].mean().reset_index()['Edition'].sum())
aux_df['Average After 1996'] = aux_df['NOC'].apply(lambda x: df[(df.NOC == x) & (df.Edition>1996)].groupby(by ='NOC')['Edition'].mean().reset_index()['Edition'].sum())

aux_df['Count Before 1996'] = aux_df['NOC'].apply(lambda x: df[(df.NOC == x) & (df.Edition<=1996)].groupby(by ='NOC')['Medal'].count().reset_index()['Medal'].sum())
aux_df['Count After 1996'] = aux_df['NOC'].apply(lambda x: df[(df.NOC == x) & (df.Edition>1996)].groupby(by ='NOC')['Medal'].count().reset_index()['Medal'].sum())


#print(df.groupby(by ='NOC').mean())

print(aux_df.to_string())