Count number of times each item in list occurs in a pandas dataframe column with comma separates vales

Question

I have a list :

citylist = ['New York', 'San Francisco', 'Los Angeles', 'Chicago', 'Miami']

and a pandas Dataframe df1 with these values

first   last            city                                email
John    Travis          New York                            [email protected]
Jim     Perterson       San Franciso, Los Angeles           [email protected]
Nancy   Travis          Chicago                             [email protected]
Jake    Templeton       Los Angeles                         [email protected]
John    Myers           New York                            [email protected]
Peter   Johnson         San Franciso, Chicago               [email protected]
Aby     Peters          Los Angeles                         [email protected]
Amy     Thomas          San Franciso                        [email protected]
Jessica Thompson        Los Angeles, Chicago, New York      [email protected]

I want to count the number of times each city from citylist occurs in the dataframe column 'city':

New York        3       
San Francisco   3
Los Angeles     4
Chicago         3
Miami           0

Currently I have

dftest = df1.groupby(by='city', as_index=False).agg({'id': pd.Series.nunique})

and it ends counting "Los Angeles, Chicago, New York" as 1 unique value

Is there any way to get counts as I have show above? Thanks

Answer 1

Try this:

Fix data first:

df1['city'] = df1['city'].str.replace('Franciso', 'Francisco')

Use this:

(df1['city'].str.split(', ')
            .explode()
            .value_counts(sort=False)
            .reindex(citylist, fill_value=0))

Output:

New York         3
San Francisco    3
Los Angeles      4
Chicago          3
Miami            0
Name: city, dtype: int64