How to count the number of occurrences in either of two columns
<p>I have a simple looking problem. I have a dataframe <code>df</code> with two columns. For each of the strings that occurs in either of these columns I would like to count the number of rows which has the symbol in either column.</p>

<p>E.g.</p>

<pre><code>g k
a h
c i
j e
d i
i h
b b
d d
i a
d h
</code></pre>

<p>The following code works but is very inefficient.</p>

<pre><code>for elem in set(df.values.flat):
    print elem, len(df.loc[(df[0] == elem) | (df[1] == elem)])


a 2
c 1
b 1
e 1
d 3
g 1
i 4
h 3
k 1
j 1
</code></pre>

<p>This is however very inefficient and my dataframe is large. The inefficiency comes from calling <code>df.loc[(df[0] == elem) | (df[1] == elem)]</code> separately for every distinct symbol in df. </p>

<p>Is there a fast way of doing this?</p>

<p>You can use <code>loc</code> to filter out row level matches from <code>'col2'</code>, append the filtered <code>'col2'</code> values to <code>'col1'</code>, and then call <code>value_counts</code>:</p>

<pre><code>counts = df['col1'].append(df.loc[df['col1'] != df['col2'], 'col2']).value_counts()
</code></pre>

<p>The resulting output:</p>

<pre><code>i    4
d    3
h    3
a    2
j    1
k    1
c    1
g    1
b    1
e    1
</code></pre>

<p>Note: You can add <code>.sort_index()</code> to the end of the counting code if you want the output to appear in alphabetical order.</p>

<p><strong>Timings</strong></p>

<p>Using the following setup to produce a larger sample dataset:</p>

<pre><code>from string import ascii_lowercase

n = 10**5
data = np.random.choice(list(ascii_lowercase), size=(n,2))
df = pd.DataFrame(data, columns=['col1', 'col2'])

def edchum(df):
    vals = np.unique(df.values)
    count = np.maximum(df['col1'].str.get_dummies().reindex_axis(vals, axis=1).fillna(0), df['col2'].str.get_dummies().reindex_axis(vals, axis=1).fillna(0)).sum()
    return count
</code></pre>

<p>I get the following timings:</p>

<pre><code>%timeit df['col1'].append(df.loc[df['col1'] != df['col2'], 'col2']).value_counts()
10 loops, best of 3: 19.7 ms per loop

%timeit edchum(df)
1 loop, best of 3: 3.81 s per loop
</code></pre>

python pandas