Count occurences in DataFrame
<p>I've a Dataframe in this format:</p>

<pre><code>| Department | Person | Power  | ... |
|------------|--------|--------|-----|
| ABC        | 1234   |  75    | ... |
| ABC        | 1235   |  25    | ... |
| DEF        | 1236   |  50    | ... |
| DEF        | 1237   | 100    | ... |
| DEF        | 1238   |  25    | ... |
| DEF        | 1239   |  50    | ... |
</code></pre>

<p>What I now want to get is the sum of occurences for each value in the power column. How can I get this from my DataFrame?</p>

<pre><code>| Department | 100 |  75 |  50 |  25 |
|------------|-----|-----|-----|-----|
| ABC        |   0 |   1 |   0 |   1 |
| DEF        |   1 |   0 |   2 |   1 |
</code></pre>

<p>You can use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.value_counts.html" rel="nofollow"><code>value_counts</code></a> with <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.sort_index.html" rel="nofollow"><code>sort_index</code></a>, then generate <code>DataFrame</code> by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.to_frame.html" rel="nofollow"><code>to_frame</code></a> and last transpose by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.T.html" rel="nofollow"><code>T</code></a>:</p>

<pre><code>print (df.Power.value_counts().sort_index(ascending=False).to_frame().T)
       100  75   50   25 
Power    1    1    2    2
</code></pre>

<p>EDIT by comment:</p>

<p>You need <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.crosstab.html" rel="nofollow"><code>crosstab</code></a>:</p>

<pre><code>print (pd.crosstab(df.Department, df.Power).sort_index(axis=1, ascending=False))
Power       100  75   50   25 
Department                    
ABC           0    1    0    1
DEF           1    0    2    1
</code></pre>

<p>Faster another solution with <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.groupby.html" rel="nofollow"><code>groupby</code></a> and <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.unstack.html" rel="nofollow"><code>unstack</code></a>:</p>

<pre><code>print (df.groupby(['Department','Power'])
         .size()
         .unstack(fill_value=0)
         .sort_index(axis=1, ascending=False))

Power       100  75   50   25 
Department                    
ABC           0    1    0    1
DEF           1    0    2    1
</code></pre>

<p>If need <code>groupby</code> by columns <code>Department</code> and <code>Person</code>, add column <code>Person</code> to <code>groupby</code> to second position (thank you <a href="http://stackoverflow.com/questions/39250504/count-occurences-in-dataframe/39250558?noredirect=1#comment65838428_39250558">piRSquared</a>):</p>

<pre><code>print (df.groupby(['Department','Person', 'Power'])
         .size()
         .unstack(fill_value=0)
         .sort_index(axis=1, ascending=False))

Power              100  75   50   25 
Department Person                    
ABC        1234      0    1    0    0
           1235      0    0    0    1
DEF        1236      0    0    1    0
           1237      1    0    0    0
           1238      0    0    0    1
           1239      0    0    1    0
</code></pre>

<p>EDIT1 by comment:</p>

<p>If need add another missing values, use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reindex.html" rel="nofollow"><code>reindex</code></a>:</p>

<pre><code>print (df.groupby(['Department','Power'])
         .size()
         .unstack(fill_value=0)
         .reindex(columns=[100,75,50,25,0], fill_value=0))

Power       100  75   50   25   0  
Department                         
ABC           0    1    0    1    0
DEF           1    0    2    1    0
</code></pre>

python pandas pivot-table reshape crosstab