Alok Shenoy Alok Shenoy - 4 months ago 6
Python Question

Sort within a group and add a columns indicating rows below and above

I have a pandas dataframe that contains something like

+------+--------+-----+-------+
| Team | Gender | Age | Name |
+------+--------+-----+-------+
| A | M | 22 | Sam |
| A | F | 25 | Annie |
| B | M | 33 | Fred |
| B | M | 18 | James |
| A | M | 56 | Alan |
| B | F | 28 | Julie |
| A | M | 33 | Greg |
+------+--------+-----+-------+


What I'm trying to do is first group by
Team
and
Gender
(which I have been able to do so by using:
df.groupby(['Team'], as_index=False)


Is there a way to sort the members of the group based on their age and add extra columns in there which would indicate how many members are above any particular member and how many below?

eg:
For group 'Team A':

+------+--------+-----+-------+---------+---------+---------+---------+
| Team | Gender | Age | Name | M_Above | M_Below | F_Above | F_Below |
+------+--------+-----+-------+---------+---------+---------+---------+
| A | M | 22 | Sam | 0 | 2 | 0 | 1 |
| A | F | 25 | Annie | 1 | 2 | 0 | 0 |
| A | M | 33 | Greg | 1 | 1 | 1 | 0 |
| A | M | 56 | Alan | 2 | 0 | 1 | 0 |
+------+--------+-----+-------+---------+---------+---------+---------+

Answer

You can apply custom function f with groupby by column Team.

In function f for each row first filter above and below values by ix, then drop value and get values desired values by value_counts. Some values are missing, so need reindex and then select by ix:

def f(x):
    for i,d in x.iterrows():
        above = x.ix[:i, 'Gender'].drop(i).value_counts().reindex(['M','F'])
        below = x.ix[i:, 'Gender'].drop(i).value_counts().reindex(['M','F'])
        x.ix[i,'M_Above'] = above.ix['M']
        x.ix[i,'M_Below'] = below.ix['M']
        x.ix[i,'F_Above'] = above.ix['F']
        x.ix[i,'F_Below'] = below.ix['F']
    return x

df1 = df.groupby('Team', sort=False).apply(f).fillna(0)
#cast float to int
df1.ix[:,'M_Above':] = df1.ix[:,'M_Above':].astype(int)
print (df1)
   Age Gender   Name Team  M_Above  M_Below  F_Above  F_Below
0   22      M    Sam    A        0        2        0        1
1   25      F  Annie    A        1        2        0        0
6   33      M   Greg    A        1        1        1        0
4   56      M   Alan    A        2        0        1        0
3   18      M  James    B        0        1        0        1
5   28      F  Julie    B        1        1        0        0
2   33      M   Fred    B        1        0        1        0