I have a pandas dataframe called hospitals and there are fields e.g number of employees and city that it is in.
I want to sum up how many hospitals are in each city and store that as a new column or field in the dataframe.
Tried this which doesn't work:
print(df.groupby('city').agg({
'city': pd.Series.nunique
}))
hospital_id name city
0 1 Cassel Hospital London
1 2 Oaklands Hospital Manchester
2 3 Bromley Hospital London
I want it to be like this:
hospital_id name city Count
0 1 Cassel Hospital London 2
1 2 Oaklands Hospital Manchester 1
2 3 Bromley Hospital London 2