Question: I have a pandas DataFrame with columns representing various attributes of data entries, including a timestamp "dh_processamento_rubrica", a unique identifier "inivalid_iderubrica", and an operation type "operacao". The operations include "inclusao" (insertion), "alteracao" (modification), and "exclusao" (deletion). For each unique identifier, there can be multiple operations performed, where "alteracao" modifies existing entries created by "inclusao", and "exclusao" removes entries. The modifications can include changing certain attributes (like "codinccp_dadosrubrica") or updating the identifier itself using "inivalid_nova_validade" (in the example below updating "inivalid_iderubrica" from 2019-11-01 to 2019-01-01) in the second row. "dh_processamento_rubrica" is when the operation was performed). There can only be one entry for each distinct "inivalid_iderubrica", but if it is changed, a new one can later be created with the old value. Here's a simplified version of my DataFrame:    codinccp_dadosrubrica inivalid_iderubrica inivalid_nova_validade operacao dh_processamento_rubrica     11 2019-11-01 NaT inclusao 2020-03-18 23:58:14   11 2019-11-01 2019-01-01 alteracao 2020-05-14 17:27:06   00 2019-11-01 NaT inclusao 2020-06-07 23:46:07   00 2019-01-01 NaT alteracao 2021-07-15 19:57:42   NaN 2019-11-01 NaT exclusao 2021-08-13 15:31:56    Code to generate DataFrame: import pandas as pd data = {'codinccp_dadosrubrica': ['11', '11', '00', '00', None], 'inivalid_iderubrica': [pd.Timestamp('2019-11-01 00:00:00'), pd.Timestamp('2019-11-01 00:00:00'), pd.Timestamp('2019-11-01 00:00:00'), pd.Timestamp('2019-01-01 00:00:00'), pd.Timestamp('2019-11-01 00:00:00')], 'inivalid_nova_validade': [None, pd.Timestamp('2019-01-01 00:00:00'), None, None, None], 'operacao': ['inclusao', 'alteracao', 'inclusao', 'alteracao', 'exclusao'], 'dh_processamento_rubrica': [pd.Timestamp('2020-03-18 23:58:14'), pd.Timestamp('2020-05-14 17:27:06'), pd.Timestamp('2020-06-07 23:46:07'), pd.Timestamp('2021-07-15 19:57:42'), pd.Timestamp('2021-08-13 15:31:56')]} df = pd.DataFrame(data)  As per the data, "inclusao" adds a new entry, "alteracao" modifies existing entries, and "exclusao" removes entries. Each "alteracao" references the original entry it modifies by the inivalid_iderubrica attribute. The modifications can either update existing attributes or change the inivalid_iderubrica. In the example:  A new entry (i) is added with inivalid_iderubrica == 2019-11-01 Entry (i) is updated to now have inivalid_iderubrica == 2019-01-01 A new entry (ii) is added with inivalid_iderubrica == 2019-11-01 Entry (i) is updated to change codinccp_dadosrubrica from 11 to 00 Entry (ii) is deleted  I need to determine the final state of each entry after all the operations have been applied, considering that the modifications are sequential (according to the dates on "dh_processamento_rubrica") and may affect subsequent operations. Final state should something look like:    codinccp_dadosrubrica inivalid_iderubrica inivalid_nova_validade operacao dh_processamento_rubrica     00 2019-01-01 NaT alteracao 2021-07-15 19:57:42    Edit: I have the following solution using iterrows, which works but is unfortunately too slow, as I need to process DataFrame which sometimes may have millions of rows: df = df.sort_values(by=['dh_processamento_rubrica']).reset_index(drop=True) df_alt_exc = df[df['operacao'] != 'inclusao'] df = df[df['operacao'] == 'inclusao'] for _, row in df_alt_exc.iterrows(): to_update = df[(df['inivalid_iderubrica'] == row['inivalid_iderubrica'])] if row['operacao'] == 'alteracao': if to_update.size > 0: if not pd.isna(row['inivalid_nova_validade']): row['inivalid_iderubrica'] = row['inivalid_nova_validade'] index_to_update = to_update.index[0] df.loc[index_to_update] = row if row['operacao'] == 'exclusao': if to_update.size > 0: index_to_update = to_update.index[0] df = df.drop(index_to_update)  I need to know if there's a way of solving this using vectorized operations (or any other approach that would be similarly as efficient).

Answer: This is indeed challenging, since the series of operations is important, which makes it hard to use vectorised operations. An extra challenge is that unique IDs are mutable. Luckily I think it is still possible to write a vectorised solution. The below solution relies on the fact that an alteracao operation that changes the unique ID contains all the up-to-date information about the record, such that we can consider it an inclusao and use the inivalid_nova_validade as the new inivalid_iderubrica. We then group by unique IDs and take the last row which is not exlucsao, relying on the fact that the dataframe is ordered. Finally we search for IDs which are deleted (exclusao) or removed implicitly by having inivalid_iderubrica modified altered, and remove them from the final result. Additional optimisation - converting the text column operacao to a Categorical to save memory. Hopefully this works for your purposes. # sorting, from OP df = df.sort_values(by=['dh_processamento_rubrica']).reset_index(drop=True) # conversion to Categorical dtype - not strictly necessary, but saves memory df["operacao"] = pd.Categorical(df["operacao"]) # check for deleted IDs. deletion means either `exclusao`, or having their `inivalid_iderubrica` replaced deleted = df["operacao"].eq("exclusao") deleted |= (df["operacao"].eq("alteracao")) & (~df["inivalid_nova_validade"].isna()) # if their final status is deleted, we will drop them at the end final_status = deleted.groupby(df["inivalid_iderubrica"]).last() excluded = final_status[final_status].index # Wherever the `alteracao` changes the unique ID, consider it a new `inclusao` operation instead alter_inivalid_mask = (df["operacao"].eq("alteracao")) & (~df["inivalid_nova_validade"].isna()) df.loc[alter_inivalid_mask, "inivalid_iderubrica"] = df.loc[alter_inivalid_mask, "inivalid_nova_validade"] df.loc[alter_inivalid_mask, "inivalid_nova_validade"] = "NaT" df.loc[alter_inivalid_mask, "operacao"] = "inclusao" # get the final state for each unique ID, except when that state is `exclusao` res = df[df["operacao"].ne("exclusao")].groupby("inivalid_iderubrica", dropna=False).last().reset_index() # now to remove those that have final status of excluded res = res[~res["inivalid_iderubrica"].isin(excluded)]  Yielding:  inivalid_iderubrica codinccp_dadosrubrica inivalid_nova_validade operacao dh_processamento_rubrica 0 2019-01-01 00 NaT alteracao 2021-07-15 19:57:42