python filter groups with the last row in groups are negative

user_16891248 0
Viewed 190

I'd like to slice my data frame, based on some group conditions, and get all the groups whose last record has a negative value.

A B C D
1 a a 1
1 a a 2
1 a a 3
2 a a 1
2 a a -1
3 a a -1
3 a a -2
3 a a -3

Suppose this is my data frame, group by column A. I want to get all the groups with a negative last value in column D. output:

A B C D
2 a a 1
2 a a -1
3 a a -1
3 a a -2
3 a a -3

Column B and C are not related to the filter. But I need all the rows in each group, not only the last rows. How to do this?

pandas-groupby pandas dataframe filter python
3 Answers

Use groupby to group by column A and filter over its result to filter which groups you want to keep.

Full functional example:


import pandas as pd

df = pd.DataFrame({"A":[1, 1, 1, 2, 2, 3, 3, 3],
"B": ["a"] *8,
"C": ["a"] *8,
"D": [1, 2, 3, 1, -1, -1, -2 , -3]
    })

df.groupby('A').filter(lambda df_group: df_group['D'].iloc[-1] < 0)

Output:

   A  B  C  D
3  2  a  a  1
4  2  a  a -1
5  3  a  a -1
6  3  a  a -2
7  3  a  a -3
user_32544000

You can pass last method to groupby.transform to get the last items and use lt to get a boolean mask that returns True if the last group value is less than 0 and filter df:

out = df[df.groupby('A')['D'].transform('last').lt(0)]

Output:

   A  B  C  D
3  2  a  a  1
4  2  a  a -1
5  3  a  a -1
6  3  a  a -2
7  3  a  a -3
Admin1

Use:

string = """A B C D
1 a a 1
1 a a 2
1 a a 3
2 a a 1
2 a a -1
3 a a -1
3 a a -2
3 a a -3"""


import numpy as np
data = [x.split() for x in string.split('\n')]
import pandas as pd
df = pd.DataFrame(np.array(data[1:]), columns = data[0])
df['D'] = df['D'].astype(int)

#solution
temp = df.groupby('A')['D'].last()
df[df['A'].isin(temp[temp<0].index)]

Output:

enter image description here

user_72816750
Related