Pfthb

I am trying to use python and pandas to get the first registry with the type A or B and the first registry with the type C or D and move that to a single row. I want this per Id.

Will be easy for me to demonstrate with this sample case


Id,type,Datetime
1, A, 2018-01-01
1, I, 2018-01-02
1, I, 2018-01-03
1, C, 2018-01-04
2, I, 2018-01-01
2, B, 2018-01-02
2, J, 2018-01-03
2, K, 2018-01-04
2, D, 2018-01-05
2, C, 2018-01-06
3, A, 2018-01-03
3, D, 2018-01-09
4, A, 2018-01-03
4, D, 2018-01-05
4, C, 2018-01-04


I wanted the result of the processing to be


Id, ProcessEvent, ProcessDate, DispatchedEvent, DispatchedDate
1, A, 2018-01-01, C, 2018-01-04
2, B, 2018-01-01, D, 2018-01-05
3, A, 2018-01-03, D, 2018-01-09
4, A, 2018-01-03, C, 2018-01-04


I am currently trying to do this using python and pandas in a Databricks cluster but I am opening to Other ideas I need to to that in files that come up to a total of few TB so it is a large dataset so speed must be in mind and I can't store the whole dataset in memory.

First filter by isin and boolean indexing, remove duplicates by drop_duplicates per column Id, set to index and rename columns names:

df['Datetime'] = pd.to_datetime(df['Datetime'])
df = df.sort_values(['Id','Datetime'])

df1 = (df[df['type'].isin(['A','B'])]
 .drop_duplicates('Id')
 .set_index('Id')
 .rename(columns='type':'ProcessEvent','Datetime':'ProcessDate'))
df2 = (df[df['type'].isin(['C','D'])]
 .drop_duplicates('Id')
 .set_index('Id')
 .rename(columns='type':'DispatchedEvent','Datetime':'DispatchedDate'))

Last concat together:

df = pd.concat([df1, df2], axis=1).reset_index()
print (df)
 Id ProcessEvent ProcessDate DispatchedEvent DispatchedDate
0 1 A 2018-01-01 C 2018-01-04
1 2 B 2018-01-02 D 2018-01-05
2 3 A 2018-01-03 D 2018-01-09
3 4 A 2018-01-03 C 2018-01-04