Pandas - Groupby Company and drop rows according to criteria based off the Dates of values being out of order

Viewed 107

I have a history data log and want to calculate the number of days between the progress by Company (Timestamp of the early stage must be smaller than the later stage).

Company   Progress      Time
AAA     3. Contract   07/10/2020
AAA     2. Discuss    03/09/2020
AAA     1. Start      02/02/2020
BBB     3. Contract   11/13/2019
BBB     3. Contract   07/01/2019
BBB     1. Start      06/22/2019
BBB     2. Discuss    04/15/2019
CCC     3. Contract   05/19/2020
CCC     2. Discuss    04/08/2020
CCC     2. Discuss    03/12/2020
CCC     1. Start      01/01/2020

Expected outputs:

Progress (1. Start --> 2. Discuss)

Company   Progress      Time
AAA     1. Start      02/02/2020
AAA     2. Discuss    03/09/2020
CCC     1. Start      01/01/2020
CCC     2. Discuss    03/12/2020

Progress (2. Discuss --> 3. Contract)

Company   Progress      Time
AAA     2. Discuss    03/09/2020
AAA     3. Contract   07/10/2020
CCC     2. Discuss    03/12/2020
CCC     3. Contract   05/19/2020

I did try some stupid ways to do the work but still need manualyl filter in excel, below is my coding:

df_stage1_stage2 = df[(df['Progress']=='1. Start')|(df['Progress']=='2. Discuss ')]
pd.pivot_table(df_stage1_stage2 ,index=['Company','Progress'],aggfunc={'Time':min})

Can anyone help with the problem? thanks

3 Answers

Create some masks to filter out the relevant rows. m1 and m2 filter out groups where 1. Start is not the "first" datetime if looking at in reverse order )since your dates are sorted by Company ascending and date descending). You can create more masks if you need to also check if 2. Discuss and 3. Contract are in order, instead of the current logic which is only checking to make sure that 1. is in order. But, with the data you provided that returns the correct output:

m1 = df.groupby('Company')['Progress'].transform('last')
m2 = np.where((m1 == '1. Start'), 'drop', 'keep')
df = df[m2=='drop']
df

intermediate output:

    Company Progress    Time
0   AAA     3. Contract 07/10/2020
1   AAA     2. Discuss  03/09/2020
2   AAA     1. Start    02/02/2020
7   CCC     3. Contract 05/19/2020
8   CCC     2. Discuss  04/08/2020
9   CCC     2. Discuss  03/12/2020
10  CCC     1. Start    01/01/2020

From there, filter as you have indicated by sorting and dropping duplicates based off a subset of the first two columns and keep the 'first' duplicate:

final df1 and df2 output:

df1

df1 = df[df['Progress'] != '3. Contract'] \
.sort_values(['Company', 'Time'], ascending=[True,True]) \
.drop_duplicates(subset=['Company', 'Progress'], keep='first')

df1 output:

    Company Progress    Time
2   AAA     1. Start    02/02/2020
1   AAA     2. Discuss  03/09/2020
10  CCC     1. Start    01/01/2020
9   CCC     2. Discuss  03/12/2020

df2

df2 = df[df['Progress'] != '1. Start'] \
.sort_values(['Company', 'Time'], ascending=[True,True]) \
.drop_duplicates(subset=['Company', 'Progress'], keep='first')

df2 output:

    Company Progress    Time
1   AAA     2. Discuss  03/09/2020
0   AAA     3. Contract 07/10/2020
9   CCC     2. Discuss  03/12/2020
7   CCC     3. Contract 05/19/2020

Something like this could work, assuming an already sorted df:

(full example)

data = {
    'Company':['AAA', 'AAA', 'AAA', 'BBB','BBB','BBB','BBB','CCC','CCC','CCC','CCC',],
    'Progress':['3. Contract', '2. Discuss', '1. Start', '3. Contract', '3. Contract', '2. Discuss', '1. Start', '3. Contract', '2. Discuss', '2. Discuss', '1. Start', ],
    'Time':['07-10-2020','03-09-2020','02-02-2020','11-13-2019','07-01-2019','06-22-2019','04-15-2019','05-19-2020','04-08-2020','03-12-2020','01-01-2020',],
}

df = pd.DataFrame(data)

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

# We want to measure from the first occurrence (last date) if duplicated:
df.drop_duplicates(subset=['Company', 'Progress'], keep='first', inplace=True)

# Except for the rows of 'start', calculate the difference in days 
df['days_delta'] = np.where((df['Progress'] != '1. Start'), df.Time.diff(-1), 0)

Output:

Company Progress    Time    days_delta
0   AAA 3. Contract 2020-07-10  123 days
1   AAA 2. Discuss  2020-03-09  36 days
2   AAA 1. Start    2020-02-02  0 days
3   BBB 3. Contract 2019-11-13  144 days
5   BBB 2. Discuss  2019-06-22  68 days
6   BBB 1. Start    2019-04-15  0 days
7   CCC 3. Contract 2020-05-19  41 days
8   CCC 2. Discuss  2020-04-08  98 days
10  CCC 1. Start    2020-01-01  0 days

If you do not want the 'days' word in output use:

df['days_delta'] = df['days_delta'].dt.days

First Problem

#Coerce Time to Datetime
df['Time']=pd.to_datetime(df['Time'])

#`groupby().nth[]` `to slice the consecutive order`
df2=(df.merge(df.groupby(['Company'])['Time'].nth([-2,-1]))).sort_values(by=['Company','Time'], ascending=[True, True])

#Apply the universal rule for this problem which is, after groupby nth, drop any agroup with duplicates
   df2[~df2.Company.isin(df2[df2.groupby('Company').Progress.transform('nunique')==1].Company.values)]

#Calculate the diff() in Time in each group

df2['diff'] = df2.sort_values(by='Progress').groupby('Company')['Time'].diff().dt.days.fillna(0)#.groupby('Company')['Time'].diff() / np.timedelta64(1, 'D')
#Filter out the groups where start and Discuss Time are in conflict
df2[~df2.Company.isin(df2.loc[df2['diff']<0, 'Company'].unique())]

Company   Progress       Time  diff
1     AAA    1.Start 2020-02-02   0.0
0     AAA  2.Discuss 2020-03-09  36.0
5     CCC    1.Start 2020-01-01   0.0
4     CCC  2.Discuss 2020-03-12  71.0

Second Problem

#Groupbynth to slice right consecutive groups
df2=(df.merge(df.groupby(['Company'])['Time'].nth([0,1]))).sort_values(by=['Company','Time'], ascending=[True, True])

#Drop any groups after grouping that have duplicates


df2[~df2.Company.isin(df2[df2.groupby('Company').Progress.transform('nunique')==1].Company.values)]


  Company    Progress       Time
1     AAA   2.Discuss 2020-03-09
0     AAA  3.Contract 2020-07-10
5     CCC   2.Discuss 2020-04-08
4     CCC  3.Contract 2020-05-19
Related