Issue with value count in Python

Question

I have a large dataset containing football data and I would like to figure something out. The dataset contains data from a lot of games, and for every game, all the players in the club is mentioned in a column in the format of list. I would like to find out how I can get an output like this

Player     clubs
    Tom     3 
    Car     2 
    Jon     2
    Tex     1

etc.

This is the code I have tried for it, but i get an error: unhashable type: Series

df = pd.DataFrame({'club': ['Bath', 'Bath', 'Bristol', 'Bristol', 'Bristol', 'Swindon'], 
                   'Players': [[ 'Tom', 'Jon', 'Tex'],[ 'Tom', 'Jon', 'Tex'],[ 'Car', 'Snow', 'Tom'], [ 'Car', 'Snow', 'Tom'], [ 'Car', 'Snow', 'Tom'], [ 'Tom',  'Car',  'Jon']]})

tr = df.groupby('club')
trt = pd.Series([bg for bgs in tr.players_as_list for bg in bgs])
trt.value_counts()

Please provide a minimal reproducible example of your problem — Mose Wintner, May 12 '22 at 18:28
Please see [How to make good pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) and [edit] your question to include a [mcve] with sample input and expected output — G. Anderson, May 12 '22 at 18:32

score 2 · Answer 1 · answered May 12 '22 at 19:08

As you have a Series of list, it will be slow to use explode and drop_duplicates.

Here pure python should be more efficient:

from collections import defaultdict
d = defaultdict(set)
for c, l in zip(df['club'], df['Players']):
    for k in l:
        d[k].add(c)
out = pd.Series({k: len(s) for k,s in d.items()}).sort_values(ascending=False)

output:

Tom     3
Jon     2
Car     2
Tex     1
Snow    1
dtype: int64

comparison on 600K rows:

# explode + groupby
605 ms ± 20.1 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

# explode + value_counts
476 ms ± 17.1 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

# pure python
259 ms ± 5.47 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

You are right. Standard python is more efficient!!! (but it's not very elegant :P) — Corralien, May 12 '22 at 19:15
@Corralien Define elegant? I think it is as elegant as the pandas solution ;) — mozway, May 12 '22 at 19:20

score 1 · Answer 2 · answered May 12 '22 at 18:49

You can try explode the Players column then count the unique values of club column in each group

df = (df.explode('Players')
      .groupby('Players')['club'].nunique()
      .to_frame('clubs').reset_index())

# or

df = (df.explode('Players').drop_duplicates(['Players', 'club'])
      .groupby('Players')['club'].count()
      .to_frame('clubs').reset_index())

# or

df = (df.explode('Players')
      .groupby('Players')['club'].unique().apply(len)
      .to_frame('clubs').reset_index())

print(df)

  Players  clubs
0     Car      2
1     Jon      2
2    Snow      1
3     Tex      1
4     Tom      3

How could I order this by the number of clubs? – sqlbik May 12 '22 at 18:54 — sqlbik, May 12 '22 at 18:54
@sqlbik Just add `.sort_values('clubs', ascending=False)`. – Ynjxsjmh May 12 '22 at 18:56 — Ynjxsjmh, May 12 '22 at 18:56

score 1 · Answer 3 · answered May 12 '22 at 19:06

You can use value_counts instead of groupby after explode your dataframe:

out = (df.explode('Players').drop_duplicates(['Players', 'club'])
         .value_counts('Players').rename('clubs').reset_index())
print(out)

# Output
  Players  clubs
0     Tom      3
1     Car      2
2     Jon      2
3    Snow      1
4     Tex      1

Issue with value count in Python

3 Answers3