How to use count, groupby and max in pandas?

You can replace value Y with True and N with False then use pandas.groupby and sum values of columns '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

You might pass dict to .agg with keys being column names and values being functions or names thereof, consider following simple example

import pandas as pd
df = pd.DataFrame({"user":["A","A","B","C","C"],"calls":[1,3,7,5,1],"appointments":[1,0,1,0,0]})
grouped = df.groupby("user").agg({"calls":"max","appointments":"sum"})
print(grouped)

output

      calls  appointments
user
A         3             1
B         7             1
C         5             0

There's a neat trick you can do with .agg, unpacking a dictionary to apply the aggregations and change the column names.

import pandas as pd

df = pd.DataFrame({
  "call_count": [1, 2, 3, 1, 2, 1],
  "Date": ["5/21/2021", "5/21/2021", "5/21/2021", "5/22/2021", "5/22/2021", "5/23/2021"],
  "Call Recieved": ["Y", "Y", "Y", "Y", "Y", "N"],
  "Appointments": ["N", "N", "N", "Y", "N", "N"]
})

df[["Call Recieved", "Appointments"]] = df[["Call Recieved", "Appointments"]].replace({'Y': 1, 'N': 0})

grouped_df = df.groupby("Date", as_index=False).agg(**{"total_call_count": ("call_count", "sum"), 
                                                      "Total Recieved Call": ("Call Recieved", "sum"), 
                                                      "Total Appointments": ("Appointments", "sum")})

Which gives the expected result:

        Date  total_call_count  Total Recieved Call  Total Appointments
0  5/21/2021                 6                    3                   0
1  5/22/2021                 3                    2                   1
2  5/23/2021                 1                    0                   0

If you don't want to change Y -> 1 and N -> 0, you can use an anonymous function as shown in ko3's answer:

df = pd.DataFrame({
  "call_count": [1, 2, 3, 1, 2, 1],
  "Date": ["5/21/2021", "5/21/2021", "5/21/2021", "5/22/2021", "5/22/2021", "5/23/2021"],
  "Call Recieved": ["Y", "Y", "Y", "Y", "Y", "N"],
  "Appointments": ["N", "N", "N", "Y", "N", "N"]
})

grouped_df = df.groupby("Date", as_index=False).agg(**{"total_call_count": ("call_count", "sum"), 
                                                      "Total Recieved Call": ("Call Recieved", lambda x: (x=="Y").sum()), 
                                                      "Total Appointments": ("Appointments", lambda x: (x=="Y").sum())})

You can just write a function that counts the number of Y values in a series. Then you can group by Date and apply this function to you serieses 'Call' and 'Received Appointments'. Simultaneously, just aggregate call_count by the max function.

Code:

import pandas as pd

df = pd.DataFrame({
    "call_count": [1, 2, 3, 1, 2, 1],
    "Date": ["5/21/2021", "5/21/2021", "5/21/2021", "5/22/2021", "5/22/2021", "5/23/2021"],
    "Call Received": ["Y", "Y", "Y", "Y", "Y", "N"],
    "Appointments": ["N", "N", "N", "Y", "N", "N"]
})

aggfunc = lambda x: (x=="Y").sum()
(
    df.groupby(["Date"], as_index=False)
    .agg({"call_count": max, "Call Received": aggfunc, "Appointments": aggfunc})
    .rename(columns={"call_count": "Total_call_count", "Call Received": "Total Received Call", "Appointments": "Total Appointments"})
    .iloc[:, [1, 0] + [i for i in range(2, df.shape[1])]]
)

Output: