Count the number of Occurrence of Values based on another column
<p>I have a question regarding creating pandas dataframe according to the sum of other column.</p>

<p>For example, I have this dataframe</p>

<pre><code> Country    |    Accident
 England           Car
 England           Car
 England           Car
  USA              Car
  USA              Bike
  USA              Plane
 Germany           Car
 Thailand          Plane
</code></pre>

<p>I want to make another dataframe based on the sum value of all accident  based on the country. We will disregard the type of the accident, while summing them all based on the country.</p>

<p>My desire dataframe would look like this</p>

<pre><code>  Country    |    Sum of Accidents
  England              3
    USA                3
  Germany              1
  Thailand             1
</code></pre>

<p>You can use the <a href="http://pandas.pydata.org/pandas-docs/stable/groupby.html" rel="nofollow"><code>groupby</code></a> method.</p>

<p>Example - </p>

<pre><code>In [36]: df.groupby(["country"]).count().sort_values(["accident"], ascending=False).rename(columns={"accident" : "Sum of accidents"}).reset_index()
Out[36]:
    country  Sum of accidents
0   England                 3
1       USA                 3
2   Germany                 1
3  Thailand                 1
</code></pre>

<p>Explanation - </p>

<pre><code>df.groupby(["country"]).                               # Group by country
    count().                                           # Aggregation function which counts the number of occurences of country
    sort_values(                                       # Sorting it 
        ["accident"],                                  
        ascending=False).        
    rename(columns={"accident" : "Sum of accidents"}). # Renaming the columns
    reset_index()                                      # Resetting the index, it takes the country as the index if you don't do this.
</code></pre>

python pandas