python - 如何在 pandas 中使用 count、groupby 和 max？

Question

我想在以下条件下使用pandas中的sql操作count、groupby、max。

我的数据框 df

call_count    Date       Call Received Appointments
1           5/21/2021     Y                N
2           5/21/2021     Y                N
3           5/21/2021     Y                N
1           5/22/2021     Y                Y
2           5/22/2021     Y                N
1           5/23/2021     N                N

我想按Date下的这些数据进行分组，并计算call_count的最大值，并计算Y和N > 已接电话 和预约。

预期输出为:

Total_call_count Date       Total Received Call Total Appointments
3                5/21/2021     3                  0
2                5/22/2021     2                  1
1                5/23/2021     0                  0

Answer 1

您可以将值 Y 替换为 True，将 N 替换为 False，然后使用 pandas.groupby以及'Call, Received Appointments'

列的总和值

cols = ['Call', 'Received Appointments']
dct = {'Y':True, 'N':False}
df[cols] = df[cols].replace(dct)
df = df.groupby('Date')[['Call', 
                         'Received Appointments']
                       ].sum().rename(columns=lambda x: 'Total '+x)
print(df)

---

           Total Call  Total Received Appointments
Date                                              
5/21/2021           3                            0
5/22/2021           2                            1
5/23/2021           0                            0