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>OK this is much trickier than I thought, not sure how this will scale but if you have a lot of repeating values then it will be more efficient than your current method, basically we can use <code>str.get_dummies</code> and reindex the columns from that result to generate a dummies df for all unique values, we can then use <code>np.maximal</code> on the 2 dfs and <code>sum</code> these:</p>

<pre><code>In [77]:
t="""col1 col2
g k
a h
c i
j e
d i
i h
b b
d d
i a
d h"""
df = pd.read_csv(io.StringIO(t), delim_whitespace=True)
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()

Out[77]:
a    2
b    1
c    1
d    3
e    1
g    1
h    3
i    4
j    1
k    1
dtype: float64
</code></pre>

<p>vals here is  just the unique values:</p>

<pre><code>In [80]:
vals = np.unique(df.values)
vals

Out[80]:
array(['a', 'b', 'c', 'd', 'e', 'g', 'h', 'i', 'j', 'k'], dtype=object)
</code></pre>

python pandas