Removing last rows of each group based on condition in a pandas dataframe

Question

I have following dataframe:

    name    gender    count
0     A      M         3
1     A      F         2
2     A      Nan       3
3     B      NaN       2
4     C      F         4
5     D      M         5
6     D      Nan       5

I would like to build a resulting dataframe df1 which deletes that last row of group of name attribute if the count of that group is greater than 1. For eq- name A is present 3 times, hence the last row containing A should be removed. B and C are only present once, hence the rows containing them should be retained.

Resulting dataframe df1 should be like this:

     name    gender    count
0     A      M         3
1     A      F         2
2     B      NaN       2
3     C      F         4
4     D      M         5

Please advice.

Zero · Accepted Answer · 2017-10-20 04:12:16Z

2

Use

In [4598]: (df.groupby('name').apply(lambda x: x.iloc[:-1] if len(x)>1 else x)
              .reset_index(drop=True))
Out[4598]:
  name gender  count
0    A      M      3
1    A      F      2
2    B    NaN      2
3    C      F      4
4    D      M      5

answered Oct 20, 2017 at 4:12

Zero

77.4k22 gold badges153 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

cs95 · Accepted Answer · 2017-10-20 04:12:59Z

1

Using groupby + head:

g = df.groupby('name', as_index=False, group_keys=False)\
          .apply(lambda x: x.head(-1) if x.shape[0] > 1 else x)
print(g)
  name gender  count
0    A      M      3
1    A      F      2
3    B    NaN      2
4    C      F      4
5    D      M      5

answered Oct 20, 2017 at 4:12

cs95

406k106 gold badges744 silver badges797 bronze badges

Collectives™ on Stack Overflow

Removing last rows of each group based on condition in a pandas dataframe

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related