题解 | #统计运动会项目报名人数(二)#

pd.DataFrame.join: Join columns with other DataFrame either on index or on a key column

join(self, other, on=None, how='left', lsuffix='', rsuffix='', sort=False) -> 'DataFrame'

If we want to join using the key columns, we need to set key to be the index in both df and other. The joined DataFrame will have key as its index.

思路：

1. 先根据两个表的 item_id 字段外连接两个表 join(on='item_id', how='outer')（注意到题目要求输出items.csv中所有项目的报名人数，因此使用外连接）
2. 再进行分组聚合统计各个项目的人数 groupby('item_name')['employee_id'].count()

import pandas as pd
signup = pd.read_csv('signup.csv')
items = pd.read_csv('items.csv')
df = items.set_index('item_id').join(signup.set_index('item_id'), 
                                     on='item_id', how='outer')
print(df.groupby('item_name')['name'].count())