Turning table data into columns and counting by frequency
<p>I have a dataframe in the following form:</p>

<p><a href="http://i.stack.imgur.com/Ej9td.png" rel="nofollow"><img src="http://i.stack.imgur.com/Ej9td.png" alt="enter image description here"></a></p>

<p>shape is 2326 x 1271</p>

<p>Column names are just serialized from 0-1269 while rows are categories that could repeat like "apple" in the example.  The internal data points can represent anything (let's say they represent stores in this example) and I'm trying to convert them into columns and having the data points become the number of times that category shows up in that "store".  Visually, here is the table I'm trying to get to:</p>

<p><a href="http://i.stack.imgur.com/2tuZQ.png" rel="nofollow"><img src="http://i.stack.imgur.com/2tuZQ.png" alt="enter image description here"></a></p>

<p>Note that Apple shows up in AA and RR twice</p>

<p>Use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.stack.html" rel="nofollow"><code>stack</code></a> along with <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.crosstab.html" rel="nofollow"><code>crosstab</code></a> to compute the frequency counts:</p>

<p><strong>Data:</strong></p>

<pre><code>index= ['Apple', 'Orange', 'Apple', 'Banana', 'Kiwi']
data = [['AA', 'DD', 'RR', ''], ['DD', 'PP', '', ''], 
        ['AA', 'RR', 'TT', 'SS'], ['EE', 'NN', '',''], ['NN', 'WW','', '']]
frame = pd.DataFrame(data, index, columns=np.arange(4))
frame
</code></pre>

<p><a href="http://i.stack.imgur.com/IXe0D.png" rel="nofollow"><img src="http://i.stack.imgur.com/IXe0D.png" alt="Image"></a></p>

<p><strong>Operations:</strong></p>

<pre><code>df = frame.stack().reset_index(0, name='values')
df = pd.crosstab(df['level_0'], df['values']).drop('', axis=1).replace(0, '')
df.index.name=None; df.columns.name=None
df
</code></pre>

<p><a href="http://i.stack.imgur.com/zorwe.png" rel="nofollow"><img src="http://i.stack.imgur.com/zorwe.png" alt="Image"></a></p>

python pandas reshape