title: Fast way to count occurrences of all values in a pandas DataFrame
body: <p>Suppose I have the following data:</p>
  
  <pre><code>import pandas as pd
  import numpy as np
  import random
  from string import ascii_uppercase
  
  random.seed(100)
  
  n = 1000000
  
  # Create a bunch of factor data... throw some NaNs in there for good measure
  data = {letter: [random.choice(list(ascii_uppercase) + [np.nan]) for _ in range(n)] for letter in ascii_uppercase}
  
  df = pd.DataFrame(data)
  </code></pre>
  
  <p>I want to <strong>quickly</strong> count the global occurrence of each value in the set of all values in the dataframe.</p>
  
  <p>This works:</p>
  
  <pre><code>from collections import Counter
  c = Counter([v for c in df for v in df[c].fillna(-999)])
  </code></pre>
  
  <p>But is very slow:</p>
  
  <pre><code>%timeit Counter([v for c in df for v in df[c].fillna(-999)])
  1 loop, best of 3: 4.12 s per loop
  </code></pre>
  
  <p>I thought this function might speed things up by using some of pandas's horsepower:</p>
  
  <pre><code>def quick_global_count(df, na_value=-999):
      df = df.fillna(na_value)
      # Get counts of each element for each column in the passed dataframe
      group_bys = {c: df.groupby(c).size() for c in df}
      # Stack each of the Series objects in `group_bys`... This is faster than reducing a bunch of dictionaries by keys
      stacked = pd.concat([v for k, v in group_bys.items()])
      # Call `reset_index()` to access the index column, which indicates the factor level for each column in dataframe
      # Then groupby and sum on that index to get global counts
      global_counts = stacked.reset_index().groupby('index').sum()
      return global_counts
  </code></pre>
  
  <p>It's definitely faster (75% of the time of the previous approach), but there must be something quicker...</p>
  
  <pre><code>%timeit quick_global_count(df)
  10 loops, best of 3: 3.01 s per loop
  </code></pre>
  
  <p>The results of the above two approaches are identical (with a little modification of the results returned by <code>quick_global_count</code>):</p>
  
  <pre><code>dict(c) == quick_global_count(df).to_dict()[0]
  True
  </code></pre>
  
  <p>What's a quicker way of counting global occurrences of values in a dataframe?</p>