Pandas groupby - grouping users and counting the type of subscription
<p>I'm attempting to use pandas to group members, to count the number of subscription types that a member has purchased and get a total spent per member. Once loaded the data resembles:</p>

<pre><code>df = 

Member Nbr  Member Name-First   Member Name-Last        Date-Joined             Member Type         Amount  Addr-Formatted  Date-Birth              Gender      Status    
1           Aboud               Tordon                  2010-03-31 00:00:00     1 Year Membership   331.00  ADDRESS_1       1972-08-01 00:00:00     Male        Active  
1           Aboud               Tordon                  2011-04-16 00:00:00     1 Year Membership   334.70  ADDRESS_1       1972-08-01 00:00:00     Male        Active  
1           Aboud               Tordon                  2012-08-06 00:00:00     1 Year Membership   344.34  ADDRESS_1       1972-08-01 00:00:00     Male        Active  
1           Aboud               Tordon                  2013-08-21 00:00:00     1 Year Membership   362.53  ADDRESS_1       1972-08-01 00:00:00     Male        Active  
1           Aboud               Tordon                  2015-08-31 00:00:00     1 Year Membership   289.47  ADDRESS_1       1972-08-01 00:00:00     Male        Active  

2          Jean                 Manuel                  2012-12-10 00:00:00     4 Month Membership  148.79  ADDRESS_2       1984-08-01 00:00:00     Male        In-Active   
2          Jean                 Manuel                  2013-03-13 00:00:00     1 Year Membership   348.46  ADDRESS_2       1984-08-01 00:00:00     Male        In-Active
2          Jean                 Manuel                  2014-03-15 00:00:00     1 Year Membership   316.86  ADDRESS_2       1984-08-01 00:00:00     Male        In-Active   

3          Val                  Adams                   2010-02-09 00:00:00     1 Year Membership   333.25  ADDRESS_3       1934-10-26 00:00:00     Female      Active  
3          Val                  Adams                   2011-03-09 00:00:00     1 Year Membership   333.88  ADDRESS_3       1934-10-26 00:00:00     Female      Active
3          Val                  Adams                   2012-04-03 00:00:00     1 Year Membership   318.34  ADDRESS_3       1934-10-26 00:00:00     Female      Active
3          Val                  Adams                   2013-04-15 00:00:00     1 Year Membership   350.73  ADDRESS_3       1934-10-26 00:00:00     Female      Active  
3          Val                  Adams                   2014-04-19 00:00:00     1 Year Membership   291.63  ADDRESS_3       1934-10-26 00:00:00     Female      Active  
3          Val                  Adams                   2015-04-19 00:00:00     1 Year Membership   247.35  ADDRESS_3       1934-10-26 00:00:00     Female      Active

5          Michele              Younes                  2010-02-14 00:00:00     1 Year Membership   333.25  ADDRESS_4       1933-06-23 00:00:00     Female      In-Active   
5          Michele              Younes                  2011-05-23 00:00:00     1 Year Membership   317.77  ADDRESS_4       1933-06-23 00:00:00     Female      In-Active   
5          Michele              Younes                  2012-05-28 00:00:00     1 Year Membership   328.16  ADDRESS_4       1933-06-23 00:00:00     Female      In-Active   
5          Michele              Younes                  2013-05-31 00:00:00     1 Year Membership   360.02  ADDRESS_4       1933-06-23 00:00:00     Female      In-Active

7          Adam                 Herzburg                2010-07-11 00:00:00     1 Year Membership   335 48  ADDRESS_5       1987-08-30 00:00:00     Male        In-Active
...
</code></pre>

<p>Since the most popular <code>Member Type</code> are <code>1 Month</code>, <code>3 Month</code>, <code>4 Month</code>, <code>6 Month</code>, and <code>1 Year</code> I would like to make a column counting the number of those <code>Member Type</code> that a given member has purchased. </p>

<p>There are also <code>2 Month</code>, <code>5 Month</code>, <code>7 Month</code>, <code>8 Month</code>, and <code>Pool-Only</code> <code>Member Type</code> that appear very infrequently and if a member has that type of contract I would like to count it as a 'Misc'.</p>

<p>I'm also trying to get a 'Total' column that sums up the total amount spent by a given member. </p>

<p>Essentially I would like to transform my previous dataframe to resemble:</p>

<pre><code>df1=
Member Nbr  Member Name-First   Member Name-Last    1_Month  3_Month  4_Month  6_Month  1_Year  Misc    Total    Addr-Formatted Date-Birth           Gender     Status
1           Aboud               Tordon              0        0        0        0        5       0       1662.04  ADDRESS_1      1972-08-01 00:00:00  Male       Active
2           Jean                Manuel              0        0        1        0        2       0       813.86   ADDRESS_2      1984-08-01 00:00:00  Male       In-Active
3           Val                 Adams               0        0        0        0        6       0       1875.18  ADDRESS_3      1934-10-26 00:00:00  Female     Active
5           Michele             Younes              0        0        0        0        4       0       1339.20  ADDRESS_4      1933-06-23 00:00:00  Female     In-Active
7           Adam                Herzburg            0        0        0        0        1       0       335.48   ADDRESS_5      1933-06-23 00:00:00  Male       In-Active
</code></pre>

<p>...</p>

<p>The problem that I'm encountering is that whenever I use <code>groupby</code> I am only able to either sum up the amount, or separately get a count for one specific type of contract, but I'm unable to get it to resemble <code>df1</code>.</p>

<p>You can first <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.map.html" rel="nofollow"><code>map</code></a> values of column <code>Member Type</code> by dict <code>d</code> and then <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.fillna.html" rel="nofollow"><code>fillna</code></a> by value <code>Misc</code>:</p>

<pre><code>d = {'1 Year Membership':'1_Year','1 Month Membership':'1_Month', '3 Month Membership':'3_Month', '4 Month Membership':'4_Month', '6 Month Membership':'6_Month'}
df['Type'] = df['Member Type'].map(d).fillna('Misc')
#print (df)
</code></pre>

<p>Then <code>groupby</code> and aggregate <code>sum</code>:</p>

<pre><code>df0 = df.groupby(['Member Nbr','Member Name-First','Member Name-Last','Addr-Formatted','Date-Birth','Gender','Status'])['Amount'].sum()
#print (df0)
</code></pre>

<p>Add column <code>Type</code> to list of grouping columns and aggreagate <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.GroupBy.size.html" rel="nofollow"><code>size</code></a>, then reshape by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.unstack.html" rel="nofollow"><code>unstack</code></a>:</p>

<pre><code>df1 = df.groupby(['Member Nbr','Member Name-First','Member Name-Last','Addr-Formatted','Date-Birth','Gender','Status', 'Type']).size().unstack(fill_value=0)
#print (df1)
</code></pre>

<p>Last <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.concat.html" rel="nofollow"><code>concat</code></a> both <code>DataFrames</code>:</p>

<pre><code>print (pd.concat([df0, df1], axis=1).reset_index())
   Member Nbr Member Name-First Member Name-Last Addr-Formatted  \
0           1             Aboud           Tordon      ADDRESS_1   
1           2              Jean           Manuel      ADDRESS_2   
2           3               Val            Adams      ADDRESS_3   
3           5           Michele           Younes      ADDRESS_4   
4           7              Adam         Herzburg      ADDRESS_5   

            Date-Birth  Gender     Status   Amount  1_Year  4_Month  
0  1972-08-01 00:00:00    Male     Active  1662.04       5        0  
1  1984-08-01 00:00:00    Male  In-Active   814.11       2        1  
2  1934-10-26 00:00:00  Female     Active  1875.18       6        0  
3  1933-06-23 00:00:00  Female  In-Active  1339.20       4        0  
4  1987-08-30 00:00:00    Male  In-Active   335.48       1        0  
</code></pre>

<p>EDIT:</p>

<p>If some values are missing in column <code>Member Type</code>, is necessary add <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reindex.html" rel="nofollow"><code>reindex</code></a>:</p>

<pre><code>df1 = df.groupby(['Member Nbr','Member Name-First','Member Name-Last','Addr-Formatted','Date-Birth','Gender','Status', 'Type']).size().unstack(fill_value=0).reindex(columns=d.values(), fill_value=0)
#print (df1)

print (pd.concat([df0, df1], axis=1).reset_index())
   Member Nbr Member Name-First Member Name-Last Addr-Formatted  \
0           1             Aboud           Tordon      ADDRESS_1   
1           2              Jean           Manuel      ADDRESS_2   
2           3               Val            Adams      ADDRESS_3   
3           5           Michele           Younes      ADDRESS_4   
4           7              Adam         Herzburg      ADDRESS_5   

            Date-Birth  Gender     Status   Amount  6_Month  3_Month  4_Month  \
0  1972-08-01 00:00:00    Male     Active  1662.04        0        0        0   
1  1984-08-01 00:00:00    Male  In-Active   814.11        0        0        1   
2  1934-10-26 00:00:00  Female     Active  1875.18        0        0        0   
3  1933-06-23 00:00:00  Female  In-Active  1339.20        0        0        0   
4  1987-08-30 00:00:00    Male  In-Active   335.48        0        0        0   

   1_Year  1_Month  
0       5        0  
1       2        0  
2       6        0  
3       4        0  
4       1        0  
</code></pre>

<hr>

<p>Instead second <code>groupby</code> (what is the fastest) is possible use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.pivot_table.html" rel="nofollow"><code>pivot_table</code></a>:</p>

<pre><code>df2 = df.pivot_table(index=['Member Nbr','Member Name-First','Member Name-Last','Addr-Formatted','Date-Birth','Gender','Status'], columns='Type', values='Amount', aggfunc=len, fill_value=0).reindex(columns=d.values(), fill_value=0)
print (pd.concat([df0, df2], axis=1).reset_index())
   Member Nbr Member Name-First Member Name-Last Addr-Formatted  \
0           1             Aboud           Tordon      ADDRESS_1   
1           2              Jean           Manuel      ADDRESS_2   
2           3               Val            Adams      ADDRESS_3   
3           5           Michele           Younes      ADDRESS_4   
4           7              Adam         Herzburg      ADDRESS_5   

            Date-Birth  Gender     Status   Amount  6_Month  3_Month  4_Month  \
0  1972-08-01 00:00:00    Male     Active  1662.04        0        0        0   
1  1984-08-01 00:00:00    Male  In-Active   814.11        0        0        1   
2  1934-10-26 00:00:00  Female     Active  1875.18        0        0        0   
3  1933-06-23 00:00:00  Female  In-Active  1339.20        0        0        0   
4  1987-08-30 00:00:00    Male  In-Active   335.48        0        0        0   

   1_Year  1_Month  
0       5        0  
1       2        0  
2       6        0  
3       4        0  
4       1        0  
</code></pre>

python pandas group-by pivot-table reshape