计算列表中每个项目在 pandas 数据框列中出现的次数，用逗号分隔值 [英] Count number of times each item in list occurs in a pandas dataframe column with comma separates vales

问题描述

我有一个列表：

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

和具有这些值的熊猫数据框df1

and a pandas Dataframe df1 with these values

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

我想统计数据列表列 city中citylist中每个城市的出现次数：

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

当前我有

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

并计入洛杉矶，芝加哥，纽约作为1个唯一值

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

推荐答案

尝试以下操作：

先修复数据：

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

使用此功能：

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

输出：

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

这篇关于计算列表中每个项目在 pandas 数据框列中出现的次数，用逗号分隔值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！