Remove rows with the help of pandas by conditions

Admin 1
Viewed 68

I have created some data which looks like this:

import pandas as pd
d = {'Time': ['01.10.2019, 09:56:52', '01.10.2019, 09:57:15', '02.10.2019 09:57:23', '02.10.2019 10:02:58', '02.10.2019 13:11:58', '02.10.2019 13:22:55', '03.10.2019, 09:56:52', '03.10.2019, 09:57:15', '04.10.2019 09:57:23', '04.10.2019 10:02:58', '04.10.2019 13:11:58', '04.10.2019 13:22:55']
     ,'Action': ['Opened', 'Closed', 'Opened', 'Closed', 'Opened', 'Closed', 'Opened', 'Closed', 'Opened', 'Closed', 'Opened', 'Closed']
     ,'Name': ['Bayer', 'Bayer', 'ITM', 'ITM', 'ITM' , 'ITM', 'ITM', 'ITM', 'Treso', 'Treso', 'Geco' , 'Geco']}
df = pd.DataFrame(data=d)

     Time                    Action    Name
0    01.10.2019, 09:56:52    Opened    Bayer
1    01.10.2019, 09:57:15    Closed    Bayer
2    02.10.2019, 09:57:23    Opened    ITM
3    02.10.2019, 10:03:58    Closed    ITM
4    02.10.2019, 13:11:58    Opened    ITM
5    02.10.2019, 13:22:55    Closed    ITM
6    03.10.2019, 09:56:52    Opened    ITM
7    03.10.2019, 09:57:15    Closed    ITM
8    04.10.2019, 09:57:23    Opened    Treso
9    04.10.2019, 10:03:58    Closed    Treso
10    04.10.2019, 13:11:58    Opened    Geco
11    04.10.2019, 13:22:55    Closed    Geco

Now i want to remove the data by these conditions:

  • if the time is under 5 mins between opened and closed and the same name, it should be deleted
  • if there is an opened action and the same name and it repeats after the line closed and its on the same day -> it should delete everything which has the same name betweeen the first opened and the last opened. For example line 2 until line 5 should be deleted but not until line 7 because its one day after.

The second Condition For Example: if there is this Input:

     Time                    Action    Name
0    02.10.2019, 09:57:23    Opened    ITM
1    02.10.2019, 10:03:58    Closed    ITM
2    02.10.2019, 13:11:58    Opened    ITM
3    02.10.2019, 13:22:55    Closed    ITM
4    03.10.2019, 09:56:52    Opened    ITM
5    03.10.2019, 09:57:15    Closed    ITM

My Output should be like that:

0    02.10.2019, 13:11:58    Opened    ITM
1    02.10.2019, 13:22:55    Closed    ITM
2    03.10.2019, 09:56:52    Opened    ITM
3    03.10.2019, 09:57:15    Closed    ITM

because it is the next day so from 2nd October to the 3rd and the others are under 5 mins

but if we have this scenario:

0    02.10.2019, 09:57:23    Opened    ITM
1    02.10.2019, 10:03:58    Closed    ITM
2    02.10.2019, 13:11:58    Opened    ITM
3    02.10.2019, 13:22:55    Closed    ITM
4    02.10.2019, 09:56:52    Opened    ITM
5    02.10.2019, 09:57:15    Closed    ITM

all of the data should be deleted except the second line and the third:

2    02.10.2019, 13:11:58    Opened    ITM
3    02.10.2019, 13:22:55    Closed    ITM

My wished output should be like this:

     Time                    Action    Name
0    02.10.2019, 09:57:23    Opened    ITM
3    02.10.2019, 13:22:55    Closed    ITM
4    03.10.2019, 09:56:52    Opened    ITM
5    03.10.2019, 09:57:15    Closed    ITM
6    04.10.2019, 09:57:23    Opened    Treso
7    04.10.2019, 10:03:58    Closed    Treso
8    04.10.2019, 13:11:58    Opened    Geco
9    04.10.2019, 13:22:55    Closed    Geco

What I tried:

df_new = df.assign(group=pd.to_datetime(df["Time"]).diff().dt.seconds.gt(300).cumsum()).groupby(["group", 
                                                                                                    "Time", 
                                                                                                    "Action",
                                                                                                    "Name"]).first()

Could anybody help me?

pandas dataframe csv python
1 Answers

Assuming your logic requires:

  1. eliminate everything that's less than 5 min apart.
  2. from REMAINING values, remove those names that have more than one open in a day:

based on your data:

import pandas as pd
d = {'Time': ['01.10.2019, 09:56:52', '01.10.2019, 09:57:15', '02.10.2019 09:57:23', '02.10.2019 10:02:58', '02.10.2019 13:11:58', '02.10.2019 13:22:55', '03.10.2019, 09:56:52', '03.10.2019, 09:57:15', '04.10.2019 09:57:23', '04.10.2019 10:02:58', '04.10.2019 13:11:58', '04.10.2019 13:22:55']
     ,'Action': ['Opened', 'Closed', 'Opened', 'Closed', 'Opened', 'Closed', 'Opened', 'Closed', 'Opened', 'Closed', 'Opened', 'Closed']
     ,'Name': ['Bayer', 'Bayer', 'ITM', 'ITM', 'ITM' , 'ITM', 'ITM', 'ITM', 'Treso', 'Treso', 'Geco' , 'Geco']}
df = pd.DataFrame(data=d)

first do some conversion and sorting, to make sure the data is in correct order.

## convert time to datetime
df['Time']=pd.to_datetime(df['Time'], dayfirst=True)

# get date
df['Date'] = df['Time'].dt.date

## make sure it's sorted by Name, and time:
df = df.sort_values(['Name', 'Time'])

## get time till next action for same client
df['time_to_next_action'] = \
np.where(((df['Name']==df['Name'].shift(-1)) & (df['Action']=='Opened')), df['Time'].shift(-1) - df['Time'], 0)

## convert time difference to minutes
df['time_to_next_action'] = df['time_to_next_action'].dt.total_seconds()/60.0

# CONDITION 1:

#delete entries under 5 minutes:

df = df[np.where(
    (
    (
        (df['Action']=='Opened') & (df['time_to_next_action']<5)
    )
    |
    (
        (df['Action']=='Closed') & (df['time_to_next_action'].shift(1)<5)
    )
    ), False, True
    )]

### explanation:  
## two possibilities: 
# 1. if action is Open and time to next action is less than 5 minutes delete it 
# 2. if action is 'Close' and time delta from previous action is less than 5 minutes, delete it

### CONDITION 2 edited based on comments:

## keep only first 'Opened'
df_first_open = df[df['Action']=='Opened'].sort_values(['Name', 'Time']).drop_duplicates(subset=['Name', 'Date', 'Action'])

## keep only last 'Closed'
df_last_close = df[df['Action']=='Closed'].sort_values(['Name', 'Time'], ascending = False).drop_duplicates(subset=['Name', 'Date', 'Action'])

## combine and sort the two
df = pd.concat([df_first_open, df_last_close]).sort_values(['Name', 'Time'])

# OPTIONAL: you can drop the extra columns:
df = df.drop(columns=['Date', 'time_to_next_action'])

print(df)

New output:

Time    Action  Name
10  2019-10-04 13:11:58 Opened  Geco
11  2019-10-04 13:22:55 Closed  Geco
2   2019-10-02 09:57:23 Opened  ITM
5   2019-10-02 13:22:55 Closed  ITM
8   2019-10-04 09:57:23 Opened  Treso
9   2019-10-04 10:02:58 Closed  Treso

For record, original condition 2 was:

## get date/name combinations that had only 1 'Open' per day:
df_to_keep = df[df['Action']=='Opened'].groupby(['Name', 'Action', 'Date']).count().reset_index()
df_to_keep = df_to_keep[np.where(df_to_keep['Time']==1, True, False)]

# those are the ones you'll keep in final output:
df = pd.merge(df_to_keep[['Name', 'Date']], df, how='left', on=['Name', 'Date'])
user_95433300
Related