Pandas: Find date of last edit for each element in Dataframe

user_10060643 0
Viewed 76

Hope to express myself correctly in the following as it seems to be a complicated one.

My department is regularly creating daily snapshots at random times for our current project portfolio website. Below, I have filtered the entire table for all snapshot with project_id = 1. Filtering here to make it more understandable as there are many projects. Moreover, I have reduced the number of columns for this example.

df_table

project_id  project_name  region  style   effect   representative lazy  timestamp
1           PullPressure  EU      A-B-C   Pull     Martin         DCA   10/01/20
1           PullPressure  EU      A-B-C   Pull     Martin         DCA   09/05/20
1           PushPressure  EU      A-B-C   Push     Martin               08/20/20
1           PressurePush  EU      A-B-C   Push     Martin               04/06/20
1           PressurePush  US      A-B-C   Push     Johnsson             12/31/19
1           PressurePush  US      A-B-C   Push     Johnsson             10/15/19

My goal is to find out when the last change for any columns of project_id (or in general any key_column) has occurred, i.e. when was each cell for a given id last edited?

My goal is to achieve something like this:

df_table_new:

project_id  project_name region       style        effect       representative  lazy        timestamp
1           08/20/20     04/06/20     10/15/19     09/05/20     04/06/20        09/05/20    10/01/20
1           08/20/20     04/06/20     10/15/19     09/05/20     04/06/20        09/05/20    09/05/20
1           08/20/20     04/06/20     10/15/19     10/15/19     04/06/20        10/15/19    08/20/20
1           10/15/19     04/06/20     10/15/19     10/15/19     04/06/20        10/15/19    04/06/20
1           10/15/19     10/15/19     10/15/19     10/15/19     10/15/19        10/15/19    12/31/19
1           10/15/19     10/15/19     10/15/19     10/15/19     10/15/19        10/15/19    10/15/19

Please let me know in case anything is unclear!

edit: having null values inside the column, results in an NaT error as such:

lazy
09/05/20
09/05/20
NaT
NaT
NaT
NaT

Whereas, instead of NaT the values the fields should refer to the oldest available timestamp in timestamp column, which is 10/15/19.

edit2: Solved with the solution of @jezrael by adding the respective elements to the function. Well appreciated!

pandas python-3.x numpy python
1 Answers

Use GroupBy.transform with GroupBy.last for each column generated by Index.difference:

df['timestamp'] = pd.to_datetime(df['timestamp'], format='%m/%d/%y')

for c in df.columns.difference(['project_id','timestamp']):
    df[c] = df.groupby(['project_id',c], sort=False)['timestamp'].transform('last')
print (df)

   project_id project_name     region      style     effect representative  \
0           1   2020-09-05 2020-04-06 2019-10-15 2020-09-05     2020-04-06   
1           1   2020-09-05 2020-04-06 2019-10-15 2020-09-05     2020-04-06   
2           1   2020-08-20 2020-04-06 2019-10-15 2019-10-15     2020-04-06   
3           1   2019-10-15 2020-04-06 2019-10-15 2019-10-15     2020-04-06   
4           1   2019-10-15 2019-10-15 2019-10-15 2019-10-15     2019-10-15   
5           1   2019-10-15 2019-10-15 2019-10-15 2019-10-15     2019-10-15   

   timestamp  
0 2020-10-01  
1 2020-09-05  
2 2020-08-20  
3 2020-04-06  
4 2019-12-31

If need original format add Series.dt.strftime:

df['timestamp'] = pd.to_datetime(df['timestamp'], format='%m/%d/%y')

for c in df.columns.difference(['project_id','timestamp']):
    df[c] = (df.groupby(['project_id',c], sort=False)['timestamp'].transform('last')
               .dt.strftime('%m/%d/%y'))
print (df)

   project_id project_name    region     style    effect representative  \
0           1     09/05/20  04/06/20  10/15/19  09/05/20       04/06/20   
1           1     09/05/20  04/06/20  10/15/19  09/05/20       04/06/20   
2           1     08/20/20  04/06/20  10/15/19  10/15/19       04/06/20   
3           1     10/15/19  04/06/20  10/15/19  10/15/19       04/06/20   
4           1     10/15/19  10/15/19  10/15/19  10/15/19       10/15/19   
5           1     10/15/19  10/15/19  10/15/19  10/15/19       10/15/19   

   timestamp  
0 2020-10-01  
1 2020-09-05  
2 2020-08-20  
3 2020-04-06  
4 2019-12-31  
5 2019-10-15

EDIT: Add fillna by minimal timestamp:

df['timestamp'] = pd.to_datetime(df['timestamp'], format='%m/%d/%y')
min1 = df['timestamp'].min()

for c in df.columns.difference(['project_id','timestamp']):
    df[c] = df.groupby(['project_id',c], sort=False)['timestamp'].transform('last').fillna(min1)
print (df)
   project_id project_name     region      style     effect representative  \
0           1   2020-09-05 2020-04-06 2019-10-15 2020-09-05     2020-04-06   
1           1   2020-09-05 2020-04-06 2019-10-15 2020-09-05     2020-04-06   
2           1   2020-08-20 2020-04-06 2019-10-15 2019-10-15     2020-04-06   
3           1   2019-10-15 2020-04-06 2019-10-15 2019-10-15     2020-04-06   
4           1   2019-10-15 2019-10-15 2019-10-15 2019-10-15     2019-10-15   
5           1   2019-10-15 2019-10-15 2019-10-15 2019-10-15     2019-10-15   

        lazy  timestamp  
0 2020-09-05 2020-10-01  
1 2020-09-05 2020-09-05  
2 2019-10-15 2020-08-20  
3 2019-10-15 2020-04-06  
4 2019-10-15 2019-12-31  
5 2019-10-15 2019-10-15
user_29010020
Related