Select rows based on condition on rows and columns

user_15842395 0
Viewed 63

Hello I have a pandas dataframe that I want to clean.Here is an example:

IDBILL IDBUYER BILL DATE
001 768787 45 1897-07-24
002 768787 30 1897-07-24
005 786545 45 1897-08-19
008 657676 89 1989-09-23
009 657676 42 1989-09-23
010 657676 18 1989-09-23
012 657676 51 1990-03-10
016 892354 73 1990-03-10
018 892354 48 1765-02-14

I want to delete the highest bills(and keep the lowest when the bills are made on the same day, by the same IDBUYER, and whose bills IDs follow each other. To get this:

IDBILL IDBUYER BILL DATE
002 768787 30 1897-07-24
005 786545 45 1897-08-19
010 657676 18 1989-09-23
012 657676 51 1990-03-10
016 892354 73 1990-03-10
018 892354 48 1765-02-14

Thank you in advance

pandas python
2 Answers

Firstly convert 'DATE' column into datetime dtype by using to_datetime() method:

df['DATE'] = pd.to_datetime(df['DATE'])

Try with groupby() method:

result=df.groupby(['IDBUYER',df['DATE'].dt.day],as_index=False)[['IDBILL','BILL','DATE']].min()

OR

result=df.groupby(['DATE', 'IDBUYER'], sort=False)[['IDBILL','BILL']].min().reset_index()

Output of result:

    IDBUYER     IDBILL  BILL    DATE
0   657676      12      51      1990-03-10
1   657676      8       18      1989-09-23
2   768787      1       30      1897-07-24
3   786545      5       45      1897-08-19
4   892354      16      73      1990-03-10
5   892354      18      48      1765-02-14
user_142898920

You could try this to keep only min values of the lowest entry which is a follow-upof the idbill:

df['follow_up'] = df['IDBILL'].ne(df['IDBILL'].shift()+1).cumsum()

m = df.groupby(['IDBUYER', 'follow_up', df['DATE']])['BILL'].idxmin()
df.loc[sorted(m)]

#    IDBILL  IDBUYER  BILL        DATE  follow_up
# 1       2   768787    30  1897-07-24          1
# 2       5   786545    45  1897-08-19          2
# 5      10   657676    18  1989-09-23          3
# 6      12   657676    51  1990-03-10          4
# 7      16   892354    73  1990-03-10          5
# 8      18   892354    48  1765-02-14          6
user_119717850
Related