How to get rows by max date and then also add a column with all dates and min date

Question

I am basically trying to do what this question is doing: How to get rows by max date with certain columns?

However, I also want to have two new columns:

1. one called dates (containing all dates for a given I and II combination, best if sorted)
2. one called min_date (containing the minimum date for a given I and II combination).
3. one called days (containing the difference in days between max and min dates)

Following the example from the original question:

   I II III        IV        dates                 min_date   days_diff
0  A  X 2017-01-30 some_data 2017-01-30|2017-01-27 2017-01-27 2
1  A  Y 2017-01-30 some_data 2017-01-30|2017-01-27 2017-01-27 2
2  A  Z 2017-01-30 some_data 2017-01-30|2017-01-27 2017-01-27 2
6  B  X 2017-01-30 some_data 2017-01-30|2017-01-27 2017-01-27 2
7  B  Y 2017-01-30 some_data 2017-01-30|2017-01-27 2017-01-27 2
8  B  Z 2017-01-30 some_data 2017-01-30|2017-01-27 2017-01-27 2

I could do this in a for loop, finding all rows for each unique I and II combination:

data = [
    ('I', 'II', 'III', 'IV'),
    ('A', 'X', '1/30/2017 9:33:00 AM', 'some_data'),
    ('A', 'Y', '1/30/2017 9:33:00 AM', 'some_data'),
    ('A', 'Z', '1/30/2017 9:33:00 AM', 'some_data'),
    ('A', 'X', '1/27/2017 4:53:00 PM', 'some_data'),
    ('A', 'Y', '1/27/2017 4:53:00 PM', 'some_data'),
    ('A', 'Z', '1/27/2017 4:53:00 PM', 'some_data'),
    ('B', 'X', '1/30/2017 9:33:00 AM', 'some_data'),
    ('B', 'Y', '1/30/2017 9:33:00 AM', 'some_data'),
    ('B', 'Z', '1/30/2017 9:33:00 AM', 'some_data'),
    ('B', 'X', '1/27/2017 4:53:00 PM', 'some_data'),
    ('B', 'Y', '1/27/2017 4:53:00 PM', 'some_data'),
    ('B', 'Z', '1/27/2017 4:53:00 PM', 'some_data'),
]

import pandas as pd
df = pd.DataFrame(data[1:], columns=data[0])
df['III'] = pd.to_datetime(df['III'])

# groupby first two columns, then get the maximum value in the third column
idx = df.groupby(['I', 'II'])['III'].transform(max) == df['III']

# use the index to fetch correct rows in dataframe
df_dedup = df[idx]
df_dedup['dates'] = ''
df_dedup['min_date'] = ''
df_dedup['days_diff'] = ''


# now iterate across all rows of df_dedup and find min and all dates
for idx, row in df_dedup.iterrows():
    target_idx = (df['I'] == row['I']) & (df['II'] == row['II'])
    dates = '|'.join(df[target_idx]['III'].astype('str'))
    min_date = min(df[target_idx]['III'])
    days_diff = row['III']-min_date
    (df_dedup['dates'],df_dedup['min_date'],df_dedup['days_diff']) = dates, min_date, days_diff

However, for a large df this is prohibitively slow. I am looking for help on vectorizing this with pandas so it's much faster. any ideas would be greatly appreciated.

The output for this specific example would be:

print(df_dedup)
   I II                 III         IV  \
0  A  X 2017-01-30 09:33:00  some_data   
1  A  Y 2017-01-30 09:33:00  some_data   
2  A  Z 2017-01-30 09:33:00  some_data   
6  B  X 2017-01-30 09:33:00  some_data   
7  B  Y 2017-01-30 09:33:00  some_data   
8  B  Z 2017-01-30 09:33:00  some_data   
                                     dates            min_date       days_diff  
0  2017-01-30 09:33:00|2017-01-27 16:53:00 2017-01-27 16:53:00 2 days 16:40:00  
1  2017-01-30 09:33:00|2017-01-27 16:53:00 2017-01-27 16:53:00 2 days 16:40:00  
2  2017-01-30 09:33:00|2017-01-27 16:53:00 2017-01-27 16:53:00 2 days 16:40:00  
6  2017-01-30 09:33:00|2017-01-27 16:53:00 2017-01-27 16:53:00 2 days 16:40:00  
7  2017-01-30 09:33:00|2017-01-27 16:53:00 2017-01-27 16:53:00 2 days 16:40:00  
8  2017-01-30 09:33:00|2017-01-27 16:53:00 2017-01-27 16:53:00 2 days 16:40:00  

Answer 1

Just follow what you have done in the previous posted , this time we also need to prepare the groupby min

s1,s2=df.groupby('I')['III'].transform('min'),df.groupby('I')['III'].transform('max')
df['min_date']=s1;df['dates']=s1.dt.date.astype(str)+'|'+s2.dt.date.astype(str);df['days_diff']=s2-s1
print(df.loc[df['III']==s2,:])
   I II                 III         IV            min_date  \
0  A  X 2017-01-30 09:33:00  some_data 2017-01-27 16:53:00   
1  A  Y 2017-01-30 09:33:00  some_data 2017-01-27 16:53:00   
2  A  Z 2017-01-30 09:33:00  some_data 2017-01-27 16:53:00   
6  B  X 2017-01-30 09:33:00  some_data 2017-01-27 16:53:00   
7  B  Y 2017-01-30 09:33:00  some_data 2017-01-27 16:53:00   
8  B  Z 2017-01-30 09:33:00  some_data 2017-01-27 16:53:00   
                   dates       days_diff  
0  2017-01-27|2017-01-30 2 days 16:40:00  
1  2017-01-27|2017-01-30 2 days 16:40:00  
2  2017-01-27|2017-01-30 2 days 16:40:00  
6  2017-01-27|2017-01-30 2 days 16:40:00  
7  2017-01-27|2017-01-30 2 days 16:40:00  
8  2017-01-27|2017-01-30 2 days 16:40:00