Loop over groups Pandas Dataframe and get sum/count
<p>I am using Pandas to structure and process Data.
This is my DataFrame:</p>

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

<p>And this is the code which enabled me to get this DataFrame:</p>

<pre><code>(data[['time_bucket', 'beginning_time', 'bitrate', 2, 3]].groupby(['time_bucket', 'beginning_time', 2, 3])).aggregate(np.mean)
</code></pre>

<p>Now I want to have the sum (Ideally, the sum and the count) of my 'bitrates' grouped in the same time_bucket. For example, for the first time_bucket((2016-07-08 02:00:00, 2016-07-08 02:05:00), it must be 93750000 as sum and 25 as count, for all the case 'bitrate'.</p>

<p>I did this :</p>

<pre><code>data[['time_bucket', 'bitrate']].groupby(['time_bucket']).agg(['sum', 'count'])
</code></pre>

<p>And this is the result :</p>

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

<p>But I really want to have all my data in one DataFrame.</p>

<p>Can I do a simple loop over 'time_bucket' and apply a function which calculate the sum of all bitrates ?
Any ideas ? Thx !</p>

<p>I think you need <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.merge.html" rel="nofollow"><code>merge</code></a>, but need same levels of <code>indexes</code> of both <code>DataFrames</code>, so use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reset_index.html" rel="nofollow"><code>reset_index</code></a>. Last get original <code>Multiindex</code> by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.set_index.html" rel="nofollow"><code>set_index</code></a>:</p>

<pre><code>data = pd.DataFrame({'A':[1,1,1,1,1,1],
                     'B':[4,4,4,5,5,5],
                     'C':[3,3,3,1,1,1],
                     'D':[1,3,1,3,1,3],
                     'E':[5,3,6,5,7,1]})

print (data)
   A  B  C  D  E
0  1  4  3  1  5
1  1  4  3  3  3
2  1  4  3  1  6
3  1  5  1  3  5
4  1  5  1  1  7
5  1  5  1  3  1
</code></pre>



<pre><code>df1 = data[['A', 'B', 'C', 'D','E']].groupby(['A', 'B', 'C', 'D']).aggregate(np.mean)
print (df1)
           E
A B C D     
1 4 3 1  5.5
      3  3.0
  5 1 1  7.0
      3  3.0

df2 = data[['A', 'C']].groupby(['A'])['C'].agg(['sum', 'count'])
print (df2)
   sum  count
A            
1   12      6

print (pd.merge(df1.reset_index(['B','C','D']), df2, left_index=True, right_index=True)
         .set_index(['B','C','D'], append=True))

           E  sum  count
A B C D                 
1 4 3 1  5.5   12      6
      3  3.0   12      6
  5 1 1  7.0   12      6
      3  3.0   12      6
</code></pre>

<p>I try another solution to get output from <code>df1</code>, but this is aggregated so it is impossible get right data. If sum level <code>C</code>, you get <code>8</code> instead <code>12</code>.</p>

python pandas dataframe count sum