Groupby/Sum in Python Pandas - zero counts not showing ...sometimes
<p><strong>The Background</strong></p>

<p>I have a data set of a <em>simulated</em> population of people. They have the following attributes</p>

<ol>
<li>Age (0-120 years)</li>
<li>Gender (male,female)</li>
<li>Race (white, black, hispanic, asian, other)</li>
</ol>

<p>df.head()</p>

<pre><code>   Age  Race  Gender  in_population
0   32     0       0              1
1   53     0       0              1
2   49     0       1              1
3   12     0       0              1
4   28     0       0              1
</code></pre>

<p>There is another variable that identifies the individual as "In_Population"* which is a boolean variable. I am using groupby in pandas to group the population the possible combinations of the 3 attributes to calculate a table of counts by summing the "In_Population" variable in each possible category of person. </p>

<p>There are 2 genders * 5 races * 121 ages = 1210 total possible groups that every individual in the population will fall under.  </p>

<p>If a particular group of people in a particular year has no members (e.g. 0 year old male 'other'), then I still want that group to show up in my group-by dataframe, but with a zero in the count. This happens correctly in the data sample below (Age = 0, Gender = {0,1}, and Race = 4). There were no 'other' zero year olds in this particular </p>

<pre><code>grouped_obj = df.groupby( ['Age','Gender','Race'] )
groupedAGR  = grouped_obj.sum()
groupedAGR.head(10)

                 in_population
Age Gender Race               
0   0      0                16
           1                 8
           2                63
           3                 5
           4                 0
    1      0                22
           1                 4
           2                64
           3                12
           4                 0
</code></pre>

<p><strong>The issue</strong></p>

<p>This only happens for some of the Age-Gender-Race combinations. 
Sometimes the zero sum groups get skipped entirely. The following is the data for age 45. I was expecting to see 0, indicating that there are no 45 year old male 'other' races in this data set. </p>

<pre><code>&gt;&gt;&gt; groupedAGR.xs( 45, level = 'Age' )
             in_population
Gender Race               
0      0               515
       1                68
       2                40
       3                20
1      0               522
       1                83
       2                48
       3                29
       4                 3
</code></pre>

<p><strong>Notes</strong></p>

<p>*"In_Population"
Basically filters out "newborns" and "immigrants" who are not part of the relevant population when calculating "Mortality Rates"; the deaths in the population happen before immigration and births happen so I exclude them from the calculations. I had a suspicion that this had something to do with it - the zero year olds were showing zero counts but every other age group was not showing anything at all...but that's not the case.</p>

<pre><code>&gt;&gt;&gt; groupedAGR.xs( 88, level = 'Age' )
             in_population
Gender Race               
0      0                52
       2                 1
       3                 0
1      0                62
       1                 3
       2                 5
       3                 3
       4                 1
</code></pre>

<p>There are no 88 year old Asian men in the population, so there's a zero in the category. There are no 88 year old 'other' men in the population either, but they don't show up at all. </p>

<p>EDIT: I added in the code showing how I'm making the group by object in pandas and how I'm summing to find the counts in each group. </p>

<p>Use <code>reindex</code> with a predefined index and <code>fill_value=0</code></p>

<pre><code>ages = np.arange(21, 26)
genders = ['male', 'female']
races = ['white', 'black', 'hispanic', 'asian', 'other']

sim_size = 10000

midx = pd.MultiIndex.from_product([
        ages,
        genders,
        races
    ], names=['Age', 'Gender', 'Race'])

sim_df = pd.DataFrame({
        # I use [1:-1] to explicitly skip some age groups
        'Age': np.random.choice(ages[1:-1], sim_size),
        'Gender': np.random.choice(genders, sim_size),
        'Race': np.random.choice(races, sim_size)
    })
</code></pre>

<p><strong><em>These will have missing age groups</em></strong></p>

<pre><code>counts = sim_df.groupby(sim_df.columns.tolist()).size()
counts.unstack()
</code></pre>

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

<p><strong><em>This fills in missing age groups</em></strong></p>

<pre><code>counts.reindex(midx, fill_value=0).unstack()
</code></pre>

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

python pandas group-by aggregation