Nrthugu

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