Count number of times each item in list occurs in a pandas dataframe column with comma separates values with additional aggregation of other columns

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           duration
John    Travis          New York                            a@email.com     5.5
Jim     Perterson       San Francisco, Los Angeles          b@email.com     6.8
Nancy   Travis          Chicago                             b1@email.com    1.2
Jake    Templeton       Los Angeles                         b3@email.com    4.9 
John    Myers           New York                            b4@email.com    1.9
Peter   Johnson         San Francisco, Chicago              b5@email.col    2.3 
Aby     Peters          Los Angeles                         b6@email.com    1.8
Amy     Thomas          San Francisco                       b7@email.col    8.8
Jessica Thompson        Los Angeles, Chicago, New York      b8@email.com    4.2

I want to count the number of times each city from citylist occurs in the dataframe column 'city' (this portion have it working, thanks to @scott-boston for answer in my prior question )

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

Additionally I want to sum by column 'duration' and group by city and calculate percent (sum of duration for group)/(total duration)

city            list    duration    %time
New York        3       11.6        0.31
San Francisco   3       17.9        0.47
Los Angeles     4       17.7        0.47
Chicago         3       7.7         0.20
Miami           0       0           0

Does this answer your question? [Pandas percentage of total with groupby](https://stackoverflow.com/questions/23377108/pandas-percentage-of-total-with-groupby) — G. Anderson, Sep 28 '20 at 22:38

David Erickson · Accepted Answer · 2020-09-29T01:16:42.767

You can explode the dataframe on the city column
Then groupby city and use .agg for some of the calculations.
For the %time, you can create a variable var in the beginning that gets the sum total of the duration column, which will be used later to get the % of total.
Finally, use some list comprehension to include rows for cities in citylist that are not in the dataframe:

citylist = ['New York', 'San Francisco', 'Los Angeles', 'Chicago', 'Miami']
var = df['duration'].sum() #to be used later for %time column calculation
df['city'] = df['city'].str.split(', ') # change from string to list in preparation for explode
df = (df.explode('city')
        .groupby('city').agg({'email' : 'count', 'duration' : 'sum'}).reset_index()
        .rename({'email' : 'list'}, axis=1))
df['%time'] = round(df['duration'] / var, 2)
df = df.append(pd.DataFrame({'city' : [x for x in citylist if x not in df['city'].unique()]})).fillna(0)
df
Out[1]: 
            city  list  duration  %time
0        Chicago   3.0       7.7   0.21
1    Los Angeles   4.0      17.7   0.47
2       New York   3.0      11.6   0.31
3  San Francisco   3.0      17.9   0.48
0          Miami   0.0       0.0   0.00

Solution #2: Per @ScottBoston 's comment, using reindex is more concise and a better method than the list comprehension. You can also see this in his answer here )

citylist = ['New York', 'San Francisco', 'Los Angeles', 'Chicago', 'Miami']
var = df['duration'].sum() #to be used later for %time column calculation
df['city'] = df['city'].str.split(', ') # change from string to list in preparation for explode

df = (df.explode('city')
        .groupby('city').agg({'email' : 'count', 'duration' : 'sum'})
        .rename({'email' : 'list'}, axis=1))
df['%time'] = round(df['duration'] / var, 2)

df.reindex(citylist, fill_value=0).reset_index()

Output:

            city  list  duration  %time
0       New York     3      11.4   0.31
1  San Francisco     3      17.9   0.48
2    Los Angeles     4      17.5   0.47
3        Chicago     3       7.5   0.20
4          Miami     0       0.0   0.00

Maybe, remove the reset_index from the groupby and use reindex(citylist, fill_value=0) then reset_index instead of doing the list comprehension. — Scott Boston, Sep 29 '20 at 01:08
Thanks for editing! I was actually in the process of doing just that, so thanks for doing that. I also checked out your other answer and saw how you did the reindex. — David Erickson, Sep 29 '20 at 01:15

Count number of times each item in list occurs in a pandas dataframe column with comma separates values with additional aggregation of other columns

1 Answers1